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

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.