Thatâs it - thatâs the difference. The other accounts had a zero opening balance, this one didnât (because I couldnât download transactions from back that far).
Okay, figured it out, thank you!
1 Like
Thank you for the Sheet. Iâve just loaded the sheet and doing my first reconcile statement to Tiller accounting.
Problem: There is a difference between the bank statement balance and the Tiller balance according to Column D & E. The statement Iâm reconciling is from January 2024 to get the year 2024 started off correctly.
-
FYI: This account does not start with a zero balance. This account has existed for years before starting Tiller. Is the problem that a starting balance needs to be added somewhere?
-
The January statement check in the âTroubleshoot Unbalanced Statementâ between the transactions in Tiller and the transactions in the Statement are identical. There is 0 difference in the Troubleshoot section. So it should be reconciled.
Any ideas?
Sounds like you havenât established the 'Statement Start/Open Date" and âTiller Opening Balanceâ (columns I & J). If youâre starting at the beginning of the year, youâll want to set that date to the day before the first statement youâre using starts (if your statement closes on 1/25/24, then the opening date should probably be 12/26/23, the day that statement starts/the day after the closing date of the previous statement). Then you need to figure out the Tiller Opening Balance. This would be the total of al the transactions you have for that account that are older than the opening date. You could filter your transactions so only that account show, and then filter again so only transactions older than the Opening date show. Then select all the totals and look at the âSumâ in the lower right corner of your screen. Once those two are set, hopefully your D & E column match. You shouldnât have to ever change I & J once that happens. Moving forward you would just chance columns D & E each month as the new statements arrive.
Thank you for such a thorough and quick reply jpfieber! I have to ask again. Iâm attaching screenshots. I figured out my problem! It was that I had entered the start date and opening balance in row 1 (thinking I was supposed to overwrite) instead of row 5 to line up with the actual account line!! Iâm sorry you had to go through instructions - but thank you very much for your help!!!
Now itâs right!!
1 Like
Sorry I started asking a question - then I saw my mistake!
1 Like
@jpfieber Joseph, a big THANK YOU for this template. Iâve been using Tiller for about 3 years, but focused on categorizing the transactions to get the a picture on expenses. I had tried the Statement Recon tabs but they didnât work for me. Your approach to keeping the Transactions sheet healthy was just right! Thanks.
For new users to this, I followed Josephâs instructions and started with the simplest of accounts - like Savings. Entered the January 2021 state date, opening balance from 12/31/2020, and entered in the 1/31/2021 Statement Balance - Col E was Green! ⌠Fast forwarded to 4/30/2024 Statement Balance, and still green - meaning 3+ years of transaction pulls in low activity account were perfect! Thatâs all you need to know to have confidence.
So far, the accounts I had problems with were the ones closed in 2022 - that last 1 or 2 transactions were never imported to close the account, even though the BALANCES sheet had zeros. The transactions were missing. (Yes, I took the time to add the missing 2022 transactions, just to get better at using this template and working with manual transactions.)
Since my Transactions have never been âcleanedâ like this, the other issue I discovered was my Transaction History used a different Account Name in Col H vs the âxxxx1234â number in Account Col I on Transactions. For example, a Savings account had 3 different names as I got started in 2021. This template needs all transactions to have the same name over period you are reconciling. I simply filtered the Account Col I value for âxxxx1234â and made them consistent across all periods in Col H Account Name.
Finally, a Credit Card statement that requires refreshing every time I update, was missing the majority of the transactions even thought the balance was updated to current balance. It was an installment purchase with 48 equal payments of $150. The refresh was updating the BALANCES tab, but only 10 transactions were imported.
As Joseph commented above, this template keeps the Transaction sheet healthy. The Helper Data and Troubleshoot UnBalanced Statement sections were very helpful.
1 Like
@jpfieber Does this New Column for ReconcileDate need to be a specific column in Transactions? I inserted it as Col Q, and doesnât seem to see it there.
EDITED: Found it in the Helper Data section on Account Reconciliation! Column AC! Thanks.
2 Likes
Question. The reconciliation isnât âcountingâ correctly on the âtroubleshootâ - Iâve manually added it up from the Tiller transactions, and the calculation for this sheet has different numbers - and therefore itâs not reconciling. I suspect thereâs a formula I need to change and need some help. In the transactions, I have added two columns. Column F is Tags and Column G is Group. Iâm wondering if this messed up a formula. When I use the Troubleshooting to reconcile the numbers, with a start date of 6/11/24, and no prepend or postpend, the transactions actually pulled into the reconciliation troubleshoot section start at 5/31/24 - instead of 6/11/24.
You can check if the columns are correct by opening the âHelper Dataâ section and checking that the columns listed in column S have the correct locations in column U.
1 Like
Hi, when I tried to add this add-on I got an error, but it still added the âCopy of Account Reconciliationâ sheet. It also seemed to have added a blank column to my Transactions sheet maybe? It seems though that the queries are not working properly, as I definitely have transactions in this date range but theyâre not showing up here. Any help? Did something not get added that was supposed to be added (e.g. some column in the Transaction sheet)?
I know on occassion it can hicup and go wrong. Iâd try using File History to go back before you tried the first time and give it another try.
Yeah Iâve tried that already, but I can try again. Is there a way to install this manually, like by adding certain columns since Iâve already got the âAccount Reconciliationâ sheet added?
UPDATE: I think adding the text âReconcile Dateâ to a blank column in my Transactions sheet seems to have fixed it. Will play around with the sheet and update here again if something is still missing.
UPDATE TO THE UPDATE: Oh man, this sheet is amazing and is going to make my life so much easier, thank you thank you thank you @jpfieber!
2 Likes
Hello. I love this sheet! But Iâm pulling my hair out over one account. The f column (calculated balance) always returns the J column (opening balance), even through there are many balance history entries. This happens regardless of what dates I use, or which row I put the account name in. The account name matches exactly in the Accounts sheet, Transactions sheet, and Balance History. The account ID matches exactly in Balance History, Transactions, and Accounts page. Any ideas?
Sorry you are having trouble with just one account. I am not sure what would cause it to be messed up with one account. Just to clarify, your opening tiller balance in the sheet is exactly what your balance history as as the first balance in the sheet? As you use this for other accounts, it seems that you have the way to set it up figured out, but sometimes double checking the little things might show the problem. Other than that, I am not sure what to check or how to best troubleshoot.
I am tagging @jpfieber as he is the template creator and may have some insight into what to check on.
1 Like
Hi @holdingsrogue Faye, Iâm sure this is frustrating! I once had a weird problem getting the template to work (Excel version). I ended up removing the template and starting over fresh with it. The template worked correctly after that. I never figured out what triggered this issue. You might give that a try.
1 Like
It doesnât matter what number I put in the opening balance - that exact same number results in the current balance. None of the related transactions are registering. Thanks for tagging the creator for me.
1 Like
Oh I really hope I can avoid this. It would be a lot of working accounts and balances to re-enter 
Hello @holdingsrogue ,
In fact, it could be really quick.
Change the name of your actual sheet to something else.
Reinstall the account reconciliation sheet in your workbook.
Copy all account names, opening account balances as well as the last statement date and balance for the accounts that work. make sure to copy paste as valve only (copy only).
Then manually enter the information for the last account you have a problem with.
This should take care of it if the problem came from copying a formula in a wrong cell.
Should not be more than 5-10 minutes to troubleshoot.
Let us know how if it worked 
3 Likes
I agree, @holdingsrogue you should try this first. If that doesnât work, then we can probe deeper to try and figure out whatâs going on.
1 Like
I renamed the Account Reconciliation sheet, exited the browser, opened a new browser and incognito window, uninstalled and reinstalled community solutions, and attempted a fresh install. Iâm getting this error:
Any suggestions?