Every statement carries its own proof. Opening balance plus money in, minus money out, must equal the closing balance printed on the same page. That one sum catches a dropped row, a duplicated row, a flipped sign and most misread digits, which is why it belongs before any file reaches QuickBooks or Xero.
The sum, with a sign
Take the opening balance, add every deposit, subtract every withdrawal, and call the result the calculated closing balance. Subtract the printed closing balance from it and call that difference V. When V is positive, your rows add up to more than the bank says: something extra went in, or something that should have come out did not. When V is negative, the rows add up to less: a credit was lost or a debit was doubled. Direction narrows the search before size does.
What the size of the difference points to
Read V against the amounts on the statement. A pattern is a lead, not a verdict, so confirm it against the page before changing anything.
| Difference looks like | Usual cause | Where to look |
|---|---|---|
| Exactly one amount from the statement | A row dropped or duplicated; the sign of V and the row type say which | Search for that amount in the file and on the page |
| Exactly twice an amount | A deposit read as a withdrawal, or the reverse | The row whose amount is half of V |
| One non-zero digit, such as 0.50, 6.00 or 80.00 | One digit misread; common pairs are 5 and 6, 3 and 8, 1 and 7, 0 and 8 | The larger amounts, digit by digit |
| Divisible by 9, read in cents | Two adjacent digits swapped, such as 45 for 54; a weak hint alone | Amounts near the first broken row |
| 99 times an amount | A decimal point lost or added: 12.50 read as 1250 | Amounts that look too large or too small |
| A bank fee such as 15.00 or 35.00 | A charge printed in a fees box, not in the transaction list | The summary and fee sections |
| Large, matching no amount | A missing page, or a table cut off at a page break | Page counts and item counts |
| Zero, yet the running balance breaks | A printed running balance misread, not an amount | The next section |
Using the running balance column
If the statement prints a balance after each row, you can do better than a total. Recompute each row from the one above it and mark the first row where the printed and recomputed balances disagree. Three patterns cover most cases. One flagged row means the amount on that row was misread, by the size of the difference. One flagged row right after a gap means a row between it and the line above was dropped, and the difference is its amount. Two consecutive flagged rows with the same difference, while the headline sum still ties, mean a printed balance was misread: the bad figure throws off its own row and then the next by the same amount in the opposite direction. Statements printed newest first must be put in date order before any of this works.
Step by step: tie out one statement
- Read the printed figures. Copy the opening balance, the closing balance and any printed totals or item counts for deposits and withdrawals from the statement’s summary box, not from the first and last rows.
- Total your rows. Sum the deposit rows and the withdrawal rows separately, and count each group.
- Compare each side to the summary. Matching totals and counts on both sides matter more than a matching net, because they show which side is wrong.
- Compute the difference. Opening + deposits − withdrawals, minus the printed closing balance. Zero means the sum ties.
- Classify any difference. Use the table above, find the matching row, and fix it at the source, not in the output. Then recompute.
- Keep the proof. File the figures and date with the statement.
What can go wrong
- Circular figures. If a statement prints no summary balances, a tool may have to infer them from the first and last running balances, and the sum then proves less. Check where the opening and closing figures came from.
- Pennies treated as noise. Every statement amount is exact, so anything above 0.00 deserves a look. A one-cent gap is usually a misread cents digit, often 5 against 6, so check the cents of the larger amounts before you accept it.
- Offsetting mistakes. Two errors of equal size and opposite direction cancel, and the sum ties. Matching totals and item counts against the summary box catches them.
- Combined statements. Where one PDF holds checking and savings, each account has its own opening and closing balance. Tie them separately.
- The wrong closing figure. Use the ending or ledger balance for the statement date. An available balance can reflect holds and will not match the posted transactions.
- Carry-forward lines. A “balance carried forward” line at a page break is not a transaction. Counting it moves the total.
Where SheetParser fits
SheetParser shows opening, deposits, withdrawals, closing and the difference above the rows, marks the first row whose running balance does not chain, and lists likely causes such as a flipped sign, a row equal to the difference, a swapped-digit pattern or a common fee amount. Editing a cell recalculates the check, and the Excel export carries a Reconciliation Audit sheet with the same figures. Try it on one statement in the bank statement to Excel tool; 1 file a day is free up to 10 pages, and paid plans open files up to 150 pages. To check that months join up as well, see the continuity guide.