🏆 Account Reconciliation - Excel

[jpfieber]

Overview

I have been using the “Bank Statement Reconciliation Sheet” (aka “Statements”) for some time to reconcile my transactions against corresponding account statements. It works great, but it takes some time to go through and assign a statement to every transaction. It occurred to me that my statements are always correct, and my transactions are usually correct as well, so most of the time I just need to validate that the closing statement balance is equal to my account balance for that same date. I looked for simpler alternatives and found “Simple Account Reconciliations” by @martha.rudkin. It is a step in the right direction, but still requires touching every transaction. Not finding what I was looking for, I built my own template.

The concept is to compare the statement’s closing date and balance, with the sum of Tiller transactions for that account on the same date. This works great for 80% of my accounts, but I found that transactions in the other 20% don’t always respect the opening and closing dates of the statement. In most cases this would be a transaction with a posting date before or on the closing date of the statement, but it instead appears on the next statement. To accommodate this issue, the template uses “Start/Open” dates and balances for each account, and a new column on the Transactions sheet to hold an alternative date to evaluate these “problem” transactions.

The Account Reconciliation template provides two functions:

1. Track if the transactions in your accounts match those in your account statements
2. Provide tools to troubleshoot transaction balances that don’t match your account statements.

It is designed to work with the Tiller Foundation Template, and references the Transactions and Accounts sheets. The general process is that when a new account statement arrives, you update the “Statement End/Close Date” and “Statement End/Close Balance” for the account with those from the statement. If the “Tiller Ending Balance” matches the “Statement End/Close Balance”, you’re done! If not, you can use the “Troubleshoot Unbalanced Statement” section to find the “problem” transactions and fix them.

Installation

1. You’ll need to add a column to your Transactions sheet called ReconcileDate. To do this, in your Transactions sheet, right-click on the letter above any column to the right of the Amount column and choose “Insert”. A new column will appear to the left of the one you clicked on. Change the column header of the new column from “Column1” to “ReconcileDate”.
2. Download the Account Reconciliation workbook.
3. Follow these instructions to copy the downloaded template into your Excel workbook and to connect the formula references to your local workbook data. (The instructions are for the desktop version of Excel, the web version is not recommended for the installation but does work once the Account Reconciliation sheet is installed.)

At this point your new template should be functional and linked to your local workbook’s data.

Setup

1. In column B, use the drop-down menus to add each account you’d like to reconcile.
2. For your first account, find the oldest statement you have with an “Open/Start” date that comes after your oldest Tiller transaction for that account (we need all transactions in the statement to be present in Tiller to get a clean start). Note you don’t necessarily have to include ALL of your transactions, you could start with your latest statement and move forward from there, but anything older than that will not be ‘reconciled’.
3. Open the “Account Start/Open Info” section by clicking the “+” above column K.
4. Enter the “Statement Start/Open Date” for the account. Most of my statements included this date on it, but if not, it would be the day after the “End/Close” date of your previous statement. Any Tiller transactions for this account that are older than this date will not be evaluated for reconciliation. You should never have to change this again.
5. Enter the “Opening” or “Previous” balance from the statement into the “Tiller Opening Balance” column. You should never have to change this again.
6. Enter the “Statement End/Close Date” from your statement. You’ll change this every time you get a new statement.
7. Enter the “Statement End/Close Balance” from your statement. You’ll change this every time you get a new statement.
8. Check the “Tiller Ending Balance” column. If the number there matches your “Statement End/Close Balance”, it will be colored green, and you are done! If it doesn’t match, it will be colored red and you’ll need to troubleshoot what doesn’t match between your statement and your Tiller transactions.
9. Repeat this for each account. Once you’re done, you can close the “Account Start/Open Info” section by clicking the “-” above column K. You shouldn’t need to change this info again.

Troubleshooting

1. Open the “Troubleshoot Unbalanced Statement” section by clicking the “+” above column R.
2. Select the Account you’re troubleshooting from the dropdown in cell L5.
3. Enter the “Start/Open Date” of the statement you’re troubleshooting in cell L7.
4. Enter the “End/Close Date” of the statement you’re troubleshooting in cell L9.
5. Enter the “Starting Balance” of the statement in cell L18.
6. Enter the “Deposits/Payments” of the statement in cell L20.
7. Enter the “Withdrawals/Charges” of the statement in cell L22.

The “Ending Balance” of the statement will be calculated, be sure it matches the “Ending Balance” of the statement. If they don’t match, there could be a problem with the statement.

The “Actual” column shows the same information based on the actual transactions in Tiller. If one of these numbers doesn’t match between “Statement” and “Actual” the difference between them is shown in cell L26. Potential problems include:

• A transaction(s) from the previous statement is being shown in this statement period
• A transaction(s) from this statement is dated in the next statement period
• A transaction(s) is missing from your feed for this account
• An extra/duplicate transaction(s) exists for this account

Whatever the case, the summed amount of the transaction(s) that are in “error” is the number you see in cell L26.

A list of transactions from the statement period is shown to the right of where you entered the statement info. If a single transaction appears in that list that has the same amount shown in L26, it will be highlighted in red so you can easily see the transaction that needs “fixing”. If you don’t see anything in red, try entering “1” in cell L11 to “Prepend Start/Open Date” (extend the statement period one day earlier). If you still don’t see a highlighted transaction, try entering “1” in cell L13 to “Extend End/Close Date” (extend the statement period one day later).

If a transaction isn’t being highlighted, it could be that it’s more than one transaction, in which case you’ll need to do some manual investigation. In my experience, most of the “problem” transactions were on the closing date of the statement, so that would be the best place to start.

Once you find the problem transaction(s) you need to “fix” them. To do that, go to the Transactions sheet and find the first transaction with a problem. In the new column you entered (“ReconcileDate”), enter a date of when you want the transaction to be evaluated for reconciliation. For example, if a transaction is being counted in the current statement period in Tiller, but doesn’t actually appear on the current account statement, you could set the ReconcileDate to be the day after the closing day of the statement.

As an example, I have a transaction that was posted on 4/27/20 for some cat food from Chewy. The account it is charged on closed on 4/28/20, but the statement doesn’t show that transaction, it instead appeared on the next statement. To fix this problem, I added an alternate “ReconcileDate” of 4/29/20. Now, the formulas on the Account Reconciliation sheet will use that date instead of the normal date when evaluating the account. This leaves the transaction unaltered for all other functions in Tiller, yet still allows you to make adjustments that correspond to account statement periods.

Once you’ve added the new date, look back at the Account Reconciliation sheet and see if your balances are now “Balanced”. If not, look back at the list of transactions and see if any are highlighted red, and repeat the same process until you get the period balanced.

Usage

Once you have all your accounts in and balanced, when the next statement shows up, enter the “End/Close Date” and “End/Close Balance” and if all the transactions are good, you’ll see a green balanced “Tiller Ending Balance”. If not, use the troubleshooting steps above to find the “problem transaction(s)” until it’s balanced.

As a point of reference, I am reconciling 11 accounts and have data for all of them back to the beginning of 2020. I found 18 transactions that needed “fixing”, all of them within two of the accounts: Discover and American Express. For the other 9 accounts, all I had to do was set the “Start/Open” information, and the “Close/End” information for the latest statement and they came up as balanced. It should be noted that I was already using the “Statement” sheet, so I had already cleaned up missing and/or duplicate transactions before using this new template, so I only had transactions showing up in the wrong statement period.

Note

I hope this works as well for others as it has for me so far! Please let me know any issues you run into so I can get them fixed. Also let me know if you have ideas on how to improve it! I’ve also released a Google Sheets version here.

[jpfieber]

It sounds like maybe you didn’t do the “Data / Edit Links…” step and change the source to point at your Tiller template. If that’s the case, it would still be trying to show the data from the shared template, which is blank. You changing the formula is one way to fix it. The same problem would affect E,F and G. They all reference the Transactions sheet, and they find out where to look on that sheet by the results of column U. If column U isn’t showing the correct paths to your Accounts and Transactions columns, we would first need to fix that, as everything else depends on those results. Again, I suspect you need to change the source of the links, which should fix this.

[david]

I did do the “Data/Edit Links…”. I pressed Change Source icon and saw your template name and it asked me to change to my Foundation template. Then your template name showed blank and it inserted the copied template into my Foundation template successfully.

Column E is still blank, but Column F showed the value and Column G #Value! I entered my account name on L5, dates on L6 & L8. I also entered the Statement values on Compare table. I just noticed that Actual Starting Balance value appeared that matched with the Statement Starting Balance. The Actual Deposits/Payments, Withdrawals/Charges, Ending Balance, Difference showed blanks. I checked Tiller Transactions from Statement Period Date (Column O) still showed nothing.

What’s your actual value on the Column O, Cell 5? That’s why I added the Filter string “=FILTER(CHOOSE({1,2,3},Transactions[Date],Transactions[Description],Transactions[Amount]),(Transactions[Account]=$L5)(Transactions[Date]>=$L7)(Transactions[Date]<=$L9))” and the Transaction rows appeared.

[jpfieber]

The formula in O5 is:

=IFERROR(IF(
$A$5=“Disabled”,
“”,
SORT(FILTER(
CHOOSE({1,2,3},INDIRECT($U$10),INDIRECT($U$11),INDIRECT($U$12)),
( INDIRECT($U$13)=$L$5)*
( IF(
ISBLANK(INDIRECT($U$14)),
INDIRECT($U$10)>=$L$7-$L$11,
INDIRECT($U$14)>=$L$7-$L$11
))*
( IF(
ISBLANK(INDIRECT($U$14)),
INDIRECT($U$10)<=$L$9+$L$13,
INDIRECT($U$14)<=$L$9+$L$13
))
),1,1)
),“”)
So it references U10, U11 and U12 to find the locations of the Date, Description and Amount on the Transactions sheet. Technically I could use the Transactions[Date] method for these since every Tiller template should have those columns, but I need to reference the ReconcileDate column, which might not initially exist, and could then break the import of the template. Do you see the proper sheet references in column U? Here’s what mine looks like:

[david]

@jpfieber

=IFERROR(IF(
$A$5=“Disabled”,
“”,
SORT(FILTER(
CHOOSE({1,2,3},INDIRECT($U$10),INDIRECT($U$11),INDIRECT($U$12)),
( INDIRECT($U$13)=$L$5)*
( IF(
ISBLANK(INDIRECT($U$14)),
INDIRECT($U$10)>=$L$7-$L$11,
INDIRECT($U$14)>=$L$7-$L$11
))*
( IF(
ISBLANK(INDIRECT($U$14)),
INDIRECT($U$10)<=$L$9+$L$13,
INDIRECT($U$14)<=$L$9+$L$13
))
),1,1)
),“”)

I copied above and got #NAME? value on Column O?

[jpfieber]

Hmm. Looks like you haven’t created the “ReconcileDate” column on the Transactions sheet, but that isn’t referenced in O5, so it shouldn’t be the cause of that error, but you should still create it. Is A5 set to ‘Enabled’?

[david]

I added a new column as “ReconcileDate” to Transactions Table and it worked fine. I saw Tiller Column E, F, G populated. Column J shows blank. On Compare all Actual numeric values appear ok. Difference total shows $0.00. That’s great! The O5 still shows #NAME?.

Yes, A5 still shows ‘Enabled’.

[jpfieber]

Glad to hear some of it’s starting to work. To figure out O5, try removing the IFERROR and see if it gives you more info about the problem:

=IF(
$A$5=“Disabled”,
“”,
SORT(FILTER(
CHOOSE({1,2,3},INDIRECT($U$10),INDIRECT($U$11),INDIRECT($U$12)),
( INDIRECT($U$13)=$L$5)*
( IF(
ISBLANK(INDIRECT($U$14)),
INDIRECT($U$10)>=$L$7-$L$11,
INDIRECT($U$14)>=$L$7-$L$11
))*
( IF(
ISBLANK(INDIRECT($U$14)),
INDIRECT($U$10)<=$L$9+$L$13,
INDIRECT($U$14)<=$L$9+$L$13
))
),1,1)
)

[david]

@jpfieber

=IF(
$A$5=“Disabled”,
“”,
SORT(FILTER(
CHOOSE({1,2,3},INDIRECT($U$10),INDIRECT($U$11),INDIRECT($U$12)),
( INDIRECT($U$13)=$L$5)*
( IF(
ISBLANK(INDIRECT($U$14)),
INDIRECT($U$10)>=$L$7-$L$11,
INDIRECT($U$14)>=$L$7-$L$11
))*
( IF(
ISBLANK(INDIRECT($U$14)),
INDIRECT($U$10)<=$L$9+$L$13,
INDIRECT($U$14)<=$L$9+$L$13
))
),1,1)
)

Still same issue with #NAME?.

[jpfieber]

I’d maybe delete the quotes from the formula and retype them, they may be pasting in as “smart quotes” (that’s how they look on the screen) which probably don’t work correctly.

[ddc]

Hi. New here. This is what I spent days looking for - a way to reconcile accounts for excel. I followed all the instructions but I must have done something wrong because none of my info is populating in the reconciliation sheet. I re-linked as instructed. I also tried the simple form you mentioned in the first paragraph to see if I can get that to work but it all still comes back to this page. Thanks for any help. D.

[jpfieber]

If either of you @ddc @david would be willing to share your template so I could troubleshoot (I completely understand if you don’t) I can hopefully figure out where things are going wrong. You can DM me to discuss…

[david]

I just made the formula simpler and it worked fine. Now I’m able to see the transactions.

=IF($A$5=“Enabled”,
SORT(FILTER(CHOOSE({1,2,3},Transactions[Date],Transactions[Description],Transactions[Amount]),(Transactions[Account]=$L5)(Transactions[Date]>=$L7)(Transactions[Date]<=$L9)),1,1))

At least it worked for me.

[jpfieber]

@david

=IF($A$5=“Enabled”,
SORT(FILTER(CHOOSE({1,2,3},Transactions[Date],Transactions[Description],Transactions[Amount]),(Transactions[Account]=$L5)(Transactions[Date]>=$L7)(Transactions[Date]<=$L9)),1,1))

It includes the basics, but it won’t evaluate dates in the ReconcileDate column, and the troubleshooting tool won’t use the prepend/append days options. Let me know if you want to troubleshoot the original formulas further…

[david]

I will evaluate yours and mine for a while. I created a simple Bank Statements that I’ve used for two months. It’s amazing that you created the very complicated template. I’ve learned a lot from you!

Thank you for your help to get the template to work. It’s really interesting.

[jpfieber]

No problem, glad is was useful!

[david]

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

[jpfieber]

Thanks, glad to hear it’s working out of the box!

[david]

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.

[jpfieber]

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!