Finding the month a bank balance broke: reconciliation as a data integrity check

A small business's books are nine months behind and the ledger's checking balance is off by $5,200.40 at the end of September. To find where that came from, treat each bank statement as a record with three invariants, compute one gap per month, and look at where the gap first goes nonzero and how it moves afterwards. That gives you the broken month, and usually the kind of bug, before you open a single transaction.

I run ZeraBooks, and this framing is what turned bookkeeping cleanup from archaeology into debugging for us. Every figure below is made up.

The three invariants

Every bank or card statement prints an opening balance, a closing balance and the lines in between. For month m:

1. Internal sum: open[m] + sum(lines[m]) == close[m]. If this fails, the statement was read wrong or a line is missing from your copy.
2. Chain: open[m] == close[m-1]. If this fails, a statement is missing between the two, or a balance was misread.
3. Ledger agreement: ledger_balance(end[m]) == close[m]. If this fails, the books disagree with the bank.

The first two test the source data. Only the third tests the books. Run them in that order. A ledger can't agree with a statement you parsed wrong, and chasing a ledger gap caused by a bad parse wastes an afternoon.

Build the gap series

from decimal import Decimal

def gap_series(months, ledger_balance):
    """months: oldest first, each {'end', 'open', 'close', 'lines'} with Decimal amounts."""
    gaps, prev_close = [], None
    for m in months:
        if m["open"] + sum(m["lines"], Decimal("0")) != m["close"]:
            raise ValueError(f"internal sum fails for {m['end']}")
        if prev_close is not None and m["open"] != prev_close:
            raise ValueError(f"chain breaks before {m['end']}")
        gaps.append((m["end"], ledger_balance(m["end"]) - m["close"]))
        prev_close = m["close"]
    return gaps

def deltas(gaps):
    out, prev = [], Decimal("0")
    for end, gap in gaps:
        out.append((end, gap - prev))
        prev = gap
    return out

Illustrative output for that checking account:

Month end	Ledger	Statement close	Gap	Change
Jan 31	18,204.11	18,204.11	0.00	0.00
Feb 28	21,977.50	21,977.50	0.00	0.00
Mar 31	17,402.92	16,882.52	520.40	520.40
Apr 30	19,915.30	18,614.90	1,300.40	780.00
May 31	23,088.75	21,008.35	2,080.40	780.00
Sep 30	28,781.07	23,580.67	5,200.40	780.00 a month

March is the first nonzero gap, so March is where the search starts. If January had been nonzero, the problem would be the opening balance itself, and nothing after it could be trusted until that was fixed.

Read the shape of the change

The deltas column is where the diagnosis lives.

• One nonzero change, then flat: a one-off. A deposit entered twice, a transfer booked as income once, two digits swapped. Look only inside that month.
• The same change every month: a recurring rule is wrong. A steady 780.00 a month smells like a monthly payment that's been posted to the wrong account by an import rule or a habit.
• Changes that wander: usually several unrelated problems, or a statement missing upstream. Go back and rerun the chain check before anything else.

The sign tells you direction. A positive gap means the ledger shows more cash than the bank, so look for money in that was recorded twice or money out that was never recorded. A negative gap is the reverse.

In this example, March's 520.40 was a customer deposit typed in by hand and then imported again from the statement. The 780.00 from April on was a monthly equipment loan payment that a rule was posting against the credit card account instead of checking, so checking never recorded the money leaving. Two fixes and the series goes back to zeros.

Fix oldest first

Each month's opening is the previous month's close. Force September to tie first and the adjustment you made there becomes wrong the moment March is corrected. Work from the oldest broken month forward, and sign off a month only when all three invariants hold, so the bug can't creep forward again.

Doing this without a notebook

You can run the code above against exported statement data. ZeraBooks is an AI-native general ledger, bank statement converter and done-for-you bookkeeping service for small businesses and accounting firms, and its ledger runs the same three checks on every uploaded statement, showing any month that fails on a timeline with the gap beside it. It reads PDF statements directly and has no bank feed. The full order of a cleanup, including transfers, owner draws and a worked nine-month example, is in the bookkeeping cleanup guide. If you'd rather someone else did the work, that's the ZeraBooks bookkeeping services team.

The gap series is cheap to compute and it's the first thing I'd look at on any set of books I didn't keep myself.