Bank reconciliation is one of those tasks that’s simple in theory and miserable in practice. Two lists of transactions that should match, a handful that don’t, and an afternoon spent finding out why. After doing it by hand one too many times, I automated it. Here’s the approach — and, more importantly, why I trust the result.
The problem isn’t the matching
Most transactions reconcile themselves: same date, same amount, same description. The work is in the exceptions — a payment that cleared a day late, a fee the bank added, a deposit split across two lines. A script that only handles the easy 95% just moves the pain around.
So the goal wasn’t “match everything automatically.” It was: match the obvious cases, and surface the exceptions clearly so a human can decide.
The shape of the script
The logic is deliberately unclever:
- Load both sides — the ledger export and the bank export — into normalized rows (date, amount, reference).
- Match on an exact key first: amount plus a date window of a few days.
- For anything left over, fall back to amount-only matches and flag them for review.
- Output three buckets: matched, needs review, and unmatched.
That’s it. No machine learning, no fuzzy-string heroics. The value is in the buckets, not the cleverness.
What makes it trustworthy
This is the part that matters for anyone in accounting. A tool you don’t trust is worse than no tool, because now you’re double-checking and maintaining a script.
- It never hides a discrepancy. Anything it can’t match confidently lands in “needs review.” The default is to surface, not to assume.
- It’s deterministic. Same inputs, same output, every time. I can re-run last month’s close and get last month’s numbers.
- It shows its work. Every match records why it matched, so I can spot-check a few and trust the rest.
The payoff
What used to take an afternoon now takes about five minutes of reviewing the exception bucket. The script doesn’t do the judgment — I still decide what each unmatched line means — but it does all the sorting that used to eat the day.
If you reconcile anything on a schedule, this pattern travels well: match the obvious, isolate the exceptions, and never let the tool make a decision quietly.