Statements: how to use when bank statement lacks total deposits and withdrawals

Get Help Spreadsheet Templates
#1

My bank statement from Capital One contains starting and ending balances, but it does not tally deposits and withdrawals. So I don’t have the info apparently needed for the Statements sheet. If I try to simply put the starting and ending balances into the Statements sheet, I get a “#REF!” error. And the “Tiller Transactions” say “Match” regardless of what I put in the ending balance cell.

I’m hoping that I can still use this wonderful tool. Is there a known workaround?

If not, how do others recommend doing reconciliation? I’m two months into Tiller and there are some important missing transactions, so it appears I do need a reconciliation process.

Thanks in advance for any help!

#2

I hope someone has a better answer than me, but here is a very convoluted way that should work but is a pain in the butt:

  1. On the statements sheet, enter the date, Account and starting balance for your statement.
  2. Filter the transactions sheet by Account so only your Capital One transactions appear.
  3. On the transaction sheet, select the new statement for all of the withdrawal/charge transactions.
  4. When they’ve all been entered, filter the transaction sheet by that statement name.
  5. select the ‘Amounts’ of all the transactions, and in the bottom right note the ‘Sum’ of what you have selected (if it doesn’t show ‘Sum’, you may need to click to change it to sum instead of the other options). Put this number in your Statement sheet for the ‘Withdrawal/Charge’ amount.
  6. Now switch the filter on the transaction sheet to hide that statement name, so the empties are still showing.
  7. Fill in the amounts for the Deposits/Payments
  8. Do the same as above to get the total and add it to your Statements sheet.
  9. See if the ending balance is equal to your statement, and that the two Match appear.
#3

This is confusing to me because you should be able to enter the Starting and Ending balances into the Statements sheet without any errors. In my example/test sheet for this, there is no issue:

Screen Shot 2021-02-10 at 9.00.23 AM
Screen Shot 2021-02-10 at 9.00.23 AM2230×274 52.7 KB

I haven’t applied a statement to any transactions on the Transactions sheet in the Statement column yet though so that’s why mine don’t mach under Tiller Transactions.

So you might want to restore your Statements sheet via the Labs add-on > Manage Solutions (archive it during the process if you’ve already added a bunch of data to it) and then see if you’re still getting that REF error.

As for Capital One not including this information on the statement, I would double check. I feel like they most certainly have to put it on there. It might be labeled differently than what you see on the table, and you might have to download the PDF version or something. It might be “purchases” instead of charges or “credits” instead of payments.

@jpfieber this is a creative solution, but I think it misses the point of reconciliation - if you use the info in the Transactions sheet vs the actual statement for deposits and withdrawals you would likely always have a match, which is a false positive, right?

#4

I agree it’s not ideal, but if you check your transactions against the actual statement and they don’t match, then the ending balance should not match the statement. So even though the statement sheet would always say “match”, it’s the ending balance that would have to be your indicator of whether or not things are right. Again, definitely not working as it should, hopefully a better alternative, but in a pinch I think it works if you know what you’re looking at.
Also, you cannot manually enter a value for the ending balance or it kills the rest of the column since this field is automated (arrayformula?). When I put a number in there, the rest of the column disappears and the header changes to #REF.

#5

@jpfieber :+1: , yes I see now “ending balance” is not supposed to be editable in fact (gray vs green cell). I was misreading it as inputting the deposits/withdrawals entry and starting balance causing errors.

I understand your recommended workaround better now. Thanks for clarifying!

1 Like
#6

Hmm that is odd that there’s no totals for debits and credits…I’ve worked with a ton of institutions and have never seen that - it’s usually a requisite for a bank statement. Did you check the bottom of the statement too? I’ve seen the totals at times be listed at the bottom of the monthly statement, instead of the top. I couldn’t check a capital one checking account, but my credit card statement does have the total for deposits and withdrawals. Is this for a credit card?