Reconciliation Engine

A fund-operations reconciliation engine. It compares an administrator's book of records against a custodian's files, flags every data break, and proves its own correctness against a known answer key.

Built on real data: the iShares Core MSCI Europe UCITS ETF EUR (Acc) holdings (as of 11-Jun-2026).

How the pipeline flows

The whole system is one path from a single source file to a scored report. Each stage has one script.

1. Source. Start with the raw iShares holdings file (data/raw/) — a single published snapshot of the fund.

2. Build the administrator side. build_clean_sources.py splits the holdings into three clean files — the administrator's book of records: instrument.csv (what each security is), position.csv (how much is held), cash.csv (cash balances). This is the "truth" side.

3. Fabricate the custodian side. inject_breaks.py copies the book of records and turns the copy into a simulated custodian feed:

• position.csv and cash.csv get deliberate breaks injected (wrong quantity, wrong value, missing positions, wrong cash).
• instrument.csv gets its asset_class written as a code (01 instead of Equity), the way a real custodian sends it — so the transcode step has something to translate.
• a fourth file, ground_truth.csv, records exactly what was broken. This is the answer key.

Break selection is random but seeded, so every run produces the same breaks.

4. Transcode the custodian feed. normalize.py translates the custodian's coded values back into the administrator's vocabulary (01 → Equity), using the reference table data/reference/asset_class_map.csv. Unknown codes are flagged, never silently passed. Both sides now speak the same language.

5. Reconcile. reconcile.py matches the two sides by key, compares the fields on matched pairs (within a materiality tolerance), and flags keys found on only one side as missing positions — for both positions and cash.

6. Run and report. run_recon.py is the runner: it drives the whole pipeline (normalize → reconcile), writes a timestamped break report to data/reports/, and self-tests the result — comparing the breaks the engine found against the ground_truth.csv of what was actually broken.

A passing run means: every injected break caught, nothing missed, nothing falsely flagged.

Running it

python src/build_clean_sources.py    # build the book of records from data/raw/
python src/inject_breaks.py          # fabricate the custodian feed + ground truth
python src/run_recon.py              # normalize, reconcile, report, self-test

Reproducing the result

1. Clone the repo and place an iShares Core MSCI Europe UCITS ETF holdings .xls in data/raw/ (exported from the iShares website).
2. Run the three commands above, in order.
3. The final command self-tests the engine. A correct run ends with:

Self-test vs ground truth
  expected : 19
  caught   : 19
  missed   : 0
  false +ve: 0
  verdict  : PASS

No dependencies beyond the Python standard library (3.10+). Generated data and reports are not committed — the pipeline recreates them from the raw file.

Project structure

recon-engine/
  data/
    raw/            source data as received (the iShares .xls)
    clean/          administrator's book of records (instrument, position, cash)
    custodian/      fabricated custodian feed + ground_truth.csv
    reference/      mapping tables (asset_class_map.csv)
    reports/        timestamped reconciliation reports
  src/
    build_clean_sources.py   raw holdings -> the 3 clean files
    inject_breaks.py         book of records -> custodian feed with known breaks
    normalize.py             transcode custodian codes -> canonical vocabulary
    reconcile.py             the engine: match, compare, flag
    run_recon.py             the runner: normalize -> reconcile -> report
  README.md

Scope and design notes

• Matching key: ticker + exchange (composite). The source has no ISIN, and the ticker alone is not unique — SAN is both Santander and Sanofi, BOL is both Boliden and Bolloré. In production the key would be an ISIN.
• v1 reconciles positions and cash. Transactions are out (the ETF snapshot has none). Futures and FX conversion are out.
• Breaks are detected but not yet categorised by cause. Every break is a bare "break"; tagging the likely cause (trade-date vs settlement timing, stale price, FX) is the next layer.

Next

• Break categorisation by likely cause.
• Materiality tolerance tuning and FX-aware value comparison.
• File-watcher (v2): auto-run the pipeline when a new custodian file lands.