FAQ: CUSIPs in WRDS output

When you download data from WRDS to an Excel spreadsheet and include CUSIPs in your file, you often have CUSIPs that that look weird. See this example from CompuStat North America Index Constituents, the column Company Cusip should contain 9-digit CUSIPs:

cusips with E

The E’s in the CUSIPs of Dun & Bradstreet and Dr Pepper are recognized by Excel as a mathematical constant (2,718) and the CUSIPs of Amazon.com and Apple are incomplete (they start with zero’s, but these are not visible). This can very unhandy if you use these codes as input in other databases.

You can solve this by downloading the data from WRDS in another output format (comma-delimited text or tab-delimited text) and then:

  • Open a new Excel sheet
  • Open the ribbon Data
  • Choose From Text
  • Browse to the CSV or txt file and click Import
  • In the Text Import Wizard, mark Delimited and click Next
  • In step 2 of the Text Import Wizard choose tab or comma (depending on the output format of your file) and click Next
  • In step 3 of the Text Import Wizard search for the column with the CUSIPs in the Data preview and mark Text under Column data format.
  • Click Finish to import the data

Text import wizard 3

You will now have 9-digit cusips in the output. The disadvantage is the header of the columns: the headers in an Excel download from WRDS contain more information. You can solve this by downloading the data as Excel as well and copy and paste the headers.

cusips with E2

Advertisements

2 thoughts on “FAQ: CUSIPs in WRDS output

  1. Reblogged this on Business Research Plus and commented:
    Using CUSIP codes can cause headaches in Excel. They can be confused with numbers expressed in scientific format or leading zeroes can be lost. The blog post below, written by EDSC, offers some assistance on the subject.

  2. Thanks Judith. Had the same problem but with the Thomson Reuters Excel Plugin, where your solution would not work. Still, your entry gave me the right idea. I fixed it by first formatting all cells in the excel sheet as “Text”. I then formatted the cell in which I started the request (usually “A1”) back to “Standard”. Then I did the request. Worked just fine 🙂

    Greetings from Germany
    Sebastian

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