Project Record

Statistical Record Linkage for Instrument Mapping

Research-style reconciliation framework that uses trade logs to infer instrument-ID mappings across disconnected financial systems.

Domains

Hedge FundPost TradePortfolio

Capability

Controls & Reconciliation

Methods

Record LinkageFuzzy MatchingEntity ResolutionControls

Executive Summary

One of the hardest data problems in post-trade infrastructure is identity. The same financial instrument can appear across trader communication, booking systems, portfolio accounting, and market data feeds with different identifiers, names, and levels of reference-data quality. When records disagree, teams need to know whether there is a genuine economic break or only a naming and identifier mismatch.

I worked on a statistical record-linkage approach for inferring instrument mappings from trade activity. Instead of relying only on static reference data, the framework used repeated trade events as evidence that two system-specific identifiers referred to the same underlying instrument.

Trades are more granular than positions. If two systems repeatedly show similar trade events around the same time, with matching price, quantity, settlement, trader, and instrument attributes, that creates statistical evidence for an ID relationship. Once the relationship is learned at trade level, it can be reused for position reconciliation, where the expected mapping should be closer to one-to-one.

This project demonstrates probabilistic record linkage, fuzzy matching, entity resolution, and reconciliation controls for messy financial systems. The same idea generalizes beyond finance: frequent logs can be used to infer the identity of slower-moving entities across disconnected systems.

Problem

Consider two systems, $A$ and $B$ , that record activity on financial instruments. Each system has its own instrument identifier space:

I_A = \{a_1, a_2, \ldots, a_m\}, \qquad I_B = \{b_1, b_2, \ldots, b_n\}

The goal is to infer a mapping:

f: I_A \rightarrow I_B

where $f(a_i) = b_j$ means that identifier $a_i$ and identifier $b_j$ represent the same underlying instrument.

Direct matching by static reference data is often insufficient. Systems may use different vendor identifiers, issuer names, descriptions, roll conventions, or historical aliases. Some records may be incomplete, and some identifiers may change during migrations or operational rekeys.

The available evidence is a stream of trade records. Each trade is an observation containing fields such as timestamp, trade date, settlement date, side, quantity, price, currency, trader, desk, and instrument-specific attributes.

Logs and Entities

The central modeling idea is to separate logs from entities.

Entities: instrument identifiers inside each system.
Logs: trade events that reference those identifiers.

If two identifiers refer to the same real-world instrument, their associated trade logs should co-occur and agree across enough attributes. The framework therefore treats each candidate ID pair $(a_i, b_j)$ as a hypothesis and scores the evidence supplied by its trade logs.

flowchart LR
  BloombergIB["Bloomberg IB messages"] --> Parser["Trade parser"]
  BookingSystem["Booking records"] --> TradeLinkage["Trade-level linkage"]
  TradeLinkage --> IDMapping["Instrument ID mapping"]
  MarketData["Reference data"] --> IDMapping
  IDMapping --> PositionRecon["Position reconciliation"]
  PositionRecon --> Breaks["Break review"]

This framing is intentionally generic. In finance, the logs are trades and the entities are instruments. In another domain, the logs could be user events and the entities could be devices, accounts, suppliers, or products.

Candidate Generation

Naively comparing every identifier in $I_A$ with every identifier in $I_B$ is expensive and creates too many false candidates. The first stage uses blocking rules to generate a smaller candidate set:

C \subset I_A \times I_B

Candidate pairs are retained when their logs overlap on coarse signals such as trading day, currency, instrument family, desk, venue, price band, or settlement convention.

For example, a candidate bond pair may be kept if trades in both systems share the same currency, trade date, settlement date, and approximate price range, even when the instrument names or internal identifiers differ.

Comparison Features

For each candidate pair $(a_i, b_j)$ , the framework builds a comparison vector:

\gamma(a_i, b_j) = (\gamma_1, \gamma_2, \ldots, \gamma_k)

Each component measures agreement on one signal:

Temporal agreement: same trade date, close execution time, overlapping trading days.
Settlement agreement: matching settlement date or settlement convention such as T+1 or T+2.
Numerical agreement: price and quantity distance within configurable tolerances.
Categorical agreement: currency, side, desk, trader, venue, or asset class.
Identifier agreement: ISIN, CUSIP, ticker, reference entity, index family, or issuer where available.
Text agreement: fuzzy similarity between instrument descriptions or vendor names.

For an educational implementation, it is useful to distinguish between blocking fields, hard agreement fields, and graded similarity fields. Currency, asset class, venue, or book can be used to block candidates. ISIN, CUSIP, side, and normalized settlement currency can be treated as exact or near-exact agreement signals when present. Prices, quantities, timestamps, dates, and descriptions should usually be treated as graded similarities rather than binary equalities.

Numerical fields are not always exact because of rounding, booking conventions, clock skew, partial fills, lot-size conventions, or stale reference data. A strict price comparison can be represented as:

\gamma_{\text{price}} = \mathbb{1}\left[\frac{|p_A - p_B|}{p_A} \leq \epsilon_p \right]

but this loses information. A trade priced one tick away is more plausible than a trade priced fifty ticks away, and both should not receive the same non-match score. A more useful feature maps the absolute difference into a similarity score in $[0, 1]$ :

d_x = \left|(x_A - x_B) - o_x\right|, \qquad \gamma_x = g_x(d_x; \alpha_x, \beta_x)

where $o_x$ is a known bias or origin shift, $\alpha_x$ is the tolerance inside which differences are expected, and $\beta_x$ controls how quickly similarity decays outside that tolerance.

The recordlinkage.compare.Numeric class is a practical way to encode these functions. Its method parameter determines the decay shape:

step: use for hard tolerances where a value is either acceptable or not, such as a coupon rounded to a fixed precision or a maturity bucket.
linear: use when every extra unit of difference should reduce confidence at a constant rate, such as quantity lots or notional distance after normalization.
squared: use when small errors are acceptable but larger errors should be penalized more sharply than a linear function.
exp: use when similarity should fall quickly but never behave like a hard cutoff, useful for timestamp or latency differences.
gauss: use for fields with natural measurement noise around zero, such as clean price, yield, execution time, or spread.

The finance-specific interpretation of the parameters is:

offset: the no-penalty zone, for example one tick, one minute of clock skew, or a minimum lot rounding difference.
scale: the distance over which similarity decays from plausible to weak evidence.
origin: a systematic offset, for example when one system records execution time after booking confirmation or uses a consistent accrued-interest convention.
missing_value: the evidence assigned when either side is missing; in controls work this is usually $0$ for strong fields and a small neutral value only for optional reference-data fields.

For trade logs, a reasonable starting configuration is:

Execution time: method="gauss", offset=60, scale=300, on timestamps converted to seconds. This treats sub-minute differences as normal latency and five-minute differences as much weaker evidence.
Trade date: Date(...) or exact comparison after calendar normalization. Same-day agreement should be strong; month/day swaps can receive partial credit only if the source is known to contain manual date-entry errors.
Settlement date: Date(...) for direct agreement, plus a precomputed business-day distance when T+1/T+2 conventions differ across venues or regions.
Clean price: method="gauss" on price or price-basis-point distance, with offset near the minimum tick size and scale set from observed rounding error.
Yield, spread, or rate: method="gauss" with tolerances in basis points; these are often better than price for bonds with different accrued-interest conventions.
Quantity or notional: method="linear" or method="squared" after normalizing units, lot size, sign, and multiplier. Partial fills should be aggregated before comparison when possible.
Coupon and maturity: exact or step for coupon; Date(...) for maturity, because adjacent maturities from the same issuer are a common false-positive source.
Issuer and description: String(..., method="jarowinkler") for issuer names, and String(..., method="cosine") or token-based similarity for long security descriptions. Thresholds should be calibrated rather than assumed.

String comparison is especially important for fixed income and OTC instruments, where descriptions often include issuer abbreviations, coupon, maturity, callability, seniority, currency, and vendor-specific formatting. These fields should be normalized before fuzzy matching: uppercase, remove punctuation, standardize common legal suffixes, map currency words to ISO codes, and split coupon/maturity into structured fields where possible. Fuzzy matching should then operate on the residual descriptive text rather than on raw vendor strings.

The practical rule is to make invariant fields exact, noisy market fields continuous, and human-entered fields fuzzy. This gives the scoring model enough information to distinguish a harmless naming difference from a real economic break.

Probabilistic Scoring

The comparison vector is converted into a match score. In a Fellegi-Sunter style linkage model, each feature contributes evidence based on how likely it is under the match and non-match hypotheses:

w(\gamma) = \sum_{\ell=1}^{k} \log \frac{P(\gamma_\ell \mid M)}{P(\gamma_\ell \mid U)}

where $M$ denotes a true match and $U$ denotes an unmatched pair. Exact fields, fuzzy fields, and numeric tolerances all contribute to the final score.

The decision rule is:

\text{decision}(a_i, b_j) = \begin{cases} \text{match} & \text{if } w(\gamma) \geq \tau_{\text{high}} \\ \text{review} & \text{if } \tau_{\text{low}} \leq w(\gamma) < \tau_{\text{high}} \\ \text{non-match} & \text{otherwise} \end{cases}

High-confidence pairs can be accepted automatically. Ambiguous cases are routed to manual review, especially when duplicate names, similar bonds, or partial booking records create competing explanations.

From Trade Matching to Instrument Mapping

The important operational step is aggregation. A single matched trade suggests a possible relationship between two instrument identifiers, but repeated matched trades create stronger evidence.

For each candidate ID pair $(a_i, b_j)$ , the system aggregates trade-level scores:

S(a_i, b_j) = \sum_{r \in L(a_i, b_j)} w_r

where $L(a_i, b_j)$ is the set of matched trade observations supporting that identifier pair.

The resulting score is used to infer key relationships across systems. For bonds, the evidence set may include many attributes: date, settlement, side, quantity, price, currency, issuer, coupon, maturity, trader, desk, and security description. The more consistently those observations align, the stronger the inferred mapping.

Position Reconciliation

Positions are aggregated outputs of trades, corporate actions, adjustments, and operational processing. Attempting to reconcile positions directly can hide the reason for a break because a position difference may combine several underlying causes.

Learning mappings from trades first gives the position reconciliation process a stronger foundation. Once instrument identifiers are linked, position records can be compared under the inferred key relationship:

\Delta q_i = q_A(a_i) - q_B(f(a_i))

If the mapping is trusted and the position still differs, the break is more likely to reflect a genuine booking, timing, corporate-action, or valuation issue rather than an identifier mismatch.

Implementation

Inputs: parsed trade events, booking-system extracts, portfolio or administrator records, and market reference data.
Blocking: candidate generation by date, currency, instrument family, settlement, desk, venue, and approximate price or quantity ranges.
Comparison: exact matching for stable identifiers, fuzzy string similarity for descriptions, date comparison for trade and settlement fields, and numeric decay functions for price, yield, quantity, notional, and timestamp differences.
Scoring: probabilistic record-linkage scores calibrated into accepted, rejected, and review bands.
Mapping: aggregation of trade-level evidence into cross-system instrument-ID relationships.
Controls: manual review for ambiguous duplicates, audit trail for approved mappings, and break reports for downstream reconciliation.

Python’s recordlinkage toolkit fits this workflow because it separates indexing, comparison, and classification. The comparison stage can be made explicit:

import recordlinkage as rl
from recordlinkage.compare import Date, Exact, Numeric, String

compare = rl.Compare([
    Exact("currency", "currency", label="currency"),
    Exact("side", "side", label="side"),
    Exact("asset_class", "asset_class", label="asset_class"),

    Date("trade_date", "trade_date", missing_value=0.0, label="trade_date"),
    Date("settle_date", "settle_date", missing_value=0.0, label="settle_date"),
    Date("maturity_date", "maturity_date", missing_value=0.0, label="maturity_date"),

    Numeric(
        "execution_ts_seconds",
        "execution_ts_seconds",
        method="gauss",
        offset=60,
        scale=300,
        origin=0,
        missing_value=0.0,
        label="execution_time",
    ),
    Numeric(
        "clean_price",
        "clean_price",
        method="gauss",
        offset=0.02,
        scale=0.25,
        missing_value=0.0,
        label="clean_price",
    ),
    Numeric(
        "yield_bp",
        "yield_bp",
        method="gauss",
        offset=1.0,
        scale=10.0,
        missing_value=0.0,
        label="yield",
    ),
    Numeric(
        "quantity_lots",
        "quantity_lots",
        method="linear",
        offset=0.0,
        scale=2.0,
        missing_value=0.0,
        label="quantity",
    ),

    String(
        "issuer_name_norm",
        "issuer_name_norm",
        method="jarowinkler",
        threshold=None,
        missing_value=0.0,
        label="issuer_name",
    ),
    String(
        "security_description_norm",
        "security_description_norm",
        method="cosine",
        threshold=None,
        missing_value=0.0,
        label="description",
    ),
])

features = compare.compute(candidate_pairs, trades_a, trades_b)

This configuration is not a universal parameter set. It is a starting point that encodes financial intuition in measurable form. The production process should estimate or validate the parameters from observed data:

Normalize each source into comparable fields: clean price, accrued interest convention, signed quantity, notional, trade date, settlement date, execution timestamp, issuer, coupon, maturity, and security description.
Generate candidates with blocking rules that preserve likely matches but remove impossible pairs, such as different currencies or unrelated asset classes.
Compute comparison vectors with exact, date, numeric, and string features.
Fit or calibrate a Fellegi-Sunter, Naive Bayes, logistic, or gradient-boosted model on adjudicated examples.
Translate scores into accepted, rejected, and review bands, then audit the feature contributions for each accepted mapping.

The thresholds should be chosen from validation data rather than hard-coded from intuition. A useful calibration target in post-trade controls is high precision at the automatic-accept threshold, with a broader review band for operational analysts:

\tau_{\text{high}} = \min \{\tau : \widehat{\operatorname{precision}}(\tau) \geq 0.995\}

Pairs below $\tau_{\text{low}}$ are rejected automatically. Pairs between $\tau_{\text{low}}$ and $\tau_{\text{high}}$ are routed to review, especially when there are competing candidates with similar scores.

The same structure can also be implemented with scalable linkage tools such as Splink when the candidate set is large.

Trade-offs

Probabilistic linkage improves recall where static identifiers are incomplete, but it requires careful thresholding. Loose tolerances can hide real breaks; strict tolerances can push valid matches into manual review.

Trade-level evidence is powerful because it is granular, but it is not perfect. Clock skew, late booking, partial fills, corporate actions, stale reference data, and similar securities from the same issuer can all create false candidates.

The framework therefore combines automated scoring with explicit review bands. The goal is not blind automation. The goal is an explainable mapping layer that shows why two system identifiers are believed to represent the same instrument, and where that belief is still uncertain.