Your converted statement balance does not reconcile: how to find the row

You rebuilt the closing balance from a converted statement and it came out wrong. The statement prints 24,671.35 and your spreadsheet says 24,471.35. Somewhere in 148 rows there is a number that does not belong, and re-running the conversion in the hope of a different answer is not a method.

There is a method. It has two stages: read the gap, then localise the row. Most discrepancies are resolved in the first stage without opening the statement at all, and the ones that are not fall in about seven comparisons.

Stage one: read the gap

Start by making the difference exact. Subtract your rebuilt closing balance from the printed one and round the result to two decimals. In this case 24,671.35 less 24,471.35 gives 200.00. Your converted file is 200.00 light, which means a credit was understated, a credit row is missing entirely, a debit was overstated, or a debit row was duplicated.

That number is more informative than it looks. Run four tests against it before you touch the statement. Each one produces a candidate, not a conclusion: a gap can be the net effect of two or three errors at once, so every lead below has to be confirmed against the printed page before you change a cell.

• Does the gap equal a transaction amount in the file? Then look first at whether that row is duplicated, or whether a row of the same value is missing. Filter the amount column for the exact figure and look at what you find.
• Does the gap equal exactly twice a transaction amount? Then look first for a sign error on the row of half that value. A debit of 420.00 recorded as a credit shifts the balance by 840.00, because you added it where you should have subtracted it.
• Is the gap divisible by nine? Then two digits inside a single number have probably been swapped. Transposing two digits always produces a multiple of nine: 1,890 read as 1,980 leaves 90, and 45.67 read as 45.76 leaves 0.09. The converse does not hold, so a multiple of nine is a hint rather than a diagnosis.
• Does the gap match a fee, interest line or charge printed in the summary block? Some banks list monthly fees or interest in a summary panel rather than in the transaction table, so the amount is on the page but was never a row.

Our gap of 200.00 fails all four tests. It is not divisible by nine, since 200 divided by 9 is not a whole number. No row in the file holds 200.00, and none holds 100.00, so the sign-flip test comes back empty too. That is the signal to move to stage two.

Stage two: binary search the period

The principle is simple. Add a column to the converted file that rebuilds the running balance: the opening balance in the first row, then each subsequent cell equal to the cell above plus that row's amount. Then compare your rebuilt balance against the statement's own printed balance at a chosen row. If they agree at that row, the amounts above it sum correctly and the error is below. If they disagree, the error is at or above it.

One assumption is buried in that sentence and it is worth stating. Agreement at a checkpoint proves the rows above it net to the right figure, not that each of them individually is right. Two errors above the checkpoint that cancel each other exactly would still let it pass. That combination is uncommon, which is why the search works in practice, and it is also why the rule below is to rebuild the entire chain from the opening balance after every correction rather than trusting the first clean pass.

Each comparison halves the search space. With 148 rows the sequence runs like this.

In words: row 74 matched, so the error was below it; row 111 differed, so the error was between 75 and 111; row 93 matched, narrowing to 94 to 111; row 102 differed, narrowing to 94 to 102; row 98 matched, narrowing to 99 to 102; row 100 differed, narrowing to 99 to 100; row 99 matched, which leaves row 100 as the only candidate. Seven comparisons for 148 rows, and the count grows very slowly: doubling the statement length adds one comparison, not double the work.

Row 100 turns out to be a credit printed on the statement as 1,250.00 and captured as 1,050.00. The 2 in the hundreds column was read as a 0, and 1,250.00 less 1,050.00 is exactly the 200.00 gap. Correct that one cell and the chain closes. That single-digit failure mode is common enough to deserve its own treatment in catching OCR digit errors in statements.

When the statement prints no running balance

Not every layout carries a balance column on every line. When it does not, use page boundaries instead. Many statement layouts print a carried-forward or brought-forward balance at each page break, and those figures are checkpoints in exactly the same sense. Where a layout offers neither a per-line balance nor a page-break figure, build your own checkpoints by totalling the extracted amounts page by page and comparing each page's movement against the statement page in front of you.

1. 1

   Tag each row with its source page

   Add a page column to the converted file so rows can be grouped the way the statement groups them.
2. 2

   Subtotal by page

   Compute the running balance at the end of each page from the extracted amounts.
3. 3

   Compare page by page

   Match each page-end figure against the carried-forward balance printed on the statement, halving the range of pages as you go.
4. 4

   Scan the offending page

   Once one page fails, read its rows against the statement directly. A page holds twenty to forty transactions, which is a short scan.

This approach also localises the two structural failures that a per-row balance check can miss. A row dropped at a page boundary and a row duplicated where consecutive statements overlap both show up as a page whose subtotal is wrong by the value of one transaction. The first of those cases is a genuine omission and needs the page reconverted; the second is the ordinary consequence of two statements sharing a boundary transaction, and it needs a deduplication key rather than a correction.

The usual culprits once you have localised the row

By the time you are looking at a single row, the diagnosis is quick. In rough order of frequency:

• A misread digit in an amount, most often in the hundreds or thousands column where the error is large enough to matter.
• A sign error, where a debit landed in the credit column or a leading minus sign or closing parenthesis was lost.
• A decimal or thousands separator misread, which turns 1.234,56 into 1,234.56 or worse, and is common on statements formatted for a European convention.
• A carried-forward line captured as a transaction, which adds the opening balance to the period a second time and produces a very large gap.
• A wrapped narration where a long description spilled onto a second line and pulled an amount with it, creating one row too many.

Each of these is a one-cell correction once you know which cell. The value of the method is that it gets you to the cell without reading 148 rows.

The search you should not have to run

Seven comparisons is a good answer to a bad question. The better outcome is never being handed an unaudited file in the first place, because the search still costs you a rebuilt balance column, a set of checkpoints keyed off the statement and a quarter of an hour of concentration, and it costs that again on the next statement.

Convert·Into recalculates the running balance line by line on every statement and compares it against the balance printed on the document, so a broken chain is identified and the offending rows are flagged before you ever open the file. The binary search above is what the engine is doing on your behalf, on every row rather than on seven of them. Extraction accuracy is 99.6%, so the flagged rows are few enough to review by hand in the time it would have taken to build the control column.

Use that as your standard when comparing methods. Manual keying, a spreadsheet paste and a generic table extractor with no statement-specific validation rules configured all leave the reconciliation entirely to you, and none of them will tell you unprompted that they dropped a row. A conversion that does not come with an arithmetic verdict is not finished work.

When the conversion is not what is wrong

Sometimes the rebuilt closing balance matches the statement perfectly and the ledger still refuses to reconcile. That is a different problem, and it is worth recognising quickly so you stop auditing the conversion. If opening plus credits minus debits equals the printed closing balance, the amounts in the converted file agree with the bank's own figures in total, which is the strongest automatic check the file can be given and the one that catches a misread digit outright.

Before you move on, spend thirty seconds closing the two gaps that arithmetic alone leaves open. Compare the row count against the statement's stated transaction count, which is what catches a dropped row offset by a duplicate, and glance down the date and description columns, which take no part in the balance and therefore cannot be certified by it. With those confirmed, the conversion is not what is wrong.

The remaining difference then sits between the statement and your books: cheques not yet presented, deposits in transit, a period boundary that does not align with your accounting period, or transactions a bank feed already posted. Working that difference is a bookkeeping exercise rather than a data quality one, and what a bank feed can and cannot prove explains why feed-posted transactions account for so much of it.

Close the file properly

Whatever you corrected, re-run the full check before you move on: opening balance plus credits minus debits equals the printed closing balance, the credit and debit subtotals match the summary block, and the row count matches the statement's stated transaction count. The complete procedure is set out in how to verify a converted bank statement.

Record the result somewhere durable. On an engagement that will be reviewed, a one-line note that the chain closed against a printed closing balance of 24,671.35 is the difference between a file you can defend and a file you merely believe in. It costs a sentence, and it is the artefact a reviewer actually asks for.