Why your bank statement spreadsheet doesn’t balance

You’ve got a statement into a spreadsheet, added up the money in and money out, and the total doesn’t reach the closing balance printed on the statement. Something is wrong, but in which of three hundred rows?

You don’t have to reread the statement line by line. The amount you’re off by usually points to the kind of mistake, and the running balance column can find the exact row.

Start with the difference

A statement always satisfies:

opening balance + money in − money out = closing balance

Work out the left-hand side from your spreadsheet and subtract the closing balance printed on the statement. The result is what’s missing, and it can tell you a lot.

If none of these fit, there may be more than one mistake. Use the running balance to find the first one, fix it, and check the difference again.

Find the exact row with the running balance

Most statements print a balance after every transaction, or at least at the end of each day. That gives you a check on every row, not just the total: each balance should equal the previous balance, plus that row’s money in, minus its money out.

Say your spreadsheet has Date in column A, Description in B, Debit (money out) in C, Credit (money in) in D and Balance in E, with the first transaction in row 2, and the statement’s opening balance typed into H1. Add a check column F. In F2, compare against the opening balance:

=ROUND($H$1 + D2 - C2 - E2, 2)

In F3, compare against the row above, and fill it down:

=ROUND(E2 + D3 - C3 - E3, 2)

Every row that’s right shows 0. The first row that doesn’t is where the problem is, either on that row or just before it. The number it shows is what went wrong there. After that row, everything reads 0 again, because each balance is checked only against the one above it, so a single mistake shows up exactly once.

A few statements need adjusting first:

• Newest first. If the statement lists the latest transaction at the top, sort the rows oldest first before adding the check column.
• Balance once a day. Some banks print the balance only on the last transaction of each day. Check only the rows that have a balance, against the last row above them that has one, and add up everything in between.
• Several accounts in one PDF. A combined statement for checking and savings has separate opening and closing balances for each account. Check each account on its own.

Credit cards run the other way

On a credit card statement the balance is what you owe. Purchases, fees and interest increase it, and payments and refunds reduce it:

previous balance + purchases + fees + interest − payments − credits = new balance

If a credit card spreadsheet is off by twice its total payments, the payments were treated as charges. That’s the sign mix-up from the table above, only it applies to every payment.

Things that aren’t mistakes

• Pending transactions. A statement only contains posted transactions. If you compare it against a download from online banking, pending items account for the gap.
• Different periods. A statement period rarely lines up with a calendar month. Check the statement’s dates before comparing it with anything else.

Or let the converter check

statement2sheets runs this check on every statement it converts. It checks the transactions against the statement’s opening and closing balances, says by how much they’re off if they don’t agree, and flags rows it was less sure about so you know where to look first. You can then correct the row in place before exporting. It runs in your browser, so the statement is never uploaded. Try it on a statement, or read the ways to get a statement into Excel if you’re starting from scratch.