User story: bank reconciliation

The story

Priya gets two files at month-end: gl_april.xlsx (the general ledger from the accounting system) and bank_april.csv (the bank statement). She has to find every transaction on one side that isn't on the other and investigate it.

In Excel: copy both into a workbook, build a helper column of date+amount, VLOOKUP across, scroll through #N/A results. Two hours.

In Python: 25 lines. Five minutes. Below.

The script

import pandas as pd
from pathlib import Path

gl   = pd.read_excel("gl_april.xlsx")
bank = pd.read_csv("bank_april.csv")

# Normalise both sides to the same key
def keyify(df, date_col, amount_col):
    df = df.copy()
    df["date"] = pd.to_datetime(df[date_col]).dt.date
    df["amount"] = df[amount_col].round(2)
    df["key"] = df["date"].astype(str) + "_" + df["amount"].astype(str)
    return df

gl_k   = keyify(gl,   "TxnDate", "Amount")
bank_k = keyify(bank, "PostDate", "Amount")

merged = gl_k.merge(bank_k, on="key", how="outer",
                    suffixes=("_gl", "_bank"), indicator=True)

unmatched_gl   = merged[merged["_merge"] == "left_only"]
unmatched_bank = merged[merged["_merge"] == "right_only"]

with pd.ExcelWriter("reconciliation_april.xlsx") as w:
    unmatched_gl.to_excel(w,   sheet_name="GL only",   index=False)
    unmatched_bank.to_excel(w, sheet_name="Bank only", index=False)
    merged[merged["_merge"]=="both"].to_excel(w, sheet_name="Matched", index=False)

print(f"GL only:   {len(unmatched_gl)}")
print(f"Bank only: {len(unmatched_bank)}")

Handling near-matches

If amounts can be off by a small fee, allow a tolerance:

def near_match(amt_a, amt_b, tol=0.50):
    return abs(amt_a - amt_b) <= tol

# For each unmatched GL row, search bank rows within ±3 days for a near-amount match.
for _, gl_row in unmatched_gl.iterrows():
    candidates = bank[
        (bank["PostDate"] >= gl_row["TxnDate"] - pd.Timedelta(days=3)) &
        (bank["PostDate"] <= gl_row["TxnDate"] + pd.Timedelta(days=3)) &
        (bank["Amount"].apply(lambda a: near_match(a, gl_row["Amount"])))
    ]
    if len(candidates) == 1:
        print(f"Possible fuzzy match for GL {gl_row['TxnId']} ↔ Bank {candidates.iloc[0]['BankRef']}")

Video walkthrough script (for recording)