Bank Reconciliation Automation: Why Matching Is the Easy Part

What bank reconciliation automation actually covers

Four jobs sit under one term, and vendors sell the second as though it were all four.

Getting both sides in front of you. The statement comes from the bank, as a portal download, a file on a server, or a PDF someone emails. The ledger side comes from an accounting export. Both have to cover the same period and be readable by a machine. On a bad month this is half the elapsed time and none of the demo.

Pairing them. Statement line against ledger entry. This is what auto-match means, and every tool on the market does it competently.

Deciding the leftovers. What is a timing difference that clears next week, what is a charge nobody booked, what is an error, what is fraud.

Closing it out. Posting adjustments, ageing the outstanding items, producing the statement that gets signed. Then the same list appears next month, one month older.

Automate job two, leave one, three and four manual, and you get a faster afternoon with the same month end.

---

Why matching is the easy part

A clean match is a statement line and a ledger entry with the same amount, a close date, and a reference that ties them. Real transaction data does that most of the time, which is why the demo works. The demo is not lying.

Here is what the rest of the file looks like.

Part payments. An invoice for 48,000 gets paid as 30,000 now and 18,000 in three weeks. Neither payment matches on amount, so a tool pairing on amount and reference finds neither, and a tool pairing on customer and date window may confidently attach the wrong one.

One receipt covering many invoices. A customer settles eleven invoices with a single transfer, and the remittance advice arrives separately as a PDF, or does not arrive. Working out which subset of open invoices adds up to the amount received is a search across combinations, not a lookup. Most auto-match engines do not attempt it, and most do not tell you they are not attempting it. The mirror case, several payments against one invoice, turns up wherever instalment terms exist.

Deductions taken at source. The customer pays the invoice less a settlement discount, or less a short shipment they never raised a debit note for. Your ledger says one number, the bank says a smaller one, and the gap is not an error. Cross border receipts do a version of this on their own, arriving with the correspondent bank fee already deducted.

Timing across the cutoff. A cheque banked on the 31st, cleared on the 3rd. A payment initiated on the last working day that leaves the account after midnight. These are the reason bank reconciliation exists as a discipline, and they split "did not match" into two completely different statements: not matched yet, and not going to match.

References damaged in transit. The narration field is short, and it passes through several systems that all feel entitled to edit it. Payment rails truncate. Remitters retype invoice numbers by hand and drop a leading zero. Correspondent banks overwrite the narration.

None of this is rare. It is the standing texture of a corporate bank account, and every one of these lands the same way: an item in the unmatched pile that a person now has to think about.

---

The rules that live in the workbook

Ask a finance team what their matching tolerance is. You get a pause, then an approximation, then someone opening a spreadsheet to check.

The tolerance is the difference you will accept while still calling two transactions the same transaction. It absorbs rounding, small fees and currency movement. It is an accounting policy with a real number attached, and in most teams that number exists in exactly one place: the third argument of an IF statement in column K. Nobody remembers who chose it. It may be an absolute amount where it should be a percentage, and on a large receipt those two answer very differently.

That is one rule. The workbook has dozens, accumulated since somebody built the first version of it years ago. A lookup into a mapping tab translating the eleven ways one account holder appears in bank narrations into a single ledger code, maintained by hand, mostly. A column of colour fills where amber means "asked the customer, waiting" and red means "checked, genuinely wrong", a workflow state living in a cell format. A hardcoded exclusion inside a SUMIFS with no comment saying why.

That workbook is the specification for the process, and it was never written to be read as one. The same failure stalls Excel automation projects generally. Reconciliation is its purest form, because here the workbook is not a report. It is the control.

Before evaluating anything, get the rules stated out loud: the tolerance, whether it is absolute or proportional, what the colours mean, which items are excluded and on whose authority. That conversation is slow and boring, and it kills more reconciliation projects than any technical limitation.

---

The unmatched pile is the actual work

Here is the arithmetic vendor pages avoid.

The lines that match cleanly are also the lines a person clears fastest. Sort by amount, eyeball the obvious pairs, tick them off. Tedious, but quick. The residue is slow: chasing a remittance advice, emailing a customer, reading the full narration in the portal, deciding whether a difference is a fee or a shortfall. So automating the clean pairs removes the fast part of the job and leaves the part that was consuming the clock, which makes match rate close to useless for choosing a tool.

A good unmatched pile gives every item an age, a reason, an owner, and whatever evidence the system already gathered: the full unedited narration, and the candidate ledger entries it considered and rejected. Near misses matter more than matches. An item that failed only on tolerance is nearly resolved, and listing it as "unmatched" next to a transaction that appears nowhere in the ledger throws away work the machine already did. The pile also has to shrink. Items carried forward month after month without review are how a reconciliation stays technically complete while being wrong.

---

The five ways teams automate reconciliation

Excel

Still where most reconciliations happen, including at organisations that bought software specifically to replace it.

A workbook with the statement on one tab, the ledger on another, a matching column and a few conditional formats is genuinely good at this. It is transparent, an auditor can follow it, and the owner can change a rule at 9pm on the last working day. Power Query handles the import side without code, and because its steps refer to columns by name it survives the bank changing its export layout.

Where it stops. VLOOKUP and its relatives are one to one, so the combination search that solves the eleven invoice receipt stays manual permanently. The workbook accumulates the undocumented rules above and becomes unmaintainable by anyone except its author.

Best fit: one account, moderate volume, a competent Excel owner who also handles the exceptions.

The bank's own tools

Underrated, and often free with the account. Corporate portals deliver structured statement files rather than PDFs, in formats like MT940, BAI2 or CAMT.053, which removes a whole class of parsing pain. Virtual account numbers are the strongest available fix for the reference problem: give each customer a unique account to pay into and identification becomes structural instead of a text matching exercise.

Where it stops. Everything the bank offers is scoped to that bank, so two banks means two portals, two formats, and a reconciliation stitched back together by hand anyway. And the bank knows nothing about your ledger.

Best fit: one main banking relationship, a treasury function, receipts that can be routed to virtual accounts.

Auto-match in the accounting software

Every modern ledger does this. Feed in, rules engine on top, suggested matches presented for approval. For a business with straightforward receipts it can reduce the job to a few minutes of confirmation, and dismissing it is a mistake.

Where it stops, in the order teams hit it. The rules engines are shallow: contains this text, equals this amount, this supplier. Combination matching is usually absent. The rules themselves become a second undocumented specification, this time in a settings screen with no version history. And the automation stops at the edge of the product, so if the deduction is explained in a PDF in a shared mailbox, the ledger cannot see it.

Best fit: one entity on one accounting system, high volume of recurring similar transactions, few disputes.

RPA through a CoE

RPA is right for the parts either side of the matching. Log into the portal, download the statement for the right date range, export the ledger, run the comparison, drop the output where it belongs, email the exception list. Enterprise RPA runs that chain with scheduling, audit logs and an exception queue somebody is paid to watch. If your reconciliation spans several banks and several entities, this is what holds it together.

The ceiling is economics rather than capability. A Center of Excellence prioritises its queue by volume and criticality, which is the correct way to run it, and a reconciliation one person does for three hours a month does not clear that bar. That is the last mile, a structural outcome rather than anyone making a bad call. The bot also runs the process without understanding it, so the tolerance rule and the colour codes move into a business analyst's document and go stale.

Best fit: several banks or entities, funded, high volume, a CoE owning it afterwards.

GPA

Guerrilla Process Automation starts somewhere else. The person who runs the reconciliation already knows the rules, and the workbook already encodes them, so build from those two sources instead of from a requirements document.

That splits across the suite. I² Recorder records the browser side: the login, the date range, the statement download, the portal lookup you do when a narration is unclear. Debrief is aimed at the workbook, reading the file and working out what process it represents from the structure, so the tolerance and the exclusions and the meaning of the colours come out as stated, reviewable rules. Deputy is the design answer to the unmatched pile: the automation stops on an ambiguous item, asks, and resumes from where it paused rather than failing the run.

Two honest notes. I² Recorder is pre-launch and Debrief comes after it, so this describes what is being built. And the privacy question applies here as much as anywhere: a workbook's logic is in its structure, the sensitive part is its figures, so ask which of those leaves the machine.

Best fit: recurring reconciliation with real accumulated logic, no developer available, too small to ever be funded.

---

How the approaches compare

The fifth row decides more outcomes than the rest. Every approach here works on a clean month. They diverge on the month where nine things did not match and two of them mattered.

---

What this looks like in real work

The cutoff week

Timing differences are at their worst in the first working days of the month, which is when everyone wants the number. Automation that treats "not matched" as one category is useless here, because half those items clear on their own within days and the other half never will.

The customer who pays in a lump

One transfer, eleven invoices, a remittance advice sitting in a shared mailbox as a PDF. Solving it means reaching outside the reconciliation entirely: open the mailbox, find the attachment, read the invoice numbers off it, then match. That is the shape of work covered in back office automation.

The statement that only comes as a PDF

Smaller banks, some overseas accounts, almost every credit card. Someone retypes it, or pastes it into a staging tab where the block lands in a different position each time, so anything reading a fixed range is already broken. And the output feeds a cash position in the monthly pack, so every unresolved item here becomes a footnote in MIS report automation.

---

How to choose an approach

Six questions. The first one you answer honestly usually decides it.

1. Can you state your matching rules out loud right now, in under ten minutes? Tolerance, date window, what the colours mean, which items are excluded. If not, stop here. Choosing a tool first is an hour you will spend twice.

2. How much of last month's residue was combinations or part payments? Count it, do not estimate. If it is most of the pile, a rules engine will not move your month end.

3. Does the automation have to fetch the statement? If a person logs into a portal every month, half the job sits outside whatever tool you are evaluating.

4. One bank and one entity, or several? Several is the line where this stops being an Excel problem and becomes a case you can genuinely take to a CoE.

5. What does the tool do with an item it cannot decide? Ask for a demo on a messy file, not a clean one. A tool that quietly drops ambiguous items into a tab is worse than no tool, because the residue now looks smaller than it is.

6. Who owns this in eighteen months? Say the name. If the honest answer is the person who built the workbook and they are on a contract, pick the approach that leaves the rules readable by somebody else.

---