We use Refinitiv's Eikon Python Application Programming Interface (API) to access financial data. We can access it via the Python library "eikon" that can be installed simply by using $\textit{pip install}$.

The following are Python-built-in modules/libraries, therefore they do not have specific version numbers.

The Python library 'os' will allow us to retrieve the path of our running script

The Python library 'warnings' will allow us to remove library depreciation warnings

numpy is needed for datasets' statistical and mathematical manipulations

The numpy library imported in this code is version: 1.18.5

pandas will be needed to manipulate data sets

The pandas library imported in this code is version: 1.0.5

openpyxl and xlsxwriter will be useful in manipulating Excel workbooks

The bellow are needed to plot graphs of all kinds

The plotly library imported in this code is version: 4.7.1
The cufflinks library imported in this code is version: 0.17.3
The seaborn library imported in this code is version: 0.10.1
The matplotlib library imported in this code is version: 3.2.1

Coding¶

Boolean data:¶

Example using 'Environmental Materials Sourcing':¶

I'd like to exemplify what we are trying to do in this article with 'Environmental Materials Sourcing'.

• 1st: Collect data from Refinitiv

Out[10]:

31583 rows × 7 columns

• 2nd: Fill in missing country values and sector

Out[11]:

31583 rows × 7 columns

• 3rd: Construct a straight-forward 'Years' column

31583 rows × 8 columns

• 4th: Make a Python variable containing all the countries we saw this far

array(['United States of America', 'Switzerland', 'France', 'Japan',
'Australia', 'Singapore', 'Spain', 'Germany', 'United Kingdom',
'Canada', 'Sweden', 'Israel', 'Austria', 'Belgium', 'Denmark',
'Netherlands', 'Ireland; Republic of', 'Norway', 'Isle of Man',
'Hong Kong', 'New Zealand', 'Luxembourg', 'Italy', 'Macau',
'Finland', 'Argentina', 'Bermuda', 'Papua New Guinea', 'Portugal',
'China'], dtype=object)

• 5th: Make a Python variable containing all the sectors we saw this far

array(['Healthcare', 'Consumer Non-Cyclicals', 'Consumer Cyclicals',
'Technology', 'Industrials', 'Financials',
'Telecommunications Services', 'Utilities', 'Basic Materials',
'Energy'], dtype=object)

• 6th: Make a Python variable containing all the years in our data-frame

array([1998, 1999, 2000, 2001, 2002, 2003, 2004, 2005, 2006, 2007, 2008,
2009, 2010, 2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018, 2019,
2020])

Now we may Aggregate data on a per-country and -sector basis for Environmental Materials Sourcing data:

• 7th: Aggregate data on a per-year basis

• 8th: Aggregate data on a per-country basis

• 9th: Aggregate data on a per-sector basis

Now let's plot this data-frame:

To get a better wholeistic picture of our data this far, we can graph it else-how:

Save our data in an excel Workbook, call it 'ESG_data'

Generalising the graphs per country and sector

Above we saw data gathered in real terms, but we can calculate a percentage of all companies supplying such data (such as 'Environmental Materials Sourcing'). The function bellow allows its user to do just that with the variable 'Percent'.

None

None

None

None

None

None

The 3rd element of the returned list (0 indexed) is the data-frame, let's have a look at it:

31583 rows × 8 columns

1st: switches between Country or Sector

2nd: switches between 'Environmental Controversies' or 'Environmental Materials Sourcing'

3rd: switches between the title/selection name, the (pandas) data-frame and the Pyplot.

'Aggregate data on a per-COUNTRY basis for Environmental Materials Sourcing'

'Aggregate data on a per-SECTOR basis for Environmental Materials Sourcing'

'Aggregate data on a per-SECTOR basis for Environmental Supply Chain Management'

Creating a Python Class

To make this as useful as possible (and to build a more intuitive session for users of our functionalities), the class bellow allows anyone to pick and choose the functions and graphs they want (as well as saving an excel workbook with the produced and 'cleaned' data.

Let's test this 'ESG_Boolean_Data' Python Class.
N.B.: remember that the default variables for 'ESG_Boolean_Data()' are:

Companies = ['0#.MIWO00000PUS'], \
Fields = ['TR.AnalyticEnvControv', 'TR.EnvMaterialsSourcing', 'TR.EnvSupplyChainMgt', 'TR.PolicySustainablePackaging'], \
SDate = '0', EDate = '-19', Period = 'FY0', Frq = 'FY', \
save_excel = False

Let's test this 'Aggregate_Per_Country_Stacked_Bar' Python Class's function.
N.B.: remember that the default variables for 'Aggregate_Per_Country_Stacked_Bar()' are:

Stacked_bar_graph_theme = "plotly_dark", \
Total_number_in_data_set = True, \
save_excel = False

2000200520102015202002004006008001000120014001600

number of companies in the data-setChinaPortugalPapua New GuineaBermudaArgentinaFinlandMacauItalyLuxembourgNew ZealandHong KongIsle of ManNorwayIreland; Republic ofNetherlandsDenmarkBelgiumAustriaIsraelSwedenCanadaUnited KingdomGermanySpainSingaporeAustraliaJapanFranceSwitzerlandUnited States of AmericaWorldwide number of comanies reporting on Environmental Supply Chain Management per countryYearsEnvironmental Supply Chain Management

Let's test this 'Aggregate_Per_Sector_Line_Graph' Python Class's function.
N.B.: remember that the default variables for 'Aggregate_Per_Sector_Line_Graph()' are:

Percent = True, \
Line_graph_theme = 'solar', \
Start_graph_year = 2000, \
End_graph_year = 2019, \
save_excel = False

But here we let Percent = False

One may also get attributes from the Python Class created. Bellow, we retrieve the filled in data-frame from which all graphs are created.

31564 rows × 12 columns

Percentage data:

Waste Recycled to Total Waste is a ratio - a percentage - already, so we don't need to aggregate the number of companies that report for this field. Instead, we use the median per country and/or sector to unveil insights behind the data:

Let's go through the function step by step.

• 1st: define its variables; let them be the following by default:

• 2nd: create a list with the fields asking for the country and sector of each company requested. This list is to appended with the list of companies to request info. for.

['TR.HeadquartersCountry',
'TR.TRBCEconomicSector',
'TR.AnalyticWasteRecyclingRatio.date',
'TR.AnalyticWasteRecyclingRatio']

• 3rd: request data from Refintiv:

17865 rows × 5 columns

• 4th: Collect the name of the fields we are interested in:

['Waste Recycled To Total Waste']

• 5th: Now we will populate every 'Country of Headquarters' and 'TRBC Economic Sector Name' column in our newly defined 'df' pandas data-frame

17865 rows × 5 columns

• 6th: Construct a straight-forward 'Years' column