Missing data under Eikon but available through Datastream
Hello,
I am running Eikon API through Python to retrieve quarterly historical information on certain indices from 2000-2022.
However, my extract has certain unexpected empty cells for common fields like Market Cap and Total Return which are available through Datastream (Excel).
Below is an example count of missing data with the FTSE100
The empty cells are more pronounced with the S&P 500 around 36 to 14 missing market cap info, hence the more the stock the more the missing data.
Below is my current code for your reference
#THIS CODE GENERATES OUTPUT ON A QUARTERLY BASIS FOR SELECTED FIELDS AND INDICES, SPECIFICALLY RELATED TO ESG SCORES
import eikon as ek
import refinitiv.data as rd
import concurrent.futures
from retry import retry
import pandas as pd
from tqdm import tqdm
from datetime import datetime
from dateutil.relativedelta import relativedelta
#The API key is obtained from Eikon's platform and might change occasionally (check before running the code)
ek.set_app_key('Enter key here')
rd.open_session()
# Specifying the desired months and days (quarters in our case)
month_days = [('01-01', '04-01', '07-01', '10-01')]
# Specifying the desired years(the range will not include the last year)
years = range(2000, 2023)
# Create a Pandas Excel, the file will be exported with all results
excel_filename = 'ESG_SCORES_QUARTERLY.xlsx'
# Add retry decorator to fetch_data function
@retry(tries=3, delay=2, backoff=2)
def fetch_data(selected_date, formatted_EDate):
try:
return rd.get_data(
universe=[f'0#.FTSE({selected_date.replace("-", "")})'],
fields=[
'TR.CommonName',
'TR.ISINCode',
f'TR.EnvironmentPillarScore(SDate={selected_date})',
f'TR.SocialPillarScore(SDate={selected_date})',
f'TR.GovernancePillarScore(SDate={selected_date})',
f'TR.TRESGScore(SDate={selected_date})',
f'TR.TRESGScoreGrade(SDate={selected_date})',
f'TR.CompanyMarketCap(SDate={selected_date})',
f'TR.CompanyMarketCap.Currency',
f'TR.PriceClose(SDate={selected_date})',
f'TR.OPENPRICE(SDate={selected_date})',
f'TR.SharesOutstandingCommonTotal(SDate={selected_date})',
f'TR.TotalReturn(SDate={selected_date},EDate={formatted_EDate})',
]
)
except Exception as e:
print(f"Error fetching data for {selected_date}: {e}")
if hasattr(e, 'response'):
print(f"API Response: {e.response}")
return pd.DataFrame()
# Use ThreadPoolExecutor with adjusted max_workers
with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
# Create ExcelWriter object outside the loop
with pd.ExcelWriter(excel_filename, engine='xlsxwriter') as writer:
for year in years:
all_dfs = []
for month_day in month_days:
for md in month_day:
selected_date = f'{year}-{md}'
selected_date_dt = pd.to_datetime(selected_date)
EDate = selected_date_dt + relativedelta(days=90)
formatted_EDate = EDate.strftime('%Y-%m-%d')
all_dates = [selected_date]
batch_size = 5 # Adjust the batch size based on the API rate limits
for i in range(0, len(all_dates), batch_size):
batch_dates = all_dates[i:i+batch_size]
future_to_date = {executor.submit(fetch_data, date, formatted_EDate): date for date in tqdm(batch_dates, desc=f'Year: {year}, Batch: {i//batch_size + 1}/{len(all_dates)//batch_size + 1}')}
for future in concurrent.futures.as_completed(future_to_date):
date = future_to_date[future]
try:
df = future.result()
if not df.empty:
df['Extract Date'] = date
df['TR-End Date'] = formatted_EDate
all_dfs.append(df)
except Exception as e:
print(f"Error processing data for {date}: {e}")
result_df = pd.concat(all_dfs, ignore_index=True)
result_df.to_excel(writer, sheet_name=str(year), index=False)
# Display a message
print(f'Data saved to {excel_filename}')
#Some stock indices codes, We add 0# plus the indices code to get the constituents
#FTSE100 - 0#.FTSE
#FTSE250 - 0#.FTMC
#FTSE350 - 0#.FTLC
#S&P500 - 0#.SPX
#EUROSTOXX - 0#.STOXXE
Best Answer
-
Thank you for reaching out to us.
I think I can replicate this issue with the following code.
selected_date='2005-01-01'
df = rd.get_data(
universe=[f"0#.FTSE({selected_date})"],
fields=[
"TR.CommonName",
"TR.ISINCode",
f"TR.EnvironmentPillarScore(SDate='{selected_date}')",
f"TR.SocialPillarScore(SDate='{selected_date}')",
f"TR.GovernancePillarScore(SDate='{selected_date}')",
f"TR.TRESGScore(SDate='{selected_date}')",
f"TR.TRESGScoreGrade(SDate='{selected_date}')",
f"TR.CompanyMarketCap(SDate='{selected_date}')",
f"TR.CompanyMarketCap.Currency",
f"TR.PriceClose(SDate='{selected_date}')",
f"TR.OPENPRICE(SDate='{selected_date}')",
f"TR.SharesOutstandingCommonTotal(SDate='{selected_date}')",
f"TR.TotalReturn(SDate='{selected_date}')"])
dfThere are some missing values.
However, this developer forum is for questions on and general discussions of Refinitiv APIs.
For content questions, the best and most efficient way to receive an answer is to open a content-related inquiry via MyRefinitiv, or to call the Refinitiv Help Desk directly.
The Helpdesk will either have the required content expertise ready available or can reach out to relevant content experts to get the answer for you.
0
Answers
-
Please paste the code in the Code block so it will be easier to read and run the code.
0 -
#THIS CODE GENERATES OUTPUT ON A QUARTERLY BASIS FOR SELECTED FIELDS AND INDICES, SPECIFICALLY RELATED TO ESG SCORES
import eikon as ek
import refinitiv.data as rd
import concurrent.futures
from retry import retry
import pandas as pd
from tqdm import tqdm
from datetime import datetime
from dateutil.relativedelta import relativedelta
#The API key is obtained from Eikon's platform and might change occasionally (check before running the code)
ek.set_app_key('Enter key here')
rd.open_session()
# Specifying the desired months and days (quarters in our case)
month_days = [('01-01', '04-01', '07-01', '10-01')]
# Specifying the desired years(the range will not include the last year)
years = range(2000, 2023)
# Create a Pandas Excel, the file will be exported with all results
excel_filename = 'ESG_SCORES_QUARTERLY.xlsx'
# Add retry decorator to fetch_data function
@retry(tries=3, delay=2, backoff=2)
def fetch_data(selected_date, formatted_EDate):
try:
return rd.get_data(
universe=[f'0#.FTSE({selected_date.replace("-", "")})'],
fields=[
'TR.CommonName',
'TR.ISINCode',
f'TR.EnvironmentPillarScore(SDate={selected_date})',
f'TR.SocialPillarScore(SDate={selected_date})',
f'TR.GovernancePillarScore(SDate={selected_date})',
f'TR.TRESGScore(SDate={selected_date})',
f'TR.TRESGScoreGrade(SDate={selected_date})',
f'TR.CompanyMarketCap(SDate={selected_date})',
f'TR.CompanyMarketCap.Currency',
f'TR.PriceClose(SDate={selected_date})',
f'TR.OPENPRICE(SDate={selected_date})',
f'TR.SharesOutstandingCommonTotal(SDate={selected_date})',
f'TR.TotalReturn(SDate={selected_date},EDate={formatted_EDate})',
]
)
except Exception as e:
print(f"Error fetching data for {selected_date}: {e}")
if hasattr(e, 'response'):
print(f"API Response: {e.response}")
return pd.DataFrame()
# Use ThreadPoolExecutor with adjusted max_workers
with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
# Create ExcelWriter object outside the loop
with pd.ExcelWriter(excel_filename, engine='xlsxwriter') as writer:
for year in years:
all_dfs = []
for month_day in month_days:
for md in month_day:
selected_date = f'{year}-{md}'
selected_date_dt = pd.to_datetime(selected_date)
EDate = selected_date_dt + relativedelta(days=90)
formatted_EDate = EDate.strftime('%Y-%m-%d')
all_dates = [selected_date]
batch_size = 5 # Adjust the batch size based on the API rate limits
for i in range(0, len(all_dates), batch_size):
batch_dates = all_dates[i:i+batch_size]
future_to_date = {executor.submit(fetch_data, date, formatted_EDate): date for date in tqdm(batch_dates, desc=f'Year: {year}, Batch: {i//batch_size + 1}/{len(all_dates)//batch_size + 1}')}
for future in concurrent.futures.as_completed(future_to_date):
date = future_to_date[future]
try:
df = future.result()
if not df.empty:
df['Extract Date'] = date
df['TR-End Date'] = formatted_EDate
all_dfs.append(df)
except Exception as e:
print(f"Error processing data for {date}: {e}")
result_df = pd.concat(all_dfs, ignore_index=True)
result_df.to_excel(writer, sheet_name=str(year), index=False)
# Display a message
print(f'Data saved to {excel_filename}')
#Some stock indices codes, We add 0# plus the indices code to get the constituents
#FTSE100 - 0#.FTSE
#FTSE250 - 0#.FTMC
#FTSE350 - 0#.FTLC
#S&P500 - 0#.SPX
#EUROSTOXX - 0#.STOXXE0 -
#THIS CODE GENERATES OUTPUT ON A QUARTERLY BASIS FOR SELECTED FIELDS AND INDICES, SPECIFICALLY RELATED TO ESG SCORES
import eikon as ek
import refinitiv.data as rd
import concurrent.futures
from retry import retry
import pandas as pd
from tqdm import tqdm
from datetime import datetime
from dateutil.relativedelta import relativedelta
#The API key is obtained from Eikon's platform and might change occasionally (check before running the code)
ek.set_app_key('Enter key here')
rd.open_session()
# Specifying the desired months and days (quarters in our case)
month_days = [('01-01', '04-01', '07-01', '10-01')]
# Specifying the desired years(the range will not include the last year)
years = range(2000, 2023)
# Create a Pandas Excel, the file will be exported with all results
excel_filename = 'ESG_SCORES_QUARTERLY.xlsx'
# Add retry decorator to fetch_data function
@retry(tries=3, delay=2, backoff=2)
def fetch_data(selected_date, formatted_EDate):
try:
return rd.get_data(
universe=[f'0#.FTSE({selected_date.replace("-", "")})'],
fields=[
'TR.CommonName',
'TR.ISINCode',
f'TR.EnvironmentPillarScore(SDate={selected_date})',
f'TR.SocialPillarScore(SDate={selected_date})',
f'TR.GovernancePillarScore(SDate={selected_date})',
f'TR.TRESGScore(SDate={selected_date})',
f'TR.TRESGScoreGrade(SDate={selected_date})',
f'TR.CompanyMarketCap(SDate={selected_date})',
f'TR.CompanyMarketCap.Currency',
f'TR.PriceClose(SDate={selected_date})',
f'TR.OPENPRICE(SDate={selected_date})',
f'TR.SharesOutstandingCommonTotal(SDate={selected_date})',
f'TR.TotalReturn(SDate={selected_date},EDate={formatted_EDate})',
]
)
except Exception as e:
print(f"Error fetching data for {selected_date}: {e}")
if hasattr(e, 'response'):
print(f"API Response: {e.response}")
return pd.DataFrame()
# Use ThreadPoolExecutor with adjusted max_workers
with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor:
# Create ExcelWriter object outside the loop
with pd.ExcelWriter(excel_filename, engine='xlsxwriter') as writer:
for year in years:
all_dfs = []
for month_day in month_days:
for md in month_day:
selected_date = f'{year}-{md}'
selected_date_dt = pd.to_datetime(selected_date)
EDate = selected_date_dt + relativedelta(days=90)
formatted_EDate = EDate.strftime('%Y-%m-%d')
all_dates = [selected_date]
batch_size = 5 # Adjust the batch size based on the API rate limits
for i in range(0, len(all_dates), batch_size):
batch_dates = all_dates[i:i+batch_size]
future_to_date = {executor.submit(fetch_data, date, formatted_EDate): date for date in tqdm(batch_dates, desc=f'Year: {year}, Batch: {i//batch_size + 1}/{len(all_dates)//batch_size + 1}')}
for future in concurrent.futures.as_completed(future_to_date):
date = future_to_date[future]
try:
df = future.result()
if not df.empty:
df['Extract Date'] = date
df['TR-End Date'] = formatted_EDate
all_dfs.append(df)
except Exception as e:
print(f"Error processing data for {date}: {e}")
result_df = pd.concat(all_dfs, ignore_index=True)
result_df.to_excel(writer, sheet_name=str(year), index=False)
# Display a message
print(f'Data saved to {excel_filename}')
#Some stock indices codes, We add 0# plus the indices code to get the constituents
#FTSE100 - 0#.FTSE
#FTSE250 - 0#.FTMC
#FTSE350 - 0#.FTLC
#S&P500 - 0#.SPX
#EUROSTOXX - 0#.STOXXE0 -
I am not sure why I still can't see indentations in the code. For example:
try:
df = rd.get_data([eikon_code],
['TR.IndexConstituentRIC', 'TR.IndexConstituentWeightPercent(S=V)'],
{'SDate': str(pool_last_date)})
except Exception as err:
print(err)
finally:
print('rd.get_data() Success!!!')Indentaions are important in Python. Otherwise, we can't run it properly.
0 -
In the continuation of the above, it seems the data pulled from Eikon API and Datastream is different. Especially ESG Scores and indices constituents.
The above code provides the following extract on 01/07/2007 (Sampled 5 stocks from FTSE100) and the same was pulled using Datastream request table for the same date, we getQuestions:
- Are ENSCORE, SOSCORE, CGSCORE, and TRESGS (Datatype from Datastream) the same as the Environmental Pillar Score, Social Pillar Score, Governance Pillar Score, and ESG Score (Datatype from Eikon API) respectively? If not, what would be the common identifiers for Environmental, Social, Governance, and ESG overall scores on both platforms?
- Extracted FTSE100 Constituents from Eikon API are different than the provided Datastream list.
For example on 01/07/2007, Abi Sab Group Holding Ltd (GB00BYZTBD95) is present in Eikon API list but missing in Datastream extra (That is just one example, there are multiple missing in one or the other - The constituents lists are not consistent across both Eikon API and Datastream).
Kindly advise if there are any mistakes from my side.
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 中文论坛