Hi @davidmark ,
I agree having a way to compare the transactions against the actual balances provides a useful check to make sure the Transactions and Account Balances sheets are accurate.
I have a sheet where I list all the accounts that i want to reconcile. Not all accounts are automatically tracked by Tiller (Home, Accounts Receivable, etc), so I only list the Tiller tracked ones.
For each account on the sheet, I have a row with:
- Account Name (used for lookups)
- The current balance from the Balance History sheet (using a VLOOKUP with the account name).
- The account balance manually entered prior to the oldest Tiller Transactions for the account in the Transactions Sheet
- Tiller Transactions Total (using a SUMIF() where the Account matches the Account Name)
- A difference column. The current balance (#2 above) minus starting balance (#3) plus Transactions Total (#4). If the accounts are in sync, the difference will be 0.
Sometimes a transactions hits the account balance before it shows up in Transactions. So if the difference is not zero, i might check back in a few days to see if it resolves itself.
You might need to adjust values for positive and negative values to get the income and expenses to work out.
I would share the sheet, but it’s not quite ready to share.
Hopefully, this provides a concept for building the sheet. If there is a lot of interest, I’ll try to create a version that I can share.
Jon