Datastream downloads & data matching continued

In the previous blog item I explained how Excel could be used to match data from Compustat with that from Datastream. The Datastream data download had to be edited to allow for a match with VLOOKUP. There is a way to make it a bit easier to match data from Datastream: by using a specific tool called a Request Table. By using a Request Table you can download the data for each year in the same columns in Excel: each year below the previous year, and so on. In a previous post I explained how to use this tool.

Using the Request Table Tool avoids having to copy the data into a single column when you want to match the data using VLOOKUP. There are three important things to remember when using this tool (in this situation):

1) First you need to upload the selected list of equities to create a list that Datastream can then use for the Time Series List requests in the Request Table search. See the previous post for an example (screenshot)

2) The usefulness of the tool depends on the size of the list of equities. It may take a long time to download a long list using this tool. Also, you need to use an Excel formula to calculate where the next year/download should start. For a list of 25 equities the formula I use would look like the following example where I skip 30 rows before the next annual prices are downloaded (I usually take a few extra rows to be safe): =”=Sheet1!$A”&30*ROW()
See the original post on the Request Table for a full example.

3) Be careful if you need data with a certain frequency. For the example I wanted to get the calendar year end price. The default download period for a regular download needed to be chosen accordingly. For the start date I used 12-30-00 and as the End date: 01-03-01. With the Frequency Annual this worked fine. Had I chosen 1 January and 31 December (for each year) the download would not have been okay (for this example). Example screenshot of the Request table search:

For some years specific equities may not be listed and the download will show the corresponding Error code. Example search result:

As the final step you need to add years for each separate yearly date range. Using the steps from the previous post to get the ISIN codes and then combine them with the years (to create unique ID’s) everything should work fine for the Vlookup option. See example screenshot:

The Request Table search I used in this example can be downloaded here.


Matching Compustat & Datastream data in Excel

Although it is recommended to use the same source for as much of your data as possible, there is not always an alternative. Compustat North America, for instance, allows you to downoad anual report data through the Fundamentals Annual database together with stock prices at the end of the calendar or fiscal year. In this case there is (often) no need to go to another source.
In the case of Compustat Global the Fundamentals Annual part database does not have these two Price variables. In this case you can choose to match data from Compustat with data from Datastream. In the example I use here I used a list of ISIN codes of current AEX index companies from datastream.

N.B.: Important: annual report data is also available through Worldscope in Datastream. In this example I assumed that the starting point for a selection would be Compustat Global.

For the example I created an example spreadsheet where you can see how I combined the data step by step. You can download the example Excel file here.

1) First I used the list of equities to download annual report data from Compustat for the period 2000 to 2012. Example Screenshot (WRDS tab in the Excel file):

2) Next I used Datastream to download the End of the calendar year price. First I uploaded the list using the option in the Excel Datastream add-in: Create List(from range):

Next I did a time series search using the uploaded list (use the List picker button below the orange button Find Series). I chose to download the data Transposed which makes it easy to use the download and making the combination. Transpose = yearly data in the columns. This way I can also choose to download the codes that I uploaded and get them in my output. See screenshot:

The output looks as you see it in the Excel spreadsheet tab Datastream:

3) To match the data from Compustat with the Datastream data we need to combine the ISIN codes with the year to make it unique. First we need to use the LEFT() function in Excel (in Dutch = LINKS() )to create a new column with only the ISIN code. See the example screenshot (= This is tab Stap 1 in the Excel spreadsheet):

4) The problem with the Datastream output is also that the ISIN codes are returned but not for each column of data. We therefore need to copy the data and make sure that the codes and annual columns are copied below each other. For each year we also need to add the year to be able to later generate a unique ISIN+year combination. See the example (= This is tab Stap 2 in the Excel spreadsheet):

5) To create unique ISIN+Year ID codes we can use the CONCATENATE() Excel function (in Dutch = Tekst.Samenvoegen). See the previous example Datastream screenshot (= In Excel see column C in the tab Stap 2 for Datastream and Column A in tab Stap 3 for WRDS) and the WRDS example here:

6) As the final step we can now use the data from Datastream and add it to the WRDS spreadsheet using the VLOOKUP() function (in Dutch = Vertikaal.Zoeken() ). The full formula looks like this: =VLOOKUP(A2,’Stap 2′!$C:$D,2,FALSE). If I break it down it looks as follows:

– Function start: =VLOOKUP(
– Look up content of Cell: A2
– In Sheet Stap 2 look for value in cell range columns C and D: ‘Stap 2’!$C:$D
– If there is a match show the value in the second selected column (= column D): 2
– Only return a value if the match is exact: FALSE.
– Function close: )

A) The elements in the VLOOKUP function are divided by comma’s. In the Dutch language version of Excel those would be semi-colons ( ; ).
B) In the example above I used Dollar signs to fixate the cell table range: $C:$D. It is not always necessary to do this in a VLOOKUP function. They also do not automatically appear when you have selected the table-range. You can automatically add them in the formula using function key F4 or by typing them in.

In the tab Stap 4 of the Excel spreadsheet you see how this works:


Selecting Cross-border M&A deals in SDC

The Mergers & Acquisitions database of SDC Platinum has a variable that easily allows you to select deals between nations. This variable is the Cross Border Transaction Flag which says Yes or No depending on the situation. When you want to use this variable you need to be aware that the Cross-border variable indicates that the country/nation of the (direct) target is different from the country/nation of the acquiring ultimate company. The Immediate acquiring company may be in the same country or even state!

SDC definition of the variable CROSS: Cross Border Transaction Flag: Yes/No flag set to ‘Y’ when the target company (or assets being sold) in the deal is not located in the same country as the acquiror ultimate parent.

Example screenshot when only using the cross-border variable:

If your research requires that the companies that are directly involved in the deal, to be in different countries/nations, then you need to add additional search steps to limit the search result. You can select all target companies that are located in a certain country: for instance the United States. You can use the variable Target Nation for this. Next, you can add the seaarch variable Acquiror Nation and exclude (direct) acquiring companies that are also located in the same country: in this case also United States.
Example screenshot when including the extra steps:

NB: Make sure to define nations when appropriate. The above example I used was United States. In this case you would have had to decide if the following should be included: Guam, Hawaii, etc.