Home > Business Databases, company information > Getting data on all UK companies except one sector in Datastream

Getting data on all UK companies except one sector in Datastream

A common request in Thomson Reuters Datastream is to get all the companies in a particular country except for a few sectors. Previously, the Criteria Search function provided an easy way to this. Since Criteria Search is being phased out (it is not compatible with Internet Explorer 11), we have been looking at alternative approaches to some of these data requests. This example shows you one.

New request table

Launch Excel with the Datastream tab and click the New Request Table button. (Request tables are covered in other posts.)

Datastream Request table for all UK companies

Datastream request table of a time series request for a list with Custom Header containing static datatypes.

In this example, I have chosen the list WSCOPEUK which is a contituent list of all active, dead and delisted UK companies (over 5,000 in size). You may wish to try this approach out with something smaller first, such as LFTSE100 for the constituents of the FTSE 100 index.

The Request Type is TSL (time series for a list) and the Datatype is WC04225 (Extraordinary Items), from 01/01/2013 to 01/01/2015. The trick here is to choose Transpose and to include a custom header of the static datatype INDM4, which is the Level 4 Industry Name, with DS Code and Name as optional extras (so Format is HRCTF:INDM4,DSCD,NAME).

Custom headers were introduced in a previous post. Again, try this out with a small date range before you are happy with the output format.

Insert Excel table

Select the output results worksheet Sheet1.

Datastream output filtered in an Excel table

The results of a Datastream request after applying an Excel table, showing blanks and errors for lines where no data was present in the selected time period.

Assuming you used Transpose in the Datastream request, there will be a row containing the headers such as Name, Industry Level 4 Name, DS Code and each date period (2013, 2014 and 2015 in this case). You can filter out certain rows by selecting the data from this row to the last row with content and choosing Insert > Table from the Excel menu (and tick “My table has headers”). Here, I selected cells A4 to G5331.

A filter icon appears at the top-right of every column. Click it in the industry column and untick the industry you wish to exclude (e.g. Financial Services(4)). Assuming you have the DS Code variable, you can now select cells from that column to create a new list and continue your research.


Datastream has a Worldscope constituent list for companies (equities) in most countries except the United States (which would be too large a list to use). These can be found in the Datastream Navigator by typing WSCOPE in the simple search box. A different industry classification may be required for some countries.

  1. Johan
    13 March 2016 at 2:52 pm

    Given that the criteria search has been phased out, do you know how to download the WSCOPEUK list only including equities with primary quote and major security?

    • Phil Reed
      15 March 2016 at 5:00 pm

      Hi Johan, that is a good question!
      The official equivalent of Criteria Search is the filters on the left side of the Datastream Navigator, but this does not cover primary quote and major security as you know. I will let you know if I find an answer. I recommend that you speak to your Thomson Reuters account manager to let them know that an important feature for researchers does not appear to be available. We may run a series of posts on Criteria Search workarounds including this example.
      Best wishes,

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s