In relation to Manual Accounts, where is Balance History used?
The only place I see it used is on the Balances tab, which shows the latest balance of each account. If that’s the only place it’s used, why does Balance History store past balances if only the latest balance is used?
The reason I’m asking is because I’m importing transactions for manual accounts and also updating Balance History, but I don’t know if/why the Balance History entries are needed. So, maybe that step can be eliminated…
I know the Accounts sheet gets it’s info from Balance History. In fact, in the Excel version, adding a transaction to the Balance History is currently the only way to add a manual account. I imagine the sheet isn’t easily cleaned since an account may not get updated for months if it isn’t used often, but you still need that transaction in there so the Accounts and Balance sheets know about it.
Tiller’s Net Worth Reports can show changes in account balances over time and draw the information to display these trends from historical balance data kept in the Balance History tab. If you have the data for the balance history for your accounts, it would be great to import these and support the net worth calculations in your sheet.
Hi @don1,
The Balance History is similar to the Transactions sheet as they are both pure data feeds. Balance History to record the latest account balances just as Transactions records each Transaction. Other sheets then use both these basic sheets to make whatever report or view of the data from these sheets.
Balance History stores past balances in case you want to get an account balance report from an historical date.
Thanks for the replies, everyone. I appreciate the help.
For my purpose, which is only accounting for end of year taxes, I don’t see much use for historic account balances but will keep the code in there to update balance history. Actually, the way I’m doing it with Stripe is to just insert one entry dated the end of the month and update it daily for the rest of the month, rather than insert a new entry daily.
Note to self (and anyone else who might be interested). Actually, a total of the actual transaction amounts grouped by account is useful. To do that, add a new sheet and paste the following into A1:
=Query(Transactions!$B:$F, “select F, sum(E) group by F”)
Notes:
Back up your spreadsheet, just in case.
I’m using the Tiller Foundation Template and don’t know if the column letters are the same in other templates. B=date, F=Account, E=Amount
The balances shown will only be accurate if you’ve entered an opening balance transaction for each account. Categorize it as a transfer.
If it still doesn’t match up with the actual balance, they need to be reconciled with your bank statements. To help with that, you can query up to a certain date by adding a where clause to the formula. i.e. To see the balances on 6/30/2022 the formula is:
=Query(Transactions!$B:$F, “select F, sum(E) where (B <= date ‘2022-06-30’) group by F”)
This is definitely an issue. What I’ve done in the past for these cases where there are no balance histories in the new year is to delete all but the last row for that account. That way, you still keep the account, and know the latest balance, but all the prior balances are safely deleted.
You can definitely remove balance history entries if you’re not tracking balance history over time. Be sure to keep the header row in the row 1 spot and make sure all the keywords remain intact (otherwise you won’t get any balance history or partial data fills on them).
If you’re having a performance issue with Google Sheets, I recommend reaching out to our support team so we can investigate as we continue to try and work on improving the performance.