Reconciling reimbursement batches against bank settlement files

A settlement or return file from your bank is a stream of fixed-width or ISO 20022 records that must be parsed, keyed by trace or idempotency id, and matched line-for-line against the payment run you sent — and the moment your matcher falls back to guessing on amount and date, two employees reimbursed the same figure on the same day become indistinguishable and the reconciliation silently mis-posts. This guide is the file-parsing and matching detail delegated by the Payment Batch Reconciliation topic area, which sits inside the broader Reimbursement Routing & Approval Sync framework. The parent guide owns run assembly and the three-way classification; here we own the concrete work of reading a BAI2, NACHA acknowledgment, or camt.053 file, joining it to the run on a stable identifier, and flagging every line that does not reconcile — so no settled dollar goes unrecorded and no unmatched line hides in a passing count.

Why Standard Approaches Fail

Reconciliation feels like a spreadsheet VLOOKUP until it meets a real bank feed, at which point three failure modes appear.

• Amount-and-date matching collisions. Without a shared identifier, engines fall back to matching on amount + posting_date. Two employees reimbursed $42.00 on the same day produce two indistinguishable statement lines; the matcher pairs them arbitrarily, and one settled payment is credited to the wrong payout while the other reads as unmatched. The books balance in total but are wrong line by line.
• Format brittleness. A settlement file is not one format. BAI2 is fixed-field with type codes, NACHA return files pack the original trace into an addenda record, and camt.053 is nested XML with amounts as decimal strings and currency in an attribute. A parser that assumes decimal points, a single date format, or dollars-not-cents silently corrupts amounts — a camt.053 value of 50.00 and a NACHA field of 0000005000 are the same money, and treating either as literal breaks the match.
• Silent one-sided drops. The dangerous unmatched item is the one that appears on only one side: a run line the bank never acknowledged (the payment was dropped in transmission) or a statement line with no run (a payment you did not initiate). A matcher that only iterates the statement file never notices the run line that vanished, so an employee goes unpaid with nothing flagged.

The fix is to parse every format into one typed line shape keyed by a stable trace id, match on that id with an exact minor-unit amount check, and reconcile both directions so a one-sided line is always surfaced.

Architecture & Algorithm

The algorithm normalizes each source format into a StatementLine, builds an index of the sent run keyed by trace id, and performs a bidirectional join: every statement line is looked up in the run, and every run line is checked for a settlement. Amounts are compared as minor-unit integers so 50.00 and 0000005000 reconcile exactly.

from __future__ import annotations

import logging
import re
from dataclasses import dataclass
from decimal import Decimal
from xml.etree import ElementTree as ET

logger = logging.getLogger("reimb.recon.parse")


@dataclass(frozen=True)
class StatementLine:
    """One normalized settlement/return line, amount in minor units."""
    trace_id: str
    settled_minor: int
    currency: str
    return_code: str | None = None


def parse_nacha_return(raw: str) -> list[StatementLine]:
    """Parse NACHA entry-detail (type 6) plus return addenda (type 99).

    The type-6 record carries the 15-char trace and a 10-digit cents amount;
    a following type-99 addenda supplies the return reason code (e.g. R01).
    """
    lines: list[StatementLine] = []
    pending: dict | None = None
    for row in raw.splitlines():
        if row.startswith("6"):
            amount = int(row[29:39])                 # 10-digit minor units
            trace = row[79:94].strip()
            pending = {"trace_id": trace, "settled_minor": amount}
        elif row.startswith("799") and pending is not None:
            code = row[3:6].strip()                  # return reason code
            lines.append(StatementLine(pending["trace_id"], pending["settled_minor"],
                                       "USD", return_code=code))
            pending = None
        elif pending is not None:
            lines.append(StatementLine(pending["trace_id"], pending["settled_minor"], "USD"))
            pending = None
    if pending is not None:
        lines.append(StatementLine(pending["trace_id"], pending["settled_minor"], "USD"))
    return lines


def parse_camt053(raw: str) -> list[StatementLine]:
    """Parse an ISO 20022 camt.053 statement into normalized lines.

    Amounts are decimal strings with a currency attribute; the end-to-end id
    carries our trace id. Convert to minor units without float arithmetic.
    """
    ns = {"d": "urn:iso:std:iso:20022:tech:xsd:camt.053.001.02"}
    root = ET.fromstring(raw)
    out: list[StatementLine] = []
    ntry_tag = "{" + ns["d"] + "}Ntry"
    for entry in root.iter(ntry_tag):
        amt_el = entry.find("d:Amt", ns)
        ref_el = entry.find(".//d:EndToEndId", ns)
        if amt_el is None or ref_el is None:
            continue
        currency = amt_el.get("Ccy", "EUR")
        minor = int((Decimal(amt_el.text) * 100).to_integral_value())
        out.append(StatementLine(ref_el.text.strip(), minor, currency))
    return out

With every source normalized to StatementLine, the matcher joins on trace id and reconciles both directions. It never falls back to amount-plus-date, and it emits an explicit outcome for each side of the join.

from __future__ import annotations

import logging
from typing import Iterator

logger = logging.getLogger("reimb.recon.match")


def match_settlement(run_lines: list[dict],
                     statement_lines: list[StatementLine],
                     tolerance_minor: int = 0) -> Iterator[dict]:
    """Bidirectional match of a sent run against parsed statement lines.

    Yields one audit record per trace on either side:
      MATCHED         -> trace hit and |settled - expected| <= tolerance
      AMOUNT_MISMATCH -> trace hit but amount out of tolerance
      UNMATCHED       -> trace present on only one side
    """
    run_by_trace = {l["trace_id"]: l for l in run_lines}
    stmt_by_trace: dict[str, StatementLine] = {}
    for s in statement_lines:
        if s.trace_id in stmt_by_trace:
            logger.warning("duplicate_statement_trace", extra={"trace_id": s.trace_id})
        stmt_by_trace[s.trace_id] = s

    for trace in run_by_trace.keys() | stmt_by_trace.keys():
        run = run_by_trace.get(trace)
        stmt = stmt_by_trace.get(trace)
        if run is not None and stmt is not None:
            variance = stmt.settled_minor - run["expected_minor"]
            if abs(variance) <= tolerance_minor:
                status, detail = "MATCHED", {"settled_minor": stmt.settled_minor}
            else:
                status, detail = "AMOUNT_MISMATCH", {"variance_minor": variance}
        elif run is not None:
            status, detail = "UNMATCHED", {"side": "RUN_ONLY", "reason": "no_settlement"}
        else:
            status, detail = "UNMATCHED", {"side": "STATEMENT_ONLY", "reason": "unknown_credit"}
        record = {"trace_id": trace, "status": status,
                  "return_code": stmt.return_code if stmt else None, **detail}
        logger.info("recon_line", extra={"trace_id": trace, "status": status})
        yield record

Because the outer key set is the union of both trace sets, a run line the bank never acknowledged and a statement credit you never initiated are both surfaced as UNMATCHED with the side that is missing — the one-sided drop can no longer hide.

Step-by-Step Integration

1. Detect the format before parsing. Dispatch on file signature — a leading 01 BAI2 header, a 1 NACHA file-header record, or an XML prolog — to the matching parser; never assume one bank speaks one format forever.

2. Normalize every amount to minor units at parse time. Convert decimal-string amounts with Decimal(...) * 100 and read fixed-width cents fields as integers, so no float ever touches money and cross-format matches compare exactly.

3. Build the run index from the frozen run, not the ledger. Key the sent payment run by trace id; matching against the mutable ledger risks pairing a settlement to an already-superseded line.

4. Match bidirectionally and persist every outcome. Reconcile the union of trace ids and append each record to the reconciliation ledger. Verify the join before wiring it live:

   run = [{"trace_id": "T1", "expected_minor": 5000, "payout_id": "P1"},
          {"trace_id": "T2", "expected_minor": 7500, "payout_id": "P2"}]
   stmts = [StatementLine("T1", 5000, "USD"),
            StatementLine("T3", 9000, "USD")]  # credit with no run line
   out = {r["trace_id"]: r["status"] for r in match_settlement(run, stmts)}
   assert out["T1"] == "MATCHED"
   assert out["T2"] == "UNMATCHED"   # run line never settled
   assert out["T3"] == "UNMATCHED"   # statement credit with no run

5. Route mismatches and unmatched lines to the exception path. Send AMOUNT_MISMATCH and UNMATCHED records to the handlers described in Handling partial and failed reimbursement payments; post only MATCHED lines to the general ledger via ERP Export Synchronization.

6. Reconcile control totals. After matching, assert that the sum of matched settled minor units plus returns equals the bank’s file control total; a discrepancy means a line was dropped in parsing before it ever reached the matcher.

Edge Cases & Gotchas

FAQ

Why match on a trace id instead of amount and date?

Amount and date are not unique — two employees reimbursed the same figure on the same day produce indistinguishable lines, and a matcher keyed on them pairs settlements to the wrong payouts while still balancing in total. A trace or idempotency id is stamped on the payment when the run is frozen and echoed back by the bank, so it uniquely links each statement line to exactly one run line. Amount then becomes a verification check that confirms the match rather than the key that makes it.

How do I match amounts across NACHA and camt.053 without float errors?

Convert every amount to minor-unit integers at parse time: read NACHA fixed-width cents fields directly as int, and convert camt.053 decimal strings with Decimal(text) * 100 before integer-casting. A camt.053 value of 50.00 and a NACHA field of 0000005000 then both become the integer 5000 and compare exactly. Never store or compare money as float, because accumulated IEEE-754 error turns identical amounts into off-by-a-fraction mismatches across a large run.

What should happen to a statement line that has no matching run line?

Treat it as UNMATCHED with side STATEMENT_ONLY and route it to investigation, never auto-post it. A credit you cannot tie to a run you sent is either a misrouted bank entry, a duplicate, or a payment initiated outside your system, and any of those is a control exception. Posting it blind would create a ledger entry with no approved payout behind it, which is exactly the kind of unsupported transaction an auditor flags.

How do I make sure the parser did not silently drop a line?

Reconcile control totals after parsing: every well-formed settlement file carries a control record with a line count and a total amount, and the sum of your parsed minor units plus returns must equal it exactly. If the totals disagree, a line was dropped or misread before the matcher ran, so fail the batch loudly rather than reconciling a partial file. This catch is independent of the trace-id match and guards against format-parsing bugs.

Can I reconcile before the return window closes?

You can match settlements as they arrive, but do not finalize a run as fully reconciled until the ACH return window closes, because a payment that reads as settled today can bounce with a return code up to two banking days later. Keep matched items in a provisional state and only mark the run closed once no further returns can arrive. The Payment Batch Reconciliation parent guide sets the return-window policy this matcher runs under.

Related

• Payment Batch Reconciliation — the parent guide that owns run assembly and the three-way match
• Handling partial and failed reimbursement payments — where mismatches and returns are triaged and re-queued
• ERP Export Synchronization — posts only the matched, settled lines to the ledger
• Automated Policy Validation & Anomaly Flagging — the upstream engine that clears records before they enter a run