Automated Bank Reconciliation with Python

What is automated bank reconciliation?

Automated bank reconciliation uses software to automatically match transactions between your bank statement and general ledger, flag unmatched items as exceptions, and produce an audit-ready report -- without manual row-by-row comparison in Excel. For finance teams processing hundreds of transactions per period, this cuts reconciliation time from hours to minutes.

Build automated bank reconciliation with Python

Step 1: Load and normalise your data

import pandas as pd

bank = pd.read_csv("bank_statement.csv", parse_dates=["Date"])
bank.columns = bank.columns.str.strip().str.lower().str.replace(" ", "_")

ledger = pd.read_csv("gl_export.csv", parse_dates=["Posting_Date"])
ledger.columns = ledger.columns.str.strip().str.lower().str.replace(" ", "_")

Step 2: Exact match on amount + date

exact = pd.merge(
    bank, ledger,
    left_on=["amount", "date"],
    right_on=["amount", "posting_date"],
    how="inner", suffixes=("_bank", "_ledger")
)

matched_b = set(exact["index_bank"]) if "index_bank" in exact.columns else set()
unmatched_bank = bank.loc[~bank.index.isin(matched_b)]
unmatched_ledger = ledger.loc[~ledger.index.isin(set())]
print(f"Exact matched: {len(exact)} | Unmatched: {len(unmatched_bank)}")

Step 3: Fuzzy matching for timing differences

Bank settlement delays mean a ledger payment on the 31st may appear on the 1st in your bank. Handle with a date tolerance window:

def fuzzy_match(unmatched_bank, unmatched_ledger, date_tol=3, amt_tol=0.01):
    matches = []
    for _, b in unmatched_bank.iterrows():
        candidates = unmatched_ledger[
            (abs(unmatched_ledger["amount"] - b["amount"]) <= amt_tol) &
            (abs((unmatched_ledger["posting_date"] - b["date"]).dt.days) <= date_tol)
        ]
        if len(candidates) == 1:
            matches.append({
                "bank_idx": b.name,
                "ledger_idx": candidates.iloc[0].name,
                "match_type": "fuzzy_date",
                "days_diff": int((candidates.iloc[0]["posting_date"] - b["date"]).days)
            })
    return pd.DataFrame(matches)

Step 4: Export audit-ready reconciliation report

from openpyxl import Workbook
from openpyxl.styles import PatternFill

wb = Workbook()
ws = wb.active
ws.title = "Matched"

ws_exc = wb.create_sheet("Exceptions")
red = PatternFill("solid", fgColor="FFC7CE")

for _, row in unmatched_bank.iterrows():
    ws_exc.append(row.tolist())
    for cell in ws_exc[ws_exc.max_row]:
        cell.fill = red

wb.save("bank_reconciliation_report.xlsx")

Automated payment reconciliation with Power Automate

For teams on Microsoft 365, schedule the Python script via Power Automate: a daily cloud flow triggers the script via an Azure Function, and posts exceptions to a Teams channel for same-day review. This combines custom Python matching accuracy with Power Automate orchestration and notifications.

Automate bank reconciliation -- how long does it take to build?

A working Python bank reconciliation script for a single entity typically takes 2-3 days to build and test. Adding fuzzy matching tuned to your data and Excel report formatting adds 1-2 more days. We typically deliver complete reconciliation automation pipelines in 1-2 weeks.

We build automated reconciliation systems for finance teams globally -- serving US, UK, Australian and Indian clients remotely. Get a free workflow audit.