How the running balance finds a missing transaction

A converted statement that is off by an amount is annoying. A converted statement that is off by an amount and 200 rows long is an afternoon. Where the bank prints a running balance, that column turns the afternoon into a few minutes, and it’s usually thrown away on import.

What the totals tell you, and what they don’t

Say Maple Ridge Landscaping Ltd. has a March chequing statement. The bank printed:

Printed on the statement
Opening balance	4,210.00
Deposits (2)	4,150.00
Withdrawals (5)	3,479.55
Closing balance	4,880.45

You convert the PDF and add up what came out. Opening 4,210.00, plus credits 4,150.00, minus debits 3,383.15 gives 4,976.85. The statement says 4,880.45. You are 96.40 over.

That tells you that something is wrong, and by how much. If the bank printed counts, you also know what kind: four withdrawals read, five printed, so one debit is missing. It doesn’t tell you where.

The running balance tells you where

Many statements print a balance on each row. Some print it only on the last row of each day (more on that below), and some not at all. Recompute your own balance down the rows you read, and put it beside the printed one:

Date	Description	Amount	Balance from your rows	Printed balance
Mar 02	Deposit	+1,850.00	6,060.00	6,060.00	agrees
Mar 04	Cheque 318	−420.00	5,640.00	5,640.00	agrees
Mar 12	Supplier payment	−1,275.00	4,365.00	4,268.60	differs by 96.40
Mar 18	Deposit	+2,300.00	6,665.00	6,568.60	differs by 96.40
Mar 23	Phone	−88.15	6,576.85	6,480.45	differs by 96.40
Mar 29	Rent	−1,600.00	4,976.85	4,880.45	differs by 96.40

The first row that disagrees is Mar 12. Every row before it agrees. So the missing line sits between Mar 04 and Mar 12, and it is a debit of 96.40. Open the PDF at that date range and there it is: a Mar 09 fuel purchase that the conversion dropped.

Notice that every row after Mar 12 is off by the same 96.40. That is the signature of one missing row. If the difference changes further down, there is a second problem, and the next place it changes is where to look.

Doing it in a spreadsheet

1. Put the printed opening balance in the first cell of a new column.
2. In each row below, add the credit and subtract the debit from the cell above.
3. In the next column, subtract the printed balance from your computed one.
4. Filter or conditionally format that column for anything that isn’t zero.

The first non-zero row is your answer. If you work in cents, you avoid a floating-point difference of 0.0000001 showing up as a false alarm; or round the difference to two decimals before comparing.

When the bank prints the balance once a day

Some banks print the balance only on the last transaction of each day. The method still works: compare only on the rows that carry a printed balance. You lose some precision (the missing row could be anywhere in that day), but you still narrow a month to a day.

Watch one trap here. If your conversion kept only the rows that had a balance printed beside them, you silently lost every earlier transaction on those days, and the sheet can still look tidy. A count check catches that: compare the number of rows you read against the number the bank printed, where it prints one.

What the running balance can’t catch

Two rows with their amounts swapped leave every total unchanged. Where the bank prints a balance on every row, the running balance still catches the swap. Where it prints one balance a day and both rows fall on that day, no arithmetic check can see it. The running balance finds rows that are missing, extra or misread in a way that changes the balance; it is not a promise that every row is right. You still review.

Doing it without the spreadsheet

DocsToBooks does this check on every statement where the bank prints a running balance, along with the opening and closing balance and the printed counts, and its certificate names each one. Where the check fails, the review screen shows the first row where the printed balance and the balance from the rows differ, with both figures, and highlights any printed figure it could not read. See how the checks work.