Extracting and Matching Data from an Excel file with Eikon API

Ollivier Taramasco

I have an excel file containing a companies/employee names, their patent applications and year of filing. However, now I need the financial information for these firms for the given year (from eikon), as listed in the excel sheet. The main problem i have is that the names in the excel sheet are not an exact match with Eikon (no RIC, PermID, or any other corresponding code). How do I work around this.

Here is an extract of my current code:

import eikon as tr

import pandas as pd

resulttable_df = pd.read_excel(r'C:\xxxxx\resulttable.xlsx')

def get_financial_data(company, year):

# Example: fetching market cap for a given company (RIC) and year

fields = ['TR.MktCap']

response = tr.get_data([company], fields=fields, raw_output=True)

if 'data' in response and response['data']:

for item in response['data']:

if 'data' in item:

financial_data_list = item['data']

for data_item in financial_data_list:

if 'field' in data_item and data_item['field'] == 'TR.MktCap':

return data_item.get('value', None)

return None

# Iterate over rows in the "resulttable" dataset and fetch financial data

for index, row in resulttable_df.iterrows():

company = row['person_name']

year = row['appln_filing_year']

financial_data = get_financial_data(company, year)

# Print or process the financial data as needed

if financial_data is not None:

print(f"For {company} in {year}, Market Cap: {financial_data}")

else:

print(f"No financial data found for {company} in {year}")

@nick.zincone

jonathan.legrand

Hi @Ollivier Taramasco,

I believe the Search API (more on it here, in Nick's article) is what you're after. Having had a look myself, I found that 'EQUITY_QUOTES' is the view you're after; for e.g., with an excel workbook 'resulttable.xlsx' like this:

I wrote the below with the RD Lib for Python (which is the new version of Eikon's Data API) (more on the RD Lib for Python can be found here):

import refinitiv.data as rd
import pandas as pd
try:  # The following libraries are not available in Codebook, thus this try loop
    rd.open_session(
        config_name="C:\\Example.DataLibrary.Python-main\\Configuration\\refinitiv-data.config.json", # this is where my config file is located
        name="desktop.workspace")
except:
    rd.open_session()

resulttable_df = pd.read_excel("resulttable.xlsx")

response=rd.content.search.Definition(
    view=rd.content.search.Views.EQUITY_QUOTES,
    query=resulttable_df.Company.iloc[0],
    filter = "RIC ne null",
    select = "RIC, _, IndustrySectorDescription",
    top = 20,
    ).get_data()
display(response.data.df)

I assumed, above, that you were after floated companies; therefore the EQUITY_QUOTES was the best `view`; but you can choose another from the list found in `help(rd.content.search.Views)`; in line with this assumption - and to make it more likely that the correct company comes up 1st in `response.data.df`, I added "Ordinary Share" at the end of what could be seen as the comany name.

I would advise using the `filter` more, to make it more likely that the correct company comes up 1st in `response.data.df`. E.g.: filter by company country, market cap, industry, ... The list of filter fields can be found with this browser or with:

response_EQ_Q = rd.content.search.metadata.Definition(
    view = rd.content.search.Views.EQUITY_QUOTES).get_data()
response_EQ_Q.data.df.to_excel("EQUITY_QUOTES.xlsx")

The 'grammar' you can use these fields in is described in Nick's article as well as on the Playgound Reference.