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?