Bank reconciliation automation, step by step and without the sales pitch

Step 2. Get both sides on a schedule

Automation dies on data supply. The external side is a statement, camt.053 or MT940, delivered on a known schedule. The internal side is a ledger export with the same fields available. If either side is a person pressing export when they remember, fix that before configuring a single rule, and put duplicate file detection in place from day one.

Data quality, and the three checks worth running first

Reconciliation is unusually good at exposing data problems that every other process tolerates, because it is the only process that compares two independent records of the same event. Before configuring a single rule, three checks on a month of real files will predict most of what the project will find.

1. 01Reference survival. Take fifty items and check whether the reference on the instruction appears, in any recognisable form, on the statement entry. The proportion that survives is the ceiling on your exact match rate, and no amount of rule writing raises it.
2. 02Date discipline. Compare booking date and value date on the statement against the date your ledger uses. If your ledger keys on booking date and the statement reports on value date, every cross-cut-off item is a timing break that never needed to be one.
3. 03Amount integrity. Check how often the amount that arrives differs from the amount instructed, and whether the difference is explained by a charge. A high unexplained rate is a fee modelling gap, not a matching problem.

These three checks take a day and change the shape of the project, because they distinguish work that rules can fix from work that only an upstream change can fix. A project that promises a high match rate on data where references do not survive is promising something the data cannot deliver.

Step 3. Agree tolerances, and write down what they mean

A tolerance is permission to stop looking. Three questions make the decision defensible: what class of difference does this absorb, what could it hide, and who approved it?

Step 4. Make the break the unit of work

An unmatched item is not an error message, it is a task. It needs a classification so similar work is handled similarly, a suggested action so the analyst starts from something, an owner so it is not everybody's problem, and an age so nobody can pretend it is new.

Six classifications cover the overwhelming majority of cash reconciliation work: FX rate source, timing or value date, duplicate posting, missing reference, fee not accrued, and partial settlement. Add classifications only when a real recurring case does not fit, because a long taxonomy is used inconsistently and an inconsistent taxonomy tells you nothing.

Step 5. Enforce approvals in the system

Write-offs, reversals, tolerance changes and routing changes are the actions that can hide a problem. Each needs a maker and a checker who are different people, a reason code, and a record that survives both of them leaving the bank.

Step 6. Prove the control worked

Evidence collected while the work happens is free. Evidence assembled afterwards is a project, and worse, it invites the kind of tidying that turns a small finding into a large one. The test is simple: can you produce, for one account and one period, the reconciliation, the open items, every action with an actor and a timestamp, the approvals and the rule versions, in one export?

Step 7. Measure the right four numbers

Reconciliation programmes accumulate metrics quickly and most of them are decoration. Four numbers tell you whether the control is working, and they are worth defending against additions.

The data quality problem you will find in week two

Every reconciliation automation project discovers the same thing shortly after the first real files are loaded, and it is never the thing in the business case. The sources do not agree about identity. The same counterparty is spelled three ways across two systems, a reference is truncated at a different length by each one, and a date field that everybody assumed was the value date turns out to be the posting date in one of them.

This is not a reason to stop and it is not a reason to launch a data quality programme first. It is the reason normalization rules exist, and the discipline that keeps it manageable is to write those rules explicitly, version them, and never let them become invisible cleanup buried inside the matcher. A normalization rule that is written down can be reviewed when a match looks wrong. Cleanup that happens implicitly cannot, and it is the single most common reason a reconciliation nobody can explain gradually stops being trusted.

Who owns it after the project ends

Automation projects are staffed as projects and then handed to an operations team that was not asked whether it wanted them. When the handover includes the ability to change a tolerance, add a normalization rule and adjust a routing target, the team owns the control and improves it. When those changes require a ticket to a technical group, the team owns the consequences without owning the mechanism, and the usual outcome is a growing set of manual workarounds sitting alongside an automation nobody adjusts. The question worth settling before the build rather than after it is simply which of those two arrangements is being created.

Report exact and tolerance matches separately, always. A combined match rate of ninety eight percent can describe an excellent reconciliation or a loose one, and the split is the only thing that distinguishes them.

Suspense accounts are where automation goes to die

Every bank has an account where unresolved items go to stop being visible. It is usually justified as temporary. It usually is not. A reconciliation programme that improves match rates while a suspense balance grows has not improved anything, it has moved the problem into a place with no owner and no age.

The fix is unglamorous and effective. Give every item posted to suspense an owner and an age at the moment it is posted, require a reason code from a short list, and report the balance by age and by reason rather than as a single figure. Items that were genuinely temporary clear. Items that were being hidden become visible, which is uncomfortable exactly once.

The parallel run, and how to end it

Running the new reconciliation alongside the old process is the right way to build confidence, and it becomes a trap if nobody defines the exit. A parallel run without exit criteria runs forever, because there is always one more month that would be reassuring.

1. 01Agree the exit criteria before the parallel run starts, in writing, with the person who owns the control.
2. 02Compare break populations rather than match rates. Two processes can agree on the total and disagree on which items are open.
3. 03Investigate every item found by one process and not the other. These are the findings that matter, and there are usually fewer than ten.
4. 04Set a fixed number of periods, typically two or three closes, and stop on schedule unless a criterion failed.
5. 05Retire the old process properly, including the spreadsheet, because a spreadsheet that still exists will still be used.

The people part, which decides the outcome

The technical work in a reconciliation automation is rarely the hard part. The hard part is that a tolerance is a decision somebody has to own, and that ownership is often unclear until the question is asked directly. The person who approves a value date window is accepting a class of difference on behalf of the institution, and they should know that is what they are doing.

The second people problem is quieter. An analyst who has worked a reconciliation for years carries knowledge that exists nowhere else: which correspondent posts late, which reference format changed last year, which counterparty always deducts a fee. Automation that does not capture that knowledge as configuration loses it, and the loss shows up six months later as a break population nobody can explain. The most productive week of a reconciliation project is usually the one spent writing down what the experienced analyst already knows.

Common failure modes, and what they look like

• Automating the wrong account first. Symptom: the pilot succeeded and nothing changed. Cause: the easy account was never the cost.
• Tolerances set to make numbers look good. Symptom: high match rate, growing suspense balance. Cause: nobody separated exact from tolerance matches.
• Classification taxonomy too long. Symptom: half the breaks are classified as other. Cause: twenty categories nobody can apply consistently.
• Rules owned by the vendor. Symptom: a change request for a value date window. Cause: the tool was bought without asking who edits a rule.
• Evidence assembled at quarter end. Symptom: a week of work every quarter. Cause: the record is a by-product of the work rather than the work itself.

What good looks like after six months

• The exact match rate is high and stable, and the tolerance band is small and understood.
• The break population is worked to zero on a rhythm, and aged items are exceptions rather than a category.
• Rule changes happen in the interface, with approvals, and are visible in a change log.
• The month end has nothing to discover, because the daily control found it already.
• The evidence pack is a download that takes minutes.

Build, buy, or extend what you have

The honest answer depends on one question: how many sources, and how often do they change. A single stable source pair with a fixed format can be reconciled adequately by a well built internal process, and many banks do exactly that for years. The costs appear when the number of sources grows, when formats change, and when the evidence requirement becomes a quarterly project. At that point the recurring cost is not the matching, it is maintaining parsers, keeping rule changes controlled and reassembling history, and those are the costs a product is actually amortising.

What the first ninety days should produce

One account, both sides arriving on a schedule, tolerances written down and approved by a named person, breaks classified into a short vocabulary with owners and ages, approvals enforced in the system, and one evidence export produced end to end for a closed period. That is a complete control on one account, and it is worth far more than a partial deployment across ten. The second account then takes days rather than months, because the arguments have already been had.

You can see steps three and four running on sample data in the Reconciliation Break Resolver: move a tolerance, watch the break population change, then look at how each break is classified and routed. The product pages are bank reconciliation software and automated reconciliation software.