Account Reconciliation - Excel

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.

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:
Screenshot 2022-08-01 065540

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

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’?

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’.

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)
)

Still same issue with #NAME?.

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.

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.

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…

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. :wink:

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…

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.

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.)

1 Like

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.