Comparing net value of all transactions for an account against actual bank balances

I’m a 25+ year Quicken user and a <1 year CountAbout user who has recently switched to Tiller. One thing that both Quicken and CountAbout do is show you the net value of all the transactions entered and the institution balance for a given account. In theory, they should always match but sometimes a transaction might be missing, a cheque hasn’t cleared…

In Tiller, what I’m currently doing to try to replicate what Quicken and CountAbout do, is look at the running balance I created on the Account Filter sheet and then compare it to the corresponding balance on the Balance sheet. To do this means toggling back and forth between the two sheets.

Ideally, I’d like to be able to look at a sheet like the Balance sheet and see the running balance for each account next to the balance on the Balance sheet. I’d also like to have a column that shows the difference between the two balances. Doing this would highlight out of balances in virtually real time. I realize the Statements sheet allows one to reconcile an account against an actual statement but I prefer knowing I’m in balance day-to-day and not just when the statement arrives. In Quicken and CountAbout, there really isn’t a need to reconcile against a statement as one can see in almost real time whether one is in balance day to day.

Before I start trying to create such a sheet, just wondering if anyone has already done something along these lines. If not, I’ll probably take a stab and see what I come up with and share, ask for help…:slight_smile:

1 Like

Check out the solutions in Tiller Labs “Statements” and “Statement Details” using that you can apply a statement to past transactions and it gives you a running balance of unreconciled transactions.
There’s also a solution called “Account Projections” but I believe that was user created and I can’t seem to find it now.

Thanks for your comments. Sincerely appreciated.

I am aware of the statements and statement details sheets. They’re are both very good and I do plan to use them. What I’m looking for is something that I can look at at any time without having to wait for a statement. That’s how Quicken and CountAbout work. They show the running balance for your transactions alongside your actual bank balances so you know in almost real time if you’re out of balance.

I’m pretty sure it’s possible to create what I’m looking for but wanted to find out what might already be out there before attempting to do so myself.

I have the same issue. I am exploring using Tiller after 25+ years with Quicken. I would like to do a “reconcile” for each account that ensures the sum of historical transactions equals the current balance. I see a few issues:

  • If I accidentally delete or corrupt some transactions, then I need to be able to quickly isolate the month where the balance on Tiller doesn’t match the balance on my historical statement (perhaps a few years back) and correct the transactions. Thus, after setting up my first bank account, I added a running balance column in the transactions sheet. This shows the balance after each transaction.

  • However, if all transactions from multiple bank accounts and credit cards are in one combined transaction sheet, this is not possible. I think I would need to set up a second sheet with a pivot table that references the transactions sheet. Then I would have the pivot table calculate and display a historical balance for the selected account. Would this work?

Thanks in advance for any suggestions.

After using Tiller for over a year now, I have found that the Statements sheet pretty much keeps everything in sync. In fact, I rarely use the Balances sheet.

As a visual check, I added a column to the right of column L and added the formula =EXACT(K3,L3). It will always be TRUE if any particular statement is in balance and FALSE if not. This way, if I ever accidentally delete a row or change a value, I can at least go back to a specific statement period to see where I have erred.

Although I built a sheet to do running balances for a specific account, I rarely use it. Remember that you can also filter by an account on the Transactions sheet and simply select desired cells to see what the running balances are at any particular date. I can’t recall whether the Statement Details sheet includes a running balance or whether I added it - but it can help. Again, I find the Statements sheet keeps me in sync.

After a year of using tiller, I could never go back to quicken. There’s just so much flexibility in tiller that allows me to do everything that I could do in quicken and more.

The only 2 things that quicken does better from my point of view is splitting transactions and split memorized transactions. It’s not that you can’t do that in tiller, it just takes a little bit more time. If you look at my post and sample spreadsheet for how I handle things like mortgage payments and pay stubs, it might be helpful. Again, it’s not quite as automated as quicken, but suffices.

Happy to answer any questions you might have about the above and also to share any sheets that might be helpful.

1 Like

Thank you for the rapid feedback. This is helpful. A few things that weren’t immediately obvious and other newbies might want to know:

  • The Statements Sheet is not part of the Foundation spreadsheet, and I was unaware of it. You need to get it through the Tiller Community Solutions: I launched from the Extensions drop down menu on Google Sheets, then I found Bank Statements under the Explore tab.

  • The description of how to use the sheet is at: Docs: Bank Statement Reconciliation Sheet

  • The associated Statement Details sheet is great, as it shows all the transactions for that statement and whether balances match.

I’m not sure I understand the value of the extra column for True/False, It’s possible that the latest version of the sheet is updated, as it already shows whether Deposits (L) or Withdrawals (M) match.

These two additional sheets are really essential in my view and offer the needed functionality I was looking for.

I have not started to import and manage brokerage transactions, so I would be interested in any similar sheets that might help with investment management. I’m mainly looking to see total return from a particular investment, including Div & CG, not just value of current shares.

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:

  1. Account Name (used for lookups)
  2. The current balance from the Balance History sheet (using a VLOOKUP with the account name).
  3. The account balance manually entered prior to the oldest Tiller Transactions for the account in the Transactions Sheet
  4. Tiller Transactions Total (using a SUMIF() where the Account matches the Account Name)
  5. 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

2 Likes

Apologies for not explaining the reason I have a True/False column. I split some transactions such as my paycheque into its components: gross income, taxes… The income is + and the deductions are - . So, when Tiller adds up all the credits and debits, the sum of the credits and the sum of the debits will be different than the sum of the credits and the sum of the debits on your bank statement. (If Tiller and the bank are in sync, the net balance will be okay despite the difference in the individual netted sums. Below is a screenshot to help show what I mean.

eg. bank entry = $75 => split into gross income of $100 and deductions of -$25. So, assuming a starting balance of 0, my end balance would be $75. But, the bank would show a deposit of $75 and withdrawal of $0, and Tiller will show deposit of $100 and withdrawal of $25. The end net balance is the same but how one gets there is different. Tiller looks to ensure that its Deposits/Payments is the same as the bank and looks to see if the Withdrawals/Charges are the same as the bank.

I have a lot of accounts. I’m reconciling about 10 per month so over a year, I have 120 rows. While Tiller makes the background green when the sum of the bank deposits at the bank equal the sum of the bank deposits on Tiller, it’s red when they’re not equal. But, if the values in Net Deposits and Net Withdrawals are the same, I know I’m in balance. So, it’s almost like I’m getting a false red. That’s why I do the True/False column. I can very quickly see which statement period might be out of whack. (It just occurred to me that what I am going to do is change the conditional formatting so that if those two values are the same, it shows green and then the True/False column is redundant! Perhaps the owner of this template could do the same so it works for everyone.)

Hope what I’m saying makes sense. Let me know if any more questions.

Thank you @davidmark. One minor newbie issue was that the Statements sheet came with an extra empty column A with just the Community logo in the first cell. So that threw off the formula by a column. The image made that obvious. Otherwise now working.

1 Like