Upgrade from Eikon -> Workspace. Learn about programming differences.

For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
1 0 0 2

Trouble Extracting Market Cap Data Using Refinitiv Data Platform API: DataFrame Ambiguity Issue

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


python#technologyapi#contentminiconda
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Upvotes
Accepted
5.3k 21 2 4

Hi @Ollivier Taramasco ,

Please note that the rdp library is being deprecated for the RD library. I tested the below and it seemed to work; do let me know if it is not what you are after:

resulttable100.xlsx:

1701090664799.png


# !pip install refinitiv-data
import refinitiv.data as rd  # pip install httpx==0.21.3 or 0.14.2
from refinitiv.data.content import search
import pandas as pd
rd.open_session()


resulttable_df = pd.read_excel(r'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 = rd.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']

        # display(rd.get_data(list(ric_df.RIC)[0],
        #     fields='TR.F.MktCap(Curn=EUR)',
        #     parameters={'SDate': filing_year, 'EDate': filing_year, 'Frq': 'FY'}
        #     ))
        #get fianncial data
        financial_data = rd.get_data(list(ric_df.RIC)[0],
            fields='TR.CompanyMarketCap(Curn=USD)',
            parameters={'SDate': filing_year, 'EDate': filing_year, 'Frq': 'FY'}
            )['Company Market Cap'].values[0]

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

# 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:")
display(ric_df)

1701090722848.png



1701090664799.png (17.5 KiB)
1701090722848.png (12.3 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.