Python Reconciliation Logic
Reconcile an internal trade record against the custodian statement in pandas: merge, classify every break with a tolerance, summarise and triage.
The scenario
Settlements hands you two files for today's value date — your firm's internal trade record and the custodian's statement — and they don't tie out. Your job is to reconcile them in Python: match on the trade, classify every break with a sensible tolerance, summarise the run, and tell the desk exactly what to chase first.
Where this shows up
Reconciling two trade files in pandas and classifying the breaks is a common data take-home for reconciliations and ops-technology roles at firms of this type.
Firms such as State Street, BNY, J.P. Morgan.
DeskPrep is not affiliated with, endorsed by, or sponsored by any named firm. Firm names are used for illustrative, educational purposes only and do not imply that these materials are official assessments of, or are connected with, those firms.
Task brief
# Python Reconciliation Logic **Role relevance:** Reconciliations / middle-office take-home. **Estimated time:** 60 minutes · **Difficulty:** Intermediate · **Format:** Python (.py) + CSV data ## What you are given - `python_reconciliation_logic_starter.py` — the brief and four TODO tasks - `internal_trades.csv`, `custodian_statement.csv` — `trade_id, counterparty, instrument, quantity, settlement_amount` ## What you must deliver 1. **Merge** the two files on trade_id (outer join + indicator) so one-sided trades survive 2. **Classify** each row: reconciled / missing each side / quantity break / amount break (state the tolerance) 3. A **summary**: count of trades by status 4. The **break detail** and a one-line read of what to escalate first
Your tasks
- 01Merge the internal and custodian files on trade_id with an outer join and an indicator, so one-sided trades survive.
- 02Classify each row: reconciled, missing at custodian, missing internally, quantity break, or amount break — with an explicit settlement-amount tolerance.
- 03Print a summary: count of trades by status.
- 04Print the break detail and a one-line read of which break you'd escalate first.
How you're assessed
The full points-based mark scheme is included with the pack.
What you'll learn
- The load → merge → classify → summarise pattern that does a reconciliation in a dozen lines.
- Why an outer join with an indicator is non-negotiable — an inner join hides the one-sided breaks.
- Why a stated tolerance is a judgement interviewers look for, not an afterthought.