From Spreadsheets to Python for End-of-Day Reconciliation
We trained a long/short equity hedge fund's operations analyst to replace a fragile manual reconciliation with a reproducible Python routine.
A long/short equity hedge fund's daily position reconciliation lived in a chain of spreadsheets that only one operations analyst fully understood. We coached that analyst — hands-on, against their own real end-of-day process — to rebuild the reconciliation as a documented Python routine that runs the same way every night, flags breaks automatically, and no longer depends on manual copy-paste.
The Challenge
Each evening the operations analyst reconciled the fund's internal position and P&L records against statements from the prime broker and custodian. The process was entirely spreadsheet-driven: export files, paste them into a workbook, line up tickers by hand, eyeball the differences, and chase anything that didn't tie out before the next morning.
It worked, but it was slow, error-prone and fragile. Ticker and identifier mismatches between systems had to be fixed manually every night. Corporate actions, short positions and swap financing lines were easy to misalign. Formulas broke silently when a broker changed a column order. And because the whole routine lived in the analyst's head and their workbook, the fund had real key-person risk — if that person was on leave, month-end reconciliation was at risk.
The analyst was capable and knew the operational logic cold, but had no programming background. Generic Python courses would have taught syntax with no connection to reconciliation, prime broker files, or the realities of a long/short book.
The Solution
We delivered practitioner-led, one-to-one training built entirely around the analyst's own end-of-day workflow. Rather than teach Python in the abstract, we rebuilt their real reconciliation step by step, so every concept was learned in the context of a task they already performed.
1. Grounded in the real process
We started from the analyst's existing spreadsheet and the actual files it consumed — prime broker and custodian position statements and the fund's internal records. Each Python concept was introduced only when the reconciliation needed it, so nothing felt like a detour from the day job.
2. Reading and normalising the data
The analyst learned to load broker, custodian and internal files with pandas, standardise instrument identifiers across sources, and handle the quirks of a long/short book — signed quantities for longs and shorts, swap versus cash lines, and the identifier mismatches that previously had to be fixed by hand.
3. Matching, tolerances and breaks
Together we built the core reconciliation: joining positions across sources, comparing quantities and market values within sensible tolerances, and producing a clean, categorised breaks report that highlights only what genuinely needs attention rather than a wall of near-zero rounding noise.
4. A repeatable, documented routine
We turned the notebook work into a single parameterised script the analyst could run each evening, with a date input, a tidy exception report, and clear comments. Crucially, the analyst wrote and understood every part of it — so the routine is maintainable in-house, not a black box handed over by a consultant.
Tools & Technologies
Python with pandas for data ingestion, identifier normalisation, position matching and breaks reporting — taught against the fund's real prime broker, custodian and internal position files, in modular one-to-one sessions timed around the operations analyst's daily responsibilities.
The Results
End-of-day reconciliation moved from a manual, spreadsheet-bound routine to a Python script that runs the same way every night. What had taken the analyst a long stretch of copy-paste and visual checking became a quick, repeatable run that surfaces breaks automatically and in a consistent format — freeing time and cutting the risk of a missed discrepancy.
Just as importantly, the fund reduced its key-person risk: the process is now documented in readable code rather than living solely in one person's workbook and memory. The analyst gained transferable Python skills they went on to apply to other operational tasks, and the fund gained an auditable, reproducible control it fully owns.