Every reconciliation project I've been near starts the same way: someone assumes the hard part is pulling data out of two systems, and it never is. The hard part is deciding what "matches" means when the bank posts a transaction two days after the ERP records the invoice, when a payment processor takes its fee out before the deposit lands, and when the same $500 could legitimately be one payment or two partial ones. Automate the wrong matching logic and you don't save the finance team time — you hand them a bigger, less trustworthy pile of exceptions than the spreadsheet they started with.
Exact match is the easy 10 percent
Matching a bank statement line to a ledger entry on amount, date, and reference number is the part every tool demos well, and it's also the part that was never the bottleneck — a simple join on transaction ID or invoice number handles it. The volume that actually eats analyst time is everything that doesn't line up cleanly: a customer pays $998 against a $1,000 invoice because of a wire fee, a payout batches forty transactions into a single bank deposit, or a transaction posts on the 31st on one side and the 1st on the other because of a cutoff difference. Real reconciliation automation is mostly about codifying tolerance and aggregation rules for these cases, not about writing the exact-match join.
def find_candidates(ledger_entry, bank_lines, amount_tolerance=1.00, day_window=3):
candidates = []
for line in bank_lines:
amount_diff = abs(ledger_entry.amount - line.amount)
date_diff = abs((ledger_entry.date - line.date).days)
if amount_diff <= amount_tolerance and date_diff <= day_window:
candidates.append((line, amount_diff, date_diff))
# Rank by closeness, not just "first match found" —
# a same-day exact amount beats a 3-day-old near match.
return sorted(candidates, key=lambda c: (c[1], c[2]))Many-to-one and one-to-many matches
A single bank deposit representing a batch of forty customer payments, or one invoice paid across three partial remittances, is where naive "one record equals one record" matching breaks completely. You need aggregation logic that groups candidate transactions and checks whether the sum matches within tolerance, not just pairwise comparison — and you need to store the grouping decision so a human reviewing it later can see which forty lines were rolled into which deposit, rather than trusting a black box. This is also where false positives get expensive: two unrelated $250 payments that happen to sum to the same total as a real $500 batch will match your sum-based logic and be wrong.
Don't build a system that tries to auto-resolve everything. Set a confidence threshold — exact amount and date, same reference number — for auto-match, and route everything below it to a review queue with the candidate matches pre-ranked. A reconciliation tool that silently force-matches ambiguous transactions is worse than one that surfaces them, because the errors compound silently until the month-end close finds them.
The exceptions queue is the real product
The auto-match rate is the number vendors put in the sales deck, but the thing that determines whether a reconciliation tool actually gets adopted is how painful the remaining exceptions are to clear. An exceptions queue that just dumps unmatched transactions in a table, with no candidate suggestions, no aging, and no way to mark "known timing difference, will match next cycle," pushes the manual work right back onto the analyst — it just moved from a spreadsheet to a UI. A queue that ranks likely candidates, remembers recurring exceptions (the same vendor's fee always creates a $12 gap), and lets someone approve a match in one click is the difference between reconciliation automation that reduces headcount-hours and one that just adds a login step.
An unmatched transaction that's three days old is normal. One that's ninety days old is a control failure waiting to be found in an audit. Surface age explicitly and force a resolution or a documented reason before it crosses your close deadline.
Where the data actually comes from
Bank feeds arrive as CSV exports, SFTP drops, or a bank API depending on the institution and country — expect to support at least two of these per client, because "just use the API" assumes every bank has a modern one, and plenty still don't. ERP or accounting-side data is usually easier since it's your own database or a well-documented API (Acumatica's contract-based REST endpoints, QuickBooks' API, or a direct SQL read), but the timing of when that data is considered final matters: reconciling against records that are still being edited same-day produces false mismatches that resolve themselves by morning. Snapshot the ledger side at a consistent cutoff before you run the match, not live against a table someone else might still be posting to.
Wrapping up
Reconciliation automation succeeds or fails on the matching logic, not the integrations. Exact matches were never the problem; tolerance-based fuzzy matching, many-to-one aggregation, and a genuinely usable exceptions queue are where the actual engineering effort belongs. Build the system to auto-resolve only what it can resolve with real confidence, and make the human review path fast and informative for everything else — a reconciliation tool that hides its uncertainty is more dangerous than a spreadsheet that admits it.
Independent software engineer in Nairobi specialising in Acumatica customisations, Laravel backends, and tax fiscalisation integrations across East and Southern Africa.