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