The Best Practice to Link CUSIP to PERMNO

WRDS provides numerous tables for you to link CUSIP to PERMNO. This post introduces the best practice to do that using the wrds_names_query table.

1 The wrds_names_query Table

The wrds_names_query table, as its name suggests, is compiled by WRDS and contains stock names information. You can find it at two places:

• From WRDS’s website: CRSP -> Annual Update -> Stock -> Version 2 (CIZ) -> Stock Header Information
• From WRDS’s SAS Directory: a_stock_v2 -> wrds_names_query.sas7bdat

wrds_names_query looks like this (I’m only keeping the columns relevent to our discussion):

You can see that in the wrds_names_query table, there’re two pairs of dates:

• secinfostartdt and secinfoenddt: they indicate the period when the permno-cusip mapping in the current row is valid.
• securitybegdt and securityenddt: they indicate the period when the stock (i.e., permno) is traded.

During the life time of a stock, its permno won’t change, but its cusip may. Therefore:

• For the same permno, it only has one securitybegdt and securityenddt
• For the same permno, it may have many different pairs of secinfostartdt and secinfoenddt.

In the following figure, you can see there’s only one securitybegdt and securityenddt value for permno=10001. However, there’re three cusip values associated with permno=10001, all with different secinfostartdt and secinfoenddt.

2 How to Link CUSIP to PERMNO

In the following case, we’ll add PERMNO to the Thomson/Refinitiv Mutual Fund data (S12). S12 records the mutual fund holdings for each quarter. It looks like this:

where each row records a stock (indicated by cusip) held by the mutual fund (fundno).

Our goal is to add permno to this table using the wrds_names_query table.

df.join_where(wrds_names_query, c.cusip=c.cusip_right,
	c.fate>=c.secinfostartdt, c.fate<=c.secinfoenddt)

Here I’m using the join_where method from Polars (read here for how amazing Polars is!) The key logic is that:

• cusip from two tables have to match.
• The date of interest (fate) has to be within the period when the cusip-permno mapping is valid (secinfostartdt and secinfoenddt).