Asset4 ESG dataset & scores

The corporate social responsibility dataset Asset4 ESG is available through Datastream / Eikon products. The dataset contains an overall score for companies, as well as individual scores for the four pillars on which it is based. In a nutshell it involved:

  • Overall Equal weighted rating score: A4IR
  • Corporate Governance score: CGVSCORE
  • Economic score: ECNSCORE
  • Environmental score: ENVSCORE
  • Social score: SOCSCORE

These individual 4 scores were based on a lot of components. From 2014 on Thomson stopped collecting data on some of the components and few more data measures from 2017 onwards. The related datatypes got the indicator inactive (between brackets). The old/stale data (as Thomson puts it) has, however, not been removed, or not yet.

A new methodlogy was created and these are no longer indicated as being a specific part of the Asset4 dataset but are Thomson series. The new overall scores are now:

  • The ESG score (TRESGS): is an overall company score based on the self-reported information in the environmental, social and corporate governance pillars.
  • The ESG controversies score (TRESGCCS): is calculated based on 23 ESG controversy topics. During the year, if a scandal occurs, the company involved is penalized and this affects their overall ESG Combined Score and grading
  • The ESG Combined score (TRESGCS): Thomson Reuters ESG Combined Score is an overall company score based on the reported information in the environmental, social and corporate governance pillars (ESG Score) with an ESG Controversies overlay. When companies were involved in ESG controversies, the ESG Combined Score is calculated as the weighted average of the ESG scores and ESG Controversies score per fiscal period, with recent controversies reflected in the latest complete period. When companies were not involved in ESG controversies, the ESG Combined Score is equal to the ESG Score.

In the extranet website of Datastream / Eikon a document is available explaining the new methodology in more detail. There is also an Excel document with details on data types as well as how they are calculated based on other ESG data types.
By filtering the list/glossary using the pillar column on ESG score you can also find new 10 main overall scores for companies for specific items which include: Community Score (TRESGSOCOS), Resource Use Score (TRESGENRRS), and Workforce Score (TRESGSOWOS).

In the recent past the ESG items have increasingly become important and are used by many investors as well as researchers & students. From the latter perspective I hope the inactive items (even though they are discontinued) will not be deleted from the original dataset. I hope this, because of reproducability of earlier research. The University of Manchester blog offers some additional information, including the 2015 list of Asset4 datatypes.

N.B.: The number of companies that have been covered through Asset4 has grown from about 1500 in 2002 to 7000+ these days (world wide). If you wish to know which companies are covered, you can use the Find series button to search on ASSET4 and you will get lists by country etc. This includes the Full universe list with the code: LAST4ESG


Fiscal Years & matching data

When you download annual report data from Compustat and need to match this with similar type data from Datastream you need to be sure that when you combine data it is from the same fiscal years. In Compustat you can download the data with both the variables Fiscal Year (= fyear) and Fiscal Year End (= FYR).

In Datastream the database Worldscope provides the annual report data. The data is usually reported with the calendar year as column or row headers. This can be compared to the Fiscal year in Compustat. For the fiscal year end the variable WC05350 can be used.

The unique combination to combine data from these sources is then:

  • Compustat Global & Datastream:
    ISIN (or Sedol) + Fiscal year + Fiscal year end
  • Compustat North America & Datastream:
    Cusip 9 / Ticker + Fiscal year + Fiscal Year End


1) In Compustat Global you need to download the Currency code as well as the data etc. to know in what currency the data is made available in the database. The data from Compustat North America is reported in US Dollar. In Datastream you can choose to download your data in a specific currency (calculated using historical exchange rates according to Thomson)

2) Accounting Standard can be an important variable as data in databases can be from different statements as reported by a company (statutory reports, SEC filings, etc.). In Compustat this variable is: acctstd. In Datastream it is the variable: WC07536


Cleaning Datastream downloads

Using ISIN codes is very often a good choice when you download data from Datastream and want to combine it with other data. ISIN codes are also regularly available in other databases. For the Datastream downloads you will have to seperate the ISIN again from the code of the variable which (as a default) is presented after the ISIN code between brackets.

Because every ISIN code consists of 12 digits, seperating them in Excel is easy: use the function =left(). Example: =left(CELL; 12)

In Stata you can use the substring command substr() to do something similar.
Example: gen isin=substr(Code, 1, 12)

Seperating the code of a company from the code of the variable in Datastream is more difficult if the company codes have varying lengths. The Datastream code (Datastream mnemonic) for instance, is such a code. If you need to seperate this from the code of the variable you need to make use of the length of the variable code which remains the same. To do this we combine to Excel functions: =left() and =len(). Example: =left(Cell; len(Cell)-11)

In Stata a similar thing can be done using a combination of substr() and strlen(). Example:
gen dscd=substr(variable, 1 ,(strlen(variable)-11))

Another thing which you may find necessary is seperating the names of the variables from the names of the companies. The variable name is most easily seperated since the name is a fixed number of characters. Again similar functions using the length of the content of CELLS can be used. A different way of seperating both is using a combination of the Left() function combined with the function Find(). For most downloads from Datastream both items are combined but there is a common element seperating the two, namely: ” “.
Using this we can build the following formula: =left(CELL;(FIND(” – “;CELL)))

In Stata a similar option is available to split a combined variable. The function split can be used as follows: split Name, generate(deel) parse(” – “). This generates two new variables that both start with the name deel and are numbered, for example: deel1 deel2. Afterwards you rename the variables deel1 and deel2 to what you need.


Is it possible to use RIC codes in Datastream?

Recently a student asked the question if RIC codes could be used in other Databases to search for more information on companies. In a nutshell, the answer is: to a certain point and with some manual searches many codes can be indeed turned into codes for other databases if you can use Datastream.

RIC codes are Reuters Instrument codes (Wikipedia). These codes usually originate from Thomson Reuters databases like Thomson One, Eikon or Thomson Reuters News Analytics. The RIC codes for companies usually have a root code and a suffix for the market of a listing. If a company has multiple listings the root stays the same but the suffix gives indicates a different market. A theoretical example: the IBN company has the root code IBM and for a listing at the london stock exchange .L would be added to get the RIC code: IBM.L . If the listing is at the New York Stock exchange .N would be added to create IBM.N .

In the example of the student he got the list from a supervisor and was asked to get some data for these companies. The trouble was: most databases cannot work with RIC codes as these are proprietary and are not included as codes. If your university/organisation does not have a license to Thomson databases (like the ones mentioned above) but does have a Datastream license, the codes can still be converted (somewhat) into codes for this database. Although Datastream is a Thomson Reuters database, and RIC codes (or Thomson1 codes) can be recognised somehow, Datastream sometimes does not provide output or does not return the RIC codes.

Here follows step by step how I still managed to convert the original RIC codes into codes that could be recognised (mostly) by Datastream and return data:

1) Create a new list of codes where the root codes are seperated from the market codes. In excel this can be done by using the function from the Data tab, called: Text to columns. Important to remember here is: not all market codes start with a dot. Some codes stqart with the “equal to” character =. In Excel this character usually signifies the start of a function. Be sure to use the Text to columns option and seperate everything out in one or more rounds and have every column seperated as Text.

2) Create another column with the root codes seperated from the market codes in a similar way as before using the Text to columns option. This time you do not seperate the RIC code out in two text colums. The first column which carries the root code should now be left at “general“:

Some company RIC codes start with numbers that include zero characters in front. These zeros need to stay there because they are part of the code and when converting RIC codes to codes for Datastream these zeros sometimes need to still be there. In essence: we get two columns where you can choose either the original RIC root codes, or the abbreviated ones when necessary.

3) Now create a new column where you recreate the original full suffix code which includes the dot or equal to (=) character. The end result should look as follows:

4) The next step is finding out which RIC market codes represent certain exchanges. I did a few searches and found an excellent site from SIRCA that provided me with a list. I copied and saved this as a text file and imported this text file where each column is a TEXT column. I did not copy the list to Excel immediately from the website as this affected some market codes: specifically the ones that start with the “equal to” character: =
The end result looked as follows:

5) As a next step I matched the market codes from the original list with the list of market codes using 3 times the Excel function =Vlookup(). The result looks as follows:

6) What I now need to do as the final step is create two more columns in case I need to change codes from a specific market/exchange and need to add a suffix code, a prefix code, or both. For the final column I can now use the Excel funstion =Concatenate() to create alternative codes for the RIC codes and have Excel automatically add prefixes or suffixes as required for a list of codes.

7) The last step involves trial and error searches to find out for certain markets what prefix or suffix codes can be added to create either Datastream Mnemonic codes or Local codes that Datastream can actually work with. Example:

Warning 1: Remember that some exchanges that numbers as codes for companies and may require either the original codes with zeroes or not. Choose the relevant Ticker column A or B (which we created) when the Mnemonic or local code requires it.

Warning 2: Most exchanges use common prefixes or suffixes but not all of them: for some exchanges you may need to look up all codes!

8) Using the list or newly created codes we can now do a Static search in Datastream to find out if all the codes are stil current and which are not: the original RIC code list may contain codes that have changed and some codes were deleted (even though the company data is still there). First copy the new codes to a new sheet (or column) and paste them as values to get rid of the Excel formulas. Then upload them using the Datastream option: “Create List(From Range)“:

9) Next Do a Static Request search and use the list to get some basic data back that signifies which companies are associated with these codes. In the example search I requested the following codes back: DSCD (Datastream code), NAME, ISIN code. The list of codes that we uploaded at step 8 we can find/use by clicking the List Picker button below the orange Find series button.

If the original list of RIC codes is a recent one, chances are you will get many good codes back which can then be used to get actual data from Datastream or another database. The result looks something like this:

N.B.: When you have access to databases like Amadeus, Orbis, Orbis Europe or Compustat you can also try uploading the RIC root company codes as tickers and see whether they are recognized there as well. This method is more quick & dirty (tricky), as you do not know whether the right listings and ticker codes will be available for the companies.


Datastream Navigator update 4.6

Recently it was announced by Thomson that the Navigator tool in Datastream will undergo some significant changes. The changes concern the addition of some important search filters to more easily allow for finding and selecting the right series and data types (variables).
Some of these filter options are older ones that have returned from a previous edition of the Datastream Navigator.

The main changes are listed in the the document that they have released and which you can find here as well.


Thomson Eikon & Event windows

Not too long ago I was working with the successor of Thomson 1 / Thomson One which is called Eikon or Thomson Eikon. This product is web-based and is similar in look and style as Bloomberg. I found out that in most cases for me the Excel add-in was the most useful part and allowed me to download larger amounts of data.
The specific Add-in did, however, miss a key feature which I use heavily in Datastream: there is no Request Table tool to allow event studies with changing dates and time windows. Using the add-in I was able to figure out how to still do something like this using the commands which Thomson inputs in your Excel worksheet to call up the data.

With a bit of help from the Eikon Helpdesk I was able to come up with a template that kind of does the same thing as a Request Table Excel tool from Datastream. You can download the example here.

Explanation of the worksheet:

0) Before using the worksheet template, make sure that the connection to Eikon on your computer is not yet live. Otherwise the sheet will immediately start updating as the Add-in and Eikon code is usually set up this way.

1) The first sheet is called Events
As the name already suggests, this is where you put the codes for the series (column A). In columns B and C the Start and End dates for the series go. If you need an exact number of trading days (of data) you can use the formulas from a previous post.
If the exact number of days need not be the exact same column D shows the number of calendar days between the start and end days. This is done by deducting the end date from the start date. This works fine as dates are also numbers in Excel. Cell E2 shows the highest number in the entire column D using formula =MAX(D:D)

2) The second sheet is Data
The example sheet is used to download the Eikon data into and is based on generated Eikon code(s).

Cell D1 in sheet Data is linked to Cell E2 from the Events sheet and will show the same value with the formula: =Events!D2
This number is important as this indicates the (maximum) number of rows for each new window to start downloading data without overwriting the data from previous (downloaded) series. This may cause empty rows between series/data, but these empty rows can be deleted afterwards

The formulas in Column C are used to calculate the row/cell numbers for each separate series to start downloading: =((B4-$B$3)*$D$1)

This is also based on the generated number of events in columb B: =B3+1.

The Helper Column is column A. This column is needed to get the download destinations to be included later in the Cells with the Thomson Eikon codes. There is just a technical reason for this, otherwise the Eikon code does not accept the generated destination cells. The formula is: =ADDRESS($C4;6) where the $C4 is the number of rows it takes from column C and 6 indicates the column number where the download should start.

The download code from eikon is as follows: =TR(Events!$A2;”TR.PriceClose”;”Frq=D SDate=#1 EDate=#2 Curn=EUR CH=Fd;date;IN Transpose=Y NULL=NA CODE=ISIN”;INDIRECT($A3);Events!$B2;Events!$C2)

  • This formula takes the series data from the Events tab in column A where it says Events!$A2
  • The start & end dates are included at the end where it says: Events!$B2;Events!$C2
  • The data download cell destinations for the events are indicated where it says: INDIRECT($A3)

You just copy everything downwards (including the Eikon formula) depending on the number of events in the dataset.

3) When you are done copying the events in to the sheet Events and copying the necessary formulas in the Data sheet, you can then go live by signing in into Eikon. You will see that the data download starts automatically.

N.B.: The example download sheet downloads the closing price for series with the Eikon datatype code TR.PriceClose. If you need another datatype this part needs to be changed in the formula.


Stata & large downloads from Datastream

In the past I demonstrated how it was possible to change a dataset from a wide presentation (= data in columns) to a long presentation (= data in rows) using the reshape command in Stata. This option works fine where smaller datasets are concerned and if there is no choice when downloading data. For larger datasets, however, it may become a problem to download the data in wide format and also the Stata software may have difficulty changing the presentation.

If you download, for instance, daily price data from Datastream for many years (using ISIN codes) for x number of listed companies the wide download format (transposed) is not a good option. Try downloading everything without transposing and you get everything in long format as follows:

Changing the data to a proper long format for Stata goes as follows:

1) Make a copy of the original data and add a new row at the top.
2) Above the Dates in column A type in a general name like “company” in Cell A1.
3) Also use numbers for each individual company at the top.
4) Insert a new row above the data and give the name Date in Column A above the actual dates
5) In the cells of this same row use an Excel formula to generate fake names. Example: =$A$1&B1
The copied Sheet should look as follows:

6) Now also make a copy of the first 3 rows and paste the data transposed using the Right-click option in a new sheet. Example:

7) Use the Excel function =left() to get the ISIN codes (12 digits) in a separate column or use Search and replace to get rid of the variable indicator (P)

8) Copy and paste this data as values to a new sheet. It should look as follows:9) Make a new copy of the first changed sheet and delete the first few rows. Make sure to copy & paste as value (right-click) to remove any formulas. The sheet should now look as follows:

Now start up Stata en use the sheets in the Excel Worksheet step by step:

10) First you import the sheet (from step 9) with the price data from Excel using the command import (or the Menu option File > Import > Excel spreadsheet).

11) Use the reshape command the rearrange the data to long format: reshape long company, i(Date) j(price). Change the variable names and make sure that the column with numbers is called company. Next save this as a Stata database.

12) Use clear all to start with the second sheet. Now import the second sheet with the company ISIN codes from step 8.

13) Use the merge command to merge this data with the Stata database we created at step 11). The command would look as follows:
merge 1:m company ” c:\ … \filname.dta”, sheet(…), firstrow

14) now do some data curation steps to change prices to numbers (destring command) and generate a newly formatted date. Make sure to save this file.

As an example I provide here a zip-file with an example download in Excel with the Steps on separate sheets and a .do file that can be run step by step. If you want to use this, be sure to extract both files to the folder c:\temp on your computer. You can also extract them in a different folder. In that case you need to change the locations of the files in the .do file before running the file in Stata.

Important to remember with this example: Stata may still run into problems if you have hundreds of companies (or more) for which you have daily data for many years. If there are problems I recommend converting the download in brackets of 100 to 200 companies and then appending the resulting databases to create a single Stata database.

N.B.: A student pointed an example of this out to me which he found at: Princeton University Library Data and Statistical Services. I do not claim any credit for the original idea but the Stata example provided on this blog was made by me.