Eikon Excel Company Tearsheet in Python (Part 1)

Company Tearsheet Template shown below is included in Eikon Excel Template Library:

In this article, we: 

• Identify the Eikon data sets within Company Tearsheet, and show how to retrieve the same data using Eikon Data API Python
• Explain how to restructure and to present the data, using helpful Python libraries
  • Pandas
  • Numpy
  • Plotly
• Enable our developer readers to reproduce Eikon Excel Company Tearsheet entirely, or selected parts of it, in a Jupyter Notebook 

The approach to a specific task that we discuss is not always the only option.  Rather we discuss a working approach to re-creating and presenting all data sets within Company Tearsheet in Python, and leave beyond the scope of the discussion, and perhaps as an exercise for the readers, finding other ways to do the same.

Getting Started

The aim of this article is to be a hands-on, fully reproducible use case reference. 

To this end we will:

• Identify the relevant online materials and list them as address links in References section
• Discuss the required setup and tools
• Focus on approaches and techniques, applied to build Company Tearsheet pybook, rather than discuss in order, how to request every required content set.  We will use specific content sets required, as examples.  We will explain the approach to achieving the required result, and go over one, or sometimes more, of the examples of how to apply the technique discussed  
• Reference the example code downloadable via GitHub , Company Tearsheet Jupyter notebook

Setting Up

Many of our readers already have installed some or all of the setup that is required, we go ahead and list all for completeness:

1. Download and install Python, at the time of this writing Python 3.7 was used for testing
2. Install Jupyter Notebook
3. Install required python modules, either beforehand, or this can also be done as we go, but prior to being able to use the module:
   1. pip install eikon
   2. pip install plotly
   3. Note that numpy and pandas do not need to be installed, they are dependencies of eikon and will automatically be installed with eikon package
4. Make sure Eikon Desktop or Refinitiv Workspace is running and we are logged in (this step will need to be performed any time we wish to make use of Eikon Data API  to obtain Eikon data)
5. Log into GitHub, download and optionally, run, the included pybook.
6.  Run Eikon Excel 
   1. From Menu on top select Refinitiv -> Sign In and,
   2. Once connected, Templates-> Search for "Company Trearsheet"->Open
   3. We are about to use Excel version of Company Tearsheet as a starting point and as a go-to reference.  Within the workbook, Company Tearsheet tab holds the presented Tearsheet and Data tab holds some of the required auxiliary data.

Examining Template Worksheet

Let us look at Excel Company Tearsheet workbook more closely.  The starting point, the trigger for retrieval and calculations is RIC identifier in cell C3 of Company Tearsheet tab.  The RIC is set to a specific value, for example, "IBM.N" and is used to pull the data for that RIC. 

We can review formulas in use, by selecting cells that carry the start of a specific Eikon Excel result.

For example, see below, by selecting cell C5, we see how the content that is presented in C5 "International Business Machine Corp" it is requested via Eikon Excel:

For instance, 

• "=TR" formula requests Eikon content 
• "$C$3" refers to cell C3,
• In C3 we enter requested Instrument, for the purposes of this example it is "IBM" 
• And "TR.CommonName()" is the requested field that results in "International Business Machines Corp".

When working with Eikon Excel, we often look to better understand the fields that are available for retrieval, their meaning and the parameters that are available.

Using DIB For Content Lookup

Eikon Data Item Browser (DIB) is the Eikon/Refinitiv Workspace tool that can be used to look up the available fields and parameters, relevant both for Eikon Excel usage and Eikon Data API usage:

We use it as reference for looking up fields and parameters available:

Basics of Translating TR Formulae into Python Code

When examining Excel worksheet cell C5 we have found the required fields and parameters, and they can be used in the same way via Eikon Data API''s get_data method.

Where in Eikon Excel we see the usage:

in Python this will translate this into the equivalent:

Where RIC is an input parameter we use in Company Tearsheet py book, analogous of C3 cell in Company Tearsheet Excel workbook.

And when we run the code, it will result in:

Let us note how the results that were returned by get_data call were df and err, where df is a Pandas Dataframe and  err is possible errors.  For more details on working with Dataframes we recommend reviewing Pandas documentation listed in References. 

Eikon Data API Python Reference Guide

We use Eikon Data API Reference Guide in References to look up get_data parameters, syntax and examples of usage, to help us along when translating Excel formulae  that populate cells with dynamic data into Python code.

Helpful Translation Details

Now, armed with the knowledge how to transition from "=TR" in Excel to "get_data" in Python,

let us review a few required details, and together, they enable us to translate Company Tearsheet Excel workbook's Eikon data retrieval and presentation in its entirety, only excluding the first tab Instructions.

Parameters

Let's look at General Information section of our resulting py book:

1. The values that parameters take can be hardcoded.  For example, in curly brackets {}, comma-delimited, is the list of the required parameters, with applied values, for example, as 'Curn' for currency.

In Excel worksheet's cell D12, the corresponding request is:

2.  The values of the parameters can be obtained from previously obtained Dataframes.  Looking at the code above, where in Excel 'curn' takes it's input from the contents of another cell D10 ($D$10 in formula), in Python, we obtain the analogous info from previously populated Dataframe df, by means of 'df.at[0,'Currency']'.

3. The fields can be passed to get_data method either as a string of semicolon separated field names or as a list of field names.  We can use this to pass two sub-lists, each with the different, required list of parameters.  Let's take another look at General Information section

4. Very common in Eikon Excel, parameter transpose

is specific to Eikon Excel's "=TR" function. It is ignored by EDAPI, and is not be used in get_data method

5. For completeness, we would like to add that Eikon Excel parameters RH and CH, that make the Column and Row Headings appear and look like this:

are not translated directly.  Row headings and Column headings can be defined per requirement.

For columns definition example in General Information:

Where 1,2,3, and 4 will be our column headers for the purposes of General Information multi-row presentation 

For row definition example in Valuation Ratios:

Resulting in:

Aggregating Results with Previously Calculated Columns

As we retrieve various pieces of content using different parameters, we end up with multiple Dataframes containing results.   This is usually not how we are required to present this content.  Especially, this is not pretty, if some of the resulting Dataframes consist of just a single column.

We can add such column from a Dataframe retrieved via Eikon Data API as a column into the main result Dataframe, for example, in Header section of py book, we add Exchange Name from df_3 to df:

Aggregating Multi-Row Dataframe of Results Via Dictionary

For an example of this approach, let's take a look at Estimates section in py book. 

First, let us let us look at the analogous section in Excel Company Tearsheet:

The result in Python looks like this:

The result is a multi-row Pandas Dataframe, structured out of multiple Dataframes that are retrieved via separate Eikon Data API calls.

1.  First we retrieve all the required content via separate Eikon Data API calls:

2. Next, we map the relevant pieces of results into a Dictionary, naming columns according to the desired final multi-row output look 

Such as 'Sales (MIL) Mean EST' 

3. Lastly, we create multi-row Dataframe, indexing by expected row names:

Resulting in the required results structure.

We use this convinent approach often, it enables us to present the Eikon content retrieved wihin py book analogous to the presentation in Excel.

Aggregating Multi-Row Dataframe via Lists and Dictionary

Let's examine sub-section Income Statement (MIL) for an example of aggregation of separate results via list.  If the columns we are looking to present in Multi-row dataframes are very close to or, in fact, are the rows in Eikon Data API call results, this approach can be useful:

1.  First, we retrieve all the required content via separate calls

2. Next, we transform the results into Lists

 Find additional information on using Lists and selecting subsets in References section.

3. Insert the lists into a single Dictionary

4. And create Multi-Row Dataframe intended for presentation of the final result, from the dictionary, using original result's column headers as index:

This was the first part of Eikon Excel Company Tearsheet in Python article.

We are going to continue the discussion of Eikon Excel Company Tearsheet in Python with Charting  in Eikon Excel Company Tearsheet in Python Part 2. 

If the material presented so far was of interest - stay with us by following this link.

References 

• Python 3
• Jupyter for Python
• Article code in refinitiv-API-Samples on GitHub
• Eikon Data API Refernce Guide
• Pandas (Dataframes)
• Python Lists 
• Selecting Subsets
• Plotly Bar Charts
• Offline Plots
• Numpy Convolve
• Plotly Line Charts