Home > company information > Linking Compustat and Mergent FISD on WRDS

Linking Compustat and Mergent FISD on WRDS

“How can we get details of corporate bonds issued by S&P 500 companies?” This enquiry has prompted this post that concentrates on the issues of linking databases, an essential skill in many research projects.

WRDS-iconMergent FISD (Fixed Income Securities Database) is a comprehensive database of publicly-offered U.S. bonds available on WRDS. It is a relatively new addition to the University of Manchester research database portfolio, but the WRDS web interface follows the same general style for all databases. The key to getting your data is having a list of appropriate identifiers.

For this query an brief review of Mergent FSID on WRDS and “Issuer CUSIP” is a valid identifier.

Step 1 – Getting CUSIPs for S&P 500 companies

This is quite easy in WRDS as Compustat has an Index Constituents query (Select Compustat – North America – Index Constituents). The i0003 identifier for the S&P500 index can be found using the code lookup facility.

You select your date range, index identifier, and check that you include CUSIP, from company information, as one of the variables to include in the results.

The results can be significantly more than 500 companies, depending on your selected date range, as the constituents of the S&P 500 change over time, and some companies have had several CUSIPs over time.

Step 2 – Greeting CUSIPs into format for Mergent FISD

In the ideal world the CUSIPs from Compustat would be exactly those required by Mergent FISD. In fact a little work is needed as the CUSIPs generated are 9 digits (for the stock issue that is in the S&P 500) and FISD requires the 6 digit issues CUSIP.

Therefore we used Excel, or another program, to trim the final 3 digits from the Compustat generated CUSIPs –

931142103 -> 931142 , 00206R102 -> 00206R

This can produce some duplicates that can be eliminated.

Step 3 – Using Issuer CUSIPs to query Mergent FISD

For a WRDS query you need to copy your identifiers into a plain text (.txt) file – one identifier per line. When indicating the variable (id) “to search by” we select the Issuer CUSIP option, and then the Upload a file containing company codes.

Selecting the FISD Bond Issues Query –  choosing some test dates and test variables for the results gave 2315 observations – some included in the screenshot below.

 

Mergent FISD sample results (click to expand)

Mergent FISD sample results (click to expand)

One of the strengths of WRDS is that once you have got your file of identifiers it is easy to submit another request if you want to vary the time period, or the variables in the results. You can also choose the format of the results.

Related posts

CUSIP – 6-digit cusip to 9-digit cusip (posted June 2010) – has more detail on CUSIPs

S&P 500 (Standard and Poor’s 500 Index) (posted December 2011) – has more on the S&P 500 on WRDS

Additional comment on S&P 500 code

When using the “Index Constituents Code Lookup” facility on WRDS there are two potential candidates for the S&P 500 – S&P 500 Comp – Ltd, with Ticker I0003, and S&P 500 Comp – Wed, with Ticker I0010.

A little investigation on the WRDS online help reveals the difference.

“The S&P 500 LTD (last trading day) has the ticker I0003 and S&P 500 WED (last Wednesday of the month) has ticker I0010. Two indices were created because some investors felt that the “last trading day” was too volatile, and preferred the last “Wednesday” methodology. Most people use I0003 (“LTD”)” (WRDS, no date)

The monthly value of an index, or equity, is by convention taken as the last trading date of the month. However, these end of month last trading day values can be more volatile for indices where there is high volumes of options traded on their expiry date. In our query to get the index constituents the choice of index will make no difference.

References

WRDS. (no date) WRDS (Wharton Research Data Services) Knowledge Base with FAQs – WRDS: S&P 500 Data. [Online]. Available at: WRDS http://wrds-web.wharton.upenn.edu/wrds/ (Accessed: 25 June 2014)

Advertisements
Categories: company information Tags: ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s