The CUSIP Problem
If you're looking at N-PORT filings data, approximately 15% of all filings will have one or more positions whose CUSIP will be auto-corrected by Excel into a number. If you're looking at NAIC transaction data, approximately 51% of life, property and casualty insurers will have one or more transactions in such securities. Data updated as of Sept4, 2021.
The 32nds Problem
Prices in Structured Products (and a few other areas of US Fixed Income) are still quoted in 32nds (Thank you England!). For bonds near par, Excel will accept them without attempting to turn these values into something they're not. For example, if you key
99-31 into a spreadsheet or load a CSV with such a value, it will remain as
99-31. However, for low dollar price bonds such as IOs they may be auto-converted to dates. For example, if you enter
11-02 into Excel it will be converted into November 2nd (of the current year). The conversion will even occur for dates that make no sense. For example,
4-31 will be converted to April 1, 1931! This will also happen for February dates above 28. These occur quite frequently for specs whose pay-ups are around 3 points.
None of the data in your spreadsheet will leave your browser and hit any remote servers. To verify, hit F12 (Developer Tools) on your browser and watch the Network tab.
Scan a Spreadsheet for Auto-Correct Errors (.xls, .xlsx, .xlsm extensions)
Links and Other Resources
- In Excel, 1900 is a leap year because Lotus 1-2-3 says so.
- Stop automatically changing numbers to dates.
- Brief Overview of the Problem and Possible Solutions
- Some thoughts about the problem on superuser.com
- Regular Expression that matches all ambiguous CUSIPs
- Our List of all Fixed Income CUSIPs Excel auto-corrects to numbers
- In genomics, scientists renamed genes rather than trying to change Excel, article link.