Trade reconciliation automation

"Trade reconciliation" is usually shorthand for three reconciliations that have to agree with each other on the same day:

• Transactions. Every executed trade on the fund's books should appear once, and only once, on the broker or custodian statement, with the same direction, quantity, price, and settlement date.
• Cash. The settlement amounts implied by those trades — plus fees, financing, margin, and non-trade movements — should roll the opening cash balance forward to the closing balance per currency and account.
• Positions. The same trades should roll the opening quantity forward to the closing quantity per instrument.

Treating them as three separate jobs is the root of most rework. A trade booked with the wrong direction produces a position break and a cash break; fixing one without the other just moves the exception. Any automation worth buying reconciles the three feeds against each other, not only against the broker.

The spreadsheet workflow scales badly for reasons that have little to do with volume:

• Formats drift. Every prime broker, clearer, and administrator ships a different file. A renamed column silently drops a mapping and the day's file reconciles to zero breaks for the wrong reason.
• Re-sends create duplicates. Funds send trades in batches, and batches overlap. An exact repeat cannot simply be dropped — it may be a genuine second trade — so it has to surface as a flagged item rather than disappear.
• Snapshots get added instead of replaced. Position and cash files are usually a full state, not a delta. Appending yesterday's unchanged corn position to today's doubles it.
• Explanations aren't retained. A break investigated on Monday and carried forward to Tuesday gets investigated again on Tuesday, because the reasoning lived in an email.

The useful comparison isn't "software versus spreadsheet" — it's what happens at each stage of the same pipeline.

Exact identifier matching clears the easy majority and leaves the entire workload. Two further passes remove most of what's left:

• Economic matching. When the fund's internal reference and the broker's execution reference differ, the same trade can still be identified by instrument, direction, quantity, price, and notional. A date-first pass followed by an economics pass keeps a genuine trade-date mismatch as one clean exception instead of two unmatched orphans.
• Notional tolerance. Rounding, fees, and conversion make a strict equality test useless. A configurable absolute-and-percentage tolerance on notional turns noise into matches and leaves real differences visible.
• Document-derived recalculation. For formula-referenced instruments — commodity swaps priced off an index plus basis, less a quality discount — both sides can agree and both can be wrong. Reading the governing clause out of the confirmation and independently recomputing the settlement is the only way that break surfaces at all.

Automation changes where the control sits; it doesn't remove the need for one.

• Completeness before accuracy. Confirm every expected feed arrived before reporting a clean day. Zero breaks on a missing file is the most dangerous state in reconciliation.
• Suppression must be evidenced. If the engine decides not to raise a break, it should record the document or rule that justified it.
• Carry-forward discipline. A break from yesterday clears only when a dated entry actually arrives for that identity — not because it stopped appearing in a file.
• Soft deletes. Nothing an operator removes should be unrecoverable.
• Ownership. Every open item should route to the party that has to act — the fund or the broker — so the queue is a work list rather than a report.

Break count on its own is a poor target; it falls when files go missing. Track instead:

• Auto-match rate, split by identifier match versus economic match.
• Time to first explanation — from file arrival to a proposed root cause.
• Ageing profile of open breaks, weighted by NAV impact rather than count.
• Carry-forward rate — how many exceptions survive to the next day.
• Feed completeness — expected versus received files per account, per day.

Expressing exposure as a percentage of the NAV of the accounts actually in scope — not the whole fund complex — keeps the number meaningful at small magnitudes.

A workable sequence: pick one fund and one broker relationship, automate ingestion and normalisation for those feeds only, and run the engine in parallel with the existing process for a few cycles. Compare the two exception lists daily. Differences in either direction are informative — items the engine raises that the spreadsheet missed, and items it suppressed that it should have raised.

Once the parallel run is quiet, extend broker by broker rather than fund by fund; the per-broker mapping work is where the effort concentrates.