Hello,

I am currently working on a project which involves finding and matching firm financial data to an excel-sheet dataset (called resulttable100.xlsx) containing a list of firms, their number of patents and respective filing-application years. Not all these firms are public so I ran a query on Eikon API to find those companies in the dataset whose RICs are known, and using the RICs, retrieve their financial data based on the year given in the dataset. For example, AAPL INC has xxx amount of patents in year 2010, so I need the financial data (such as Market capitalization, revenue, assets, liabilities, etc, I can check the Eikon DIB for more field names) for AAPL in 2010.

This is where my problem begins. I was able to run a code that successfully finds the necessary RIC but I was not able to get their financial data.

Here is a snippet of my workflow for extracting market cap using RIC:

import eikon as tr
 tr.set_app_key('xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx')
import pandas as pd
import refinitiv.data as rdp
from refinitiv.data.content import search
rdp.open_session()

resulttable_df = pd.read_excel(r'C:\xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx\resulttable100.xlsx')

# Create an empty list to store dictionaries of company and RIC
ric_list = []

# Iterate over rows in the "resulttable" dataset and fetch financial data
for index, row in resulttable_df.iterrows():
    # Convert the company_name to a string
    company_name = str(row['doc_std_name'])
    filing_year = row['appln_filing_year']
    
    # Use RDP for searching the company and getting its RIC
    search_results = rdp.discovery.search(
        view=search.Views.ORGANISATIONS,
        filter=f"CommonName eq '{company_name}' and IsPublic eq true",
        select="PrimaryRIC",
        top=1,
    )

    # Extract the RIC from the search results
    ric = None
    if not search_results.empty:
        first_result = search_results.iloc[0]
        ric = first_result['PrimaryRIC']

        # Append the company and RIC to the list
        ric_list.append({'Company': company_name, 'RIC': ric, 'Year': filing_year})

# Create a DataFrame from the list
ric_df = pd.DataFrame(ric_list)

# Print the DataFrame with companies, RICs, and filing years
print("Companies with RICs:")
print(ric_df)

# Create an empty list to store dictionaries of company, RIC, and market cap
financial_data_list = []

# Define a function to fetch financial data for a company (RIC)
def get_financial_data(ric, filing_year):
    fields = ['TR.CompanyMarketCap']
    response = rdp.get_data([ric], fields=fields, parameters={'SDate': filing_year, 'EDate': filing_year, 'Frq': 'FY'})

    if not response.empty and 'data' in response and not response.data.empty:
        data_item = response.data.iloc[0]

        if 'field' in data_item and data_item['field'] == 'TR.CompanyMarketCap':
            financial_data = data_item.get('value', None)
            return financial_data

    return None

# Iterate over rows in the "resulttable" dataset and fetch financial data
for index, row in resulttable_df.iterrows():
    # Convert the company_name to a string
    company_name = str(row['doc_std_name'])
    filing_year = row['appln_filing_year']
    
    # Use RDP for searching the company and getting its RIC
    search_results = rdp.discovery.search(
        view=search.Views.ORGANISATIONS,
        filter=f"CommonName eq '{company_name}' and IsPublic eq true",
        select="PrimaryRIC",
        top=1,
    )

    # Extract the RIC from the search results
    ric = None
    if not search_results.empty:
        first_result = search_results.iloc[0]
        ric = first_result['PrimaryRIC']

        # Get financial data for the company
        financial_data = get_financial_data(ric, filing_year)

        # Append the company, RIC, filing year, and market cap to the list
        financial_data_list.append({'Company': company_name, 'RIC': ric, 'Year': filing_year, 'MarketCap': financial_data})

# Create a DataFrame from the list
financial_data_df = pd.DataFrame(financial_data_list)

# Print the DataFrame with companies, RICs, filing years, and market caps
print("Financial Data:")
print(financial_data_df)

The first two blocks work but not the third.

The goal here was to find the RIC, then Market Cap in the year given in "resulttable100" and finally collate the result in a new table called financial_data_df. For some reason, the output shows no financial data could be gotten for the firms whose RICs was found. How do I address this problem?

@nick.zincone @jonathan.legrand