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.

Email

%d bloggers like this: