True Balances based on starting balance and downloaded transactions


Write a brief description of what the template does.
What is the goal of your workflow? What problem does it solve?
How did you come up with the idea for your workflow?

To provide a method to get account balances that ensured Transactions and Account Balances were always in sync and to get true balances for a specific date, even if Transactions prior to that date were not yet cleared.

The issue is that Tiller sometimes pulls down Account Balances that include Transactions that are not yet downloaded. This creates issues with cash flow calculations.

I also wanted to get true end-of-month, quarter, year, or whenever Account Balances, based on all Transactions from that month. Given the delay in processing Transactions near the end of each month (for example), the Account Balance and Balance History at the end of the month would not include any “pending” Transactions, so they did not have the correct Balances which included all the month’s Transactions.

You can download the Sheet from here.


Explain how to install the template in their spreadsheet…
Are there dependent sheets?
Where is the template hosted?
What steps are required to add it to a user’s personal spreadsheet?

Simply copy the sheet into your spreadsheet.


Explain what the user needs to do to initially configure the template…

Choose which Accounts you want to calculate the true balance for, enter the Starting Date and Starting Balance on that date for each Account, and it uses the Transaction sheet to calculate Today’s Balance


You can these Starting Balances to calculate the balance of any date you want. Simply replace “TODAY()” with the date you want to calculate the balance for…


Is it ok for others to copy, use, and modify your workflow?



Anything else you’d like people to know?

Let me know if you have any questions or feedback and enjoy!


Optionally, add common questions and answers…

Account and class not showing to select in the dropdown list.

The drop down list is from column G, which is pulled from the Accounts sheet. What is showing up in your column G and what’s in L and M 24, 27, 28?

G is blank, L and M 27 (H ‘Accounts’!H) , 27 ( O ‘Accounts’!O) 28 (M ‘Accounts’!M)

Pretty cool concept, @Cowboy13.

Worked great in my spreadsheet out of the box. Only minor enhancement I’d suggest is changing your “Indirect Range Start” column to be “Indirect Range” and include the trailing $2:$X rather than replicating that in each reference.

Thanks for sharing.

Thanks. I go back and forth on that depending on what I’m referencing in the sheet. You’re right that it would be better to put the entire range for this sheet. I’ll update when I get a chance.

Not sure what’s happening. It found the columns from your Accounts sheet but the query function in Column G doesn’t appear to be pulling any data.

No pressure to make the change. It’s working great as-is. Just an idea.
Thanks again!