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
Best Answer
-
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:
# !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)0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 367 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛