Python Doc & Data Automation

Reconcile a Bank Statement CSV with a Ledger in Python

Month-end reconciliation matches every line on the bank statement to an entry in the ledger. A first attempt with merge on amount and date matches about half the lines and reports the rest as unexplained: card payments that settle two days after they were booked, one customer transfer that pays three invoices, bank charges nobody entered, references typed as INV 2231 in one system and INV-002231 in the other. Worse, two £45.00 payments on the same day each match both ledger entries, producing four matches and a reconciliation that appears to balance when it does not.

Root Cause

A bank statement and a ledger describe the same money from different systems with no shared identifier. Matching is therefore a set of rules, not a join, and plain merge misapplies them in two ways. It matches all candidates at once, so duplicate amounts create many-to-many matches and the same bank line is "used" several times. And it has no notion of precedence: a weak match on amount alone can consume a ledger entry that a strong match on reference should have claimed. Real reconciliation matches in passes from strongest to weakest evidence and removes matched records from both pools after each pass, so every line is matched at most once and strong evidence always wins.

Minimal Diagnostic

Measure how ambiguous the data is before choosing rules: how many amounts repeat on each side, how far booking and value dates drift, and whether references can be extracted at all.

# pip install "pandas>=2.2"
import re
from pathlib import Path
import pandas as pd

BANK = Path("in/bank-2026-09.csv")
LEDGER = Path("in/ledger-2026-09.csv")
REF = re.compile(r"INV[\s\-]*0*(\d{3,6})", re.I)

def load(path: Path, date_col: str, amount_col: str, text_col: str) -> pd.DataFrame:
    try:
        frame = pd.read_csv(path, dtype={text_col: "string"})
    except (OSError, pd.errors.ParserError) as exc:
        raise SystemExit(f"cannot read {path}: {exc}")
    return pd.DataFrame({
        "date": pd.to_datetime(frame[date_col], dayfirst=True, errors="coerce"),
        "amount": pd.to_numeric(frame[amount_col], errors="coerce").round(2),
        "text": frame[text_col].fillna(""),
    })

if __name__ == "__main__":
    bank = load(BANK, "Date", "Amount", "Description")
    ledger = load(LEDGER, "posting_date", "amount", "memo")
    for name, f in (("bank", bank), ("ledger", ledger)):
        repeats = f["amount"].duplicated(keep=False).mean()
        refs = f["text"].str.contains(REF).mean()
        print(f"{name}: {len(f)} lines, {repeats:.0%} share a repeated amount, {refs:.0%} carry an invoice ref")
    pairs = bank.merge(ledger, on="amount", suffixes=("_bank", "_ledger"))
    lag = (pairs["date_bank"] - pairs["date_ledger"]).dt.days
    print("date lag for same-amount pairs:", lag.clip(-10, 10).value_counts().sort_index().to_dict())

bank: 612 lines, 23% share a repeated amount, 41% carry an invoice ref
ledger: 655 lines, 27% share a repeated amount, 88% carry an invoice ref
date lag for same-amount pairs: {-1: 12, 0: 301, 1: 84, 2: 61, 3: 19, 4: 3, ...}

A quarter of amounts repeat, so amount alone is ambiguous; most lags fall between zero and three days, which sets the date window; and references exist on enough lines to be the strongest first pass.

Matching passes from strongest to weakest Pass one matches on an extracted invoice reference plus equal amount. Pass two matches equal amount and the same date among remaining lines. Pass three matches equal amount within a three day window, choosing the closest date. Pass four matches one bank line to a group of ledger entries for the same customer whose amounts sum to the bank amount. Lines matched in any pass are removed from both pools before the next pass. Whatever remains is reported as unmatched. 1. Reference + amount invoice number extracted from both texts; strongest evidence 2. Amount + same date exact amount, identical date, one candidate each side 3. Amount + date window exact amount within ±3 days; nearest date wins 4. One-to-many one bank line equals the sum of several ledger lines Unmatched reported with totals; must tie out to both balances

Fix: Match in Passes and Consume Matched Lines

Each pass selects candidates, resolves ambiguity to strictly one-to-one pairs, records them, and removes them from both pools. Changed lines are commented.

# pip install "pandas>=2.2"
import re
from pathlib import Path
import pandas as pd

REF = re.compile(r"INV[\s\-]*0*(\d{3,6})", re.I)
WINDOW_DAYS = 3

def prepare(frame: pd.DataFrame, prefix: str) -> pd.DataFrame:
    out = frame.copy().reset_index(drop=True)
    out[f"{prefix}_id"] = out.index                                          # changed: stable row ids
    out["ref"] = out["text"].str.extract(REF, expand=False)                  # changed: normalised ref
    out["cents"] = (out["amount"] * 100).round().astype("Int64")             # changed: exact integer money
    return out

def one_to_one(cands: pd.DataFrame, sort_cols: list[str]) -> pd.DataFrame:
    """Keep each bank line and each ledger line at most once, best candidates first."""
    cands = cands.sort_values(sort_cols, kind="stable")
    cands = cands.drop_duplicates("bank_id", keep="first")                   # changed: no reuse of bank lines
    return cands.drop_duplicates("ledger_id", keep="first")                  # changed: no reuse of ledger lines

def reconcile(bank: pd.DataFrame, ledger: pd.DataFrame) -> tuple[pd.DataFrame, pd.DataFrame, pd.DataFrame]:
    bank, ledger = prepare(bank, "bank"), prepare(ledger, "ledger")
    matches = []

    def take(pairs: pd.DataFrame, rule: str) -> None:
        nonlocal bank, ledger
        if pairs.empty:
            return
        matches.append(pairs[["bank_id", "ledger_id"]].assign(rule=rule))
        bank = bank[~bank["bank_id"].isin(pairs["bank_id"])]                 # changed: consume
        ledger = ledger[~ledger["ledger_id"].isin(pairs["ledger_id"])]

    c = bank.dropna(subset=["ref"]).merge(ledger.dropna(subset=["ref"]), on=["ref", "cents"],
                                          suffixes=("_b", "_l"))
    take(one_to_one(c, ["bank_id"]), "reference")

    c = bank.merge(ledger, left_on=["cents", "date"], right_on=["cents", "date"], suffixes=("_b", "_l"))
    take(one_to_one(c, ["bank_id"]), "amount+date")

    c = bank.merge(ledger, on="cents", suffixes=("_b", "_l"))
    c["lag"] = (c["date_b"] - c["date_l"]).dt.days.abs()
    c = c[c["lag"] <= WINDOW_DAYS]
    take(one_to_one(c, ["lag", "bank_id"]), f"amount+{WINDOW_DAYS}d")        # changed: nearest date first

    matched = pd.concat(matches, ignore_index=True) if matches else pd.DataFrame(
        columns=["bank_id", "ledger_id", "rule"])
    return matched, bank, ledger

if __name__ == "__main__":
    from diagnose import load, BANK, LEDGER                                  # loader from the diagnostic
    matched, open_bank, open_ledger = reconcile(load(BANK, "Date", "Amount", "Description"),
                                                load(LEDGER, "posting_date", "amount", "memo"))
    print(matched["rule"].value_counts().to_string())
    print(f"unmatched: {len(open_bank)} bank lines, {len(open_ledger)} ledger lines")

reference      248
amount+date    219
amount+3d      118
unmatched: 27 bank lines, 70 ledger lines

Money is compared as integer cents. Floating-point amounts that print identically can differ in the fifteenth decimal place after arithmetic in a spreadsheet export, and a join on float equality misses them unpredictably. Sorting by date lag before de-duplicating makes the window pass choose the nearest date when two ledger entries have the same amount — a deterministic rule an auditor can follow.

Reading the Match Profile

The counts per rule are more than a progress report — they describe the health of the bookkeeping process. A month where most matches come from the reference pass means invoices carry references that customers copy into their payments. A growing share of window matches means booking dates drift further from settlement, often because card batches are posted weekly. Track the profile month to month next to the reconciliation itself:

September matches by rule Of 612 bank lines in September, 248 matched on invoice reference and amount, 219 on exact amount and date, 118 on amount within a three day window, 12 through proposed many-to-one groups, and 15 remained unmatched after classifying bank fees and interest. 612 bank lines, September 2026 Reference + amount 248 lines Amount + same date 219 lines Amount within 3 days 118 lines Many-to-one (proposed) 12 lines Unmatched after rules 15 lines

When the unmatched bar grows while the others stay flat, something upstream changed — a new payment provider with a different description format, or a ledger export that dropped the memo column. Those are process fixes, and the profile finds them faster than inspecting individual lines.

Variant Fix 1: One Payment Settles Several Invoices

A customer pays £3,840.00 for invoices of £1,200.00, £1,440.00 and £1,200.00. No single ledger line matches. For the remaining unmatched lines, look for combinations of a customer's open ledger entries within the date window that sum exactly to the bank amount:

# pip install "pandas>=2.2"
from itertools import combinations
import pandas as pd

MAX_GROUP = 4                                  # combinations grow fast; keep the search bounded

def many_to_one(bank: pd.DataFrame, ledger: pd.DataFrame, customer_col: str = "customer") -> list[dict]:
    found, used = [], set()
    for b in bank.itertuples():
        pool = ledger[(ledger[customer_col] == getattr(b, customer_col))
                      & ((ledger["date"] - b.date).dt.days.abs() <= 10)
                      & (~ledger["ledger_id"].isin(used))]
        cents = list(zip(pool["ledger_id"], pool["cents"]))
        hit = None
        for size in range(2, min(MAX_GROUP, len(cents)) + 1):
            for combo in combinations(cents, size):
                if sum(c for _, c in combo) == b.cents:
                    hit = [lid for lid, _ in combo]
                    break
            if hit:
                break
        if hit:
            used.update(hit)
            found.append({"bank_id": b.bank_id, "ledger_ids": hit, "rule": "many-to-one"})
    return found

The search is restricted to the same customer and a wider date window, and capped at four invoices, because unrestricted subset-sum search over hundreds of lines is both slow and prone to coincidental matches. The customer column has to be derived on the bank side, usually from the payer name, which is the kind of normalisation described in clean column names and whitespace in pandas. Treat many-to-one matches as proposals for review rather than automatic postings.

One bank payment matched to three invoices A bank line for 3,840.00 from Northwind on 12 September fans out to three open Northwind invoices within ten days, for 1,200.00, 1,440.00 and 1,200.00. Their sum equals the bank amount exactly, so the group is proposed as a many-to-one match and marked for review before posting. Bank 12 Sep Northwind 3,840.00 INV-2231 1,200.00 on 3 Sep INV-2240 1,440.00 on 5 Sep INV-2252 1,200.00 on 8 Sep Proposed match sum equals payment

Variant Fix 2: Bank Charges and Interest Not in the Ledger

Some statement lines have no ledger counterpart until someone posts them: account fees, interest, card terminal charges. Classify them by description before matching so they appear as "to be posted" rather than as unexplained differences:

# pip install "pandas>=2.2"
import pandas as pd

UNPOSTED_RULES = {
    "bank fee": r"\b(ACCOUNT FEE|MONTHLY FEE|CHARGES?)\b",
    "interest": r"\bINTEREST\b",
    "card fees": r"\b(MERCHANT|TERMINAL) (FEE|SERVICE)\b",
}

def classify_unposted(bank_open: pd.DataFrame) -> pd.DataFrame:
    out = bank_open.copy()
    out["category"] = "unexplained"
    for category, pattern in UNPOSTED_RULES.items():
        hit = out["text"].str.upper().str.contains(pattern, regex=True) & (out["category"] == "unexplained")
        out.loc[hit, "category"] = category
    return out

A short, reviewed rules table beats clever fuzzy matching here: it is predictable, and the unexplained remainder shrinks to lines that genuinely need a person.

Verification

A reconciliation is only valid if the numbers tie out. For each side, the total of matched lines plus the total of unmatched lines must equal the side's total, and no line may appear in two matches.

# pip install "pandas>=2.2"
import pandas as pd

def verify(bank_all: pd.DataFrame, ledger_all: pd.DataFrame, matched: pd.DataFrame,
           open_bank: pd.DataFrame, open_ledger: pd.DataFrame) -> None:
    assert not matched["bank_id"].duplicated().any(), "a bank line was matched twice"
    assert not matched["ledger_id"].duplicated().any(), "a ledger line was matched twice"
    b = prepare(bank_all, "bank")                         # same ids as during matching
    l = prepare(ledger_all, "ledger")
    matched_bank = b.loc[b["bank_id"].isin(matched["bank_id"]), "cents"].sum()
    matched_ledger = l.loc[l["ledger_id"].isin(matched["ledger_id"]), "cents"].sum()
    assert matched_bank == matched_ledger, f"matched totals differ: {matched_bank} vs {matched_ledger}"
    assert matched_bank + open_bank["cents"].sum() == b["cents"].sum(), "bank side does not tie out"
    assert matched_ledger + open_ledger["cents"].sum() == l["cents"].sum(), "ledger side does not tie out"
    print(f"tied out: matched {matched_bank / 100:,.2f}; "
          f"open bank {open_bank['cents'].sum() / 100:,.2f}, open ledger {open_ledger['cents'].sum() / 100:,.2f}")

The matched totals must be equal on both sides because every one-to-one match pairs identical amounts; if they differ, a pass allowed non-equal amounts or reused a line. The open balances on each side are what the accountant investigates and, eventually, what appears on the reconciliation statement.

FAQ

Should I allow small amount differences, such as FX rounding? Only in a separate, late pass with a very small tolerance and a distinct rule name, so those matches can be reviewed. Never mix tolerance into the exact passes.

What date should I use from the bank file? Value date if available — it reflects when money moved — otherwise booking date. Record which one, because the lag distribution differs.

How do I carry unmatched items into next month? Save the open lines with their original ids and include them as inputs next month; many timing differences clear within days.

Can this handle multiple bank accounts? Run it per account. Mixing accounts multiplies ambiguous amounts and hides transfers between your own accounts, which are best matched as their own pass.

Part of Comparing and Reconciling Spreadsheets with Python.