How to reconcile positions against your books: a step-by-step guide
To reconcile positions against your books, take the external position file (administrator, broker or prime broker) and your internal book for the same date, match them on a composite key such as portfolio + security identifier + date, map the columns once, compare quantity exactly and price and market value within agreed tolerances, then list every break: positions missing on either side, duplicates, and value differences. Save the rules so the next run is the same check.
Step 1: Fix the cut-off
Both sides must be as at the same date and the same point in the day. Most "breaks" on day one are timing: trades booked internally after the external file was cut. Record the cut-off for each source on the report.
Step 2: Choose the composite key
A position is unique on something like portfolio + ISIN + as-of date. If one side uses SEDOL or an internal ID, build a cross-reference table first and match on the cross-referenced identifier. Check for duplicate keys on each side before comparing; duplicates are breaks in their own right.
Step 3: Map the columns once
| Books | External file | Rule |
|---|---|---|
| Fund | Account | Mapped via account-to-fund table |
| ISIN | Security ID | Upper-case, trimmed |
| Quantity | Units | Exact |
| Price | Close Px | Tolerance 0.0001 |
| Market value (GBP) | MV Base | Tolerance 0.01 or 1bp |
Write the mapping down. It is the part that drifts when a provider changes a file.
Step 4: Set tolerances by asset type
Quantities should match exactly. Prices and values need tolerances that reflect rounding and FX conventions, and they differ by asset type: bonds priced per 100 nominal, funds priced to more decimals, derivatives with notional versus market value. Keep the tolerance table next to the mapping.
Step 5: Produce a break report and re-run
Group breaks into: missing in books, missing in external, duplicate key, quantity break, price break, value break. Each line should show the key, both values, the difference and the rule that flagged it. Investigate, correct, and re-run with the same rules so you can show the breaks clear.
Common causes of position breaks
- Trades booked after the external cut-off.
- Corporate actions processed on different dates.
- Identifier changes not reflected in the cross-reference.
- Different pricing sources or FX rates.
- Lots split one-to-many on one side.
When files are large or in mixed formats
Spreadsheets stop at 1,048,576 rows, and administrator files often arrive as PDF or XLSX while internal books come as CSV or XML. ShipDataFast applies the same key, mapping and tolerances to PDF, XLSX, CSV, XML and JSON and produces the break report, including on multi-GB files. See also why the same cut-off matters.
Frequently asked questions
What key should I use to reconcile positions?
Portfolio or fund, security identifier and as-of date. Cross-reference identifiers first if the two sides use different ones.
What tolerance should I use for position reconciliation?
Zero on quantity; small agreed tolerances on price and market value, set per asset type and written down with the mapping.
Why do my positions not match on the first run?
Usually timing (different cut-offs), identifier mismatches, corporate actions or pricing sources, rather than genuine errors.
Can I reconcile positions in Excel?
For small files, yes, with care over keys and duplicates. Excel stops at 1,048,576 rows and VLOOKUP returns only the first match, so large or duplicated data needs a proper keyed comparison.
Want to check two real files?
ShipDataFast's Recon Check gives you a first break report at no charge. Anonymised or sample files are fine; agree a safe transfer method before sharing sensitive data.