How to use historical identifiers to link across different data products within Compustat and CRSP
About Compustat's Historical Identifiers
Compustat now offers historical data on certain identifiers, such as Ticker, CUSIP, and exchange information, that can change over time. Two datasets, the sec_history and sec_idhist tables, contain this historical identifier information. Combined, they provide historical information on securities, such as primary share, exchange, and stock identifiers including CUSIP and Ticker. See the two tables below for the coverage.
sec_history table
See Historical Tables for more detailed information about the items in this table.
| Item | Description | Minimum effdate | Real Start Date |
|---|---|---|---|
EXCHG |
Exchange Code, see comp.r_ex_codes |
28-Jul-1988 |
01-Apr-1998 |
EXCHGTIER |
Exchange Tier Code, see comp.r_exchgtier; |
12-Aug-2003 |
|
MKVALINCL |
Flags securities that should be included in the traded issue market value calculation |
1-Jan-1900 |
1950 |
PRIHISTCAN |
Primary Issue Tag – Canada |
1-Jan-1900 |
1950 |
PRIHISTUSA |
Primary Issue Tag – US |
1-Jan-1900 |
1950 |
sec_idhist table
Sec_idhist table contains historical IDs for securities. The table below summarizes the identifiers and respective nominal start dates. See Historical Tables for more detailed information about this table.
| ID | Min Efffrom |
|---|---|
CUSIP |
01-JAN-1900 |
EXCHG |
01-APR-1998 |
ISIN |
01-JUL-1994 |
SEDOL |
01-JUL-1994 |
TIC |
31-DEC-1960 |
Using CUSIP to Match CRSP to Compustat
Without using the CCM linking table, researchers primarily used CUSIP to match CRSP to Compustat. In the past, this approach had several limitations.
- Compustat historically included only the most recent CUSIP, while CRSP maintained historical CUSIPs aligned with trading activity.
- This mismatch often resulted in failed linkages, particularly when the most recent CUSIP in Compustat corresponded to securities listed on OTC or regional exchanges not included in CRSP’s coverage.
- Researchers also lacked access to consistent indicators of a firm’s historical primary share, making it difficult to isolate the main equity security for return-based analyses.
The following Jupyter notebook steps you through how to use the enhanced Compustat tables to match with CRSP Version 2 data for constructing firm-level metrics such as size and book-to-market ratios. We also compare the results to those obtained using the standard CCM linking table to highlight some limitations of that traditional approach. Visit Historical Tables: sec_history and sec_idhist if you would like an in-depth review of the contents of the two tables.