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:
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 be 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
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.