šŸ† Account Reconciliation - Excel

No problem, glad is was useful!

Joseph,

I want to sincerely apologize for confusion it may cause you. I decided to recopy the Account Reconciliation template and then entered all statement values from January through July. Your original O5 formula is still working ok. Itā€™s indeed powerful and automatic! I love using the template now.

Thank you for sharing this excellent template with us. Iā€™m giving you another vote for this very useful tool.

Cheers,
David

Thanks, glad to hear itā€™s working out of the box!

Does this template populate a date to ReconcileDate column of the Transaction table? If so, how does it work?

This morning I reconciled Amex statement and found out that there was a missing transaction so I manually inserted it to match a total of new charges.

Have you experienced some missing AMEX transactions that Yodlee may fail to pull them?

It is essential to reconcile all Tiller-pulled transactions to match any bank or credit statement transactions! I find Troubleshoot Unbalanced Statement on Account Reconciliation very helpful.

The ReconcileDate column is where you can ā€˜overrideā€™ the date of the transaction in case it appears on a statement where itā€™s date is outside the statement period. It is not automatic, you need to manually add that date. I havenā€™t had any issues with AMEX transactions being missed. I do have occasional AMEX transactions that appear on the ā€˜wrongā€™ statement, so I have to use the ReconcileDate column to suggest when the date should be so the account reconciliation adds up correctly. Glad youā€™re liking the template so far!

Hey @jpfieber!

This is really cool. Youā€™ve stitched together a really great solutionā€¦ and I agree that the TCS Statements solution can be tedious.

Some thoughts and observations:

  • I like how the solution is forward looking without getting bogged down in logging all past statements.
  • I like the grouped debug tools youā€™ve implemented to problem solveā€” since that is an essential part of statement reconciliation workflows.
  • I like your prepend & extend features to quickly expand the date range and identify missed transactions.
  • I really like your ReconcileDate solution for moving stray transactions into the correct statement periodā€” super clever! (I just wish you included a note about adding that column to Transactions in the Setup instructions since my worksheet wasnā€™t functional until it was in place.)
  • The enable/disable dropdown is a great performance optimization. We had considered implementing something similar in our TCS sheets long ago but never quite settled on a solution. (I just wish Excel made it more clear that it is a dropdownā€¦ or offered an in-cell checkbox.)
  • Iā€™m curious if you can leverage Balance History for the balance comparisonsā€¦ or if the erratic timestamps leave too much to chanceā€¦
  • It is AWESOME :star_struck: that you continue to build and support your creations with Excel and Google Sheets versions!!!

It took me a little bit to get my head around this but it is great and I think it will help a lot community members interested in statement reconciliation.

We are excited to award you a $300 gift certificate :trophy: as part of our 2022 Microsoft Excel Builders Challenge . (Thanks for your patience with this review. Had hoped to dig into it closer to posting.)

2 Likes

Woops, youā€™re right, forgot to explain inserting the ReconcileDate column. Just added that. Thanks for the kind words, glad you like the template. It has been working well for me so far, and I much prefer it in my workflow over the options I had previously tried. I thought a bit about the Balance History, but since we canā€™t guarantee a user will have downloaded a balance on the closing day (and even at the appropriate time on that day) I think it would be near impossible to have them match consistently.

I just updated the shared template, changing the way E, F and G are calculated (now use array formulas). If things are already working OK for you, probably no need to update. The version is 1.50 (didnā€™t have a version number on it before).

@jpfieber I am assuming this sheet is designed to have just one row per account and then you updated it each month you get a statement. Is that correct? I started out entering one line per statement for each account. But then there is no way to sort/group the lines to keep them together. Thoughts?

It is one row per account, and each month when you receive your statement you update the Statement End/Close Date, and the Statement End/Close Balance for the appropriate row, and see if the Statement End/Close Balance matches your Tiller Ending Balance.

Perfect! Iā€™m using it now to go back to 2021 and make sure I have all transactions in my transactions table. This is another great use for this tool!

Thanks so much for putting it together!

Regards,

Bob

1 Like

@jpfieber Any idea how you can have this much discrepancy between Actual Ending Balance and what the spreadsheet ending balance is?

The interesting items are:

  • The Troubleshooting tool you provided shows a ā€œDifferenceā€ of $0,00 in cell L26
  • If you subtract the Actual ending balance from what the spreadsheet says is the ending balance, you get $7042.02, which is the value I entered for the opening balance to end 2019

@jpfieber The other interesting thing is that no matter what month I start on, the same thing happens. The first month is fine, but all future months are off, eventhough the troubleshooting tools shows a $0 difference

Your picture shows two rows for the account. You should only have one row per account. The date and amount get updated each month. Itā€™s not a log of statements (thereā€™s a different template for that), itā€™s instead a running check that your statements match your Tiller transactions. Change your first row to ā€œ2/12/20ā€ and ā€œ$7395.17ā€ and I bet it works outā€¦

Understood and it does! I was just trying to use the tool to check all my accounts/transactions since the beginning of 2020.

See below:

Best to start from the beginning and work forward, since any changes you make to past transactions will affect the future numbers. Also, doing it that way means you only need to set the opening balance once.

Added conditional formatting to highlight accounts that havenā€™t been updated within 31 days. The version is now 1.6.

Iā€™ve moved your Account Reconciliation worksheet into my Tiller workbook and updated links. Iā€™m stuck at Setup, step 1. Column B dropdown only lists one of my 36 accounts.

If you expand the Helper Data by clicking on the ā€œ+ā€ above column W you can see column V, which has a list of the accounts it found. The list is grabbed from the range shown in cell U9. If you donā€™t see all your accounts in column V, first check if the range in U9 includes all your accounts. If not, then some logic might have to be adjusted there. You could just manually enter the range there if itā€™s not properly finding it, though changes in your accounts might later require you to manually adjust that again.

My Accounts worksheet is blank. I used the Foundation Template. To see my account names I look in the Balances worksheet. The Account Reconciliation worksheet cell u9 shows Accounts!$H2:$H1 on the face of it but inside the cell says =$S$4&ā€œ!$ā€&$T9&ā€œ2:$ā€&$T9&ROW(OFFSET(INDIRECT($S$4&ā€œ!A1ā€),COUNTA(INDIRECT($S$4&ā€œ!A:Aā€))-1,0)). I certainly donā€™t want to manually add accounts in the future. I figure thereā€™s something simple I need to change.