Datastream instructor training 20190702

ASSET4 for ESG data; Custom headers for combining static and time series requests

Last session’s challenge

Last session we looked at panel data and request tables. I set a challenge:

Download the ‘Environmental Score’ for the largest emerging markets BRICS countries.

The learners attempted this in various ways:

  • Perform standard time series requests for constituents of the five countries (BRICS).
  • Got a list of companies for the main index in each of these countries (different length for each). Get the market value for this year and sort the answers, keep the top 5.
  • Looked for a different approach to find the environmental score, browsed the sample sheets and found ASSET4 sheets. Tried the sectors sheet, then the companies version. The companies version let us search for one company to get the various scores.

The ‘envscore’ item is used, despite it saying ‘inactive’. (Perhaps I should have set the challenge to find a different item.)

There is no single way to answer the challenge. The purpose was to get the learners more familiar with various aspects of Datastream and to practice breaking down a request in different ways.

Samples sheets including ASSET4

Sample sheets are created by data experts at Refinitiv. They contain macros to populate useful tables and charts of data on a particular topic. The two most commonly used sample sheets we use are for ASSET4 Environmental, Social and Governance (ESG) data (one focuses on sectors, the other on companies).

When you downloaded sample sheets they are saved to a hidden folder. Instructions are on another post.

Constituent lists for entire countries

There are pre-made lists of all active companies in a country, produced by Worldscope. To find one, start a new request, search for series, type ‘wscope’ then a space then the name of the country, for example, ‘wscope brazil’. You can get a list with code ‘wscopebr’ for Brazil.

The 2-character country codes are not the ISO standard. There is a reference document but it is easier to type the name of the country, as described above.

There is no list for the United States, it is too big.

Custom header, to combine static and time series requests

We learn in the Datastream workshop that you cannot mix time series and static variables in the same request. This is not actually entirely true. You can add static datatypes in a ‘Custom Header’ of a time series request.

  • Start a time series request, for example, for the active companies in South Africa (the Worldscope code is ‘wscopesa’).
  • Look for daily price for the last week (the datatype ‘P’ from ‘-7d’ to today).
  • Before you run the search, click on the custom header button… TK
  • Use the orange ‘Datatypes’ button to find static datatypes such as ‘sector’ level 4 name …TK — make sure you don’t have a filter for time series items.
  • Use the ‘+’ button to add the static items to the search. Press OK.
  • Tick the ‘Transpose’ button then run the search.

When the data is downloading, you will get the name colulmn and time series columns first, in chunks from top to bottom. After that, the static columns will download, in chunks from top to bottom. If there is no time series data in the requested period, these fields show error messages (including the name column), but the static data may still download.

Side note: sector codes

There are different levels of sector code, 1 through 6, getting increasingly specific. There is a friendly name and a mnemonic datatype for each. TK


Which sector (level 4) had the most employees in Colombia in 2017? (Bonus marks for pivot tables).


  • Time series request
  • Worldscope list of companies
  • Custom header
  • Remove blanks and sort

Second challenge, if you need more

Total profit for the largest banks in Scandinavia (top 5 per country), and GDP for each country, for 2005 to 2014. How can you combine these requests? Advanced: How can you see the relationship between the performance of banks and the country’s wealth?




‘Train the trainer’ materials for the Library's specialist financial and business databases at The University of Manchester.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Phil Reed

Phil Reed

Librarian Data Specialist, The University of Manchester. Supporting teaching, learning and research with financial databases, digital skills and scholarship.

More from Medium

Blxckout point of view, status update & example use cases

OpenShift Service Mesh on IBM System Z/LinuxONE Part I: Installation

The big red letter `O` with parts of it shifted is the logo of Red Hat OpenShift. The little blue sails are the logo of Maistra, the main component of Red Hat OpenShift Service Mesh. The asymmetrical vesica piscis below the OpenShift logo is the logo of the Kiali subsystem of Service Mesh. The little blue Go Gopher wearing a Tyrolean hat perched atop an IBM LinuxOne chassis is the logo for the Jaeger Tracking system used in Red Hat OpenShift Service Mesh.

veTokens overview

6WIND vRouter Provisioning Guide for Bare Metal and VM Deployments