Automating reconciliations: bank, cash receipts, and subledger matching

Reference article · 5 min read

Monthly reconciliation is one of the few finance processes that is almost entirely comparison. A bank statement line has to be matched to a recorded deposit, a card settlement batch to the payments posted in a utility billing or permitting system, a subledger balance to its general ledger control account. Very little of that requires judgment until something fails to match. Yet in many agencies it consumes days of clerical time every month, and when a position goes vacant it slips, sometimes by several cycles. Backlogged reconciliations are also how small errors and misappropriations stay hidden longest.

Automation helps because matching is deterministic more often than people expect. The goal is not a system that closes the books on its own. The goal is a system that clears routine matches quietly, leaves a small and clearly described set of exceptions for a person to work, and produces a record an auditor can follow without asking staff to reconstruct it from memory.

Know which reconciliations you are automating

These are different problems with different data, and they are worth separating before you buy or build anything.

• Bank to book. Statement or BAI2/ISO 20022 activity compared to cash receipts, disbursements, and wires recorded in the ledger. High volume, strong identifiers, the best candidate for automation.
• Merchant and gateway settlement. Card and ACH batches compared to the payments recorded in the originating system. Complicated by gross versus net deposits, fees, refunds, and chargebacks landing in later periods.
• Subledger to general ledger. Utility billing, receivables, payroll, or fixed asset balances compared to control accounts. Usually a balance and activity comparison rather than line matching, and it fails through interface errors more often than through data entry.
• Clearing, suspense, and interfund accounts. Low volume, high audit interest, and the place where unresolved items quietly accumulate.

Rules should carry most of the volume

Well written rules handle the large majority of lines without any modeling. Design them in tiers, most specific first.

• Exact key match. Trace number, settlement batch ID, deposit slip number, check number. If the identifier exists, carry it end to end. The single highest return change most agencies can make is capturing the deposit or batch identifier at the point of receipt so it survives into both systems.
• Amount and date window. One-to-one on amount within a tolerance of a few days, then one-to-many for a deposit that aggregates several receipts, then many-to-many for pooled batches.
• Normalization. Strip bank descriptor noise, standardize department prefixes, and map recurring counterparties before matching rather than writing a rule per variation.
• Tolerances. Set a small rounding tolerance deliberately and log every match that used it. Unlimited tolerance is how differences disappear instead of getting explained.

Where machine assistance earns its place

Machine-assisted matching is useful on the residual: free text descriptions, transposed references, recurring items an analyst matches the same way every month. Systems that learn from prior analyst decisions can rank candidate matches and shorten the search. Treat those outputs as suggestions with a confidence score and a stated reason, and require a person to accept anything that creates or adjusts a journal entry. A proposal you cannot explain is not usable as audit evidence, so insist that the system show which fields drove each suggestion.

Design the exception queue as the actual work product

The queue, not the match rate, is what staff live in.

• Classify by reason, not only by amount: timing difference, missing in subledger, missing in bank, duplicate, fee or adjustment, unidentified deposit.
• Age every item from the date it first appeared, and report aging to the finance director each close.
• Assign ownership so items route to the department that can resolve them.
• Constrain dispositions to a short list, each requiring a comment, so "resolved" always carries a reason.
• Review suppression rules periodically. Anything auto-cleared by a rule nobody revisits is an unmonitored control.

Controls and the evidence auditors still expect

Automation changes who prepares the reconciliation, not who reviews it. Keep preparer and reviewer separate, and treat the software as the preparer. In general, auditors look for a reconciliation tied to a stated cutoff, a complete list of reconciling items with explanations and aging, evidence of timely independent review, and support for any journal entries produced. They will also ask how the automated matching works and what can bypass it, so retain rule versions and change history alongside the matched detail, not just the summary. Standards and record retention requirements differ by state and change over time, so confirm specifics with your external auditor, your state records schedule, and your counsel.

What to ask vendors, and what to put in the contract

• Which source formats are ingested natively, and what happens when a bank changes its file layout.
• Whether rules are configurable by finance staff without a vendor work order, and what that costs if not.
• How suggested matches are explained, and whether confidence thresholds are agency-controlled.
• Whether the full matched detail, exception history, and rule versions are exportable in open formats on demand and at termination.
• Contract terms worth insisting on: data ownership and export at no additional charge, audit support obligations, security documentation appropriate to banking data, and acceptance criteria tied to reconciling one real historical month before go-live.

How to start

1. Pick one high volume reconciliation, usually the main operating bank account.
2. Reconcile a recent closed month by hand and categorize every exception by reason.
3. Write rules against those categories and replay three to six months of history.
4. Fix identifier capture upstream where the replay shows it is missing.
5. Run parallel for a cycle or two, then move review, not preparation, to staff.

HQT works with state and local agencies on finance system integration, data matching, and automation of this kind.

Frequently asked questions