When the imported closing balance disagrees with the statement, the difference itself is the diagnosis. Its size, its sign, and whether it appears at one row or grows through the file each point at a different cause. Work it out before you touch anything, because the obvious fix, a balancing entry, is the one that hides the problem for good.
Rule out the differences that are not errors
Before hunting for a bad row, check that you are comparing like with like. A surprising share of mismatches are two correct numbers measuring different things.
- Dates. The software shows the balance as at today or at the last imported line; the statement shows it at the statement end date. Compare at the same date.
- Opening position. The software’s balance includes whatever was in the account before this import: an opening balance entered at setup, earlier imports, bank feed lines. The statement starts from its own opening balance.
- Available versus ledger balance. Online banking often shows an available balance that includes pending items or an overdraft limit. The statement’s closing balance is the ledger figure.
- Gaps and overlaps between imports. A missing week between two statements, or an overlapping week imported twice, moves the balance without any single file being wrong.
- Currency. A foreign-currency account imported into a base-currency account, or the reverse.
Xero, for example, calculates the statement balance only from the statement lines it holds, imported or fed, rather than reading a balance from your file. A line missing from the import, or brought in twice, shows up there first.
A converter that checks every row against the statement’s own running balance finds this before you import rather than after, and names the row where the ledger first breaks.
First: find where it breaks
Do not start from the closing balance. Start from the opening one and walk forward, adding each transaction, until your running total stops matching the balance printed on that row. The row where they first disagree is the row with the problem; everything after it inherits the gap and tells you nothing new.
- 1Export or copy the imported transactions into a spreadsheet, in statement order.
- 2Put the statement’s opening balance at the top and a running total beside each row: previous total plus this amount.
- 3Add the statement’s printed balance beside it, and a difference column.
- 4Filter the difference column for the first non-zero value. That row, or the one just above it, is where to look.
If the statement has no running balance column, do the same per page using page subtotals, or against the printed credit and debit totals for the period. Splitting the period in half and checking each half also narrows it quickly.
Reading the gap
Once you have the size and shape of the difference, it usually names its own cause. This table is the short version; the sections below explain each.
| The gap is… | Most likely cause | Where to look |
|---|---|---|
| Exactly one transaction | A row was dropped or doubled | Page boundaries, wrapped descriptions |
| Exactly twice one transaction | A sign was inverted | Debit and credit columns, refunds, card statements |
| Divisible by 9 | Two digits transposed | Hand-typed or OCR-read amounts |
| A factor of 10 or 1,000 off a real amount | A digit or separator misread | Scanned pages, European number formats |
| Present from the first row, constant | Wrong opening balance | Setup balance, previous period, available balance |
| Growing page by page | Brought-forward lines counted as transactions | First row of each page |
| Growing month by month | Overlapping imports | Statement periods, bank feed start date |
The gap equals one transaction exactly
A transaction was dropped, or one was imported twice. Search the statement for the amount and check how many times it appears in the imported data. If the gap is in your favour, something is missing on the money-out side or doubled on the money-in side, and the reverse.
- Rows on a page boundary are lost most often, particularly the first row after a page header.
- Multi-line descriptions can be read as two rows or as none.
- A row with an unusual layout, a reversal or a fee bundled into a line, can be skipped by a parser expecting a regular shape.
- A duplicate is more likely where a statement import overlaps a bank feed, or the same file was imported twice.
The gap is exactly twice a transaction
A sign was inverted. A debit of 500 read as a credit moves the balance by 1,000, not by 500, which is why doubling is the signature of a sign error rather than of a duplicate.
This comes from statements with separate debit and credit columns, from a “Debit” column of positive numbers where the sign is implied by the heading, and from refunds on credit card statements, which are often printed with a CR suffix that a conversion drops. Where every row is inverted, the gap is twice the net movement for the period, which is the signature of a whole file imported with the wrong sign convention.
The gap divides by 9, or is a factor of ten
A difference divisible by 9 is the old bookkeeper’s sign of a transposition: 1,254 typed as 1,524 leaves a gap of 270. It points at an amount that was typed or read by OCR rather than copied digitally.
A digit misread leaves a different signature. 220,000.00 read as 22,000.00 leaves a gap of 198,000: not round, but obviously related to a real amount once you look. Dropped zeros are the classic scan error; so is a decimal separator read as a thousands separator, which multiplies or divides by a thousand.
The gap appears at the start and never grows
The opening balance is wrong, and the transactions are fine. Usually the opening balance was taken from the wrong line: the previous period’s closing balance, an available balance rather than a ledger balance, or a figure that includes pending items. In accounting software it can also be the balance entered when the account was set up, which no import corrects.
The gap grows steadily through the file
Something is being counted that should not be. The usual candidate is a brought-forward or carried-forward line treated as a transaction: each page adds the running balance again as though it were a movement. Look at the row after each page break. If it repeats the previous page’s closing figure, that is the double count.
If the gap grows by month rather than by page, look at how the periods were imported. Two statements whose dates overlap by a few days, or a statement imported over a period the bank feed already covered, add the overlap every time.
What to do once you have found it
- 1Fix the specific row rather than adjusting a total. A balancing entry hides the error and leaves the underlying row wrong.
- 2Re-check the whole ledger afterwards. One cause often has siblings: a parser that lost one page-boundary row usually lost several.
- 3Compare against the statement’s printed credit and debit totals as a second, independent check.
- 4If the error came in with an import, delete the imported statement or lines and import a corrected file, rather than editing lines one by one.
- 5Only then reconcile.
A balance that agrees is not a proof of correctness
Two compensating errors agree at the bottom. It is unlikely, but it happens, most often when a transposition in one row is offset by another, or a row dated into the wrong month leaves the period total right and both months wrong. Reconciliation is a strong check, not an absolute one, which is why flagged rows are worth reading even when the total comes out right.