I’ve always wanted to redo the Balance History sheet to track by day (if you run Tiller, you can often get multiple updates by day) and then by account/institution, etc. I then saw a post about tracking balances and it pushed me to see if I can finish it up.
All filters are optional, and the combinable (but if you select all criteria, it’s possible that nothing will return based on the conditons).
Notes
This uses the last balance update of the day.
Caveats
There is a limit of about 10K rows on the sheet, so if you filter for “All Dates” and you have multiple years in your sheet, you might not get everything. For example, the earliest I have in this tracker is Feb 2021, it didn’t include anything prior to that. If you notice that it didn’t include all dates, you can unhide column H and drag the formula down until the end. (IF you figure out how to ARRAY that, please let me know.)
Permissions
Is it ok for others to copy, use, and modify your workflow?
Yes
Hi @yossiea - I copied over to my Foundation Template but is not working. The Transaction Tracker works perfectly when I copied that one over. Any ideas as to what I may be doing wrong? Thanks.
If you go to the right, are there any cells that have a #REF in them?
What do you see on the screen?
Also, on your Balance History sheet, does the data start in column A or B?
OK, I’m trying to get to the bottom of this. I made one change in the filter. Can you try and grab it again?
Also, can you filter on just the account and not class or type? I want to see where the problem happens.
I should probably note this, but right now, the TYPE and CLASS are synced, and the INSTITUTION and ACCOUNT are synced, but they aren’t all in sync yet, since that would require some filter calculations using ANDs etc.
-edit- OK, I see an issue with accounts from the same institution. Let me troubleshoot.
-edit- Fixed. Sorry for this, but please try again.
Thanks for doing some user testing for me.
The issue was that I was de-duping the data but since I was changing some columns, it wasn’t deduping on account, but only on institution.
Darn. The revised version is populating data but I am getting DIV/O under “Average Balance” and getting
“Nothing returned based on filter conditions.” under Balance and VALUE error under “Updated Balance”
Everything else populating correctly.
I just downloaded a new version of Tiller template and the Balance History table has a new column called Balance ID in it which the older template didn’t have. That is most likely causing the error.
If you go back to my shared spreadsheet, you’ll see a new Balance History Tracker with _balanceID appended. Try that one.
YW, one thing I want to do is tie in the class to account, but I would need some sort of AND filter, but it would get too crazy I think to have all the filters mixed up. If someone can think of an easy way to have everything connected, I would love to see an example.
Sorry, you can try again, I just fixed the link to the desktop version. Just click on the drop down arrow on the bottom, by the page name and copy to your sheet.
I’ve installed the balance history tracker so I can use the account balance forecast. Balance history tracker seems to work, but the account balance forecast is completely blank.
