The File That Decides Which Company Your Rows Are About

A price file says what traded and when. It does not say who. That answer lives in a separate record, and if that record describes today rather than the day of the row, it will answer every question confidently and some of them wrongly. This is how to build one, how to check one you were handed, and what the wrong kind quietly does to a decade of history.

Exchanges recycle ticker strings. A company loses its listing, the letters return to the pool, and some time later a different business is given them. Keyed on the string, two unrelated price histories become one series with no gap, no duplicate and no error — we took that mechanism apart in a separate piece. This article is about the file that prevents it.

What the file has to contain

A symbol map is a list of intervals, not a lookup table. Each row states that one ticker, on one venue, named one instrument between two moments.

Two constraints follow from the shape and both are worth enforcing rather than documenting.

One ticker cannot name two instruments at the same instant. A file that says otherwise is broken. Software that resolves the contradiction by picking one — normally whichever survived being loaded into a dictionary — turns a loud problem into a permanent quiet one. Rejecting the file at load time is the correct behaviour.

Between two bindings the ticker names nothing. After a delisting and before a reassignment, a lookup must return nothing at all. Both substitutions available are wrong: the previous owner no longer exists, and the next owner has not arrived. Attaching the next owner is the splice itself, applied one row at a time, and a nearest-match lookup does exactly that.

The same universe, two files

This is the part worth seeing rather than reading about. Below are two reference files describing the same four companies over the same period. The first is point-in-time: it records that the ticker ABC belonged to one company until early 2020, was unassigned for a while, and then went to a different one. The second is what a free data source returns — today's mapping, with no end dates.

Run against the same fifteen rows of prices:

$ mdnorm instruments pit_map.csv prices.csv

assignments          6
symbols              5
instruments          5
reused symbols       1
renamed instruments  1
  reused: ABC names 2 instruments: US0000000001 -> US0000000002

rows mapped          15
rows unmapped        0  (dropped)
rows reassigned      5
note: those rows carry a ticker that named a different instrument at the
      time than it names now. Keyed on the string, they would have been
      spliced onto the wrong history.

$ mdnorm instruments snapshot_map.csv prices.csv

assignments          4
symbols              4
instruments          4
reused symbols       0
renamed instruments  0
note: no ticker in this file ever named more than one instrument. Over a
      long history that is unusual; check that the file is point-in-time
      rather than a snapshot of today.
note: every binding is open-ended, so nothing in this file has an end
      date. It cannot tell you what a ticker meant in the past.

rows mapped          12
rows unmapped        3  (dropped)
rows reassigned      0

The second run looks cleaner. Nothing was reassigned, no reuse was found, and a reader skimming for problems finds none. What actually happened:

Five checks for a file you were handed

You will usually not build this yourself; you will be given one and have to decide whether to trust it. Five counts settle most of it.

1. How many rows have an end date? If the answer is none, the file describes the present and nothing else. It cannot be wrong about history because it makes no claims about history — it just answers today's mapping to every question you ask it.

2. How many tickers ever named more than one instrument? Zero across a decade is the finding, not the pass. Symbol recycling is ordinary market plumbing, and a file that reports none of it over a long window is far more likely to be incapable of expressing it.

3. How many instruments ever carried more than one ticker? The inverse, and it matters for a different reason. Renames are common, and a file that records none of them will make you treat one continuous business as two short unrelated ones.

4. Do any intervals overlap? Any overlap is a contradiction. Find out how many before you decide whether the file is usable — one is a data-entry error worth fixing by hand, hundreds means the file was assembled by a process that does not model time at all.

5. How many of your own rows does it fail to resolve? Run it against your actual data rather than inspecting it in isolation. Unresolved rows are the honest output: they are the ones where the file admits it does not know, which is far better than the ones where it guesses. A file that resolves 100% of a ten-year history is suspicious for the same reason a guard that removes nothing is.

Applying it: three operations

Once the file is trusted, the work is small and it is only ever these three things.

Re-key every row by the instrument in force at its own timestamp. Not at the timestamp of the query, and not at the moment the file was downloaded. The count of rows this changes is the measure of how much damage the string key was doing.

Split each ticker's history at every handover. Then no mean, no volatility, no correlation and no rolling window is ever computed across a boundary between two businesses. A warning in a log is not a control here — by the time it is written, the wrong number already exists.

Report what was dropped and what moved. The counts are the product; the mapping is just what produces them. If nothing was reassigned across a long history, that fact belongs in the report next to the result, because it is a statement about the reference file rather than about the data.

$ pip install market-data-normalizer

$ mdnorm instruments symbol_map.csv prices.csv \
    --segments ABC -o keyed.csv

Where these files come from

This is the part that has no clever answer.

Commercial reference data vendors sell point-in-time security masters, and they are the reliable route. Exchanges publish symbol change and delisting notices, which can be assembled into one over time with patience and a tolerance for inconsistent formats. Regulatory filings record identifier changes for listed companies in most major markets. Some open datasets cover specific universes.

What almost never includes one is a free market-data API. Those return the ticker as it is understood at the moment of the call, which is precisely the snapshot in the comparison above. That is not a criticism of them — they are answering the question they were built for. It does mean that anyone working from one has this problem and no built-in way to find out.

When yours is inadequate

Assume for a moment that you cannot get a proper file. Three things still help, in descending order of usefulness.

Shorten the window. Ticker reuse needs time — a listing has to end and a reassignment has to follow. A study over three years carries far less of this risk than one over twenty, and saying so is more honest than implying a twenty-year result was checked.

Screen for the fingerprint. A splice leaves one trace: an implausible single-period move and the volatility that follows from it. It is a weak screen — it disappears under standardisation and inside a large cross-section, and genuine markets do produce large moves — but on raw levels it catches the worst cases.

Write down what you did not check. A result that states "instrument identity was not verified over this period" is worth more than one that leaves the reader to assume it was. The related discipline on membership records is in our pipeline audit, and the wider family of errors that improve results rather than breaking them is in the piece on selection and look-ahead bias in as-of joins.

Why we publish this

Because the fix is unglamorous and free, and the absence of it is invisible. Nobody is going to discover this problem by staring at their results, because the results look fine — better than fine, in the direction the splice pushes them.

The library is open source, MIT-licensed and has no runtime dependencies. What we are building next, and what we have decided not to build, is in our public roadmap. The implementation is on GitHub and installable from PyPI. What people have said about the work, with a link to each original, is on our community page, and if you are building research infrastructure on these foundations our partnership page is open to independent developers and firms alike.

Frequently asked questions

What is a point-in-time symbol map?

A record of which instrument each ticker named over each period of time, rather than which instrument it names now. Each row binds one symbol on one venue to one stable instrument identifier over a half-open interval: a start that is inclusive and an end that is exclusive. It answers the question a price file cannot answer on its own, which is what the letters in a row actually referred to on the day that row was written.

Why can't I just use the ticker as an identifier?

Because exchanges recycle ticker strings. When a company loses its listing the letters become available again, and a different business can be given them later. A history keyed on the string joins the two into one series with no gap, no duplicate and no error, so nothing in the pipeline objects. The only wrong thing is the assumption that the column header names a single company.

What should the instrument identifier be?

Anything stable that outlives the ticker. An ISIN, a FIGI, a vendor's permanent identifier, or an internal number you assign yourself all work. The identifier does not need to be recognisable or standard; it needs to keep referring to the same business after that business changes its name, its listing venue or its ticker.

How do I check whether my reference file is point-in-time or a snapshot?

Count the rows with an end date, and count the tickers that appear against more than one instrument. If every row is open-ended, the file has no vocabulary for the past and can only describe today. If no ticker in a decade of history ever named two instruments, the likeliest explanation is the same thing rather than a market that never recycled a symbol.

What happens if a ticker points at two instruments on the same date?

The file is broken and should be rejected rather than resolved. Software that silently picks one of the two — usually whichever the dictionary happened to keep — converts a loud, fixable problem into a quiet, permanent one. A contradiction in reference data is worth an error at load time.

What should a lookup return between a delisting and a reassignment?

Nothing. During that window the ticker named no instrument, and both plausible substitutions are wrong: the previous owner is gone, and attaching the future owner is exactly the splice the map exists to prevent. A nearest-match lookup is the natural thing to write here and it fails in both directions.

Where do point-in-time symbol maps come from?

Commercial reference data providers sell them, exchanges publish symbol change and delisting notices, and regulators publish filings that record identifier changes. Free market-data APIs almost never include them: they return the ticker as it is understood today, which is what makes this error so common outside institutions that already pay for a security master.

Does a renamed company need the same treatment as a reused ticker?

No, and confusing the two causes a different error. When one company changes ticker, the instrument is continuous and only the label moved, so nothing should be split — a pipeline that treats the two labels as two instruments halves the sample and makes every statistic noisier. Reuse splits, renaming joins, and only an identifier that survives both tells you which case you are in.