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)

Looking for (company) codes in Compustat

Recently Compustat eadded a second search option in Step 2:  How would you like to search this dataset?
Namely code lookup Beta.
The look is slightly different, and importanlty search is not limited to left to right "as is". You can choose between contains, starts with, is exactly and sounds like anymore. Simply move cursor over Search.

The result looks like this.
New is that you easily can select more than item. Tricky is that more identifiers are listed than can be used in the 4 four step menu. Stick to the ones you CAN use.

                   










Have fun.