Article

Dataquery – a syntax builder for screening with Eikon Data API.
Leonid Sopotnitskiy
Director, Eikon Platform, Emerging & Frontier Markets Director, Eikon Platform, Emerging & Frontier Markets

Introduction

Introduction

This article describes a syntax builder designed to assist Eikon Data API users with creating screening and data aggregation requests. It is expected that the reader is familiar with the capabilities and methods available in Eikon Data API, alternatively the introduction can be found here.

Today Refinitiv Workspace and Eikon users can access company fundamentals and deals data in with different apps, and the Screener is one of them. It supports searching and pre-processing results based on user-defined conditions.

For example, you can:

  • Calculate the standard deviation of EPS of public tech from the Russel 3000 index, that have a market cap above 7 BLN USD.
  • Screen for M&A deals with target companies in Germany that have a deal value between 100 and 150 MLN EUR

The Screener app has a flexible UI that allows users to structure such search and aggregation queries either directly in the desktop or via Eikon Excel (in which case the output is exported into a formula). The same workflow can be done with Eikon Data API, however in order to do so, the user has to create the screener request using the Excel formula, and then copy it as a string value into the get_data() method.

To make this task easier, Eikon Data API users can try out the dataquery.py, which is designed to act as a syntax builder for the screening queries. Let’s have a look at how to use it.

Setup

To set up working with dataquery, simply copy this Python file into the same directory, where the model using it will be running (otherwise a relative path will have to be specified in the import statement). Once this step is done, we can import this module into a notebook or another Python file.

You will find the GitHub link to the dataquery.py as well as a Jupyter notebook with usage samples below.

The structure of the module

The dataquery.py script represents a collection of functions and the screener class. We will not be going through each function, as their names are self-explanatory, but let’s have a look at the logic.

All functions in dataquery can be split into 2 categories:

  • individual functions.
  • group functions.

The individual functions are meant to work with single-name instruments when deriving a statistic. For example, if we use the AVG() function on a series of close prices, it will calculate the average close price for the single-name instruments specified in the get_data() method of Eikon Data API.

    	
            
df, err = ek.get_data('0#.SPX', ['TR.TRBCEconomicSector', AVG('TR.Close(Sdate=-30d)')]) 


df


Instrument TRBC Economic Sector Name AVG(TR.Close(Sdate=-30d))
0 CHRW.OQ Industrials 77.62
1 AJG.N Financials 90.69
2 CNP.N Utilities 16.92
3 AMCR.N Basic Materials 9.45
4 WM.N Industrials 99.45
... ... ... ...
500 DXC.N Technology 16.98
501 SNPS.OQ Technology 165.47
502 J.N Industrials 76.41
503 SIVB.OQ Financials 194.92
504 AVY.N Basic Materials 105.75

Group functions on the other hand allow users request processed data for a list of instruments based on a specified criterion (i.e. a common value of a data field). For example, GAVG() will return the group’s average for a defined timeseries.

    	
            
df, err = ek.get_data('0#.SPX', ['TR.TRBCEconomicSector', GAVG('TR.TRBCEconomicSector', 'TR.Close')]) 


df
  Instrument TRBC Economic Sector Name GAVG(TR.TRBCECONOMICSECTOR,TR.Close)
0 CHRW.OQ Industrials 2524.145564
1 AJG.N Financials 5543.795331
2 CNP.N Utilities 2685.887936
3 AMCR.N Basic Materials 2172.034612
4 WM.N Industrials 2524.145564
... ... ... ...
500 DXC.N Technology 1813.344623
501 SNPS.OQ Technology 1813.344623
502 J.N Industrials 2524.145564
503 SIVB.OQ Financials 5543.795331
504 AVY.N Basic Materials 2172.034612

We can see the difference in the returned values when comparing the above outputs.

Each function returns a string value, which can be either used as a data field input in the get_data() method or as one of the conditions of the screening query.

The final bit of dataquery is the screener class, which is initiated on import in the SCREEN object. The screener class works with 3 main parameters:

  • universe (e.g. index constituents, list of stocks, M&A deals, private companies);
  • conditions (e.g. are you looking for companies with market cap exceeding a certain value or would you like to exclude entities with certain characteristics form the universe);
  • currency (i.e. the currency of financials that you are using in your conditions, i.e. USD, EUR, etc.)

The screener class also has a sub-class – express, which can be used to construct the screening query in a single line of code as shown in the next section.

In order to retrieve the string value with the screening syntax, the user has to call the query method. This string value should be used in the instruments parameter of the get_data() method of Eikon Data API.

How to use

Let’s start by importing all methods and classes of dataquery.py into our notebook:

Once this statement is executed, our notebook will inherit all the globals from dataquery, which means that all functions will appear in intellisense:

An instance of the screener object will be created upon import, and the user can use it immediately by calling the SCREEN object.

1. Input the screening parameters when explicitly creating an instance of the screener class:

    	
            
my_screen= screener(FORMULA('TR.CompanyMarketCap','>20000000000'),         


        IN('TR.TRBCEconomicSector', 'Technology'), 


        universe='0#.SPX',         


        currency='USD') 


query = my_screen.query

2. Construct the query in a single line of code:

    	
            
query=SCREEN.express.universe('0#.SPX').conditions(FORMULA('TR.CompanyMarketCap','>20000000000'), 


    IN('TR.TRBCEconomicSector', 'Technology')).currency('USD').query

3. Set the screener attributes separately:

    	
            
SCREEN.universe = '0#.SPX' 


SCREEN.conditions = FORMULA('TR.CompanyMarketCap','>20000000000'), IN('TR.TRBCEconomicSector', 'Technology') 


SCREEN.currency = 'USD' 


query = SCREEN.query

In all of the above cases we end up getting:

    	
            'SCREEN(U(IN(0#.SPX)),TR.CompanyMarketCap>20000000000,IN(TR.TRBCEconomicSector,Technology),CURN=USD)'

This value can then be used with Eikon Data API as follows:

    	
            df, err = ek.get_data(query, 'TR.Close')

In the above case we will retrieve the official close prices on S&P 500 constituents from the technology sector and with a market cap of over 2 BLN USD. Please note that the conditions for screening contained other dataquery functions such as IN() and FORMULA(). It is also possible to list each screening condition as a list:

    	
            query = SCREEN.express.universe('0#.SPX').conditions(

    ['TR.CompanyMarketCap','>20000000000'],

    ['TR.TRBCEconomicSector','=Technology']).currency('USD').query

As we can see, the screener syntax builder is quite flexible in the ways allowing to build screening queries. Each function in dataquery.py contains a description of what it does. This way Eikon Data API users no longer need to rely on Excel, and generate the screening request along with various pre-processing functions directly in the Python script.