Historical universe audit: retain missing IDs before selecting today’s survivors

Choose the historical population before asking about its results

A list of securities available today answers a different question from a list included in a research universe years ago. If a project starts with today’s surviving names and reaches backwards, missing historical members can disappear before any performance calculation begins. This worksheet makes that selection visible without calculating a return or claiming that a sample is unbiased.

Supply a historical inclusion list and a later comparison snapshot. The output retains every historical row, including identities that are absent, reported delisted or unresolved in the later file. It also lists later-only IDs. These are comparisons of your inputs, not a reconstruction of every security that existed or a certification that either dataset is complete.

US data documentation separates identity from a symbol label

The CRSP research-data documentation published by Morningstar describes permanent identifiers for securities and companies and distinguishes them from changing tickers. Its PERMNO material provides a security-lifecycle example. A company-level identifier and a security-level identifier are different grains; a company can have more than one security to track.

The worksheet therefore asks for the identifier scheme and security-level grain explicitly. It does not generate a PERMNO, map a ticker to one or obtain licensed data. Its DEMO-security-ID values are invented. You must establish the meaning and scope of the identifiers from your actual provider before treating equal strings as the same security.

Sources: Morningstar / CRSP — PERMNO and PERMCO.

Retired records belong in the evidence question

Morningstar’s description of the CRSP survivor-bias-free mutual-fund database explains that it preserves retired funds alongside active records for historical research. That is a concrete data-design example of retaining the observations that a current-only view could omit. It does not make this page’s input lists equivalent to that database.

Missing members are not automatically poor performers. A security may be absent because of a scope change, a merger, an unavailable record, a different identifier scheme or another cause. The worksheet never fills in a zero price, a total loss or an assumed delisting return. Identifying a selection gap is useful even when its economic effect remains unknown.

Sources: Morningstar / CRSP — Survivor-Bias-Free US Mutual Fund Database.

Align the underlying scope before matching the IDs

Give both snapshots the same declared underlying inclusion rules: market coverage, instrument category, identifier provider and the level at which a row is represented. Status is then an attribute used in the comparison. For example, a later reference file may retain inactive records even though a separate active-only screen would exclude them.

If one file covers ordinary shares and another mixes instruments or providers, identical identifier text is not enough. Missing or different namespace labels, inconsistent scope, issuer-level grain and missing source references withhold the membership comparison. This conservative check does not authenticate the declarations; it prevents a visibly incompatible pair from producing a misleading coverage percentage.

The worked set keeps all five historical rows visible

The fictional historical list contains IDs 0001, 0002, 0003 and 0004, plus a MYSTERY-labelled row with no resolved stable ID. The later file reports 0001 and 0004 active, reports 0003 delisted and has no row for 0002. The resulting fraction is two active matches among four identifiable historical IDs: 2/4, displayed as 50.00%.

That denominator excludes the one unidentified historical row and says so explicitly. It is not five known securities, a market survival rate or the percentage of invested money retained. No position sizes, prices, dividends or cash flows are supplied. The reported-delisted label is retained as a source claim; the tool does not independently verify the event or its date.

A reused-looking ticker cannot repair missing identity

The later fictional list also contains ID 0005 with ticker REUSE. The same text appeared beside historical ID 0002, but those IDs differ. The audit flags the shared ticker across different IDs and keeps the records separate. It does not declare proven ticker reuse: incorrect mapping or another source issue could explain the conflict.

The reverse case is equally useful. ID 0001 remains the same while its ticker changes from ALPHA to RENAMED, so it can still match under the declared stable-ID scheme. Leading zeroes are preserved; 0001 is not silently converted to 1. Ticker case and punctuation are also retained rather than normalised into an unsupported identity match.

File generation, trade date and as-of scope are not interchangeable

Nasdaq’s symbol-directory documentation says the files are updated during the day and describes a file-creation timestamp for checking timeliness. In its when-issued directory description, an effective-date field is explicitly distinguished from trade date. Those definitions illustrate why a saved file’s timestamp needs a label rather than being assumed to represent historical universe membership.

This form asks for the New York as-of date you intend to compare and an optional precise as-of timestamp. The fictional historical instant 2021-01-01T01:00:00Z falls on 31 December 2020 in New York, so it agrees with the historical date cell. Entering 1 January in that cell would withhold comparison until the mismatch is resolved. No trade date is inferred from the conversion. When both snapshots have the same date but either precise instant is missing, their order stays unknown and comparison is withheld.

Sources: Nasdaq Trader — Symbol Directory Definitions.

Unknown identity and repeated identity have different meanings

A row with no stable ID remains unidentified even if a ticker occurs in both files. It is counted separately from the identifiable-ID denominator. If every historical row lacks an ID, the fraction is unknown rather than 0/0 or zero percent. A present ID with later status unknown is also separate from an ID absent from the supplied file.

Duplicate nonblank IDs are rejected. Two identical-looking rows might be an accidental repeated export, while conflicting rows might reflect different share classes, dates or provider revisions. The tool does not choose one or count both. Resolve that source issue explicitly and keep the reasoning with your snapshot before comparing the lists again.

Save the selection evidence without claiming a back-test

The CSV includes both raw lists, snapshot references, namespaces, dates, comparison issues and every historical-row outcome. The percentage uses exact integer counts and half-up rounding to two decimal places; the unreduced count fraction remains beside it. Later-only IDs are simply IDs not present in the supplied historical set, not proven new listings after that date.

Completeness of either list, confirmed ticker reuse and historical return remain unknown. The worksheet sends no market-data request and needs no subscription or account key. Editing clears the old export before another calculation. The guide and report are complete on this page; the optional link leads to the existing English-language crypto signals and Telegram updates offer on this site.

Worked examples

{
  "identifier_grain": "security",
  "historical_date": "2020-12-31",
  "historical_timestamp": "2021-01-01T01:00:00Z",
  "later_date": "2026-10-20",
  "later_timestamp": "2026-10-20T21:00:00Z",
  "historical_namespace": "DEMO-security-ID",
  "later_namespace": "DEMO-security-ID",
  "historical_scope": "DEMO selected US-share records; all reported statuses retained",
  "later_scope": "DEMO selected US-share records; all reported statuses retained",
  "historical_reference": "DEMO saved 2020-12-31 universe snapshot",
  "later_reference": "DEMO saved 2026-10-20 comparison snapshot",
  "historical_rows": "stable_id|ticker|status|reference\n0001|ALPHA|included|DEMO historical row A\n0002|REUSE|included|DEMO historical row B\n0003|EXIT|included|DEMO historical row C\n0004|KEEP|included|DEMO historical row D\n|MYSTERY|included|DEMO row with no resolved stable ID",
  "later_rows": "stable_id|ticker|status|reference\n0001|RENAMED|active|DEMO later row A\n0003|EXIT|delisted|DEMO later row C and delisting label\n0004|KEEP|active|DEMO later row D\n0005|REUSE|active|DEMO later row E with different stable ID\n0006|NEWLABEL|unknown|DEMO unresolved later status\n|MYSTERY|unknown|DEMO later unidentified row"
}

{
  "historical_identifiable_IDs": 4,
  "historical_unknown_ID_rows": 1,
  "later_unknown_ID_rows": 1,
  "matched_active_IDs": 2,
  "known_ID_active_retention_fraction": "2/4",
  "known_ID_active_retention_pct": "50.00",
  "later_only_IDs": [
    "0005",
    "0006"
  ],
  "historical_return": null,
  "current_list_complete": null
}

Questions about the method

Primary sources and scope

1. Morningstar / CRSP — PERMNO and PERMCOChecked 2026-10-20. Permanent security/company identifiers and changing tickers. No IDs or licensed data are obtained; fictional IDs are not presented as CRSP identifiers.
2. Morningstar / CRSP — Survivor-Bias-Free US Mutual Fund DatabaseChecked 2026-10-20. Product description retains retired funds for historical research. No completeness or performance claim transferred to this worksheet or equity example.
3. Nasdaq Trader — Symbol Directory DefinitionsChecked 2026-10-20. Periodic file updates, file-creation timestamps and the specified effective-date versus trade-date distinction. No current directory or listing-status dataset downloaded.

Examples explain a method. They do not predict prices or establish where a trading service is available. Check the named source before relying on future schedules.