Build a Bank Reconciliation Report with Python and Excel

A bank reconciliation proves that the cash book and the bank agree, and explains exactly why their balances differ. The differences are normally timing — cheques written but not yet cashed, receipts banked on the last day that the bank will show tomorrow — plus a few items the bank recorded first, such as charges and interest. Doing it by hand means ticking lines on two printouts. Doing it in Python means matching in passes, from the most certain to the least, and making sure no line is matched twice. This page is part of Building Finance and Operations Reports.

Prerequisites

pip install pandas openpyxl xlsxwriter

cash_book.xlsx has the month's eight ledger entries and bank_statement.xlsx the seven statement lines. Both started the month at 20,000.00 and were reconciled at the end of August.

Step 1 — Load both sides and extract references

Statement narratives and ledger descriptions rarely match as text — "CHQ 100231" against "Chq 100231 Northline" — but they often share a reference. Pull cheque numbers and invoice numbers out of both with the same regular expressions:

import pandas as pd

book = pd.read_excel("cash_book.xlsx", parse_dates=["Date"])
bank = pd.read_excel("bank_statement.xlsx", parse_dates=["Date"])
bank.insert(0, "Line", [f"B{i}" for i in range(1, len(bank) + 1)])

def reference(text):
    s = text.str.upper()
    return s.str.extract(r"CHQ\s*(\d{6})", expand=False).radd("CHQ").fillna(
        s.str.extract(r"(INV-\d+)", expand=False))

book["Key"] = reference(book["Description"])
bank["Key"] = reference(bank["Narrative"])
for df in (book, bank):
    df["Cents"] = (df["Amount"] * 100).round().astype("int64")
print(book[["Ref", "Description", "Key"]].to_string(index=False))
print(bank[["Line", "Narrative", "Key"]].to_string(index=False))

Upper-casing first makes the patterns case-insensitive, and prefixing cheque numbers with CHQ stops a cheque number colliding with a number from some other reference. Lines without a recognisable reference get no key and go to the second pass. The Cents column is the amount as a whole number of cents: matching on floats is fragile — 0.1 + 0.2 is not exactly 0.3 — and the two files may even load with different types, integers on one side and floats on the other, which pandas warns about when merging. Extract Numbers and Codes from Text with pandas covers extraction patterns in detail.

Step 2 — Pass one: match on reference and amount

A shared reference and an identical amount is as certain as a match gets. Merge on both and record the pairs:

pass1 = book.dropna(subset=["Key"]).merge(bank.dropna(subset=["Key"]), on=["Key", "Cents"], suffixes=("_book", "_bank"))
assert not pass1["Ref"].duplicated().any() and not pass1["Line"].duplicated().any(), "a reference matched twice"
matches = pass1[["Ref", "Line", "Amount_bank"]].rename(columns={"Amount_bank": "Amount"}).assign(Rule="reference")
print(matches.to_string(index=False))

Three pairs: the Acme receipt by invoice number and two cheques by number. The assertion guards against a reference appearing twice on one side, which would pair lines many-to-many; if it fires, the duplicate needs a person to look at it. Cheque 100233 has a reference but is not on the statement yet — it stays unmatched.

Step 3 — Pass two: match on amount within a few days, one to one

For lines still unmatched, pair entries with equal amounts whose dates are within a tolerance — here five days, enough for normal clearing. This must be one to one: a plain merge on amount would pair the single Brightside bank credit of 4,100 with both ledger receipts of 4,100. Build the candidate pairs, sort them by date gap, and accept each pair only if neither line has been used:

left = book[~book["Ref"].isin(matches["Ref"])]
right = bank[~bank["Line"].isin(matches["Line"])]

candidates = left.merge(right, on="Cents", suffixes=("_book", "_bank"))
candidates["Gap"] = (candidates["Date_bank"] - candidates["Date_book"]).abs().dt.days
candidates = candidates[candidates["Gap"] <= 5].sort_values(["Gap", "Date_book"])

used_book, used_bank, pass2 = set(), set(), []
for c in candidates.itertuples():
    if c.Ref not in used_book and c.Line not in used_bank:
        used_book.add(c.Ref); used_bank.add(c.Line)
        pass2.append({"Ref": c.Ref, "Line": c.Line, "Amount": c.Amount_bank, "Rule": f"amount, {c.Gap} day(s)"})
matches = pd.concat([matches, pd.DataFrame(pass2)], ignore_index=True)
print(matches.to_string(index=False))

The Brightside credit on 11 September pairs with the receipt recorded on the 10th — one day apart — and the INV-2170 receipt from the 20th, nine days away, is outside the tolerance and stays unmatched. The rent pairs on the same day. Sorting by gap before accepting pairs is what makes the closest match win; Match Rows by Nearest Date with merge_asof covers a related technique, though merge_asof alone does not prevent one line being matched twice.

Step 4 — Classify what is left

Everything unmatched now falls into one of the standard categories. Ledger payments not on the statement are outstanding cheques; ledger receipts not on it are deposits in transit; statement lines not in the ledger are bank-only items, which usually need a journal. Items older than the clearing time deserve a closer look:

period_end = pd.Timestamp("2026-09-30")
book_open = book[~book["Ref"].isin(matches["Ref"])].copy()
bank_only = bank[~bank["Line"].isin(matches["Line"])].copy()

book_open["Category"] = book_open["Amount"].map(lambda a: "deposit in transit" if a > 0 else "outstanding cheque")
book_open["Age"] = (period_end - book_open["Date"]).dt.days
book_open["Investigate"] = book_open["Age"] > 5
bank_only["Category"] = "bank only — post to ledger"
print(book_open[["Ref", "Date", "Description", "Amount", "Category", "Age", "Investigate"]].to_string(index=False))
print(bank_only[["Line", "Narrative", "Amount", "Category"]].to_string(index=False))

The receipt for INV-2201 on 29 September and cheque 100233 on the 30th are ordinary month-end timing differences. The INV-2170 receipt is ten days old and still not in the bank — it may have been recorded before the money arrived, or banked into the wrong account — so it is flagged. The bank charges and interest are on the statement only and need posting to the cash book.

Step 5 — Prove the balances agree

The reconciliation statement adjusts each side for the items the other has not yet recorded. If every line was classified correctly, the two adjusted balances are equal:

opening = 20000.00
book_balance = opening + book["Amount"].sum()
bank_balance = opening + bank["Amount"].sum()

deposits = book_open.loc[book_open["Amount"] > 0, "Amount"].sum()
cheques = -book_open.loc[book_open["Amount"] < 0, "Amount"].sum()
bank_items = bank_only["Amount"].sum()

adjusted_bank = bank_balance + deposits - cheques
adjusted_book = book_balance + bank_items
statement = pd.DataFrame({"Item": ["Balance per bank", "Add: deposits in transit", "Less: outstanding cheques", "Adjusted bank balance",
                                   "Balance per cash book", "Add/less: bank-only items", "Adjusted book balance", "Difference"],
                          "Amount": [bank_balance, deposits, -cheques, adjusted_bank, book_balance, bank_items, adjusted_book,
                                     round(adjusted_bank - adjusted_book, 2)]})
print(statement.to_string(index=False))
assert round(adjusted_bank - adjusted_book, 2) == 0

Both sides come to 15,047.40. A non-zero difference means a line has been mismatched or misclassified, or a balance is wrong — the report must not be signed off until it is explained.

Step 6 — Write the reconciliation workbook

Write the statement, the matched pairs with the rule that matched each one, and the open items, so a reviewer can check any match and follow up every exception:

with pd.ExcelWriter("bank_rec_2026-09-30.xlsx", engine="xlsxwriter") as writer:
    statement.to_excel(writer, sheet_name="Reconciliation", index=False)
    matches.merge(book[["Ref", "Date", "Description"]], on="Ref").merge(
        bank[["Line", "Date", "Narrative"]], on="Line", suffixes=(" book", " bank")).to_excel(writer, sheet_name="Matched", index=False)
    pd.concat([book_open.rename(columns={"Ref": "Item"}), bank_only.rename(columns={"Line": "Item", "Narrative": "Description"})])[
        ["Item", "Date", "Description", "Amount", "Category", "Investigate"]].to_excel(writer, sheet_name="Open items", index=False)
    money = writer.book.add_format({"num_format": "#,##0.00;(#,##0.00);-"})
    writer.sheets["Reconciliation"].set_column("A:A", 28)
    writer.sheets["Reconciliation"].set_column("B:B", 14, money)
print(pd.ExcelFile("bank_rec_2026-09-30.xlsx").sheet_names)

Next month, carry the open items forward: outstanding cheques and deposits in transit from September should clear in October, and anything still open after that is an exception in its own right.

Common pitfalls

• Many-to-many matches. Merging on amount alone pairs lines repeatedly; accept each pair once.
• Loose tolerances. A wide date window matches unrelated payments of the same amount; keep it tight and let people handle the rest.
• Matching on float amounts. Convert to integer cents before merging.
• Sign conventions. Some banks export debits as positive with a separate type column; normalise to one signed amount first.
• Ignoring old open items. A deposit in transit for three weeks is a problem, not timing.
• Forcing a zero difference. Never add a balancing figure; find the line that explains it.

Performance notes

The candidate merge in pass two grows with the number of lines sharing each amount, which stays small for real statements. For tens of thousands of lines per month, restrict candidates by date window before merging, or match within each day's lines.

Conclusion

Extract cheque and invoice references from both sides, match on reference and amount first, then on amount within a few days, accepting each pair only once and closest date first. Classify what remains as outstanding cheques, deposits in transit and bank-only items, flag anything older than the clearing time, and prove that the adjusted bank and book balances agree before writing the statement, the matches and the open items.

Frequently asked questions

How do I match bank transactions to the ledger in Python? In passes. First match on something both sides share — a cheque number or invoice reference extracted with a regular expression — plus the amount. Then match what is left on equal amount within a few days, pairing each line at most once and preferring the closest date.

Why must bank matching be one to one? A merge on amount pairs every bank line with every ledger entry of the same amount. Two receipts of 4,100 would both match one bank credit, hiding the fact that one of them has not reached the bank. Pair each line once, closest date first, and leave the rest unmatched.

What goes on a bank reconciliation statement? The bank balance, plus deposits in transit, less outstanding cheques, gives the adjusted bank balance. The ledger balance, plus or minus items only the bank has recorded — interest, charges — gives the adjusted book balance. The two must be equal.

Which unmatched items need attention? Items that have been unmatched for longer than the normal clearing time — a deposit recorded a week ago that the bank has not received, a cheque months old. Recent items at month end are expected; old ones may be errors, duplicates or lost payments.

Related

• Up one level: Building Finance and Operations Reports — the pipeline these reports share.
• Build an Accounts Receivable Aging Report — the receivables side.
• Compare Two Excel Files for Differences with Python — general-purpose comparison.
• Reconcile Excel Totals with Control Totals — tie-outs in general.