Configuring Rolling 3-Day Reconciliation Windows for ACH & Wire

You joined last night's inbound settlement file to the open ledger on an equality predicate over the date field, and this morning the exception queue holds 4,000 items — nearly all of them Friday-effective ACH entries that settled Monday, plus a fistful of Fedwire messages the host statement reports at T+1. None of them are broken; the join simply had no room for settlement lag. A rolling 3-day window is the narrowest fix: it retains each still-unpaired item for exactly three business days, probes it against every incoming file in that span, then expires it deterministically so the buffer never grows without bound. This page sits under sliding-window date reconciliation within the broader transaction matching and reconciliation algorithms framework, and it drills into the single most common window configuration in production ACH and wire pipelines: the three-day horizon, why it is three, and how to build one that expands only the date dimension without ever masking a genuine finality failure.

The three-day span is not folklore. It absorbs the effective-versus-settlement gap on standard ACH, captures late-file arrivals from Federal Reserve processing-cutoff variances, and lands exactly on the audit boundary where Regulation E error-resolution clocks begin to matter. Get the window right and unattended straight-through processing climbs; get it wrong and you either resurrect the false breaks you were trying to kill or, far worse, auto-match a later, unrelated same-amount transaction and make a real break disappear.

Concept Spec: A Bounded, Advancing Business-Day Interval

A rolling 3-day window is a half-open interval over normalized settlement business days, [processing_day − 3, processing_day], that advances by one business day each reconciliation cycle. On every cycle the engine ingests the new inbound file, probes the residual (still-unpaired) buffer against it, and expires any retained item whose normalized effective day has aged past the trailing edge.

Three invariants make it correct and cheap:

  • Normalization first. Every inbound timestamp — NACHA YYMMDD effective date, the Fedwire IMAD cycle date, an ISO 20022 IntrBkSttlmDt, or a UTC core-ledger stamp — is reduced to one canonical business day before the interval test runs. A wire stamped 23:30 America/New_York and a ledger entry stamped 03:30Z are the same business day; if they compare as two, the bug is missing normalization, not a window too narrow.
  • Business days, not calendar days. The trailing edge counts against a Fed holiday calendar. A ±3 calendar-day window misses a Friday-effective item settling the following Wednesday; a 3 business-day window does not.
  • Bounded memory. The retained buffer is a FIFO whose depth is capped by the window, so eviction is amortized per record and total resident state is for w window-days and r records per day — never a function of the full file history.

The buffer is a collections.deque: appends and left-pops are , and because effective days arrive roughly monotonically, expiry is a cheap left-drain rather than a scan.

A rolling 3-business-day window advancing one cycle, carrying a Friday ACH item across the weekend and expiring an aged item A business-day axis runs left to right across two stacked reconciliation cycles: Tue Jun 09, Wed Jun 10, Thu Jun 11, Fri Jun 12, then a collapsed Saturday–Sunday non-settlement gap, then Mon Jun 15. In the top row (Cycle · Fri Jun 12, processing_day Friday) the highlighted window covers effective days on or after Jun 09; a Friday-effective ACH item TRACE …543 for 1,284.50 dollars is ingested and sits PENDING, and an older item LEDGER-40 effective Jun 09 is still retained inside the window. Between the rows an arrow marks that on the next cycle the window slides one business day to the right. In the bottom row (Cycle · Mon Jun 15, processing_day Monday) the window has advanced to cover effective days on or after Jun 10: the Friday item is still inside, so the RDFI posting arriving 04:15Z normalized to Monday probes back to it, matches on exact trace and exact 128450-cent amount, and turns MATCHED green. The LEDGER-40 item, effective Jun 09, now falls to the left of the advanced trailing edge, ages out, and drops down into a dead-letter lane as ERR_WINDOW_EXPIRED, routed to the Reg E error-resolution queue and never silently dropped. The window advances one business day per cycle — [ processing_day − 3, processing_day ] TueJun 09 WedJun 10 ThuJun 11 FriJun 12 Sat·Sun no settle MonJun 15 Cycle Fri Jun 12 Cycle Mon Jun 15 window · effective ≥ Jun 09 retained LEDGER-40 $312.00 in window processing_day · ingest TRACE …543 $1,284.50 PENDING · no pair next cycle · slide +1 business day window advanced · effective ≥ Jun 10 aged out LEDGER-40 eff Jun 09 EXPIRED retained → matched TRACE …543 $1,284.50 ✓ MATCHED processing_day RDFI posting 04:15Z → Mon 15 amount-exact Dead-letter / exception queue LEDGER-40 · ERR_WINDOW_EXPIRED → routed to the Reg E error-resolution queue with full retention history — never silently dropped

Full Annotated Python Implementation

The class below builds the window, enforces the trailing boundary on a business-day calendar, probes retained items nearest-day-first, and routes expired-but-unmatched records to a Reg E exception queue. Money is carried as integer cents end to end — never float — so an amount equality probe cannot be corrupted by IEEE 754 drift. Timezones resolve through zoneinfo so a daylight-saving transition never silently shifts a boundary.

python
from __future__ import annotations

import logging
from collections import deque
from dataclasses import dataclass, field, replace
from datetime import date, datetime, timedelta
from zoneinfo import ZoneInfo

logger = logging.getLogger(__name__)


@dataclass(frozen=True)
class TransactionRecord:
    """A single normalized settlement item. amount_cents is integer cents:
    money never touches float, so amount equality is exact."""
    trace_id: str
    amount_cents: int
    effective_at: datetime      # timezone-aware; naive values are rejected upstream
    channel: str                # "ACH" | "WIRE" | "ISO20022"
    status: str = "PENDING"


@dataclass
class RollingWindow:
    window_days: int = 3
    tz: ZoneInfo = field(default_factory=lambda: ZoneInfo("America/New_York"))
    holidays: frozenset[date] = frozenset()

    # Retained residual, ordered by effective day; matched IDs guard double-consumption.
    _buffer: deque[TransactionRecord] = field(default_factory=deque)
    _consumed: set[str] = field(default_factory=set)
    _exceptions: list[TransactionRecord] = field(default_factory=list)

    def _business_day(self, dt: datetime) -> date:
        """Collapse a timezone-aware instant to its settlement business day."""
        if dt.tzinfo is None:
            raise ValueError(f"naive datetime rejected for {dt!r}; normalize before ingest")
        return dt.astimezone(self.tz).date()

    def _rewind_business_days(self, anchor: date, n: int) -> date:
        """Step back n business days, skipping weekends and the holiday set."""
        cursor = anchor
        stepped = 0
        while stepped < n:
            cursor -= timedelta(days=1)
            if cursor.weekday() < 5 and cursor not in self.holidays:
                stepped += 1
        return cursor

    def ingest(self, record: TransactionRecord) -> None:
        """Idempotent append; a re-transmitted trace_id already paired is ignored."""
        if record.trace_id in self._consumed:
            logger.debug("duplicate trace_id %s suppressed at ingest", record.trace_id)
            return
        self._buffer.append(record)

    def advance(self, processing_at: datetime) -> list[TransactionRecord]:
        """Slide the trailing edge to processing_day − window_days and drain
        everything now past it. Returns the expired records (may be empty)."""
        boundary = self._rewind_business_days(
            self._business_day(processing_at), self.window_days
        )
        expired: list[TransactionRecord] = []
        # Left-drain: cheap because effective days arrive near-monotonically.
        while self._buffer and self._business_day(self._buffer[0].effective_at) < boundary:
            expired.append(self._buffer.popleft())
        return expired

    def match(self, statement: list[TransactionRecord]) -> list[str]:
        """Probe each inbound statement line against the retained residual.
        Nearest-day-first so the smallest-drift candidate wins; a consumed
        item cannot be matched twice within or across cycles."""
        paired: list[str] = []
        for line in statement:
            candidates = sorted(
                (
                    r for r in self._buffer
                    if r.trace_id not in self._consumed
                    and r.amount_cents == line.amount_cents          # exact integer compare
                    and r.trace_id == line.trace_id
                ),
                key=lambda r: abs(
                    (self._business_day(r.effective_at)
                     - self._business_day(line.effective_at)).days
                ),
            )
            if candidates:
                self._consumed.add(candidates[0].trace_id)
                paired.append(candidates[0].trace_id)
        logger.info("paired %d of %d inbound lines", len(paired), len(statement))
        return paired

    def route_expired(self, expired: list[TransactionRecord]) -> None:
        """Any expired item never paired becomes a Reg E exception. It is
        never dropped — a silent drop is the one unrecoverable failure here."""
        for rec in expired:
            if rec.trace_id in self._consumed:
                continue
            self._exceptions.append(replace(rec, status="ERR_WINDOW_EXPIRED"))
            logger.warning("routed %s to exception queue (window expired)", rec.trace_id)

    def sweep_consumed(self) -> None:
        """Bound the guard set: drop consumed IDs no longer in the buffer so
        _consumed does not grow unbounded across a long-running process."""
        live = {r.trace_id for r in self._buffer}
        self._consumed &= live

    def exceptions(self) -> list[TransactionRecord]:
        return list(self._exceptions)

Each cycle is advance() → match() → route_expired() → sweep_consumed(), in that order: expire first so the residual you probe is already trimmed, then pair, then bank the unmatched expirees, then bound the guard set.

Calibration & Configuration

The window_days, tz, and holidays knobs are the whole calibration surface. Size them to the rail's known settlement skew and nothing more — every extra day multiplies the candidate set every downstream stage must scan.

  • Standard ACH: three business days is the workhorse. It covers the effective-versus-settlement gap plus a same-day-versus-next-day return offset and one cutoff slip. Set tz to America/New_York because NACHA effective dates and Fed settlement both key off Eastern.
  • Fedwire / same-day wire: drop to window_days=0 or 1. Wires settle intraday, so any multi-day drift is a genuine anomaly that must surface, not be absorbed. A 3-day window here is a liability — it hides misroutes.
  • Cross-border ISO 20022 (pacs.008): widen to 4–5 to absorb intermediary-bank hops and timezone rollovers, and normalize CreDtTm to UTC before deriving the business day so a corridor spanning date lines does not split one settlement across two days.

Two operational rules ride alongside the size. First, holidays must be a live Fed calendar, not a static literal — an unloaded holiday makes the trailing edge count a non-processing day and quietly shortens the effective window by one. Second, drive advance() from a deterministic scheduler aligned to the settlement calendar, never from wall-clock time; a cron that fires on a holiday would slide the window against a day on which nothing settled. The date window is the last stage before tolerance threshold configuration and the multi-field fallback chain, so it must expand the date axis and hand a clean residual forward — it is not the place to also relax amount.

Validation Example: A Friday ACH Entry Across the Weekend

Take a PPD credit, trace 091000019876543, $1,284.50 (128450 cents), effective Friday 2026-06-12, transmitted 18:40 America/New_York. The counterpart posts in the RDFI statement the following Monday 2026-06-15 at 04:15Z (00:15 ET).

  • Cycle Friday. ingest() appends the item. match() against Friday's inbound finds no counterpart yet.
  • Cycle Monday. advance(2026-06-15 …) computes the trailing edge by stepping back three business days from Monday: Friday → Thursday → Wednesday, i.e. 2026-06-10. The item's normalized effective day is 2026-06-12, which is ≥ the boundary, so it is retained, not expired. match() normalizes the 04:15Z posting to 2026-06-15 ET, finds the retained candidate on exact trace and exact 128450-cent equality, pairs it, and adds the trace to _consumed. No exception is raised.

Under a naive same-day equality join, that pairing never happens — Friday ≠ Monday — and the item lands in the queue as a false break. The window absorbs the two-calendar-day / one-business-day weekend gap without touching amount or trace precision. Now flip it: if the counterpart never arrives, then on the cycle whose trailing edge passes 2026-06-12, advance() left-pops the record and route_expired() writes it to the exception queue as ERR_WINDOW_EXPIRED with its full retention history — a genuine finality failure surfaced deterministically, exactly when Reg E expects it.

Failure Modes & Guardrails

Three edge cases turn a working window into a silent data-integrity hazard:

  1. Calendar-day counting instead of business-day. Rewinding with a plain anchor - timedelta(days=3) counts the weekend, so a Friday item settling Wednesday falls outside a "3-day" window and false-breaks. The guard is _rewind_business_days, which skips weekends and the holiday set — and the holiday set must be loaded, or the same bug returns one day at a time.
  2. A later, unrelated same-amount transaction. A generous lookahead lets a second $1,284.50 credit inside the window match the first item's counterpart. Two guards: keep the window as narrow as the rail's real lag allows, and probe nearest-day-first (as match() does) so the smallest-drift candidate wins. If two retained records genuinely share trace, amount, and day, that is duplicate settlement — a re-presented ACH or a retransmitted file — and must raise its own exception, never be silently consumed.
  3. Unbounded auxiliary state. The deque is bounded by the window, but _consumed is not: without sweep_consumed() it grows for the life of the process and leaks memory on a long-running reconciler. And an amount parsed as float upstream (128450.00000000001) breaks the exact-cent probe from the inside — enforce integer cents (or decimal.Decimal) at the ingestion boundary, and reject timezone-naive datetimes there too, so _business_day never has to guess an offset. Amounts that are genuinely a cent off should fall through the window into tolerance threshold configuration, not be masked here.