6Reference Data and Symbology Services

In January 2022 the ticker META belonged to an exchange-traded fund. At the end of that month the fund changed its ticker to METV, and on 9 June 2022 a large company that had traded as FB for ten years began trading as META, keeping the same identifier number for its shares. A system that keyed its history by ticker now held, under one symbol, half a year of a fund followed by the history of a different issuer, and nothing warned it: both were valid prices of a security that really did trade as META on those days. This chapter builds the service that prevents that (firm.refdata): it keeps every fact every vendor ever delivered about an instrument, bitemporally; maps every external symbol to a permanent identifier as of a date and as known at a time; assembles a golden copy with the provenance of each value; serves corporate actions as events whose meaning can change before they happen; and keeps the firm’s counterparties the same way. A synthetic universe with planted ticker reuse, a vendor that misses a rename and a mistyped lot size measures what each piece is worth.

6.1 What the reference-data service serves

One Quant Book 1 (chapter 28) defined reference data as everything about an instrument that is not a price, and symbology as the set of identifiers it is known by; One Quant Book 7 (chapter 4) built a security master of permanent identifiers and ticker changes for research. The platform needs the same facts as a service, read by every system of the map of chapter 1: the tick store to name its partitions, trading to know a lot size and a tick, risk to find an instrument’s underlying, post-trade to find its ISIN and settlement currency.

The chapter’s universe has 110 listings over 500 trading days: 100 at the start, 10 listed later. Each has a permanent external key (its ISIN in the synthetic data), a ticker, a name, a lot size and a tick. Twelve listings change ticker; five of their old tickers are later given to five of the new listings, as META passed from a fund to a company. Twenty listings split, each announced 10 to 30 days before the split takes effect; one announced split is cancelled.

6.2 Vendors, conflicts and the golden copy

No single source is right about everything. Vendors differ in coverage, speed and care: one receives exchange notices directly and updates tickers the same day, another is better on names and legal details; each makes mistakes the other does not. Two vendors deliver the chapter’s facts. Vendor A is prompt, but mistypes one listing’s lot size (10 instead of 100) for six days before correcting it. Vendor B learns two ticker changes two days late, and misses one rename entirely until three days after its old ticker has been given to a new listing: for 146 days it maps that ticker to the listing that no longer uses it.

    def golden(self, pid: int, field: str, valid, known):
        order = self.precedence.get(field, tuple(sorted(self.vendors)))
        for vendor in order:
            x = self.value(vendor, pid, field, valid, known)
            if x is not None:
                return x, vendor
        return None, None

    def conflicts(self, field: str, valid, known) -> list:
        out = []
        for pid in sorted(set(self.keys.values())):
            vals = {v: self.value(v, pid, field, valid, known)
                    for v in sorted(self.vendors)}
            vals = {v: x for v, x in vals.items() if x is not None}
            if len(set(vals.values())) > 1:
                out.append((pid, vals))
        return out

Table 6.1 counts, over the 52 492 instrument-days of the universe and as known each day, how often each source and the golden copy are wrong, and how often the service reports a conflict. With vendor A first for tickers the golden copy is never wrong about a ticker, and the conflict report shows every day vendor B was wrong except the three days it had no value at all. The lot size shows the other side: vendor A is first for lots too, so the golden copy copies its typo for all six days. Precedence chooses whose errors the firm inherits; the conflict report is what catches them, since both sources had a value and they differed on each of those days.

The lot typo also shows why the service is bitemporal rather than a table that is updated. Vendor A’s typo is a fact valid from day 100, delivered on day 100; its correction is another version of the same fact, still valid from day 100, delivered on day 106 (Figure 6.2). Asked about day 103 as known on day 103, the service answers 10, the lot a trading system actually used that day; asked about day 103 as known on day 106 or later, it answers 100, the truth. An updated table can give only the second answer, and every order sized with 10 becomes inexplicable.

6.3 Symbol resolution through time

Both times matter. The valid date answers “which company was META on 3 January 2022?” — a fund. The knowledge time answers “what did the firm believe on the morning it traded?” — which, for a vendor that learned of a change late, may differ from the truth. resolve takes both (Listing 6.2), and the service stores every fact in Book 7’s bitemporal store under an effective-date index, so that “the value in effect on a date” is the last fact valid at or before it, in the version known at the time asked.

class _Effective:
    """Effective-dated values in a firm.pit store: the value in effect on `valid` is the one
    of the latest valid date at or before it, each as known at `known`."""

    def __init__(self):
        self.store = Store()
        self.valids: dict[tuple, list] = {}

    def put(self, entity, field, valid, known, value) -> None:
        v = self.valids.setdefault((entity, field), [])
        if valid not in v:
            bisect.insort(v, valid)
        self.store.put(entity, field, valid, known, value)

    def get(self, entity, field, valid, known):
        v = self.valids.get((entity, field), [])
        for i in range(bisect.bisect_right(v, valid) - 1, -1, -1):
            x = self.store.asof(entity, field, v[i], known)
            if x is not None:
                return x
        return None

On the chapter’s universe, sampling every fifth day, resolution with the last day’s mapping names a different listing than point-in-time resolution on 384 of 10 493 ticker-days, 3.7%. Figure 6.3 follows one ticker through its life: it belongs to one listing until day 33, to none until day 176, and then to the listing that reused it.

6.4 Corporate actions as a service

A corporate action is known before it happens, and what is known can change: a split is announced, confirmed, sometimes amended or cancelled. A service that stores only the final record of each action cannot say what a strategy knew on the day it traded.

    def add_action(self, event: str, pid: int, kind: str, ratio: float, effective,
                   status: str, known) -> None:
        """A new version of event `event`: announced, confirmed or cancelled; the latest
        version known wins."""
        versions = self._actions.setdefault(pid, {}).setdefault(event, [])
        versions.append((known, kind, ratio, effective, status))
        self._actions[pid][event].sort(key=lambda r: r[0])

    def actions(self, pid: int, known) -> list:
        out = []
        for event, versions in self._actions.get(pid, {}).items():
            live = [r for r in versions if r[0] <= known]
            if live:
                _, kind, ratio, effective, status = live[-1]
                out.append((event, kind, ratio, effective, status))
        return sorted(out, key=lambda r: (r[3], r[0]))

    def factor(self, pid: int, start, end, known) -> float:
        """Price adjustment factor for splits effective after `start` and at or before `end`,
        as known: a price at `start` divided by this factor is comparable with prices at
        `end`."""
        f = 1.0
        for _event, kind, ratio, effective, status in self.actions(pid, known):
            if kind == "split" and status == "confirmed" and start < effective <= end:
                f *= ratio
        return f

In the chapter’s corporate-action feed each split is announced, confirmed three days later, or cancelled. The adjustment factor between two dates is then a question with a knowledge time: for the first split (a four-for-one, announced on day 386, effective on day 412) the factor over the two years is 1 as known on day 385 or 386, and 4 from day 389; the cancelled split never enters it. A backtest that asks the factor with the knowledge time of each decision is point in time; one that asks with today’s knowledge is not, for any signal that uses price levels.

6.5 Counterparties, accounts and the rest

Instruments are half of reference data. The other half is the parties the firm deals with.

Its first use is aggregation: limits and exposures are set on an ultimate parent, so the parent chain must be right on the date of the exposure and as known when the limit was checked. In the chapter’s example a fund is acquired by a bank’s subsidiary on day 250 and the firm learns it on day 260: asked on day 255 about day 255, the fund’s ultimate parent is itself; asked on day 262, the holding company. Both answers are correct, and a credit report produced on day 255 must be reproducible with the first one (chapter 25).

6.6 Tutorial: two vendors and a reused ticker

6.7 Build: the reference-data service

6.8 Exercises

6.9 Problem: The Ticker That Changed Hands

6.10 Interview questions