Reconciliation

Deep dive

How it works

In: two normalized record sets (own side, external side) + date window · Out: matches (exact, windowed, grouped) and an exception queue · State: ingested lines keyed by (source, statement, line), open exceptions · Cost: O(n + m) reference join; O(n log n) windowed matching

1. Ingest each source idempotently: key every external line by (source, statement id, line number) or the provider's id, so re-imports don't duplicate.
2. Normalize: parse amounts straight into integers (money representation), map signs to one convention, convert local value dates to a common day number, extract references (end-to-end id, PSP reference, payout id) from free-text fields with per-bank rules.
3. Pass 1, reference join: hash the right side by reference; for each left record pick the unused candidate with the same currency and amount (exact) or report the same-reference candidate as an amount mismatch (fees, FX, partial).
4. Pass 2, amount + window: bucket unmatched records by (currency, amount); within a bucket match each left record to the closest unused right record whose date lies within ±w days (w = 1-3 for bank transfers, wider for cards).
5. Pass 3, grouping: aggregate the internal side by payout/batch id (Σ charges − refunds − fees) and match the sums against single bank lines. General subset-sum matching without a group key is NP-hard; get the group key from the processor's settlement report instead.
6. Exceptions: everything unmatched or mismatched goes to a queue with age, amount and a reason; resolutions are recorded as ledger adjustments, never as edits of either source.
7. Report: matched totals per side must reconcile to statement opening/closing balances; a balance check catches missing lines that one-by-one matching cannot.

Code

use std::collections::HashMap;

#[derive(Clone, Debug)]
pub struct Rec { pub id: u32, pub reference: Option<String>, pub currency: [u8; 3], pub amount: i64, pub day: i32 }

#[derive(Debug, PartialEq, Eq)]
pub enum Kind { Exact, AmountMismatch { diff: i64 }, Window { day_diff: i32 } }

#[derive(Default, Debug)]
pub struct Outcome { pub matched: Vec<(u32, u32, Kind)>, pub left_only: Vec<u32>, pub right_only: Vec<u32> }

pub fn reconcile(left: &[Rec], right: &[Rec], window_days: i32) -> Outcome {
    let (mut used_l, mut used_r) = (vec![false; left.len()], vec![false; right.len()]);
    let mut out = Outcome::default();

    // Pass 1: hash join on reference.
    let mut by_ref: HashMap<&str, Vec<usize>> = HashMap::new();
    for (j, r) in right.iter().enumerate() {
        if let Some(x) = r.reference.as_deref() { by_ref.entry(x).or_default().push(j); }
    }
    for (i, l) in left.iter().enumerate() {
        let Some(cands) = l.reference.as_deref().and_then(|x| by_ref.get(x)) else { continue };
        let exact = cands.iter().copied()
            .find(|&j| !used_r[j] && right[j].currency == l.currency && right[j].amount == l.amount);
        let Some(j) = exact.or_else(|| cands.iter().copied().find(|&j| !used_r[j])) else { continue };
        used_l[i] = true;
        used_r[j] = true;
        let kind = if exact.is_some() { Kind::Exact } else { Kind::AmountMismatch { diff: right[j].amount - l.amount } };
        out.matched.push((l.id, right[j].id, kind));
    }

    // Pass 2: same currency and amount, closest date within the window, one-to-one.
    let mut by_amt: HashMap<([u8; 3], i64), Vec<usize>> = HashMap::new();
    for (j, r) in right.iter().enumerate() {
        if !used_r[j] { by_amt.entry((r.currency, r.amount)).or_default().push(j); }
    }
    let mut order: Vec<usize> = (0..left.len()).filter(|&i| !used_l[i]).collect();
    order.sort_by_key(|&i| (left[i].day, left[i].id));                  // deterministic greedy order
    for i in order {
        let l = &left[i];
        let Some(cands) = by_amt.get(&(l.currency, l.amount)) else { continue };
        let best = cands.iter().copied()
            .filter(|&j| !used_r[j] && (right[j].day - l.day).abs() <= window_days)
            .min_by_key(|&j| ((right[j].day - l.day).abs(), right[j].day, right[j].id)); // closest, earliest, id
        if let Some(j) = best {
            used_l[i] = true;
            used_r[j] = true;
            out.matched.push((l.id, right[j].id, Kind::Window { day_diff: right[j].day - l.day }));
        }
    }

    out.left_only = (0..left.len()).filter(|&i| !used_l[i]).map(|i| left[i].id).collect();
    out.right_only = (0..right.len()).filter(|&j| !used_r[j]).map(|j| right[j].id).collect();
    out
}

/// Pass 3 input: collapse internal records into one record per group (e.g. payout id).
pub fn group_by_ref(recs: &[Rec], group_of: impl Fn(&Rec) -> Option<String>) -> Vec<Rec> {
    let mut sums: HashMap<(String, [u8; 3]), (i64, i32, u32)> = HashMap::new();
    for r in recs {
        if let Some(g) = group_of(r) {
            let e = sums.entry((g, r.currency)).or_insert((0, r.day, r.id));
            e.0 += r.amount;
            e.1 = e.1.max(r.day);                                          // payout date = last item
        }
    }
    let mut v: Vec<Rec> = sums.into_iter()
        .map(|((g, c), (a, d, id))| Rec { id, reference: Some(g), currency: c, amount: a, day: d }).collect();
    v.sort_by_key(|r| r.id);                                               // HashMap order is random
    v
}

Complexity and constants

• Pass 1: O(n + m) expected (hash join; sort-merge join for data larger than memory).
• Pass 2: O(n log n) for the sort plus the bucket scans; buckets for popular amounts (9.99) get large, so sort each bucket by day and binary-search the window if they exceed a few hundred items.
• Volumes: a processor with 10⁶ charges/day produces 10⁶-row reports; in-memory reconciliation of that is seconds; SQL hash joins handle it as well.

Efficiency

Cost: the bound is reading and parsing the statement files plus hash or sort memory; matching is linear. A processor with 10^6 charges/day produces 10^6-row reports; in-memory reconciliation of that takes seconds (order of), and SQL hash joins handle it too.

Levers

• Your own reference in every payment — pass 1 matches nearly everything, fuzzy passes see only leftovers — whenever you control the outbound id.
• Bucket leftovers by (currency, amount), sort by day, binary-search the window — avoids bucket scans — popular amounts.
• Incremental runs — new day plus open exceptions, not full history — daily operation.
• Control totals first — counts, sums, opening + lines = closing; skip fuzzy passes if they agree — clean days.
• Partition by account and currency, keep sorted iteration inside — parallel and deterministic — many accounts.

Naive → standard → state of the art

• Naive: eyeball two exports in a spreadsheet · hours per day, errors unnoticed
• Standard: one SQL FULL OUTER JOIN on (reference, currency, amount) for the daily batch · seconds at 10^6 rows (order of)
• State of the art: cascade of strict-to-loose passes with grouped payout matching and an owned exception queue · linear plus sort, streaming only for intraday deadlines

Implementation notes

• References: put your own id into every outgoing payment (ISO 20022 EndToEndId, up to 35 characters; processor metadata / merchant reference). Pass 1 then matches nearly everything.
• Dates: banks report booking date and value date; processors report creation, capture and payout dates in their own time zone. Normalize to one date convention per source and use a window that covers cut-off times and weekends.
• Signs and fees: decide one sign convention (inflow positive). Expect netting: payout = Σ captures − Σ refunds − fees − chargebacks ± adjustments.
• FX: match on the settlement currency amount actually received; record the implied rate and the FX difference as its own exception type if outside tolerance.
• Tolerance: allow small differences only with an explicit reason code (rounding, known fee), never as a silent "close enough".
• Duplicates: two identical amounts on the same day with no reference are inherently ambiguous; the deterministic tie-break makes the result stable, and a human checks the bucket if counts differ.
• Idempotent runs: store match results with a run id; re-running on the same inputs must give the same matches (sorted iteration, no HashMap order dependence, as in group_by_ref).
• Balance proof: per account and day, opening balance + Σ statement lines = closing balance; check before matching to detect missing statements.

Pitfalls

• Float parsing of statement amounts ("1234.10" → 1234.0999…).
• Many-to-many fuzzy matching: "these 3 equal those 2" hides errors; insist on a group key.
• Greedy matching in input order: results change with file order. Sort first.
• Treating a reversed or returned payment (SEPA/ACH return days later) as a new unrelated credit.
• Marking exceptions as "resolved" without a ledger entry: the next run finds the same difference.
• Ignoring aged exceptions: unmatched incoming money may be a customer's payment you never credited.

Variants and alternatives

In production

Payments: finance operations of payment companies; processors provide reconciliation reports for this purpose (e.g. Stripe's payout reconciliation report, Adyen's settlement details report). Banking and brokerage: bank treasury systems, broker back offices. Telecom: roaming usage and settlement processing, e.g. Oracle BRM roaming documentation.

Implementations

Sources

• ISO 20022 message definitions, BankToCustomerStatement (camt.053), BankToCustomerAccountReport (camt.052), BankToCustomerDebitCreditNotification (camt.054). spec
• SWIFT, MT940 Customer Statement Message (Category 9 standards).
• Stripe documentation, Payout reconciliation report. docs