Combining data: Another Vlookup example

In the past I created a Vlookup example to show it is possible to use Microsoft Excel to combine data from datasets. The example was a small example based on CRSP and Compustat. I sometimes get questions on how to use Excel to do this and that is why I thought another example using other databases may prove useful. This time I use output data from SDC Platinum (sometoimes known as ThomsonOne) and combined it with data from Datastream.

Before you use Excel to combine data it is important to remember that (in my experience) Excel and Excel worksheets usually work from left to right. That means that very often searches will go through worksheets and within worksheets in rows fromk left to right while searching. The original source sheets where I want to extract data from, I usually put to the left of the destination sheet where I want to combine the data. This is usually my final worksheet on the right.

Example:

1) Download from SDC Platinum’s database Global New Issue
This database is also available from Thomson Reuters as the Deals database in Thomson One. My starting point for the exercise is a download of IPO data for companies that issued ordinary or common shares at an American exchange. I made sure that I included identification codes like ISIN in the download. Using these ISIN codes I will look up additional data and combine it later (other codes may also be used). As a first step I needed to filter the SDC output to make sure that I have only IPO’s with ISIN codes. The filter option in Excel is available in the first Tab (Home) on the right side and in the tab Data (= Gegevens).

  • Select the first row and click the Filter option.
  • Click the Filter icon and (in the ISIN column) unmark the option: Blanks (= empty cells / lege cellen).

2) Copy the filtered data

  • Use excel to copy the filtered result to another Excel sheet. We will use this sheet to add Datastream data to at a later stage. This will be the destination worksheet.
  • Make sure that the ISIN codes are copied and pasted as the first Column in this sheet: only 1 column of ISIN codes should remain!
  • Now copy the the column ISIN codes to a new sheet (If necessary remove duplicate ISIN codes using this option in the Excel Data tab [= Gegevens]).
  • Use the Datastream Add-in to upload the ISIN codes. Click the Datastream option called: Create list (/from range) and follow the steps.

3) Using the list we can now find out what the first trading days are for each stock.

  • Go to the Datastream tab/Add-in
  • Click the option Static search
  • Using the List picker button (below the orange button “Find series”) to select the uploaded list
  • Use the Datatype Base date (BDATE) and click the submit button to download these dates.

4) For the example we also need price data from Datastream
In this example we want the price data for the first 5 trading days, and specifically the original historical unaltered closing price: UP.

  • To download the data using the Request Table option in Datastream we first need to calculate the dates 5 days after each Base date. Do this by adding 5: like you would with regular numbers:
  • Copy the result in the column next to it. Use the option Past Special > values. This way you lose the function but keep the calculated dates.
  • Now use the ISIN codes as well as the column Base Dates and Calculated end dates to create a Request table search in Datastream.Make sure to have the original ISIN codes returned as well in the output! Another blog post explains exactly how to do a Request Table search. The output from Datastream should look as follows:

5) Remove unnecessary rows in Excel
For the matching data we first need to do the following:

  • Use the Excel Text filter option to kep only the price data and the ISIN codes. See example 8 and 9.
  • Now insert a column to the right of the column with the ISIN codes AND datatype code UP. Use the Left() (= links() ) function to extract the 12 digit ISIN codes.
  • Copy the result to a new sheet. Use Past Special > Values to keep the ISIN codes and remove the Excel functions.

6) Matching data using Vlookup
Important: make sure that both the source/data-sheets where you get data from are in the same Excel worksheet but to the left of the worksheet in which you want to combine the data (destinatin worksheet).

  • Go to the destination sheet with the IPO data from step 2 and click in the cell to the right of the last column with data
  • Use the Excel function search option (fx button) to find the Vlookup function (= Verticaal.zoeken):
  • The Vlookup popup has 4 arguments that need to be used:
  • Argument/box 1: indicate the value that needs to be looked up in a data/source worksheet: in this example the isin code of the first row:
  • Argument/box 2: indicate the full table where it needs to search for this specific value (= ISIN code). This includes the first column of ISIN codes and the second column with the first trading date = Base Date.
  • Argument/box 3: type in the number of the column where the data comes from that you want to see in the destination sheet (IPO data from SDC) from the source sheet (= output from static Datastream search). In this case column 2 (= B).
  • Argument/box 4: indicate with the text TRUE or FALSE whether the code should be an exact or approximate match. In this case we need FALSE as we need an exact match.
  • Click OK to continue. The result for the first row: it shows the base date from the original result sheet with the output of the Static search in Datastream.Copy this Vlookup function downward for the other rows.
  • In the column next to it (right side) we can add the first price from another worksheet (created at step 5) using a similar Vlookup option:

Email

%d bloggers like this: