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 name information. You can find it in two places:

• WRDS website: CRSP -> Annual Update -> Stock -> Version 2 (CIZ) -> Stock Header Information
• WRDS SAS directory: a_stock_v2/wrds_names_query.sas7bdat

wrds_names_query looks like this (keeping only the columns relevant to our discussion):

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

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

During the lifetime of a stock, its permno does not change, but its cusip may. Therefore:

• For the same permno, there is only one securitybegdt and securityenddt.
• For the same permno, there may be multiple 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 are 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 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 (see the Polars docs at https://pola.rs). The key logic is that:

• cusip from the two tables must match.
• The date of interest (fate) must fall within the period when the cusip–permno mapping is valid (secinfostartdt to secinfoenddt).