DEV Community: Damin Mutti

Detecting a missing bank statement from balance carry-forward (not from dates)

Damin Mutti — Wed, 04 Nov 2026 21:49:34 +0000

To find a missing bank statement, sort each account's statements by period and compare every statement's closing balance with the next statement's opening balance. They should be identical to the cent. When they aren't, something sits between them: a missing statement, a missing page, or a statement from a different account mixed in. The difference tells you how much activity you haven't seen.

Checking dates alone misses things. Balance carry-forward doesn't, because the bank has already done the arithmetic for you.

Why dates aren't enough

The obvious check is "do I have a statement for every month?" It fails in at least four ways:

Statement cycles often don't follow calendar months. A cycle of the 15th to the 14th means "October" and "November" are both partly in one statement.
Some banks issue an extra statement when an account changes (a product switch, a new account number, a fraud reissue). The months look complete but a short statement is missing in the middle.
Two statements for different accounts at the same bank look like one complete series if you only read dates.
A statement can be present but truncated: the first page is there, the transaction pages aren't.

Balances catch all of these.

The data

One row per statement, as printed in each statement's summary:

from dataclasses import dataclass
from datetime import date
from decimal import Decimal as D

@dataclass
class Stmt:
    account: str        # last 4 is usually enough
    start: date
    end: date
    opening: D
    closing: D
    source: str         # file name, so you can point to the PDF

statements = [
    Stmt("2208", date(2026, 3, 15), date(2026, 4, 14), D("8102.44"), D("9930.18"), "mar.pdf"),
    Stmt("2208", date(2026, 4, 15), date(2026, 5, 14), D("9930.18"), D("7415.60"), "apr.pdf"),
    Stmt("2208", date(2026, 6, 15), date(2026, 7, 14), D("11288.02"), D("10640.95"), "jun.pdf"),
    Stmt("2208", date(2026, 7, 15), date(2026, 8, 14), D("10640.95"), D("12004.31"), "jul.pdf"),
    Stmt("5519", date(2026, 7, 1),  date(2026, 7, 31), D("2250.00"), D("1980.40"), "sav-jul.pdf"),
]

The check

from itertools import groupby

def chain(statements):
    findings = []
    key = lambda s: s.account
    for account, group in groupby(sorted(statements, key=lambda s: (s.account, s.start)), key=key):
        prev = None
        for s in group:
            if prev:
                gap_days = (s.start - prev.end).days
                if s.start <= prev.end:
                    findings.append((account, "overlap", prev.source, s.source, None))
                elif s.opening != prev.closing:
                    findings.append((account, "balance gap", prev.source, s.source, s.opening - prev.closing))
                elif gap_days > 1:
                    findings.append((account, "date gap, balances agree", prev.source, s.source, D("0")))
            prev = s
    return findings

for f in chain(statements):
    print(f)

Output:

('2208', 'balance gap', 'apr.pdf', 'jun.pdf', Decimal('3872.42'))

April's statement closes at 7,415.60. June's opens at 11,288.02. The May 15 to June 14 statement is missing, and $3,872.42 of net activity is in it. That is a specific, short request to make: "please send the statement for account 2208 ending June 14."

Account 5519 has only one statement, so it can't fail a chain check. That's worth flagging separately: a single statement proves nothing about completeness.

The case where dates have a gap but balances agree

The "date gap, balances agree" branch is there on purpose. It usually means the account had no activity, and some banks don't issue a statement for an empty cycle. That's fine. But it can also mean the missing statement had deposits and withdrawals that net to exactly zero. Rare, but in litigation work rare is the point. Flag it, don't fail it.

Pair it with the per-statement check

The chain check assumes each statement is internally right. Prove that first: opening plus the statement's own transactions should equal its closing. If a statement fails that, its closing balance is still printed by the bank, so the chain check still works, but the transaction rows you extracted are incomplete.

Five statements uploaded to ZeraBooks Convert, each proven to the cent on its own. Setting March's closing beside May's opening is what shows April is missing.

So the order is:

Each statement ties to itself (rows add up from opening to closing).
Each account's statements chain (closing equals the next opening).
Request anything missing, and rerun 1 and 2 on what arrives.
Only then, start analysis, with the source file kept on every row.

Where this matters most

Forensic accountants, divorce teams and lenders all receive statement productions from someone else, and an incomplete production is the norm, not the exception. The ZeraBooks write-up on forensic bank statement analysis covers the columns reviewers want once the set is complete.

It matters in ordinary bookkeeping too. Disclosure: I work on ZeraBooks, and its general ledger software reconciles every month against the uploaded statement, so a month that doesn't carry forward shows up as a month that doesn't tie, instead of a quiet hole in the books.

One bank CSV, three importers: the format QuickBooks Online, Xero and Wave will all accept

Damin Mutti — Wed, 04 Nov 2026 21:49:06 +0000

The safest bank transaction CSV for QuickBooks Online, Xero and Wave has three columns, Date, Description and Amount, with one header row, one transaction per row, a single signed amount (money in positive, money out negative), no currency symbols, no thousands separators, and dates in one consistent format that matches the company's region. Each app accepts more than that, but all three accept that.

If you write converters, scripts or bookkeeping tools, it pays to know where each importer is strict, because the error messages rarely say what is actually wrong.

Where each importer differs

Based on each vendor's help pages as of November 2026. Importers change, so re-check the current help page before you rely on an edge case.

	QuickBooks Online	Xero	Wave
Amount layout	3 columns (one signed Amount) or 4 columns (separate Credit and Debit)	One signed Amount column	One Amount column, or separate deposit and withdrawal columns
Extra columns	Ignored if you don't map them	Payee, Reference, Cheque Number are optional named columns	You pick which column is which during upload
Date	One date column; the format must be consistent	Must match the organisation's regional format	Only one date column; two date columns cause errors
Things that trip it	Currency symbols, commas in numbers, blank rows	Column order and header names	Symbols other than the currency sign, descriptions that are only numbers

Wave's own help suggests that if a split deposit and withdrawal file won't load, move the amounts into one column. That's the main reason the single signed column is the common denominator.

A normalizer

Real statement extractions come in every shape: debit and credit columns, (45.00) for negatives, 45.00 CR, $1,204.18, dates like Oct 3 with the year only in the header. Normalize once, then write whichever file you need.

import csv
from datetime import datetime
from decimal import Decimal

def money(raw: str) -> Decimal | None:
    s = (raw or "").strip().upper().replace("$", "").replace(",", "")
    if not s:
        return None
    sign = -1 if (s.startswith("(") or s.endswith("-") or s.endswith("DR")) else 1
    s = s.strip("()-").replace("CR", "").replace("DR", "").strip()
    return sign * Decimal(s)

def normalize(rows, date_format: str, debit_col=None, credit_col=None, amount_col=None):
    out = []
    for r in rows:
        if amount_col:
            amount = money(r[amount_col])
        else:
            debit = money(r.get(debit_col, "")) or Decimal("0")
            credit = money(r.get(credit_col, "")) or Decimal("0")
            amount = credit - abs(debit)
        if amount is None or amount == 0:
            continue
        d = datetime.strptime(r["date"].strip(), date_format).date()
        desc = " ".join(r["description"].split())       # collapse wrapped lines
        if desc.isdigit():
            desc = f"Ref {desc}"                         # Wave rejects number-only descriptions
        out.append({"Date": d, "Description": desc[:200], "Amount": amount})
    return out

def write_csv(path, txns, us_dates=True):
    fmt = "%m/%d/%Y" if us_dates else "%d/%m/%Y"
    with open(path, "w", newline="") as f:
        w = csv.writer(f)
        w.writerow(["Date", "Description", "Amount"])
        for t in txns:
            w.writerow([t["Date"].strftime(fmt), t["Description"], f'{t["Amount"]:.2f}'])

f'{amount:.2f}' on a Decimal gives -1204.18: no comma, no symbol, always two decimals. That one line removes the most common import failure.

Card statements need a sign flip

On a credit card statement, a purchase increases what you owe. Some extractors write it as positive. Accounting importers expect a card purchase to come in as money out, the same as a debit from checking. Flip the sign for card accounts before writing, and flip payments to the card the other way. Do it by account type, not by guessing from the description.

Check before you import, not after

An importer will happily accept a file that is missing half a page of transactions. It can't know. You can:

def ties(txns, opening: Decimal, closing: Decimal) -> bool:
    return opening + sum(t["Amount"] for t in txns) == closing

If that returns False, don't import. Find the gap first. Cleaning up a bad import in QuickBooks or Xero (undoing matches, deleting duplicates) takes much longer than fixing the CSV.

What about .qbo, OFX and IIF?

Some importers prefer OFX or QBO files when the bank provides them, and it's worth using those if your bank exports them directly. If you are starting from a PDF, CSV is the honest choice: it's what you can build and verify yourself, and all three apps accept it.

The bookkeeper version of this problem

For one client, a script is fine. A bookkeeper with 30 clients is running this every month for every account, which is where the time goes. Disclosure: I work on ZeraBooks. The ZeraBooks Converter writes QuickBooks-ready and Xero-ready CSV from PDF statements, checked against the statement balances first. For bookkeepers who would rather skip the import step entirely, there is a write-up on bookkeeping automation for bookkeepers that covers keeping every client's books in one place.

ZeraBooks Convert's download step: pick the importer's layout (QuickBooks-ready, Xero-ready, Sage-ready) and the file type, after the statement is proven.

Build a cost-per-mile report for a truck from bank and card CSVs

Damin Mutti — Wed, 04 Nov 2026 21:48:41 +0000

Cost per mile is total operating cost for a period divided by the miles driven in that period. To get it from bank data you need four things: every transaction from the business bank account, card and fuel card for the month, a rule that puts each one in a cost bucket, the interest part of the truck payment (not the whole payment), and the month's miles from the ELD. Divide each bucket by the miles, add them up, and you have the number that tells a driver which loads lose money.

Here's a working version with pandas, and the mistakes that make most homemade versions wrong.

The inputs

Three CSVs for October 2026 for a fictional one-truck owner-operator, Pinecrest Hauling, plus one number from the ELD:

import pandas as pd
from decimal import Decimal

MILES = 10_400   # ELD total for October 2026

frames = []
for path, source in [("checking.csv", "bank"), ("card.csv", "card"), ("fuelcard.csv", "fuel_card")]:
    df = pd.read_csv(path, dtype=str)          # keep amounts as text until we convert them
    df["source"] = source
    frames.append(df)
tx = pd.concat(frames, ignore_index=True)
tx["amount"] = tx["amount"].map(lambda s: Decimal(s.replace(",", "")))

Read amounts as strings and convert to Decimal. Pandas will happily turn 1,412.66 into NaN or a float, and either one ruins a report you're going to make decisions from.

Rule-based buckets

Merchant text on truck statements is repetitive, which makes plain rules work well. Order matters: the first match wins.

RULES = [
    (r"PILOT|FLYING J|LOVE'?S|TA |PETRO|SHELL|CHEVRON", "Fuel"),
    (r"PROGRESSIVE|GREAT WEST|OOIDA INS|NATIONAL INTERSTATE", "Insurance"),
    (r"TIRE|SPEEDCO|TRUCK SERVICE|FREIGHTLINER|PETERBILT|KENWORTH", "Maintenance and tires"),
    (r"E-ZPASS|PIKEPASS|TOLL|BESTPASS", "Tolls"),
    (r"IRS 2290|IFTA|IRP|DMV|PERMIT", "Permits, plates and 2290"),
    (r"VERIZON|T-MOBILE|MOTIVE|SAMSARA|KEEPTRUCKIN", "ELD and phone"),
    (r"TRUCK LOAN|COMMERCIAL CREDIT|FINANCIAL SVCS", "Truck payment"),
    (r"TRANSFER TO .*SAVINGS|OWNER DRAW", "Owner pay"),
]

import re
COMPILED = [(re.compile(p, re.IGNORECASE), name) for p, name in RULES]

def bucket(desc: str) -> str:
    for pattern, name in COMPILED:
        if pattern.search(desc or ""):
            return name
    return "Uncategorized"

tx["bucket"] = tx["description"].map(bucket)

Print the Uncategorized rows every month and add a rule for each new merchant. After two or three months the list is almost empty. Don't skip this: an unbucketed $900 repair makes the month look $0.09 a mile cheaper than it was.

Four mistakes that break cost per mile

1. Counting the whole truck payment. A $2,150 payment is mostly principal, which pays down the loan. Only the interest is a cost. If you have the lender's statement, use its interest figure. If you don't, estimate it from the balance:

loan_balance = Decimal("92000.00")
annual_rate = Decimal("0.095")
interest = (loan_balance * annual_rate / 12).quantize(Decimal("0.01"))   # 728.33

Then replace the "Truck payment" bucket with a "Truck loan interest" line of 728.33. The other 1,421.67 is principal, not a cost.

2. Counting owner pay. Transfers to the driver's personal account are not truck costs. They come out of what's left after costs. The Owner pay rule exists only so those rows can be dropped.

3. Counting fuel twice. If the carrier gave a fuel advance on the settlement and the same fill also shows on the fuel card statement, it is one cost, not two. Pick one source of truth for fuel per month (usually the fuel card) and drop the other. This is also where your IFTA fuel-by-state numbers come from, so getting it right matters twice.

4. Counting escrow as a cost. If you work from settlements rather than the bank, the weekly escrow deduction looks like an expense. It isn't. It's the driver's money held by the carrier and comes back when the lease ends. Leave it out of cost per mile.

The report

costs = tx[~tx["bucket"].isin(["Owner pay", "Truck payment", "Uncategorized"])]
by_bucket = costs.groupby("bucket")["amount"].apply(lambda s: -sum(s))   # money out is negative
by_bucket["Truck loan interest"] = interest

![ZeraBooks dashboard for a two-truck carrier's September: $17,118.83 spent, led by $12,190.75 at Comdata fuel, $2,840.00 in truck tires and $1,386.00 in truck insurance, against $39,666.95 in Landstar revenue](https://zerabooks.github.io/images/app/truck-dashboard.png)
*The month's spend by vendor in ZeraBooks for a fictional two-truck carrier. Divide each bucket by the month's miles and you have cost per mile.*

report = pd.DataFrame({"October": by_bucket})
report["Per mile"] = report["October"].map(lambda v: (v / MILES).quantize(Decimal("0.001")))
report.loc["Total cost"] = [sum(by_bucket), (sum(by_bucket) / MILES).quantize(Decimal("0.001"))]
print(report.sort_values("October", ascending=False))

For Pinecrest's October:

Cost	October	Per mile
Fuel	6,912.40	0.665
Insurance	1,520.00	0.146
Maintenance and tires	1,384.75	0.133
Truck loan interest	728.33	0.070
Permits, plates and 2290	410.00	0.039
Tolls	187.60	0.018
ELD and phone	98.00	0.009
Total cost	11,241.08	1.081

Settlements paid $24,440 gross for the month, which is $2.35 a mile. So Pinecrest kept about $1.27 a mile, or $13,198.92, before depreciation, owner pay and income tax. Any load offered below $1.08 a mile all-in loses money no matter how many miles it adds.

Make the inputs trustworthy first

The report is only as good as the CSVs. Before running it, check that each account's transactions add up from its opening to its closing balance on the statement. A missing page of the fuel card statement looks exactly like a cheap month.

The profit and loss built from the same statements, after the month was tied to the bank.

That's the order a monthly close should run in anyway. The ZeraBooks trucking bookkeeping checklist puts cost per mile as the last step for that reason: settlements booked gross, accounts reconciled, loans split, then the review.

Disclosure: I work on ZeraBooks. If you are the driver and would rather not write Python, the owner-operator bookkeeping guide walks through the same numbers as a 20-minute weekly routine, and ZeraBooks can keep the books for you.

Turning a trucking settlement statement into ledger lines (and refusing to post when it doesn't add up)

Damin Mutti — Wed, 04 Nov 2026 21:48:19 +0000

A carrier settlement becomes ledger lines like this: each load's linehaul and fuel surcharge is revenue, each deduction gets its own expense account, escrow and Form 2290 savings go to an asset account because they are still the driver's money, and the net is the cash that hit the bank. Before any of it posts, the code adds the loads, subtracts the deductions and compares the result with the net printed on the settlement. If it is off by a cent, nothing posts.

This post is the data model and the checks, in Python. It comes from building this for a bookkeeper whose clients are all leased-on owner-operators, about 80 drivers paid weekly.

What a settlement looks like

Every carrier prints it differently, but the content is the same. Once extracted, a week for one driver looks something like this (fictional numbers):

settlement = {
    "carrier": "Example Freight Lines",
    "driver": "R. Alvarez",
    "truck": "4471",
    "pay_period_end": "2026-10-24",
    "pay_date": "2026-10-30",          # a Friday
    "loads": [
        {"ref": "L-88120", "linehaul": "2140.00", "fuel_surcharge": "386.50"},
        {"ref": "L-88191", "linehaul": "1875.00", "fuel_surcharge": "301.20"},
    ],
    "deductions": [
        {"type": "FUEL ADVANCE",       "amount": "1412.66"},
        {"type": "OCC/ACC INSURANCE",  "amount": "41.00"},
        {"type": "PHYS DAMAGE INS",    "amount": "118.00"},
        {"type": "ESCROW",             "amount": "100.00"},
        {"type": "2290 SAVINGS",       "amount": "15.00"},
        {"type": "ELD",                "amount": "22.00"},
        {"type": "TOLLS",              "amount": "64.35"},
    ],
    "printed_net": "2929.69",
}

Step 1: map deduction types to accounts

The bookkeeper already has a chart of accounts. The mapping is his, not the software's. Two types are never left to a guess: escrow and 2290 savings are assets.

from decimal import Decimal

ACCOUNT_MAP = {
    "FUEL ADVANCE":      ("Fuel", "expense"),
    "OCC/ACC INSURANCE": ("Insurance", "expense"),
    "PHYS DAMAGE INS":   ("Insurance", "expense"),
    "ESCROW":            ("Escrow held by carrier", "asset"),
    "2290 SAVINGS":      ("Form 2290 savings", "asset"),
    "ELD":               ("ELD and phone", "expense"),
    "TOLLS":             ("Tolls", "expense"),
}

LOCKED_TYPES = {"ESCROW", "2290 SAVINGS"}   # never auto-mapped by a model

When a type shows up that isn't in the map (a new "LUMPER" line, a "PLATE PRORATE"), the settlement waits. You can let a model suggest an account, but a person confirms it once, and that answer goes into the map for every driver after that.

Step 2: prove it before posting

D = Decimal

class NeedsReview(Exception):
    pass

def prove(s):
    gross = sum(D(l["linehaul"]) + D(l["fuel_surcharge"]) for l in s["loads"])
    taken = sum(D(d["amount"]) for d in s["deductions"])
    computed_net = gross - taken
    printed = D(s["printed_net"])
    if computed_net != printed:
        raise NeedsReview(
            f"lines add to {computed_net}, settlement prints {printed}, "
            f"difference {printed - computed_net}"
        )
    unknown = [d["type"] for d in s["deductions"] if d["type"] not in ACCOUNT_MAP]
    if unknown:
        raise NeedsReview(f"new deduction types: {unknown}")
    return gross, computed_net

For the example: gross is 4,702.70, deductions are 1,773.01, net is 2,929.69. It ties, so it can post.

This is the most important part. Carriers do sometimes print a total that their own lines don't add up to. In testing we saw a settlement whose printed total was $10 more than its lines. Posting the printed total would put $10 of revenue in the books that no load supports. Posting the lines would make the bank deposit look short. Neither is right without a human looking, so the code stops.

Step 3: build the journal entry

def to_journal(s):
    gross, net = prove(s)
    lines = []
    for load in s["loads"]:
        lines.append(("Freight revenue: linehaul", None, D(load["linehaul"])))
        lines.append(("Freight revenue: fuel surcharge", None, D(load["fuel_surcharge"])))
    for d in s["deductions"]:
        account, _kind = ACCOUNT_MAP[d["type"]]
        lines.append((account, D(d["amount"]), None))
    if net >= 0:
        lines.append(("Bank: business checking", net, None))
    else:
        lines.append(("Payable to carrier", None, -net))

![ZeraBooks ledger for a two-truck carrier's September: Landstar settlements in Freight Settlement Revenue, truck notes in Notes Payable - Equipment, tires in Repair and Maintenance - Truck 102](https://zerabooks.github.io/images/app/truck-ledger.png)
*A fictional two-truck carrier's September in the ZeraBooks ledger: settlements land in freight revenue, truck notes in the equipment loan and repairs by truck.*

    debits = sum(l[1] or 0 for l in lines)
    credits = sum(l[2] or 0 for l in lines)
    assert debits == credits, "entry does not balance"

    return {"date": s["pay_date"], "memo": f'{s["carrier"]} settlement, truck {s["truck"]}', "lines": lines}

A few decisions are baked in here, and each one came from a real week.

The date is the pay date. Not the pay period end and not the upload date. The bookkeeper's drivers are paid on Fridays, and the bank deposit lands that day, so matching the deposit later is trivial.

A negative week goes to a payable. When deductions are bigger than the loads (a short week with a full insurance charge), the carrier collects it from a later settlement. Booking it as a weird negative bank line or an expense hides what actually happened: the driver owes the carrier money.

Escrow is an asset, every week. The $100 isn't a cost. It is the driver's money sitting with the carrier, and the balance in the books should match what the carrier says it holds.

Booking gross, not net. The tempting shortcut is one line: "deposit 2,929.69, revenue". That makes the P&L wrong in two directions. Revenue is understated by 1,773.01 and every real cost (fuel, insurance, tolls) disappears. It also makes escrow invisible, which matters the day the driver leaves the carrier and wants it back.

Step 4: don't post the same week twice

Bookkeepers re-download files. Carriers re-send them. Hash what makes a settlement unique and refuse duplicates:

import hashlib, json

def fingerprint(s):
    key = {k: s[k] for k in ("carrier", "driver", "truck", "pay_period_end", "printed_net")}
    return hashlib.sha256(json.dumps(key, sort_keys=True).encode()).hexdigest()

Also refuse a settlement whose pay period hasn't ended yet. It happens more than you would think, usually from a preview PDF.

Matching the driver

Names are not unique, and two drivers with the same name at one carrier is a case the setup has to handle. Match on truck number first, name second, and if neither matches exactly, stop and ask. A settlement posted to the wrong driver is worse than one that waits a day.

How it held up

Before go-live we ran 648 settlement statements across 9 weeks against an answer key built from the bookkeeper's own entries. Every one that posted matched to the cent. The rest stopped for a decision, which is the behavior you want: a short list of "look at these" instead of a silent wrong number.

The same month tied to the bank statement before anything is closed.

If you want the result without writing it

Disclosure: I work on ZeraBooks. The page on trucking bookkeeping automation describes the full setup we built (drivers board, P&L by driver, the stop rules above), and we build it free for trucking bookkeepers in their carriers' layouts. If you are the driver rather than the bookkeeper, the owner-operator bookkeeping guide covers the same gross-versus-net rule in plain English, with a weekly routine.

Proving a bank statement CSV is complete: tie the running balance to the printed totals

Damin Mutti — Wed, 04 Nov 2026 21:47:43 +0000

A bank statement CSV is complete when two things hold: the opening balance plus every transaction equals the printed closing balance, and every row's running balance equals the previous balance plus that row's amount. If both checks pass to the cent, no row is missing, duplicated or misread. If either fails, the row where it first breaks tells you where to look.

That is the whole idea. The rest of this post is the code, and the edge cases that make it less simple than it sounds.

Why "the rows look right" isn't enough

Anyone who has pulled transactions out of a PDF statement has seen these:

A row that wrapped onto two lines and came out as two transactions, one with no amount.
A page break that dropped the last row of page 3.
A 1,234.50 that became 1234.5, or a (45.00) that lost its minus sign.
An OCR'd 8 that is really a 3.

Eyeballing 400 rows catches none of these reliably. Arithmetic catches all of them, because the bank already printed the answer: the closing balance.

The data

Assume the extractor (any tool, or your own parser) gives you rows like this, plus the opening and closing balances printed in the statement summary:

date,description,amount,balance
2026-09-01,OPENING BALANCE,,4210.55
2026-09-02,DEPOSIT MOBILE,1500.00,5710.55
2026-09-03,CHECK 1042,-842.10,4868.45
2026-09-03,CARD PURCHASE SHELL 0447,-96.37,
2026-09-05,ACH PAYROLL GUSTO,-2310.00,2462.08
2026-09-08,WIRE IN,3000.00,5462.08

Notice the blank balance on the Shell row. Many banks print one running balance per day, not per row. The check has to cope with that.

The code

Use Decimal, never float. 0.1 + 0.2 is not a rounding error you want in a reconciliation.

import csv
from dataclasses import dataclass
from decimal import Decimal, InvalidOperation

CENT = Decimal("0.01")

def parse_money(raw: str) -> Decimal | None:
    s = (raw or "").strip().replace(",", "").replace("$", "")
    if not s:
        return None
    negative = s.startswith("(") and s.endswith(")")
    s = s.strip("()")
    if s.endswith("-"):          # some banks print 45.00-
        negative, s = True, s[:-1]
    try:
        value = Decimal(s)
    except InvalidOperation:
        raise ValueError(f"not a money value: {raw!r}")
    return -value if negative else value

@dataclass
class Break:
    row: int
    expected: Decimal
    printed: Decimal

def tie_out(path: str, opening: Decimal, closing: Decimal):
    running = opening
    breaks: list[Break] = []
    total_in = total_out = Decimal("0")

    with open(path, newline="") as f:
        for i, row in enumerate(csv.DictReader(f), start=2):
            amount = parse_money(row["amount"])
            if amount is None:
                continue                      # header rows like OPENING BALANCE
            running += amount
            if amount > 0:
                total_in += amount
            else:
                total_out += amount

            printed = parse_money(row.get("balance", ""))
            if printed is not None and printed.quantize(CENT) != running.quantize(CENT):
                breaks.append(Break(i, running, printed))
                running = printed             # resync so one bad row doesn't flag every row after it

    return {
        "ties_to_closing": running.quantize(CENT) == closing.quantize(CENT),
        "difference": (closing - running).quantize(CENT),
        "money_in": total_in,
        "money_out": total_out,
        "breaks": breaks,
    }

Two details worth calling out.

Resync after a break. If row 40 is misread, every running balance after it will be off by the same amount. Without the running = printed line you get 360 errors instead of one. With it, you get the one row that matters.

Blank balances are skipped, not failed. On a per-day balance statement, the check only fires on the last row of each day, which is exactly where the bank printed a number. The final closing check still covers every row.

Checking the printed totals too

Most statements also print "Total deposits" and "Total withdrawals" in the summary box. Compare them as well:

The same check inside ZeraBooks Convert: opening plus every transaction has to land on the printed closing before the file is ready to download.

result = tie_out("sept.csv", opening=Decimal("4210.55"), closing=Decimal("5462.08"))

printed_deposits = Decimal("4500.00")
printed_withdrawals = Decimal("-3248.47")

assert result["money_in"] == printed_deposits, "a deposit is missing or doubled"
assert result["money_out"] == printed_withdrawals, "a withdrawal is missing or doubled"

This catches a case the closing balance alone can miss: a $200 deposit that was dropped and a $200 withdrawal that was also dropped. The net is still right. The totals are not.

What a failing result tells you

Result	Most likely cause
Closing doesn't tie, no row breaks	A row is missing where the bank printed no running balance, or the sign of one amount is flipped (the difference is then exactly twice that amount)
One break, closing ties after resync	A single misread amount on that row
Difference equals one row's amount	That row is duplicated or missing
Difference divisible by 9	A transposed digit, like 54.10 read as 45.10

The "twice the amount" and "divisible by 9" tricks are old bookkeeper habits, and they still work on extracted data.

Credit cards and the sign problem

On a card statement, purchases increase the balance you owe. If your extractor writes purchases as negative numbers (money out of your pocket), flip the logic: running -= amount, or normalize the sign first. Decide once, per account type, and write it down. Mixing conventions between a checking account and a card is the most common reason a perfectly good extraction "fails".

Where this fits

This check is the floor, not the ceiling. It proves one statement is internally complete. It doesn't prove you have every statement in the set; that is a different check (each closing balance should equal the next statement's opening balance), and it is the first thing forensic accountants run on a document production. The ZeraBooks guide to forensic bank statement analysis walks through that chain check with a missing April.

A converted statement in ZeraBooks Convert, with the proof above the rows.

Disclosure: I work on ZeraBooks, and the ZeraBooks bank statement converter runs this kind of proof on every statement before you download it, then shows the difference when one doesn't tie. If you are building your own parser, the code above is enough to stop trusting rows you haven't proven.

Designing an AI general ledger: the model proposes, deterministic code disposes

Damin Mutti — Sat, 24 Oct 2026 05:09:37 +0000

An AI general ledger works when the model only produces proposals (a category, a match, a question with a confidence) and deterministic code validates, applies, logs and can reverse them. Every balance and total comes from the ledger, never the model. That is how ZeraBooks is built. This post sketches that shape in TypeScript. It illustrates the design; it isn't our source code.

I'm on the ZeraBooks team. When people hear "AI does the bookkeeping" they picture a model writing journal entries. That's the design we avoided. The model never writes to the ledger and never does arithmetic. It returns a small structured proposal, and everything after that is ordinary code with tests.

Proposals, not writes

type Confidence = "sure" | "fairly_sure" | "not_sure";

type Proposal =
  | { kind: "categorize"; lineId: string; categoryId: string;
      confidence: Confidence; p: number; evidence: string }
  | { kind: "match_invoice"; lineId: string; invoiceId: string;
      confidence: Confidence; p: number; evidence: string }
  | { kind: "pair_transfer"; outLineId: string; inLineId: string;
      confidence: Confidence; p: number; evidence: string }
  | { kind: "ask"; lineId: string; question: string;
      options: { label: string; categoryId: string; p: number }[] };

Note what's missing: amounts. A categorize proposal says which account a statement line belongs to. The amount is already on the line, read from the statement. A match_invoice proposal says which invoice; the ledger decides whether the amounts agree. There's no field where the model could put a number that ends up in the books.

evidence is a short string quoting what the model relied on ("same vendor and amount as the last eight months"), shown to the user.

A policy layer that can say no

function admit(p: Proposal, ctx: LedgerContext): Decision {
  if (p.kind === "ask") return { action: "queue_question" };

  const line = ctx.line(p.kind === "pair_transfer" ? p.outLineId : p.lineId);

  if (ctx.isClosedPeriod(line.date)) return { action: "reject", why: "closed period" };
  if (line.setByUser)                return { action: "flag_only", why: "user decision stands" };
  if (line.payeeIsTaxAuthority)      return { action: "queue_question", why: "tax payments always ask" };
  if (p.kind === "pair_transfer" && line.isTransfer) return { action: "reject", why: "already paired" };

  switch (p.confidence) {
    case "sure":        return { action: "apply" };
    case "fairly_sure": return { action: "apply_and_flag" };
    case "not_sure":    return { action: "queue_question" };
  }
}

These rules mirror the guardrails we publish: the AI doesn't overwrite a category you set, doesn't touch closed periods, and always asks about payments to a tax authority. They're plain if statements on purpose. You can read them, test them and be sure they hold.

Possible duplicates get their own path: the model can flag a pair, and there is no proposal type for deleting a line at all. Same for moving money: no bill pay, no transfers, no bank connection. The safest permission is the one that doesn't exist.

Every applied action is reversible

interface ActionLog {
  id: string;
  at: string;
  proposal: Proposal;
  before: Snapshot;   // the fields this action changed, as they were
  after: Snapshot;
  undoneAt?: string;
}

function apply(p: Proposal, ctx: LedgerContext): ActionLog {
  const before = ctx.snapshot(p);
  ctx.write(p);                // validated, transactional
  const after = ctx.snapshot(p);
  return ctx.log({ proposal: p, before, after });
}

function undo(log: ActionLog, ctx: LedgerContext) {
  if (!ctx.unchangedSince(log)) throw new Error("line edited since; review manually");
  ctx.restore(log.before);
  ctx.markUndone(log.id);
}

In the product this is the "Done by AI" list. Each card has Undo. unchangedSince prevents an undo from clobbering a later human edit.

Arithmetic lives in the ledger

The month-close check is a pure function over data the model never touched:

function statementTies(s: Statement, prev?: Statement): TieResult {
  const sum = s.lines.reduce((acc, l) => acc + l.amountCents, 0);
  const adds    = s.openingCents + sum === s.closingCents;
  const follows = !prev || prev.closingCents === s.openingCents;
  const agrees  = ledgerBalanceCents(s.accountId, s.endDate) === s.closingCents;
  return { adds, follows, agrees, ok: adds && follows && agrees };
}

Integer cents, no floats. A fictional October checking statement: opening 800000, lines summing to +117945, closing 917945. It adds. If last month closed at 800000, it follows. If the ledger balance at October 31 is 917945, it agrees, and only then can the month be signed off. When it doesn't tie, the model can be asked to re-read the statement, but its proposed fixes are only accepted if they make all three checks pass.

Where confidence comes from

In ZeraBooks, confidence comes from the company's own reviewed history: how lines like this one were categorized before. Below 60%, the question card says the best guess is a low-confidence one. Corrections become history, and the user can apply a correction to similar lines (same merchant, same direction, any account or date), with hand-set lines skipped.

The published accuracy number for this setup: after a couple of months of bookkeeping, ZeraBooks categorizes 99.6% of transactions correctly, tested on years of bookkeeping for several companies. The design goal is that the remaining errors are visible, explained and undoable.

The product

ZeraBooks is an AI-native general ledger. It works from uploaded statements (PDF or CSV, no bank feeds), proves each one against its printed balances to the cent, and costs $79 a month billed annually or $149 monthly after a 7-day trial. The non-technical explanation, including how this differs from a feed-and-rules ledger, is on our page about the AI general ledger.

Disclosure: I'm on the ZeraBooks team, and this describes our product.

Splitting loan payments into principal and interest with Decimal, and generating the journal entries

Damin Mutti — Sat, 24 Oct 2026 05:05:27 +0000

To split a loan payment into principal and interest in code, compute each month's interest as the outstanding balance times the annual rate divided by 12, rounded to the cent, and treat the rest of the payment as principal. Then emit a balanced journal entry per payment and check the running balance against the lender's statement. ZeraBooks' ledger does the same thing in its own code, never in the AI.

I'm on the ZeraBooks team. Loan payments are a nice small example of a rule we hold to everywhere: the AI decides what a bank line is ("this is a payment on the equipment loan"), and deterministic code does the math. Here's that code, simplified, in Python.

Payment amount

For a fixed-rate amortizing loan, the level payment is the standard annuity formula:

from decimal import Decimal, ROUND_HALF_UP, getcontext

getcontext().prec = 28
CENT = Decimal("0.01")

def level_payment(principal: Decimal, annual_rate: Decimal, months: int) -> Decimal:
    r = annual_rate / 12
    if r == 0:
        return (principal / months).quantize(CENT, ROUND_HALF_UP)
    p = principal * r / (1 - (1 + r) ** -months)
    return p.quantize(CENT, ROUND_HALF_UP)

pay = level_payment(Decimal("24000"), Decimal("0.06"), 24)
assert pay == Decimal("1063.69")

Fictional loan: 24,000.00 at 6% fixed over 24 months. Decimal with an explicit rounding mode matters. Floats can drift a cent somewhere over 24 periods, and a one-cent drift means your loan balance never matches the lender's.

The schedule

from dataclasses import dataclass

@dataclass
class Period:
    n: int
    payment: Decimal
    interest: Decimal
    principal: Decimal
    balance: Decimal

def schedule(principal, annual_rate, months, payment=None):
    payment = payment or level_payment(principal, annual_rate, months)
    bal, out = principal, []
    for n in range(1, months + 1):
        interest = (bal * annual_rate / 12).quantize(CENT, ROUND_HALF_UP)
        prin = payment - interest
        if n == months:              # last payment absorbs rounding
            prin = bal
            payment = prin + interest
        bal -= prin
        out.append(Period(n, payment, interest, prin, bal))
    return out

for p in schedule(Decimal("24000"), Decimal("0.06"), 24)[:3]:
    print(p.n, p.interest, p.principal, p.balance)

Output:

1 120.00 943.69 23056.31
2 115.28 948.41 22107.90
3 110.54 953.15 21154.75

Interest on period 1 is 24,000.00 × 0.06 / 12 = 120.00. Three payments total 3,191.07: 345.82 interest and 2,845.25 principal, and 24,000.00 − 2,845.25 = 21,154.75.

The last-period adjustment is there because rounding each month's interest leaves a few cents unaccounted for by the end. Lenders do the same thing; the final payment is usually slightly different from the rest.

Journal entries

Each payment becomes one entry with a debit to the loan, a debit to interest expense, and a credit to the bank. Optional fee lines get their own account:

def payment_entry(period: Period, date: str, loan_acct: str,
                  bank_acct: str, fee: Decimal = Decimal("0")):
    lines = [
        {"account": loan_acct, "debit": period.principal, "credit": Decimal("0")},
        {"account": "Interest Expense", "debit": period.interest, "credit": Decimal("0")},
    ]
    if fee:
        lines.append({"account": "Bank and Loan Fees", "debit": fee, "credit": Decimal("0")})
    lines.append({"account": bank_acct, "debit": Decimal("0"),
                  "credit": period.payment + fee})
    dr = sum(l["debit"] for l in lines)
    cr = sum(l["credit"] for l in lines)
    assert dr == cr, f"unbalanced entry: {dr} vs {cr}"
    return {"date": date, "lines": lines}

The assertion is cheap and catches every bug where someone adds a fee to the bank line but not to the debits. The credit to the bank must equal the withdrawal on the bank statement, same amount and same date, so the bank reconciliation is unaffected.

When the lender's numbers win

The formula above assumes interest is charged monthly on the balance. Many lenders compute it daily, so a payment made late carries a bit more interest. If the lender's statement prints "applied to interest" and "applied to principal", use those numbers and let your schedule be the fallback:

def reconcile_to_lender(book_balance: Decimal, lender_balance: Decimal):
    diff = book_balance - lender_balance
    if diff:
        raise ValueError(f"loan off by {diff}: a payment or split is wrong")

Run that after every payment. It's the loan equivalent of a bank reconciliation, and it's the check people skip.

How ZeraBooks does it

In ZeraBooks, a loan payment on the bank statement is booked to the loan account. At month end, Autopilot asks for the rate if it doesn't know it (it offers 3.75% for an SBA EIDL), then moves that month's interest from the loan to Interest Expense: balance owed × rate ÷ 12. It never splits interest from a guessed rate, and each entry appears in Done by AI with Undo. Loan statements uploaded alongside bank statements are used as evidence for the split. At year end, it flags any loan that got six or more payments with no interest recorded.

Pricing: $79/month billed annually or $149/month billed monthly, 7-day trial. Nothing connects to a lender or bank; statements are uploaded.

The accounting side, with the entries for funding, an origination fee and a late fee, is in our guide on how to record loan payments.

Disclosure: I work on ZeraBooks.

A pre-flight validator for QuickBooks Online bank CSV uploads

Damin Mutti — Sat, 24 Oct 2026 04:57:49 +0000

A QuickBooks Online bank CSV should be validated before upload against two sets of rules: Intuit's format rules (3 or 4 columns, 1,000 lines or fewer, 350 KB or less, one date format, no zeros in Credit or Debit) and the statement's own arithmetic (opening plus rows equals closing). Below is a small Python validator for both. ZeraBooks Convert runs the arithmetic check before it lets you download.

I'm on the ZeraBooks team. Our converter outputs QuickBooks-ready CSVs, so we spend a lot of time on what QuickBooks Online will and won't accept, and on what it will accept that's still wrong. The second category is the dangerous one. A file that uploads cleanly can still be missing a line, and once it's in the For review list you won't see it until reconciliation fails.

Intuit's rules, as checks

From Intuit's help article on manually uploading transactions (updated August 24, 2026):

3 columns: Date, Description, Amount. Or 4: Date, Description, Credit, Debit.
File size 350 KB or less, up to 1,000 lines per upload.
One date format throughout.
In the 4-column layout, leave the empty side blank, not zero.
Don't put the word "amount" in the Credit or Debit headers.

Here they are as code:

import csv, io, os, re
from datetime import datetime
from decimal import Decimal, InvalidOperation

MAX_BYTES = 350 * 1024
MAX_ROWS = 1000
DATE_FORMATS = ["%m/%d/%Y", "%d/%m/%Y", "%Y-%m-%d"]

def detect_date_format(values):
    for fmt in DATE_FORMATS:
        try:
            for v in values:
                datetime.strptime(v, fmt)
            return fmt
        except ValueError:
            continue
    return None

def money(s):
    s = s.strip().replace(",", "")
    if s == "":
        return None
    try:
        return Decimal(s)
    except InvalidOperation:
        raise ValueError(f"not a number: {s!r}")

def validate_qbo_csv(path):
    errors = []
    if os.path.getsize(path) > MAX_BYTES:
        errors.append(f"file is {os.path.getsize(path)} bytes, limit {MAX_BYTES}")

    with open(path, newline="", encoding="utf-8-sig") as f:
        rows = list(csv.reader(f))
    header, data = rows[0], rows[1:]
    if len(data) > MAX_ROWS:
        errors.append(f"{len(data)} rows, limit {MAX_ROWS}; split by date")

    cols = [h.strip().lower() for h in header]
    if len(cols) == 3:
        layout = "3col"
    elif len(cols) == 4:
        layout = "4col"
        for h in cols[2:]:
            if "amount" in h:
                errors.append(f"header {h!r} must not contain 'amount'")
    else:
        return [f"expected 3 or 4 columns, got {len(cols)}"], None

    fmt = detect_date_format([r[0] for r in data])
    if fmt is None:
        errors.append("dates are not in one consistent format")

    signed = []
    for n, r in enumerate(data, start=2):
        if layout == "3col":
            amt = money(r[2])
            if amt is None:
                errors.append(f"line {n}: empty amount")
                continue
        else:
            cr, dr = money(r[2]), money(r[3])
            if (cr is None) == (dr is None):
                errors.append(f"line {n}: needs exactly one of Credit/Debit")
                continue
            if (cr is not None and cr == 0) or (dr is not None and dr == 0):
                errors.append(f"line {n}: use a blank, not 0")
            amt = cr if cr is not None else -dr
        signed.append(amt)
    return errors, signed

detect_date_format checks that one format parses every row. Mixed formats are common when someone pastes two exports together. A file where 03/04/2026 is ambiguous will still pass; that's a limit of looking at strings, and why the next check matters more.

The check Intuit doesn't run: does the file tie?

QuickBooks has no way to know your statement's balances. You do. Opening balance plus every signed row should land on the printed closing balance:

def ties(signed, opening, closing):
    total = opening + sum(signed, Decimal("0"))
    return total == closing, total - closing

A fictional quarter: checking ending 2290, July to September 2026, in one PDF. Opening 8,214.55, deposits 31,402.10, withdrawals 29,880.43, closing printed as 9,736.22.

errors, signed = validate_qbo_csv("chk_2290_2026Q3.csv")
ok, diff = ties(signed, Decimal("8214.55"), Decimal("9736.22"))
print(errors or "format ok", "| ties" if ok else f"| off by {diff}")

8,214.55 + 31,402.10 − 29,880.43 = 9,736.22. If diff isn't zero, don't upload. A diff equal to one row's amount means that row is missing or doubled. A diff of twice a row means its sign is flipped.

Splitting a big year

A busy account can blow through 1,000 rows. Split on month boundaries, not at row 1,000, so each chunk starts and ends on a statement date and can be tied on its own:

from collections import OrderedDict

def split_by_month(rows, date_fmt, max_rows=MAX_ROWS):
    months = OrderedDict()
    for r in rows:
        key = datetime.strptime(r[0], date_fmt).strftime("%Y-%m")
        months.setdefault(key, []).append(r)
    chunks, current = [], []
    for key, mrows in months.items():
        if current and len(current) + len(mrows) > max_rows:
            chunks.append(current)
            current = []
        current.extend(mrows)
    if current:
        chunks.append(current)
    return chunks

1,640 rows over a year, for example, comes out as two files of whole months. If a single month is over 1,000 rows on its own, you'll need to split inside it by date and tie against a mid-month balance if the statement prints one.

Overlap with what's already there

The last check isn't about the file at all: find the last date already in that QuickBooks account and drop rows on or before it. Overlap is where most duplicates in For review come from.

Where ZeraBooks fits

ZeraBooks Convert reads the PDF (scans and multi-account PDFs included), splits by account and cardholder, checks each statement against its printed opening and closing balance to the cent, and gives you a QuickBooks-ready CSV in the 3-column layout, one file per account. It doesn't connect to QuickBooks and doesn't make .qbo or IIF files. It's in the ZeraBooks subscription, $79 a month billed annually or $149 monthly, after a 7-day trial.

The non-code walkthrough, with all four ways to get a PDF into QuickBooks Online, is our guide on importing a PDF bank statement into QuickBooks.

Disclosure: I'm part of the ZeraBooks team.

Bank reconciliation as code: deposits in transit, outstanding checks, book errors and one assertion

Damin Mutti — Sat, 24 Oct 2026 02:04:05 +0000

A bank reconciliation in code is: match book lines to bank lines, treat unmatched book deposits as in transit and unmatched book payments as outstanding, treat unmatched bank lines and amount differences as book-side adjustments, then assert that the statement balance adjusted for the first two equals the book balance adjusted for the rest. The book-side items become journal entries.

I'm on the ZeraBooks team. Reconciliation is the core of what our ledger does, and it's often explained as a form to fill in. Written as code it's short, and the structure makes it obvious which side of the reconciliation each item belongs on, which is the part people get wrong by hand.

The two sides

Every reconciling item belongs to exactly one side:

Item	Where it is	Side	Why
Deposit in transit	Books, not bank yet	Bank	The bank will catch up next month
Outstanding check	Books, not bank yet	Bank	Same
Bank fee, interest	Bank, not books	Book	You haven't recorded it; you need to
Amount recorded wrong	Both, different amounts	Book	The bank cleared what was actually written

Bank-side items need no entry; they resolve themselves. Book-side items need a journal entry, or the books stay wrong.

The code

from dataclasses import dataclass
from decimal import Decimal as D

@dataclass(frozen=True)
class Line:
    date: str
    ref: str          # check number or short description
    amount: D         # money in positive, money out negative

def match(book: list[Line], bank: list[Line]):
    """Pair book and bank lines: same check number, else same amount. Each line used once."""
    unmatched_bank = list(bank)
    matched, book_only = [], []
    for b in book:
        cand = [x for x in unmatched_bank if x.ref == b.ref and b.ref.isdigit()] \
            or [x for x in unmatched_bank if x.amount == b.amount and not x.ref.isdigit()]
        if cand:
            matched.append((b, cand[0]))
            unmatched_bank.remove(cand[0])
        else:
            book_only.append(b)
    return matched, book_only, unmatched_bank

def reconcile(book, bank, book_balance: D, statement_balance: D):
    matched, book_only, bank_only = match(book, bank)
    in_transit  = sum((b.amount for b in book_only if b.amount > 0), D("0"))
    outstanding = sum((-b.amount for b in book_only if b.amount < 0), D("0"))
    # matched pairs whose amounts differ are book errors (the bank cleared what was written)
    errors = [(b, k, k.amount - b.amount) for b, k in matched if b.amount != k.amount]
    book_side = sum((k.amount for k in bank_only), D("0")) + sum((e[2] for e in errors), D("0"))

    adj_bank = statement_balance + in_transit - outstanding
    adj_book = book_balance + book_side
    assert adj_bank == adj_book, f"unreconciled difference {adj_bank - adj_book}"
    entries = [(k.ref, k.amount) for k in bank_only] + [(f"correct {b.ref}", diff) for b, _, diff in errors]
    return adj_bank, in_transit, outstanding, entries

Signed amounts throughout: money in positive, money out negative. Decimal, never float.

The matching rule is intentionally simple: checks match on check number, everything else on exact amount, each bank line used once. Matching on check number first is what lets the code detect an amount error on a check: the pair is found by number, and the amounts disagree. Real-world matching needs date windows and handling for split deposits, but the reconciliation logic after matching doesn't change.

A fictional October

Books for a small firm's checking account at October 31 show a balance of 19,826.65. The bank statement's closing balance is 18,962.40.

book = [
    Line("10-03", "2210",    D("-392.00")),
    Line("10-14", "Deposit", D("5200.00")),
    Line("10-20", "2214",    D("-612.00")),
    Line("10-27", "2217",    D("-874.25")),
    Line("10-31", "Deposit", D("2350.00")),
]
bank = [
    Line("10-06", "2210",        D("-362.00")),
    Line("10-15", "Deposit",     D("5200.00")),
    Line("10-31", "Service fee", D("-35.00")),
    Line("10-31", "Interest",    D("4.50")),
]

reconcile(book, bank, D("19826.65"), D("18962.40"))
# (Decimal('19826.15'), Decimal('2350.00'), Decimal('1486.25'),
#  [('Service fee', Decimal('-35.00')), ('Interest', Decimal('4.50')), ('correct 2210', Decimal('30.00'))])

(Only the lines relevant to reconciling items are shown; the month's other matched transactions don't change the result.)

Reading the result

Bank side. The 2,350.00 deposit made on October 31 hasn't reached the bank: in transit. Checks 2214 and 2217, for 612.00 and 874.25, haven't cleared: 1,486.25 outstanding.

18,962.40 plus 2,350.00 minus 1,486.25 is 19,826.15.

Book side. The bank charged a 35.00 service fee and paid 4.50 of interest, neither recorded yet. And check 2210 was written for 362.00 but entered in the books as 392.00; the bank cleared 362.00, so the books understate cash by 30.00.

19,826.65 minus 35.00 plus 4.50 plus 30.00 is 19,826.15.

Both sides land on 19,826.15. The assertion passes, and the function returns three journal entries to post: bank fees 35.00, interest income 4.50, and a 30.00 correction to whatever check 2210 was charged to.

Why the assertion matters

If the assertion fails, the difference is real and unexplained. Common causes, roughly in order:

A transaction that's in the books twice. The difference equals that amount.
A transposition, such as 874.25 entered as 847.25. The difference is divisible by 9 (27.00 here).
Last month's reconciliation wasn't actually finished, so an old outstanding item is missing from this month's list.
A bank line matched to the wrong book line by amount, when two transactions share an amount.

Never "fix" a failed reconciliation by posting the difference to a suspense or miscellaneous account. That makes the assertion pass and hides the problem.

One thing to add before using this

Carry forward outstanding items. A check outstanding at September 30 that clears in October appears on October's bank statement with no October book line. As written, the code would treat it as a bank-only item and propose an entry, which would double-count it. Seed book with last month's outstanding and in-transit items to fix that.

How ZeraBooks does it

ZeraBooks is an AI-native general ledger. It reads your bank and card statements, matches them to the books and proves each month against the statement's printed opening and closing balance: the statement must add up, follow the previous statement and agree with the ledger before a month closes. The AI never does the arithmetic; balances are computed in code. Proposed entries like the three above are approved by a person and can be undone. There's no live bank feed; statements are uploaded as PDF or CSV. It's $79 a month billed annually or $149 monthly, with a 7-day trial.

The step-by-step version without code, with a worked month, is our guide on how to do a bank reconciliation.

Disclosure: I work at ZeraBooks.

Wells Fargo statements: using the half-empty 'Ending daily balance' column as a free parser test

Damin Mutti — Sat, 24 Oct 2026 00:05:06 +0000

Wells Fargo statements print an ending daily balance only on the last transaction of each day, leaving the column blank on other rows. That half-empty column is a free test for a parser: group rows by date, add each day's amounts to a running total, and compare with the printed balance.

A mismatch tells you which day lost or mangled a row, not just that the month is off.

I'm on the ZeraBooks team, where we convert a lot of these. The usual advice is "check that the month ties to the ending balance," which is right but coarse: a month that's off by 425.00 could be off anywhere in 30 days. The daily balance narrows it to one day, and it's sitting in the statement already.

The layout

A Wells Fargo business checking statement typically prints one transaction table with columns for date, check number, description, deposits or credits, withdrawals or debits, and ending daily balance. Credits and debits are separate unsigned columns, and the balance column is filled only on a day's last row. Dates print as month and day; the year comes from the statement period.

Assume your PDF extraction produces this (fictional) October 2026, opening balance 6,215.40, ending balance 7,548.83:

Date,Check Number,Description,Deposits/Credits,Withdrawals/Debits,Ending daily balance
10/1,,Square deposit,1240.00,,7455.40
10/2,1188,Check,,425.00,
10/2,,Gusto payroll ACH,,2880.15,4150.25
10/6,,Mobile deposit,3600.00,,
10/6,,Purchase Home Depot,,186.42,7563.83
10/9,,Wire transfer fee,,15.00,7548.83

Two rows on October 2 and two on October 6 share one balance each. That's the whole point.

The checker

import pandas as pd
from decimal import Decimal

def dec(x):
    return Decimal(str(x)) if pd.notna(x) and str(x).strip() != "" else Decimal("0")

def load(path: str, year: int) -> pd.DataFrame:
    df = pd.read_csv(path, dtype=str, keep_default_na=False)
    df["Date"] = pd.to_datetime(df["Date"] + f"/{year}", format="%m/%d/%Y")
    df["Amount"] = [dec(c) - dec(w) for c, w in zip(df["Deposits/Credits"], df["Withdrawals/Debits"])]
    df["Printed"] = [dec(b) if b.strip() else None for b in df["Ending daily balance"]]
    return df

def check_days(df: pd.DataFrame, opening: Decimal) -> list[str]:
    problems, running = [], opening
    for day, g in df.groupby("Date", sort=True):
        running += sum(g["Amount"], Decimal("0"))
        printed = [p for p in g["Printed"] if p is not None]
        if len(printed) != 1 or g["Printed"].iloc[-1] is None:
            problems.append(f"{day:%Y-%m-%d}: no single ending daily balance on the last row; a row was dropped or split")
            continue
        if running != printed[0]:
            problems.append(f"{day:%Y-%m-%d}: rows give {running}, statement prints {printed[0]}, "
                            f"off by {printed[0] - running}")
            running = printed[0]  # re-anchor so one error doesn't cascade
    return problems

def check_month(df: pd.DataFrame, opening: Decimal, closing: Decimal) -> None:
    total = opening + sum(df["Amount"], Decimal("0"))
    assert total == closing, f"month: {total} != {closing}"

Amounts and balances are read as strings and converted to Decimal, so nothing passes through a float. Two details matter:

The balance must be on the last row of the day. If a day has a balance in the middle and none at the end, rows were reordered or a row was split across lines during extraction. That's flagged separately.
Re-anchoring after a mismatch. Once a day fails, the running total resets to the printed balance. Without that, one dropped row would make every later day fail too, and you'd lose the localization you came for.

Running it

On the clean file:

df = load("wf.csv", 2026)
check_days(df, Decimal("6215.40"))                      # []
check_month(df, Decimal("6215.40"), Decimal("7548.83"))  # passes

Day by day: 6,215.40 plus 1,240.00 is 7,455.40 on October 1. Minus 425.00 and 2,880.15 is 4,150.25 on October 2. Plus 3,600.00 minus 186.42 is 7,563.83 on October 6. Minus 15.00 is 7,548.83 on October 9, the statement's ending balance.

Now simulate the most common extraction failure, a check row that didn't come through because the check number confused the row detection:

check_days(df.drop(index=1), Decimal("6215.40"))
# ['2026-10-02: rows give 4575.25, statement prints 4150.25, off by -425.00']

One line, one day, one amount. Go to October 2 in the PDF and look for a 425.00 debit.

A dropped row that carried the balance looks different:

check_days(df.drop(index=4), Decimal("6215.40"))
# ['2026-10-06: no single ending daily balance on the last row; a row was dropped or split',
#  '2026-10-09: rows give 7735.25, statement prints 7548.83, off by -186.42']

October 6 has no balance on its last remaining row, which is flagged, and since the running total couldn't be re-anchored there, the 186.42 shows up at the next printed balance. Still two adjacent days to look at instead of a whole month.

Other things this catches

Credit and debit columns swapped on a page where the header was misread. Every day on that page fails by twice the day's net.
A row duplicated across a page break, which happens when a table continues onto the next page with a repeated last line. The day fails by exactly that row's amount.
The wrong year on a January statement won't fail this check, because the order of days is unchanged. That needs a separate date-range check against the statement period.

Limits

This relies on the statement printing a daily balance. Many banks don't, and some print one on every row (easy) or never (you're back to the monthly check). Check-image pages at the back of the PDF aren't transactions and shouldn't be parsed into this table. And it can't catch two errors on the same day that cancel out; the deposit count in the statement summary can, if you check it.

Where ZeraBooks Convert fits

ZeraBooks Convert reads Wells Fargo and other bank and card statement PDFs, merges credits and debits into one signed Amount column and proves each statement against its printed balances to the cent, flagging any that don't tie. You download Excel or CSV, including QuickBooks-ready and Xero-ready layouts. It doesn't connect to Wells Fargo or ask for your sign-in. It's part of the ZeraBooks subscription at $79 a month billed annually or $149 monthly, 1,000 pages a month, after a 7-day trial.

Download steps and a converted sample month are in our guide to converting a Wells Fargo bank statement to Excel.

Disclosure: I work at ZeraBooks.

A sales tax payable tie-out in 50 lines of Python, for more than one jurisdiction

Damin Mutti — Fri, 23 Oct 2026 22:02:58 +0000

A sales tax tie-out needs three checks per jurisdiction per month: each opening payable equals the prior closing, opening plus collected minus remitted equals the ledger's closing payable to the cent, and tax collected is within a small tolerance of taxable sales times that jurisdiction's rate. The first two are exact.

The third is a reasonableness test, and its gap divided by the rate points to the sales to review.

I'm on the ZeraBooks team. We published a single-rate version of this as an open example this week. The obvious next question was what happens with two states, so this is the multi-jurisdiction version, with Decimal throughout and a hint for the most common failure.

The input

One row per jurisdiction per month. Remittances come from the bank statement (what was actually paid). Closing payable comes from the ledger. Keeping those two sources separate is what lets the roll-forward catch anything.

Jurisdiction,Month,Taxable sales,Rate,Opening payable,Collected,Remitted per bank,Closing payable per ledger
State A,2026-04,41200.00,0.0725,2610.40,2987.00,2610.40,2987.00
State A,2026-05,38650.00,0.0725,2987.00,2802.13,2987.00,2802.13
State A,2026-06,44120.00,0.0725,2802.13,3194.20,2802.13,3194.20
State B,2026-05,11500.00,0.0600,655.20,690.00,655.20,690.00
State B,2026-06,12400.00,0.0600,690.00,744.00,690.00,744.00
State B,2026-07,13510.00,0.0600,744.00,810.60,744.00,1554.60

All figures are fictional. State A is the original single-rate example. State B is new, and its July has a deliberate error.

The checker

import csv, sys
from collections import defaultdict
from decimal import Decimal, ROUND_HALF_UP

CENT = Decimal("0.01")

def d(s: str) -> Decimal:
    return Decimal(s.replace(",", ""))

def check(path: str, tolerance: Decimal = Decimal("1.00")) -> list[str]:
    rows = defaultdict(list)
    with open(path, newline="") as f:
        for r in csv.DictReader(f):
            rows[r["Jurisdiction"]].append(r)

    problems = []
    for juris, months in rows.items():
        months.sort(key=lambda r: r["Month"])
        prior_close = None
        for r in months:
            m = f'{juris} {r["Month"]}'
            opening, collected = d(r["Opening payable"]), d(r["Collected"])
            remitted, closing = d(r["Remitted per bank"]), d(r["Closing payable per ledger"])

            # 1. continuity: this opening is last month's closing
            if prior_close is not None and opening != prior_close:
                problems.append(f"{m}: opens at {opening}, prior month closed at {prior_close}")

            # 2. roll-forward, to the cent
            expected = opening + collected - remitted
            if expected != closing:
                diff = closing - expected
                hint = " (equals the remittance: booked to an expense?)" if diff == remitted else ""
                problems.append(f"{m}: closing {closing}, roll-forward gives {expected}, off by {diff}{hint}")

            # 3. reasonableness: collected vs sales x rate
            rate = d(r["Rate"])
            should = (d(r["Taxable sales"]) * rate).quantize(CENT, ROUND_HALF_UP)
            gap = collected - should
            if abs(gap) > tolerance:
                implied = (gap / rate).quantize(CENT, ROUND_HALF_UP)
                problems.append(f"{m}: collected {collected}, expected {should}; gap {gap} is about {implied} of sales")
            prior_close = closing
    return problems

if __name__ == "__main__":
    tol = Decimal(sys.argv[2]) if len(sys.argv) > 2 else Decimal("1.00")
    for p in check(sys.argv[1], tol) or ["all months tie"]:
        print(p)

Standard library only. Run it as python3 check.py sales-tax.csv, with an optional tolerance as the second argument.

The output

State A 2026-06: collected 3194.20, expected 3198.70; gap -4.50 is about -62.07 of sales
State B 2026-07: closing 1554.60, roll-forward gives 810.60, off by 744.00 (equals the remittance: booked to an expense?)

Two different kinds of problem, and the checker distinguishes them.

State A, June: a reasonableness gap

The roll-forward ties: 2,802.13 plus 3,194.20 minus 2,802.13 is 3,194.20, matching the ledger. But taxable sales of 44,120.00 at 7.25% should have produced 3,198.70. The ledger collected 4.50 less. Dividing 4.50 by 0.0725 says roughly 62.07 of sales were taxed wrong or marked non-taxable.

The fix belongs in the invoices, not in the payable. Typical causes: an item flagged non-taxable by mistake, tax rounded per line instead of per invoice, a discount applied after tax.

State B, July: a roll-forward break

June closed at 744.00. July collected 810.60, which is exactly 13,510.00 times 6%, and the bank shows 744.00 paid to State B. So the payable should close at 744.00 plus 810.60 minus 744.00, which is 810.60. The ledger says 1,554.60.

The difference is 744.00, the same as the remittance. That's the signature of a payment posted to an expense account ("Taxes and licenses" is the usual one) instead of against the payable. The liability grows by every remittance, and profit is understated by the same amount. The hint in the output is just that equality check, but it identifies this case most of the time.

Choosing the tolerance

I use 1.00 by default. Per-line rounding on a busy month can legitimately produce a few cents of difference, and a tolerance of zero would flag every month. With a 5.00 tolerance the State A gap disappears, which may be fine for your purposes or may hide 62 dollars of mis-taxed sales. Pick it on purpose and write it down.

A better long-term approach is a tolerance proportional to invoice count, say half a cent per invoice, since that's the maximum per-invoice rounding error. That needs invoice counts in the input, which most sales tax reports include.

What the script deliberately doesn't do

Mixed rates within one jurisdiction. A state with county and city add-ons needs one row per rate, or the rate column needs to be an effective rate you computed elsewhere. The script assumes the rate in the file is correct.
Filing periods that aren't monthly. Quarterly filers can still check monthly, but the remittance lands once a quarter, so most months will show zero remitted and a growing payable. That's correct.
Marketplace sales. Where a marketplace collects and remits the tax, those sales shouldn't be in your taxable sales column at all.
Canada. GST/HST works as collected minus input tax credits rather than collected minus remitted alone. The same structure works with an extra column for credits.

Where this sits in a close

Sales tax payable is one account in a balance sheet reconciliation, where every asset and liability is matched to an outside document: bank and card accounts to statements, payroll liabilities to the payroll register, loans to lender statements, sales tax to the return. Cash goes first, because a remittance that cleared on the wrong date shows up there before it confuses this check.

ZeraBooks is an AI-native general ledger that does the bank and card part: it reads statements, categorizes, and proves every month against each statement's printed opening and closing balance. The AI never does the arithmetic. Entries are approved by a person and reversible. There's no live bank feed; statements are uploaded. It's $79 a month billed annually or $149 monthly, with a 7-day trial.

The full method for every account type, with a worked month, is our guide to balance sheet reconciliation.

Disclosure: I work at ZeraBooks.

Parsing RBC and TD chequing statements in Python: year-less dates, header-mapped columns and a balance proof

Damin Mutti — Fri, 23 Oct 2026 20:45:46 +0000

To parse RBC and TD business chequing statements, map the debit and credit columns by header name, because the two banks order them differently. Take each row's year from the statement period, not the row. Merge the two amount columns into one signed Amount. Then assert, with Decimal, that the opening balance plus the sum equals the printed closing balance.

I'm on the ZeraBooks team, and we've just published both layouts with a fictional sample month each as open reference data. This post is the code side: what a Canadian statement parser has to do that a U.S.-tuned one often doesn't.

The problem in one table

Quirk	Why it breaks naive parsers
Dates print as day and month, no year	A January statement starting with a late-December row gets the wrong year
Separate debit and credit columns, no minus signs	Read the wrong column and a cheque becomes a deposit
RBC and TD order columns differently	Position-based templates swap amounts and dates
Running balance often only on a day's last row	Can't check every row, only day ends and the close

I'll assume the PDF text extraction is done and you have each table row as a dict keyed by the printed header. That's the hard part, but it's library-specific; everything after it is not.

Map columns by header

Headers vary in wording between account types and years, so normalize them against a small synonym table:

import re

HEADER_MAP = {
    "date": "date",
    "description": "description",
    "cheques & debits": "debit",
    "cheques and debits": "debit",
    "withdrawals": "debit",
    "cheque/debit": "debit",
    "deposits & credits": "credit",
    "deposits and credits": "credit",
    "deposits": "credit",
    "deposit/credit": "credit",
    "balance": "balance",
}

def normalize_header(h: str) -> str:
    key = re.sub(r"\s+", " ", h.replace("($)", "")).strip().lower()
    if key not in HEADER_MAP:
        raise KeyError(f"unknown column header: {h!r}")
    return HEADER_MAP[key]

Raising on an unknown header is deliberate. Silently guessing is how a "Withdrawals" column ends up as credits.

When it raises, add the new spelling to the table after looking at a real statement. Over time the table becomes a record of every header variant you've seen, which is more useful than a fuzzy matcher that is right most of the time and silently wrong the rest. Keep the raw header in your logs too, so a support question about a strange file can be answered without the PDF.

Give each row a year

The statement header prints the period, for example December 15, 2025 to January 14, 2026. A row reading "29 Dec" belongs to 2025 and "03 Jan" to 2026. The rule: try the period's end year; if that date falls after the period end, use the start year.

from datetime import date, datetime

MONTHS = {m: i for i, m in enumerate(
    ["jan","feb","mar","apr","may","jun","jul","aug","sep","oct","nov","dec"], 1)}

def parse_day_month(s: str) -> tuple[int, int]:
    s = s.strip().lower().replace(".", "")
    m = re.fullmatch(r"(\d{1,2})\s*([a-z]{3})", s) or re.fullmatch(r"([a-z]{3})\s*(\d{1,2})", s)
    if not m:
        raise ValueError(f"unparseable date: {s!r}")
    a, b = m.groups()
    day, mon = (int(a), b) if a.isdigit() else (int(b), a)
    return day, MONTHS[mon]

def with_year(s: str, start: date, end: date) -> date:
    day, month = parse_day_month(s)
    d = date(end.year, month, day)
    if d > end:
        d = date(start.year, month, day)
    if not (start <= d <= end):
        raise ValueError(f"{s} falls outside {start}..{end}")
    return d

It accepts "05 Aug" and "AUG05" because the exact text differs by bank and format. The range check at the end catches a row from the wrong statement that slipped into the batch.

Merge to one signed Amount

from decimal import Decimal

def money(s: str | None) -> Decimal:
    if not s or not s.strip():
        return Decimal("0")
    return Decimal(s.replace(",", "").replace("$", "").strip())

def to_rows(raw_rows, start, end):
    out = []
    for r in raw_rows:
        r = {normalize_header(k): v for k, v in r.items()}
        debit, credit = money(r.get("debit")), money(r.get("credit"))
        if debit and credit:
            raise ValueError(f"both columns filled: {r}")
        out.append({
            "date": with_year(r["date"], start, end),
            "description": " ".join(r["description"].split()),
            "amount": credit - debit,
            "balance": money(r.get("balance")) if r.get("balance") else None,
        })
    return out

A note on money: this assumes English formatting (1,420.50). Some Canadian bank CSV exports in French-language profiles use a comma as the decimal separator. If you handle those, detect the locale per file rather than guessing per value.

The proof

The Cedar Row Landscaping sample (RBC, August 2026, fictional) has an opening balance of 8,240.19 and a printed closing balance of 10,742.35:

rows = [
    {"date": date(2026,8,5),  "description": "Deposit - Customer payment 2204", "amount": Decimal("3850.00")},
    {"date": date(2026,8,12), "description": "Cheque 118",                      "amount": Decimal("-1420.50")},
    {"date": date(2026,8,14), "description": "PAD - Fleet fuel card",           "amount": Decimal("-612.34")},
    {"date": date(2026,8,20), "description": "Interac e-Transfer received",     "amount": Decimal("975.00")},
    {"date": date(2026,8,27), "description": "Monthly fee",                     "amount": Decimal("-6.00")},
    {"date": date(2026,8,31), "description": "PAD - Commercial insurance",      "amount": Decimal("-284.00")},
]

opening, closing = Decimal("8240.19"), Decimal("10742.35")
assert opening + sum(r["amount"] for r in rows) == closing

8,240.19 plus 3,850.00, minus 1,420.50, minus 612.34, plus 975.00, minus 6.00, minus 284.00 is 10,742.35. The TD sample (Maple Harbour Cleaning) goes from 3,105.60 to 5,883.12 the same way.

Use the half-filled balance column too

Where the statement prints a balance, check it:

running = opening
for r in rows:
    running += r["amount"]
    if r.get("balance") is not None:
        assert running == r["balance"], f"{r['date']}: {running} != {r['balance']}"

Because the balance often appears only on a day's last row, this localizes an error to a single day instead of the whole month.

The rows that go missing

In practice the rows dropped most often are the small fee lines at month end (6.00 here, 4.95 on the TD sample), because they sit near the footer with no payee. The closing-balance assertion catches them. Keep cheque numbers in the description so they can be matched to a cheque register later.

Where ZeraBooks Convert fits

If you'd rather not maintain this per bank, ZeraBooks Convert reads bank and credit card statement PDFs, including scans, proves every statement against its printed opening and closing balance to the cent, and flags any that don't tie. Output is Excel or CSV, including QuickBooks-ready and Xero-ready layouts. It doesn't connect to banks. It's in the ZeraBooks subscription at $79 a month billed annually or $149 monthly, 1,000 pages a month, after a 7-day trial.

The non-code overview, for any bank and card, is our guide to the bank statement converter.

Disclosure: I work at ZeraBooks.