Dates and data sets from Wharton databases

When you get data from a database through Wharton you need to watch out for dates. The dates are presented in an English format and your computer may have a different default (language) notation for dates. For instance, when you are working on a computer which has the Dutch language as a default setting or when you are using a program which is a Dutch language version of that program. Dates may then not be presented or translated correctly. This is especially a problem when you need dates for graphics or calculations.

For example: when you open a text file with data which from a Compustat database dates like 29 March 2006 will not be interpreted and translated as dates usually are by Microsoft Excel. The month March was not understood to be Maart. The same usually goes for the months May, June, July and October which in Dutch are: mei, juni, juli and oktober.

There is of course and easy solution to the problem: do a couple of search and replace actions in the column with the dates and everything should be okay again. You can usually find this option through the menu of the program you are using under "Bewerken"  or "Edit".

Another problem which can happen is that the date is a string of numbers. Microsoft Excel does not understand that a string like 20010131 is the date 31 January 2001. Excel can be used to solve this problem using some of the standard functions available in the program. Below you can download an original small file with data from a CRSP (Center for Research in Security Prices) database:

crsp-example.xls

In this file I have used the functions Left (= Links), Part or Mid (= Deel) and Right (= Rechts) in columns J, K and L to pull apart the date in column C. Then I used the date function of Excel in column M to combine the parts so Excel understands it.

The date which is shown in column M cannot be used for calculations because the content of each cell is not actually a date but a function/formula. To get a regular date that you can work with, you then need to copy the column to another column as a value using the menu option: Edit > Paste special (See column N as an example). If the column first shows only numbers you can edit the makeup of the column to show the dates in a date format of your choice.

Alternatively, you can also save the Excel file as a text file and open the new text file using Excel. Column M will now have a regular date in each cell.

12:49 AM - 6 August 2008