Reconciliation by Design, Not by Spreadsheet

FinTech Engineering

Reconciliation is not a month-end spreadsheet exercise. I design it as a control system that compares independently produced financial facts, proves completeness, classifies differences, and routes exceptions to accountable owners without erasing evidence. The settlement cases below are synthetic.

Independence is the point

Comparing a balance projection with another projection built from the same tables can reproduce the same defect twice. A meaningful control compares records created through independent paths: for example, an internal ledger against a processor settlement file, a bank statement, or a scheme report.

The principle is consistent with BCBS 239, which emphasizes accuracy, completeness, timeliness, adaptability, and traceability in risk-data aggregation. BCBS 239 directly applies to banks’ risk-data capabilities, not to every payment company; here it is a rigorous analogue for designing financial-data controls. The Basel Committee’s January 2026 implementation newsletter also identifies fragmented data estates, weak lineage, and manual processes as continuing obstacles.

Prove completeness before comparing values

A matching engine cannot detect a missing file it never expected. Begin each run with a source manifest:

• source, business date, timezone, and cutoff;
• file or API page sequence and expected count;
• record count, control total, and cryptographic checksum where available;
• schema and producer version;
• ingestion start, completion, and retry history.

Quarantine malformed or incomplete deliveries. Do not mark a business date reconciled because the records received happen to match. A green result requires evidence that the population itself is complete.

Normalize without destroying provenance. Convert external identifiers, signs, currencies, decimals, and timestamps into a comparison model, but retain the raw source reference and transformation version. ISO 20022’s standards repository provides official payment and cash-management message definitions; a local implementation must still pin the message version and market-practice profile it accepts.

Matching is a sequence of explainable rules

Start with deterministic exact matches using stable references, amount, currency, and expected date. Then apply controlled rules for known differences:

1. one-to-one: one ledger movement equals one external movement;
2. one-to-many: one settlement deposit corresponds to many captured payments;
3. many-to-one: split internal postings correspond to one provider record;
4. timing: both facts are valid but fall on different business dates;
5. value: amounts differ because of fees, foreign exchange, rounding, or an error;
6. missing/duplicate: a fact exists on one side only or appears more than once.

Every rule needs an identifier, version, effective date, owner, priority, and test set. Record which rule produced a match and the evidence it consumed. Tolerances must be justified by currency, rail, or contract; “less than 1” is not a safe global rule. Automatic write-offs need separately approved thresholds and aggregate monitoring so many small differences cannot hide a material loss.

Exceptions are products, not leftovers

An unmatched record should become a durable case with a reason category, amount at risk, age, owner, service-level target, and links to both source facts. Distinguish “awaiting expected late file” from “unknown value difference” and “suspected duplicate.” That lets operations prioritize risk instead of working a flat queue.

Investigation notes and evidence belong to the case. If an adjustment is approved, post a new balanced ledger transaction with the case identifier, reason code, approver, and effective date. Never edit the original entry to force a match. Re-run the comparison after the correction and retain both the pre- and post-adjustment results.

Ageing matters as much as counts. Monitor unreconciled value and item count by reason and age bucket, source completeness failures, late deliveries, tolerance usage, manual-adjustment volume, reopened cases, and the percentage matched by each rule version. A falling exception count can be bad news if a broad new rule is swallowing genuine breaks.

Design the operating boundary

The ledger owns internal postings. The ingestion layer proves and stores external evidence. The reconciliation service compares normalized facts and records decisions; it does not silently create money movements. The case workflow owns investigation and approvals. A posting service executes approved adjustments through the same ledger controls as any other financial transaction.

This separation makes replay safe. If a parser or matching rule changes, rebuild normalized facts and match decisions from immutable source snapshots. Compare old and new results before promoting the rule. Do not re-download a mutable provider endpoint and assume it represents what was originally received.

Test the control, not only the code

Use synthetic scenarios for missing files, truncated pages, duplicate records, daylight-saving cutoffs, one-to-many settlements, partial refunds, fee netting, currency precision, late reversals, and provider corrections. Inject a defect into a complete dataset and verify that the expected exception is created, aged, approved, adjusted, and closed with evidence.

Also test segregation of duties: the person who changes a matching rule should not approve the breaks it automatically resolves. Access to raw account data, case evidence, and adjustment actions should be independently authorized and logged.

The design decision

Reconciliation is a continuous, evidence-preserving control loop. Prove the population, normalize with lineage, match through versioned rules, classify every difference, and correct through controlled new entries. A spreadsheet can support investigation, but it cannot be the authoritative workflow. When reconciliation is designed into the platform, every green result explains what was compared, which rules were used, and who accepted the remaining risk.

Related reading

• ISO 20022 as a Domain Model, Not an XML Problem
• Double-Entry Ledger Architecture for Product Engineers

References

• Basel Committee — Principles for effective risk data aggregation and risk reporting
• Basel Committee — BCBS 239 implementation newsletter, January 2026
• ISO 20022 — Standards repository