Databaser: on financial databases: Excel tips for data

Here are some common Excel functions you might run into when using datasets from our databases.

Autofilter
Often overlooked, but very useful
Go to the Data (gegevens) TAB en press this icon:

Select the entire column in one Go.
Type in CTRL-Shift-arrow down ↓ simultaneously

Straight up to the first cell
(CTRL-Home)
Straight down top the last cell
(CTRL-end)

Solving the ##### when columns are too narrow.
(applies to all columns in one single go)
Select the entire work sheet with CTRL-A or by clicking the arrow symbol top left
Double click column border to the right of the specific column

Calculating difference between two dates (handy in event studies> time window)
Take two ultimate dates
Dutch (language setting excel)
=datumverschil(A1;A2;"y") Years

=datumverschil(A1;A2;"m") months
=datumverschil(A1;A2;"d") days
English (language setting excel)
=datedif(A1;A2;"y") years
=datedif(A1;A2;"m") months
=datedif(A1;A2;"d") days

Subtracting or adding days to a date:
=(A1-30) or =(A1+30)

Changing data into text :
=text(A1;"dd-mm-jj" )
Dutch Excel
=tekst(A1;"dd-mm-jj" )

Adding or retrieving add-ins for Excel:
Step by step. Click the Excel symbol

Select Excel options (bottom of the pop-up menu)
Select invoegtoepassingen (add-ins)
When you can't find the needed ad-in select add-ins en pres start (go)

A list pops up:

If the add-in isn't there, choose the Browse button (bladeren)

An explorer windows opens where the desired add-in should be present.
If not, things get more complicated.

Combining cells

Concatenate cells (in Dutch samenvoegen)
=CONCATENATE(A1," ", B1)

Dutch Excel
TEKST.SAMENVOEGEN
=TEKST.SAMENVOEGEN(A1,"",B1)

Same effect, other formula:

Given a table with two data columns
=A1&B1
e.g.

The formula is visible behind the ƒx symbol and has to be filled in in Cell C1

With a space between the data:
=A1&" "&B1&"

NOTE: WHEN THIS DOESN'T WORK BECAUSE EXCEL CONFLICTS, TRY DOWNLOADING YOUR FILE IN EXCEL AND REPAIR THE CODES BY ADDING LOST ZEROS, IF THAT HAPPENS!!!!
See below

Completing company identifiers (CUSIP)
What if you got company dentifiers, and they lost the zeros preceeding the code?
That happens in some computers (guess it's a settings thing, since it doesn't always happen) when saving as Excel, and open in Excel... Excel considers those zeros worthless.

Example.
GVKey codes are used in the Wharton (WRDS) databases to identify companies. Thoese number consist of 6 numbers. Often the preceding 00 , to make of the six numbers of companies with smaller GVkeys, are removed.
Same happens with e.g. CUSIPs.

Checking how many numbers you need for CUSIP is to count the longest CUSIP codes. In this case 9.
Since CUSIP consist of
company issue check digit
123456 78 9
we can conclude that the most right digit in the codes are the check digits.
In other words, we need to add zeros.
In order to get back 9 digit CUSIP codes we can add the missing zeros with formula

=REPT("0";9-LEN(cell nr))&cell nr

GVKEY:
=REPT("0";6-LEN(cell nr))&cell nr
LEN stands for length

The following image shows codes without and with added zeros, plus the formula

IMPORTANT SETTINGS for the formula
Go to control panel > set language to English (USA) and region United States
Under TAB language > additional settings
List separator ;
or else the formula won't work
Additional additional settings (for Europeans)
Decimal symbol ,
Digit group symbol (thousands and such) .

Close with Apply and OK

RUNNING INTO PROBLEMS?

That happens to some people, depending on the computer they are using.
I get it . . .
If you get this in view, you'll get a hard time to use concatenate.
To be precise: you see a little green triangle in the top left of your sells > there is a hidden apostrophe... and it needs to be removed . . .

How to solve this:

1. make a selection of all codes
2. Copy to Notepad
3. Select all
4. Paste as number
5. It should be OK now.
6. This also (partially) happen to your CUSIP codes.
7. Repeat the same action
8. Note: I ran into another problem > the preceding zeros were removed in all cases where the green triangle happened
9. Use the REPT formula to repair this

Removing or adding the ninth digit?

As for removing the ninth digit from CUSIP:

Open your Tab delimited TXT file (using the wizard) in Excel

Create a new column ; select first cell in that column

Click formula button in the command rule ƒx

Type in LEFT (enter) and select from options

A pop up appears.

Click in the first cell of the CUSIP column

Next rule: type in number 8 saying you only want to keep the first 8 digits

Drag down result of double click the + in right lower corner of the cell

Note: I am certain there are more catches lurking to meddle your results.

I guess it is recommended to download as many identifiers and information on the issues as you can find.

Don't forget: the number of available stock exchanges in CRSP is limited (NASDAQ, NYSE, AMEX). Compustat might cover more. Download stock exchange code as well.

Second: when you want to download from CRSP and venture to data from Fundamentals Annual, keep in mind that both databases cover different entities.

Difference between two values (i.e. simplified return)
=(B2-B1)

Return (in finance) link
=(B2-B1)/B1

Converting dates.
When you got a row of dates that aren't recognised as dates.
For instance Compustat output format dates.

Make values from dates.

Best is to choose 07/25/1984 as it converts easily with the VALUE function (WAARDE).
Working with Dutch Excel (images)

The used table is an out file from Compustat with the date notation 25/07/1984
Wizard opening Text files in Excel: here

Once the value (waarde) function is selected, the next screen appears: