How to test ledger reconciliation against settlement files and bank statements

Where a green reconciliation run hides missing money

The defect this procedure targets is a matcher that reports zero breaks while money is missing. A refund posts at the processor but never in the ledger, a retried webhook posts a capture twice, or a payout lands at the bank three days late and still matches because the date window is wide. Each of these passes a test suite built on one day of clean files, unique amounts, and same-day settlement.

In production the gap shows up as a ledger total above the balance of the safeguarding or for-benefit-of (FBO) account. In Canada, the safeguarding framework of a payment service provider that holds end-user funds includes a ledger of the funds held for each end user at the end of each day, and the provider takes measures to identify, as soon as feasible, any instance where those funds would not be payable after an insolvency event (RPAR 15(2)(b) and 16(1), checked on 2026-09-22). A silent matcher defeats both duties; the RPAA testing requirements list the evidence the Bank of Canada asks for. In the EU, Regulation (EU) 2022/2554 (DORA) Article 12(7) requires checks and reconciliations after recovery from an ICT-related incident, also for data rebuilt from external stakeholders; step 6 below is the test behind the DORA Article 12(7) row.

Ledger reconciliation as tested here is a three-way match: ledger entries against processor settlement lines, and settlement payouts against bank statement entries. Reconciliation testing proves that each break class you inject comes out of the matcher with the right class, on the item and run date where it was injected.

Procedure

1. Model the three record sets and their join keys

Load each source into its own table and keep amounts in integer minor units with a currency column. The keys that link the sets:

The bank key depends on the rail and the bank. Confirm from your bank's implementation guide which element carries the processor's payout reference before you write any match rule.

2. Build synthetic files with injected breaks

Generate the settlement file from the day's synthetic ledger postings, then change specific lines. The excerpt below uses our synthetic layout with a synthetic fee rule; a real processor file follows that processor's report specification.

record_type,psp_reference,merchant_reference,payout_id,currency,gross_minor,fee_minor,net_minor,booked_at_utc
CAPTURE,PSP-0001,ORD-1001,PO-0412,USD,12500,-393,12107,2027-02-12T15:04:11Z
CAPTURE,PSP-0002,ORD-1002,PO-0412,USD,4999,-175,4824,2027-02-12T15:09:40Z
REFUND,PSP-0003,ORD-0977,PO-0412,USD,-2000,0,-2000,2027-02-12T16:21:05Z
CHARGEBACK,PSP-0005,ORD-0931,PO-0412,USD,-3000,-1500,-4500,2027-02-12T17:02:57Z
CAPTURE,PSP-0007,ORD-1007,PO-0412,USD,8000,-262,7738,2027-02-12T18:45:12Z
PAYOUT,PO-0412,,PO-0412,USD,,,18169,2027-02-12T23:00:00Z

Injected breaks for this fixture: the ledger has no entry for refund PSP-0003, posts capture PSP-0007 twice, and records PSP-0002 as 4990 minor units. The payout PO-0412 closes on Friday 12 February 2027. Use synthetic references and synthetic bank account data only; production statements never enter a test environment.

The matching bank statement entry arrives in camt.053. Element names follow the SIX camt.053 implementation guideline (version 1.0, 28 February 2022):

<Ntry>
  <Amt Ccy="USD">181.69</Amt>
  <CdtDbtInd>CRDT</CdtDbtInd>
  <Sts><Cd>BOOK</Cd></Sts>
  <BookgDt><Dt>2027-02-16</Dt></BookgDt>
  <ValDt><Dt>2027-02-16</Dt></ValDt>
  <AcctSvcrRef>BNK-20270216-000871</AcctSvcrRef>
  <BkTxCd><Domn><Cd>PMNT</Cd><Fmly><Cd>RCDT</Cd><SubFmlyCd>DMCT</SubFmlyCd></Fmly></Domn></BkTxCd>
  <NtryDtls><TxDtls><Refs><EndToEndId>PO-0412</EndToEndId></Refs></TxDtls></NtryDtls>
</Ntry>

That guideline defines AcctSvcrRef as the bank's unique reference for the entry, used to link a booking across camt.054, camt.053, and MT940 and for duplicate checking. Statement parsing rules sit in the camt.053 reconciliation guide.

3. Match ledger entries to settlement lines

Run a full outer join per reference and event type, so an item missing on either side appears as a row.

-- Check 1: each capture, refund, and chargeback has exactly one ledger entry of the same amount
WITH l AS (
  SELECT payment_ref, event_type, currency, SUM(amount_minor) AS amount_minor, COUNT(*) AS n
  FROM ledger_entries
  WHERE account = 'processor_receivable'
    AND posted_at >= :'period_start'::timestamptz AND posted_at < :'period_end'::timestamptz
  GROUP BY payment_ref, event_type, currency
), s AS (
  SELECT psp_reference AS payment_ref, lower(record_type) AS event_type, currency,
         SUM(gross_minor) AS amount_minor, COUNT(*) AS n
  FROM settlement_lines
  WHERE record_type IN ('CAPTURE', 'REFUND', 'CHARGEBACK')
  GROUP BY psp_reference, record_type, currency
)
SELECT COALESCE(l.payment_ref, s.payment_ref) AS ref,
       CASE
         WHEN s.payment_ref IS NULL THEN 'missing_in_settlement'
         WHEN l.payment_ref IS NULL THEN 'missing_in_ledger'
         WHEN l.n > 1 OR s.n > 1 THEN 'duplicate'
         ELSE 'amount_mismatch'
       END AS break_class,
       l.amount_minor AS ledger_minor, s.amount_minor AS settlement_minor
FROM l
FULL OUTER JOIN s
  ON l.payment_ref = s.payment_ref AND l.event_type = s.event_type AND l.currency = s.currency
WHERE s.payment_ref IS NULL OR l.payment_ref IS NULL
   OR l.n > 1 OR s.n > 1 OR l.amount_minor <> s.amount_minor;

The posted_at window must equal the settlement file's own period. A ledger filter by local business date against a file cut in UTC produces false breaks at both edges of every file.

4. Check each payout against its lines and its bank credit

-- Check 2: payout net equals the sum of its lines, and exactly one booked credit carries it
WITH lines AS (
  SELECT payout_id, SUM(net_minor) AS lines_minor
  FROM settlement_lines WHERE record_type <> 'PAYOUT' GROUP BY payout_id
), bank AS (
  SELECT end_to_end_id, SUM(amount_minor) AS bank_minor, COUNT(*) AS n
  FROM bank_entries WHERE status = 'BOOK' AND cdt_dbt_ind = 'CRDT'
  GROUP BY end_to_end_id
)
SELECT p.payout_id, p.net_minor, lines.lines_minor, bank.bank_minor, bank.n
FROM settlement_lines p
LEFT JOIN lines ON lines.payout_id = p.payout_id
LEFT JOIN bank ON bank.end_to_end_id = p.payout_id
WHERE p.record_type = 'PAYOUT'
  AND (lines.lines_minor IS DISTINCT FROM p.net_minor
       OR (bank.n IS NOT NULL AND (bank.bank_minor <> p.net_minor OR bank.n > 1)));

-- Check 3: one booking loaded twice, for example from camt.054 and again from camt.053
SELECT acct_svcr_ref, COUNT(*) AS n, array_agg(source_file ORDER BY source_file) AS files
FROM bank_entries WHERE status = 'BOOK'
GROUP BY acct_svcr_ref HAVING COUNT(*) > 1;

A payout with no booked credit yet passes the bank part of check 2 and goes to check 4.

5. Classify missing credits as timing or missing with a business-day calendar

-- Check 4: a payout without a booked credit is a timing break until its expected date
SELECT p.payout_id, c.expected_date,
       CASE WHEN :'run_date'::date <= c.expected_date THEN 'timing' ELSE 'missing_at_bank' END AS break_class
FROM settlement_lines p
CROSS JOIN LATERAL (
  SELECT d AS expected_date FROM bank_calendar
  WHERE is_business_day
    AND d > (p.booked_at_utc AT TIME ZONE 'America/New_York')::date
  ORDER BY d OFFSET (:lag_business_days - 1) LIMIT 1
) c
LEFT JOIN bank_entries b
  ON b.end_to_end_id = p.payout_id AND b.status = 'BOOK' AND b.cdt_dbt_ind = 'CRDT'
WHERE p.record_type = 'PAYOUT' AND b.end_to_end_id IS NULL;

bank_calendar holds the settling bank's business days, loaded from the published holiday schedule of the rail. Conversions between the processor's time zone and the bank's are covered in the settlement timing guide.

6. Assert the exact break set, then replay after a restore

EXPECTED = {
    ("PSP-0002", "amount_mismatch"),
    ("PSP-0003", "missing_in_ledger"),
    ("PSP-0007", "duplicate"),
    ("PO-0412", "timing"),
}

def test_presidents_day_fixture_reports_exactly_the_injected_breaks(recon, load_fixture):
    load_fixture("2027-02-12-po-0412", bank_statements=[])
    actual = {(b.ref, b.break_class) for b in recon.run(run_date="2027-02-16", lag_business_days=1)}
    assert actual == EXPECTED

def test_missing_credit_becomes_missing_at_bank_after_expected_date(recon, load_fixture):
    load_fixture("2027-02-12-po-0412", bank_statements=[])
    actual = {(b.ref, b.break_class) for b in recon.run(run_date="2027-02-17", lag_business_days=1)}
    assert ("PO-0412", "missing_at_bank") in actual

Compare sets, never counts. A count assertion passes when one injected break disappears and an unrelated false break takes its place.

For the DORA Article 12(7) case, restore the ledger from a snapshot taken before the last settlement file was processed, rerun the checks, and expect a missing_in_ledger break for every line after the snapshot. A run that reports zero breaks after that restore is a failed test.

The pitfall: date windows in calendar days

Matchers usually accept a bank credit within N days of the payout. Counted in calendar days, the window is wrong in both directions.

The fixture shows the first case. Under the Federal Reserve holiday schedule (checked on 2026-09-22), Monday 15 February 2027 is Washington's Birthday. A payout closed on Friday 12 February with a one-business-day lag books on Tuesday 16 February, four calendar days later. A one-calendar-day window raises a false missing-at-bank break every Monday and after every holiday.

The second case follows from the usual fix. Teams widen the window to four or five calendar days to silence those Monday alerts. Midweek, a payout closed on Monday and due on Tuesday can then arrive on Friday, three business days late, and still match as on time. Over that period the ledger total exceeds the safeguarding or FBO balance and no break is raised. Count the window in business days of the settling bank's calendar, per rail, and keep a separate aging threshold for each rail.

Amount tolerances fail the same way when they sit at the wrong level. If the processor rounds fees once per payout and your ledger rounds per transaction, each line can differ by up to half a minor unit, so a payout of 2,000 lines can differ by up to 10.00 in its currency. A zero payout tolerance then flags that payout every day, and a payout tolerance widened to 10.00 hides a missing 9.99 refund. Replicate the processor's rounding level in the ledger and keep the tolerance at zero for same-currency matches. Rounding modes are in the bankers' rounding guide.

Two lines of the same amount in one window add a third trap. A greedy matcher pairs the first candidate it finds, so a missing refund can surface against the wrong order and age from the wrong date. Inject two equal amounts with one missing and assert which reference carries the break.

Test matrix

The ledger and reconciliation testing service delivers these scenarios as reconciliation test scenarios against bank, processor, and scheme files. When a break has already reached customers or a partner bank, the reconciliation incident scenario sets out the recovery sequence.