Bank statements

Converted a scanned statement? Prove it is right with three totals (Canada)

Conversion errors hide in plain sight. Check money in, money out and the closing balance against your statement before trusting any converted rows, in C$.

Editorial illustration for Converted a scanned statement? Prove it is right with three totals (Canada)

A converted statement looks right until you add it up. Three totals tell you whether a scanned statement made it into your sheet intact: total money in, total money out, and the closing balance. If all three match the printed statement, the rows are almost certainly correct.

The three-totals test

Most statements print a summary: opening balance, deposits, withdrawals and closing balance. Take those figures as your reference and compute the same three from your converted rows.

CheckFormula ideaCompare with
Money in=SUMIF($C$2:$C$300,">0")Total deposits on the statement
Money out=SUMIF($C$2:$C$300,"<0")Total withdrawals on the statement
Closing balance=opening balance + SUM($C$2:$C$300)Closing balance on the statement

Here the amounts are in column C, with deposits positive and withdrawals negative. Money out will show as a negative figure, so compare it ignoring the sign.

Worked example in Canadian dollars

Say the statement prints an opening balance of C$4,215.30, deposits of C$3,800.00, withdrawals of C$3,947.85 and a closing balance of C$4,067.45. Check: 4,215.30 + 3,800.00 - 3,947.85 = C$4,067.45.

Your converted sheet gives deposits of C$3,800.00, withdrawals of C$3,847.85 and a closing balance of C$4,167.45. Withdrawals are C$100.00 short, and the closing balance is C$100.00 too high, so both agree on one problem: a withdrawal worth C$100.00 is missing, or it was read as C$0.00 or lost on a page break.

How do I find a mistake of C$100.00?

  • Look for a C$100.00 line on the statement and check it exists in the sheet.
  • If it does, check its sign. A withdrawal that lost its minus sign would be off by C$200.00, not C$100.00.
  • If no C$100.00 line exists, a single amount may have lost a digit, such as C$150.00 read as C$50.00.
  • Compare page by page, using each page's closing balance as a checkpoint.

Why three totals instead of one?

Because the errors point in different directions. A lost minus sign moves money out into money in, so deposits rise and withdrawals fall by the same amount. A dropped row moves only one side. A misread digit changes one total and the closing balance. Reading the pattern is much faster than scanning every row.

What if the statement has no summary?

Then use the balances at the top of the first page and the bottom of the last. Opening plus the sum of all amounts must equal closing. If you only have one page, compare page totals. Fees, interest and charges are in your own account agreement, so do not assume anything about them from the rows.

The same idea works page by page. If each page of the statement prints a running balance at the bottom, treat that as a checkpoint and run your totals on that page alone. When a page fails, you only have to compare a dozen or so lines instead of the whole month, and you quickly learn whether the problem is the scan quality or one particular row.

Rounding can cause a one-cent difference when you add many amounts. Use =ROUND(value,2) around your totals so that tiny floating-point differences do not look like errors, and treat anything above a cent as real.

Closing the loop

  1. Compute your three totals from the converted rows.
  2. Compare each with the printed statement and note the difference.
  3. Fix the rows that explain it, and recompute.
  4. Save the original scan, the sheet and your note together.
SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports