Migrating books between providers: tying opening balances to bank statements, with uncleared items

When books move between providers, the new ledger opens with the old provider's last balance sheet. Each bank and card balance on it must equal the bank's printed statement for that day, plus or minus uncleared items such as an uncashed check. The rule is difference = 0. An unexplained gap means the old books need another look first.

That is a data validation problem with a very clear rule, so it is worth writing as code. I work on ZeraBooks, where we set opening balances this way for businesses moving from Bench and elsewhere. All figures below are fictional.

What data do you need for an opening balance tie-out?

Two sources per account: the closing balance sheet from the old provider, and the printed statement balance from the bank, both on the cutover date.

from dataclasses import dataclass, field
from decimal import Decimal as D

@dataclass
class OpeningAccount:
    name: str
    books: D              # old provider's balance sheet at cutover
    statement: D          # bank's printed balance on the same date
    uncleared: list[tuple[str, D]] = field(default_factory=list)
    # each item: (description, signed amount as it affects the bank)

Use Decimal, not floats. A one-cent mismatch from binary rounding looks exactly like a real one-cent error, and you will lose an afternoon to it.

What is the rule for tying opening balances to a statement?

For a bank account, the statement balance plus the uncleared items must equal the books balance. A check written on December 29 reduces the books immediately but has not reached the bank, so it appears as a negative uncleared item.

def tie_out(a: OpeningAccount) -> D:
    explained = a.statement + sum((amt for _, amt in a.uncleared), D("0"))
    return a.books - explained   # must be zero

How does a clean cutover look in code?

accounts = [
    OpeningAccount("Business checking", D("18420.55"), D("18970.55"),
                   [("Check #1043, written Dec 29", D("-550.00"))]),
    OpeningAccount("Savings", D("25000.00"), D("25000.00")),
    OpeningAccount("Business credit card", D("-2316.40"), D("-2316.40")),
    OpeningAccount("Equipment loan", D("-14800.00"), D("-14800.00")),
]

for a in accounts:
    gap = tie_out(a)
    print(f"{a.name:24} {'OK' if gap == 0 else f'UNEXPLAINED {gap}'}")

All four print OK:

Account	Books, Dec 31	Statement, Dec 31	Uncleared	Gap
Business checking	18,420.55	18,970.55	-550.00 (check #1043)	0.00
Savings	25,000.00	25,000.00		0.00
Business credit card	-2,316.40	-2,316.40		0.00
Equipment loan	-14,800.00	-14,800.00		0.00

Checking is $550.00 higher at the bank, and the uncleared check explains all of it. The opening balance stays at the books figure, $18,420.55, and the check is carried forward as an outstanding item so the first month's reconciliation picks it up when it clears.

The same rule in the ZeraBooks opening books screen: one uncleared check explains the whole gap, so the account shows Agrees.

What does an unexplained gap look like?

Change one input. Suppose the card statement shows -2516.40 and nobody can point to a pending charge.

Business credit card     UNEXPLAINED 200.00

Now stop. Don't plug the $200.00 to an equity account and carry on.

The difference was created in some month the old provider closed, and the cheapest time to find it is now, before twelve new months are layered on top.

Walk back month by month, comparing the ledger balance with each statement's printed close, until you find the first month the gap appears.

Which checks do people skip?

Same date on both sides. A balance sheet dated December 31 compared with a statement that closes on December 27 will never tie, and the "difference" is four days of activity. Match statement periods to the cutover date, or use the bank's daily balance for that date.

Sign conventions. Liabilities come out of most balance sheet exports as positive numbers and out of most statements as negative ones, or the reverse. Normalise once at load time, not inside the comparison.

def normalise(amount: D, is_liability: bool, export_positive: bool) -> D:
    return -amount if (is_liability and export_positive) else amount

What happens after the tie-out?

Only once every account returns zero do the opening balances go into the new ledger. From there, each month is proven the same way: statement opening plus lines equals statement closing, opening equals last month's closing, and the ledger equals the printed close.

The full move off Bench, including what to download before you leave and which months are worth re-checking, is on the ZeraBooks page about moving your books off Bench.

Key takeaways

• Opening balances come from the old provider's last closed balance sheet, dated the same day as the statements you compare.
• The rule per account: books minus (statement plus uncleared items) must equal 0.00.
• Use Decimal and normalise signs once, at load time.
• An unexplained gap gets traced back to the month it first appears. Never plug it to equity.

FAQ

Should the opening balance be the books figure or the bank figure?

The books figure. In the example, checking opens at $18,420.55, and the $550.00 check is carried as an outstanding item until it clears.

What if the old provider's balance sheet and the statement have different dates?

They will never tie. Use statements that close on the cutover date, or the bank's daily balance for that day.

Does this need a bank feed?

No. Everything here runs from the printed statement balances in the PDFs. ZeraBooks has no bank feed either; it works from uploaded statements.

Disclosure: I'm on the ZeraBooks team. Our done-for-you bookkeeping services run this tie-out for new clients from their uploaded statements, from $150 a month in U.S. dollars, priced after a free call.