Currency conversion in FactSet

Some databases offer currency conversion as an option which makes it (for instance) easier to combine and compare data on companies or series that report in different currencies. FactSet has such a currency conversion as an option as well. Below is a brief explanation I got from the helpdesk on how this works.

1) FactSet uses WM/Reuters exchange rate series as the source to recalculate data

2) Annual income Statements and P&L statements are recalculated using the average of the daily exchange rate of a specific currency for the last “X” months until the end of that period. This depends on the frequency of the dataset used. In this case FactSet uses a single overall daily average exchange rate for the specified time period based on calendar or fiscal year (which is usually 12 months).

3) The recalculation for quarterly income Statements and P&L statements works the same way, only this time the overall average daily exchange rates used are based on the 3 months prior to the end of each specified time period based on calendar or fiscal quarters.

4) Balance sheet items use currency conversion as of the period end dates. Again, this is a single specific average daily exchange rate but this time for the specific period end date.

5) Where the data has a monthly frequency, the recalculation is done using the specific average daily exchange rate at the month-end dates of the specified time period.

6) Time series with daily data are recalculated using the average daily exchange rates on the days.

To sum it up I created a picture with an overview here:

Email

FactSet and historical index lists

Somebody asked me recently: is it possible to call up historical lists of an index, for instance: the AEX for an older year? My answer was yes, although I do not think this option is available for all indexes as some of them are proprietary and may require an extra license. I am also not sure how far back index compositions are available.

This is how it works:

• Start up FactSet and click the Universal Screening option
• In the next screen select the option Starter Screen to get to the Basic Start Screen
  
• Remove the example S&P 500 index and in the Add criteria box search for the index: AEX
• Select the option that is presented for Netherlands AEX (Index). The selection of this list will automatically generate a list of companies that are currently part of the AEX index
  
• In the criteria screen now click on the selected index: Netherlands AEX (Index). Clicking the option will make it turn yellow and show the following formula in the criteria search box: FG_CONSTITUENTS(181010,0,CLOSE)
   
• Clicking on this formula calls up a popup that shows more options that can be changed. Replace the zero in the formula with the date of choice to get something like: FG_CONSTITUENTS(181010,1/1/2005,CLOSE)
   
• Changing the formula will generate a new list for the index with the selection of companies in the index of that time

You can now choose to download the list or change it first (by adding columns with static data) and then download the list as you need it

Email

FactSet and large lists of series

FactSet is an application that has similar datasets compared to Datastream and Bloomberg although there are also many differences. If you need access a subscription is required. The software can be downloaded from their website.

The main FactSet application can be used to quickly search and finc out if certain information is available or where it can be found. Quick comparisons can be done using the Charting option. If you need to download more data on more than 25 series/companies the Excel add-in for FactSet is what you need to use as the final step to actually download data. Below follows a step by step approach on how to download data:

1) A list of companies/series
There are two ways to work with lists in FactSet:

• You can use the Screening tool (through which you can use universal screening or Idea screening) in the FactSet menu to create your own list and download codes to Excel
  
• If you have a list of codes (ISIN, Sedol, etc.) you can use these in Excel.

2) Preparing the list
Below you see an example list that I downloaded from FactSet.

To use the list with FactSet I create a new sheet in the Excel Worksheet and copy the codes there transposed. This can be done using the right-click mouse button:

The limit for using the list depends on the version of Microsoft Excel. A list of 200 companies should not be a problem. Prepare the list by adding two dates indicating the start and end of the period for which you wish to download data. You can indicate a Frequency too but this is not necessary:

3) Downloading the data for 1 company

• Make sure to click a cell below the rows with data, for instance: cell A7
• If it has not been done already: now click the FactSet Tab / Add-in and click the option “Show or Hide Sidebar“. This option is the first button on the left side in the FactSet add-in
• In the FactSet Sidebar click the middle option “Insert“
• Click the Select “cell reference button” to the right of the “identifier lookup button”
  
  
• Click the first cell with a company code (B1). When you do this FactSet will immediately lookup the code and present the name of the corresponding company / series
• In the Data Item section search for a data variable you need. The example is: Total Assets. Type this in and press the Enter button on your keyboard. The search result shows available options (depends on whether the Sources are filtered)
  
• Click the top option: “Total Assets > Factset Fundamentals – FF_ASSETS“
• A new section appears showing the options at Inputs for FF_ASSETS. In this section again use the Cell Reference buttons to select the time frame: cells B3 and B4 in the example
  
• A temporary result is shown (in green). Now click the big grey button option “Insert” in the sidebar to finalize
  

4) Downloading the data for the rest of the series/codes
To download the data for the other codes we need to use the $ sign to fix cell or row references.

• First change the formula in Cell B7 from:
  =FDSC(“NA”;B1;”FG_COMPANY_NAME()”)  to:
  =FDSC(“NA”;B$1;”FG_COMPANY_NAME()”)
• Change the formula in B8 from
  =FDSC(“NA”;B1;”FORMULA_DESCRIPTION(“”FF_ASSETS””)”) to:
  =FDSC(“NA”;B$1;”FORMULA_DESCRIPTION(“”FF_ASSETS””)”)
• Change the third cell in B9 but use the key-combination SHIFT+CTRL+Enter to finalize the change! You change the formula in the cell from:
  =FDSC(“NA”;B1;”FF_ASSETS(ANN_R,”&B3&”,”&B4&”)”) to:
  =FDSC(“NA”;B$1;”FF_ASSETS(ANN_R,”&$B$3&”,”&$B$4&”)”)
• Select the Cells from B7 to B26 and use the little black square in the bottom left corner of the seclected cells to B26 to copy the formulas to the right for the other codes
  
• The screen will not immediately show data but the text #Calc
  
• To get the actiual data now click the button Refresh in the FactSet Add-in. The result should look like this:
  

 

Email

WorldScope coverage updates 1st and 2nd Quarter 2018

WorldScope company records now cover annual reports data for 85581 companies. This includes 49453 active and 36128 inactive companies. The last 2 updates: 1462 companies were added. Company data for a few new countries has been added: Iraq, Rwanda and Syria. WorldScope company records are also available through Datastream and LexisNexis.

Today I have updated the WorldScope country coverage file and it now includes the latest updates as they were posted in the first two Thomson Reuters Infostream quarterly publications of 2018.

Major updated Countries (new records):
China (150+)
Hong Kong (110+)
India (160+)
Iraq (71)
Rwanda (4)
Syria (21)
United States (180+)
Viet Nam (110+)

Email

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

Email

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

N.B.:

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

Email

Compustat database & non-US data

Based on a query from a student I investigated a problem getting output for some variables in the Compustat Global Fundamentals Annual database. I tried to get data for a selection of Dutch and UK companies for the variables: Long-Term Debt – Issuance (DLTIS), and Long-Term Debt – Reduction (DLTR).
No matter what I did, I just could not get data for these variables for the past 10 years.

Many of the regular variables (Total Assets, Revenue, etc.) were not a problem. I studied the manual but this also gave no indication of what I needed to do. Screening variables also did not seem to be causing the problem.

In the end I contacted the the Capital IQ company to ask what the solution was. It turns out that the two variables I was looking for had their origin in filings done with the SEC in America. If I needed data for Dutch or UK companies all I need do is load a list of Global Company Keys (GVKEYs) as a text file in Compustat North America Fundamentals Annual and download the data items there. This was not what I expected but it is good to know the data is available.

N.B.: In Compustat Global you need to download the ISO 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.

Email