Both FactSet and CRSP offer identifier files that contain primary identifiers at the entity level and security level (note: an entity may issue multiple securities). These files provide a mapping between their primary identifiers and all other historical identifiers such as CUSIP and ticker. By utilizing these identifiers, we can construct a link table.<\/p>\n
The CRSP STOCKNAMES file provides a mapping between CRSP permanent identifiers and all historical CUSIPs, company names, and exchange tickers, along with their effective date ranges.<\/p>\n
PERMCO<\/code> is the unique identifier for CRSP entities. PERMCO<\/code> remains constant over time, even if the company changes its name, CUSIP, or exchange ticker.<\/li>\nPERMNO<\/code> is the unique identifier for CRSP securities. PERMNO<\/code> also does not change over time too.<\/li>\nCOMNAM<\/code> represents the company name in CRSP at a specific point in time.<\/li>\nNCUSIP<\/code> is the 8-digit historical CUSIP for CRSP securities. A change in CUSIP can occur due to various reasons, including non-fundamental events like splits and company name changes. CRSP also has a header variable named CUSIP<\/code> that reflects only the most recent CUSIP for the entire time series.<\/li>\nNAMEDT<\/code> and NAMEENDDT<\/code> represent the first and last effective dates of each name structure, which includes CUSIP, company name, exchange code, exchange ticker, share class, and SIC code. Each PERMNO<\/code> has at least one name structure in the STOCKNAMES file.<\/li>\n<\/ul>\nRelevant FactSet Variables<\/strong><\/h4>\nThe FactSet SYM_V1_SYM_CUSIP_HIST file provides a mapping between FactSet permanent identifiers and all historical CUSIPs, along with their effective date ranges.<\/p>\n
\nFSYM_ID<\/code> is the unique identifier for FactSet securities (similar to PERMNO<\/code> in CRSP). FSYM_ID<\/code> remains unchanged over time. In another identifier file, ENT_V1_ENT_SCR_SEC_ENTITY_HIST, FactSet provides a mapping between FactSet securities and their issuing entities, uniquely identified by FACTSET_ENTITY_ID<\/code>, which, like PERMCO<\/code> in CRSP, does not change over time.<\/li>\nCUSIP<\/code> is the 9-digit historical CUSIP for FactSet securities. FactSet also has a header variable named MOST_RECENT<\/code> that reflects only the most recent CUSIP for the entire time series.<\/li>\nSTART_DATE<\/code> and END_DATE<\/code> represent the first and last effective dates of each name structure.<\/li>\n<\/ul>\nLinking FactSet FSYM_ID and CRSP PERMNO using CUSIP<\/strong><\/h4>\nBoth FactSet FSYM_ID<\/code> and CRSP PERMNO<\/code> are used to uniquely identify securities. We can link the two using the associated historical CUSIPs, which serve as a common identifier in both FactSet and CRSP.<\/p>\nThe following two-part code generates a link table that provides a mapping between FactSet FSYM_ID<\/code> and CRSP PERMNO<\/code>, along with their effective date ranges (START_DATE<\/code> and END_DATE<\/code>). The first part uses SAS, and the second part uses Stata. An SAS expert should be able to complete the task entirely in SAS. I use Stata for the second part simply because a handy command (tsspell<\/code>) is available in Stata.<\/p>\nFirst-part SAS code:<\/h5>\nproc sort data=sym_v1_sym_cusip_hist out=fs (keep=fsym_id cusip start_date end_date);\r\nwhere not(missing(cusip));\r\nby fsym_id cusip start_date;\r\nrun;\r\n\r\nproc sort data=stocknames out=crsp1 (keep=permno ncusip comnam namedt nameenddt);\r\nwhere not missing(ncusip);\r\nby permno ncusip namedt;\r\nrun;\r\n\r\ndata crsp2;\r\nset crsp1;\r\ndo i=0 to (nameenddt-namedt);\r\ndate=namedt+i;\r\noutput;\r\nend;\r\nformat namedt nameenddt date date9.;\r\ndrop i;\r\nrun;\r\n\r\nproc sql;\r\ncreate table linktable_temp as\r\nselect distinct a.permno, a.date, b.fsym_id\r\nfrom crsp2 a, fs b\r\nwhere a.ncusip=substr(b.cusip,1,8) and b.start_date<=a.date<=coalesce(b.end_date,today());\r\nquit;\r\n\r\nproc export data= linktable_temp\r\noutfile= \"C:\/linktable_temp\"\r\ndbms= dta replace;\r\nrun;\r\n\r\n<\/pre>\nSecond-part Stata code:<\/h5>\nuse linktable_temp, clear\r\n\r\negen pid=group(permno fsym_id)\r\n\r\ntsset pid date\r\n\r\ntsspell pid\r\n\r\nbysort pid _spell: egen start_date=min(date)\r\nbysort pid _spell: egen end_date=max(date)\r\n\r\nformat start_date end_date %td\r\n\r\nduplicates drop pid _spell, force\r\n\r\nkeep permno fsym_id start_date end_date\r\n\r\nsaveold linktable, replace v(12)\r\n\r\n<\/pre>\nPlease set up the LIBNAME<\/code> and directories in SAS and Stata to properly reference the relevant datasets. In Stata, use the command ssc install tsspell, replace<\/code> to install the third-party command.<\/p>\nPlease note that in the link table, the END_DATE<\/code> column represents the most recent update date of the CRSP STOCKNAMES file (rather than a missing value) if the link is still active. Therefore, it is important to use the most recent CRSP STOCKNAMES file to avoid any unexpected error.<\/p>\nI find that the link table only covers a small portion of the FactSet universe, as CRSP only keeps historical CUSIP data for securities traded in major exchanges, while FactSet extracts data from a much large security universe. Specifically, only 36,560 FSYM_ID<\/code>s can be linked to a PERMNO<\/code>, whereas there are 310,234 FSYM_ID<\/code>s in FactSet with FACTSET_ENTITY_ID<\/code>s.<\/p>\nI would like to express my gratitude to two WRDS articles: Merging CRSP and COMPUSTAT Data<\/a> and Linking IBES and CRSP Data<\/a>\u00a0for their valuable insights.<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":"
Both FactSet and CRSP offer identifier files that contain primary identifiers at the entity level and security level (note: an entity may issue multiple securities). These files provide a mapping between their primary identifiers and all other historical identifiers such … Continue reading