Business data induction 2022–11–29
Discussing CUSIP codes, practising opening and transforming financial CSV files in Excel and SPSS
Today we worked through our new CUSIP code blog post:
What is a CUSIP code and where are they used?
We explored the CUSIP code format, what the groups of digits mean, what the check sum digit means. We talked about the codes which open nicely in Excel and which don’t, with examples of lost leading zeros and incorrect scientific notation.
We talked about the sources of data files containing CUSIP codes, which countries use them, which databases use them. This year, if you download an Excel format file from WRDS which contains CUSIP codes, they will correctly open; this was not always the case!
Opening CSV files carefully in Excel and SPSS
We explored the two ways to custom import a CSV file into Excel, the different techniques depending on the version of Excel. We preferred the modern way (Power Query). We discussed the differences between various kinds of delimited files and how they vary with locale (especially in countries that use the dot and comma the other way from us for numbers).
We discussed the choices with formatting cells as text or numbers, and if it matters whether we make these changes before or after opening the file. We saw how decimal places and formulas work differently depending on the format. For example, if you import all columns as text, you cannot apply numerical formulas. Sometimes, the original data is lost and you cannot use formatting or formula to get it back; the student may have to re-run their query to get the data again.
We discussed where else these CSV import techniques could be useful, such as working with ISBN numbers in reports. There are applications within and outwith the subject of accounting and finance.
Finally, we looked at using SPSS to import CSV files then export them as Excel format. You can also use SPSS to open SAS-formatted files and export them to Excel format; this is very useful if you are using the Eventus database, something we will come back to another time.
Homework
- Have a go at opening CUSIP CSV files yourselves. Use the example file and create one with Compustat North America.
