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:

Select the first cell that needs to be converted.
The result looks like this: drag the cell downwards in order to apply all cells

The values have to be converted into text with formula
Convert values into text
=text(A1;"dd-mm-jj" )
Dutch Excel
=tekst(A1;"dd-mm-jj" )

Once the dates have been converted they can be used in other applications without problem.

All stocks from a region in exchanges - Compustat

Students often ask me how to get data from al stocks from contry A, perferably from stock exchange B
(with a lot of links to other blog items)

The least complex merthod is by using Compustat. Sometimes it possible to download historic compositions from certain indexes.
Earlier I explained how and where < this link >
But chances are -m see the lists - there are no historic compositions available.

The easiest (but largest) method is doing it like this.
1. Select e.g. Compustat North America (only S&P compositions)
2. Fundamentals Annual
3. Select your time scope
4. Click link "search entire database"
5. Make certain to select in Screening variables Ïndustry format also FS (fiancials)
6. Select all in Idfentifying information, including Stock Exchange code (for filtering later on)
Searching Data items with CTRL-F:
a. LOC (location, country code) > tells location of head quarters (for filtering later on)
b. Financial data items like balance sheet data and income statement (blog items)

Output format: recommended is Tab delimited TXT (no data loss opening in spread sheet programs)
Date format, by preference
Download and wait untill ready. Open your download in Excel.

Now use Auto filter.
When you want to use this output in either Compustat, of Datastream, copy identifiers, e.g. into another file and Remove Duplicates (excel TAB data)

In our example criterium ISIN

Now use this file in other databases.

Financial statement: connecting income statement and balance sheet

For most people reading income statements and balance sheets of companies and connecting them comes close to reading Latin upside down.
Simplified it boils down to this:
When you make a sale, your assets grow and your liabilities decrease
When you make an expense your liabilities grow and your assets decrease


Uiversity libraries may have access to several, less or more data sources to annual reports:
1.Annual reports > the library has a collection of digital annual reports .
2. Datastream
3. Compustat (fundamental annuals)
4. Company.Info

This post tries to chart which parts of Balance sheet and income statement can be downloaded in which database.


The arrows point connections between  Blance sheet and income statement.

Datastream

Since our World scope license in Datastream allows only 120 items from the balance sheet, not all balance data can be downloaded. (see posts in the scheme)

Compustat
It is interesting to check if Compustat provides more data.
The only data item not providing any data is SPPCH (fixed assets) Datastream does, however.

Drawing conclusion: you can't retrieve all data from a single database.
This means you'll have to combine databases.

note: the number of possible data-items within Compustat is much larger than the ones I mentioned, showing more complex parts of the balance sheet and income statement. I have decided to stick, when possible, to totals.

Company identifiers - Datastream

In an ideal world all data of all databases would be inter exchangeable without effort. Unfortunately that's not the case. This series of posts will provide hands on info on combining data sets between different data bases.

Why would you bother?

In cases with only a handful of company it would be no big deal to search your companies one by one, but when your work sets exceeds a significant number, say over 50, your searches would get too time consuming.

Using company names in combining data bases:

Not a good idea. Names change, and different data bases may decide to present company names differently: word order, language or abbreviated names.

Example:
Koninklijke Ahold
Datastream: Ahold Kon.
Compustat: KONINKLIJKE AHOLD NV
Amadeus: Koninklijke Ahold N.V.
Audit analytics: Royal Ahold
It's a better idea to use objective methods to identify company by, the 'company identifiers.'

 Datastream

This blog post assumes you are familiar with the basics of Datastream.
If not, then you can consult the following posts:

Balance sheet in Datastream
Event studies 1
Event studies 2
Making a data set is best done in Static Search.
See also: exporting data from Datastream into Compustat

Note: Datastream data sets can be translated ino more databases than just Compustat.

First. Search your set (e.g. a constituent of an index or all active equities on a stock exchange)
The identifiers can be found in datatypes search.
Recommended are Name, time, ISIN, SEDOL, Ticker, Cusip, dscode (datastream code)
Example. (NA is not available)

Converting downloaded Russell cusip for Wharton / Compustat
(USA companies only > download ISIN for non USA companies)
The Wharton machine has a conversion  option under the TAB Research.
(starting page)
When you got a Datastream output with Russell Cusip (USA companies) first remove the U in front of the code with function Right. First make new columns. Next click fucntion key ƒx; next search for right (find) Once found click OK to open the menu
TEXT: Click first cell with Russell CUSIP.
Num Chars: fill in 9
OK
You got CUSIP 9












Double click the + in the bottom right of the newly created code.
Select all new CUSIPs and copy into Notepad.
Save.
Press TAB Research and click option CUSIP converter
Use browse Button and select the desired output number (in my case I tested for CUSIP 6)
This works wonderfully.


Uploading CUSIP in Datastream
If you want to upload company identifiers from other databases into Datastream ISIN works best.
Wharton North America does not download ISIN (neither does SDC M&A).
From Wharton North America you can download CUSIP. These have to be converted. You can do that like this:
If you downloaded 9 digit cusip from Wharton / Compustat, and you want to use it in Datastream and / or change it into an ISIN code, you might consider doing the following.

Convert it from a Worldscope code.

CUSIP 123456789 Put U in front: U123456789
(this only works for companies that are available through Worldscope)
Method:
Make a column with U
Have your column with CUSIPS
Use function Concatenate (tekst samenvoegen) in Excel.
 = CONCATENATE ( A1, A2)

Insert new column. Type U: Double click + in right bottom corner
Use Concatenate formula:
= CONCATENATE ( A1, A2)

Double click + symbol
Upload into Datastream using the option Create list from range (the selected CUSIPS)

Perform Static search:
Find your uploaded list with the list picker
(magnifying glass symbol right beneath Select/find series button)
Datatypes: name, ISIN.
Also select DScode for your output.
Submit
Now you got ISIN

Use the ISIN for Time series data: repeat upload from range.

Also see mark Bruyneel's blog on this subject. click
Information on the create list from range option click
Same item for list picker click
Also see easy Excel tips click

Combining data sets 3: Amadeus, Zephyr and Bankscope to other databases

In an ideal world all data of all databases would be inter exchangeable without effort. Unfortunately that's not the case. This series of posts will provide hands on info on combining data sets between different data bases.

Why would you bother?

In cases with only a handful of company it would be no big deal to search your companies one by one, but when your work sets exceeds a significant number, say over 50, your searches would get too time consuming.

Using company names in combining data bases:

Not a good idea. Names change, and different data bases may decide to present company names differently: word order, language or abbreviated names.

Example:
Koninklijke Ahold
Datastream: Ahold Kon.
Compustat: KONINKLIJKE AHOLD NV
Amadeus: Koninklijke Ahold N.V.
Audit analytics: Royal Ahold
It's a better idea to use objective methods to identify company by, the 'company identifiers.'

Amadeus, Zephyr and Bankscope
These three Bureau van Dijk databases use the same company identifiers. Selecting the identifiers works with the same interface, so the presented method applies to all three databases. (all three databases use the same company identifiers)

Example: Netherlands, public, active companies, retailing.
(Select and press OK.)
Region



Legal form: public

Legal status: active companies







Sector. It's possible to select different industries. NACE, NAICS, US SIC and NACE rev.2 main section

The following blog item (click link) gives insight in the balance sheet data Amadeus delivers.
But what if that is not sufficient and you want to further your quest?

In that case it is a good idea to check out which identifiers can be  used.
You can do that either from the main menu: identification numbers

Or, from the 'list format' option. This can only be done after generating your list with results.

Choose New format. Third: from the results list you can also click the link : "add" (top right of the list screen).
It brings up te same window as list format > new format:

Now unfold the identifiers: stock data > identification numbers

Since you already generated the results list, the numbers of hits wont be further limited.

Export your results list.

To prevent issues with conversions and mess ups wit your original data, it is best recommended to export your data as tabulated TXT.
Next open your exported document in Excel with a wizard and select the identifier you want to use in another database.
See for an illustration Opening your compustat save (click link)

See also: Loading Zephyr data into Amadeus. (click link)

Combining data sets 1. SDC and Datastream

In an ideal world all data of all databases would be inter exchangeable without effort. Unfortunately that's not the case. This series of posts will provide hands on info on combining data sets between different data bases.
Why would you bother?

In cases with only a handful of company it would be no big deal to search your companies one by one, but when your work sets exceeds a significant number, say over 50, your searches would get too time consuming.

Using company names in combining data bases:

Not a good idea. Names change, and different data bases may decide to present company names differently: word order, language or abbreviated names.

Example:

Koninklijke Ahold

Datastream: Ahold Kon.

Compustat: KONINKLIJKE AHOLD NV

Amadeus: Koninklijke Ahold N.V.

Audit analytics: Royal Ahold

It's a better idea to use objective methods  to identify company by, the 'company identifiers.'

Between SDC and Datastream
Identifiers they use:
Datastream codes, codes constructed by Tomson Financial for their own databases; Ticker; CUSIP;
SEDOL (excepti SDC database Venture capital); ISIN only datasream and SDC database Global new Issues

In practice, it works like this:

When you are making your report, choose Custom reports > all items > type name identifier, where you can add extra (meta)data to your output, among them company identifiers. Depending on the SDC database there are more or less options. Obviously it is possible to download more identifiers in your output.
1.

2.

Repeat this for all items (depending on the SDC database you are using)
Press OK
3.

Press OK. Give a name for your saved file in the SDC database. Press save.
The next screen is optional to add some personal info to the report. (4 rows)
Or click cancel.
Press the Execute button to generate your report.

4.

There is no 100% guarrantee that all selected identifiers do provide an identifier for each item in the work set, as the next image illustrates.

5.

So, sometimes it is necessary to use more identifiers when you uploading your work set to another database, even when using Datastreamcodes. When you download you report in Excel you can use the autofilter to save you some work.

Closing SDC, the program askes you to save your search. Do it. You can recall your last actions when using SDC again.

Using your workset:

Removing empty cells: autofilter > deselect lege cellen / empty cells (or not null)

In my case I use Datastream codes. Next select all cells > go to the Datastream TAB and select option 'Create List from range'

The next menu give the possibilty to add an own name to the uploaded file. But often this is not presentend as a choice when trying to open the the list. Memorize date.

When you upload your selection to the remote server of Datastream, memorize the upload mnemonic that's given to the document, it identifies your upload.

Using the uploaded list:

Go to either the Static search menu or Times series (depending on what you want to do)

Static Search for creating a work set in Datastream, where you might find other identifiers (coming from M&A does not give the possibility to download ISIN, but in Datastream you can.

In the menu use this button (Listpicker > looking glass)

Typ the mnemonic of the uploaded list: L#G74705

TIP: if you have got difficulty incorporating your works set into Datastream. lack of hits might be the result of the type of security you are using. Mark Bruyneel explains here, how you can solve this.