Problem with delisted stocks and TR.AvgDailyValTraded20D
Hi,
I've been trying to use TR.AvgDailyValTraded20D and similar fields for average traded values. But I've been encountering issues when the stocks are delisted. Usually, delisted stocks are reported with some error message, NaN or whatever, but an outcome exist.
With TR.AvgDailyValTraded20D, that is not a possibility. I attached a sheet with ID's in which I tried making the requests one-by-one on a single column, and in the other column, a request with .instrument for the same date but all companies at once.
Is there a way to bypass this error in Excel?
This also happens in Python, for this same universe of companies. I have not tried this yet but I wonder if the request for Python would have any success (because when I request it it sends back an empty dataframe with no instruments whatsoever).
Has anyone encountered this type of error before?
Best Answer
-
I have tested the code and found that the problem has been resolved.
0
Answers
-
@22ca8780-99e4-42fa-9ca2-b27abad3c0c4
I'm not sure I understand the issue. I tried requesting TR.AvgDailyValTraded20D field for the list of identifiers you provided and I have no problem retrieving the data. The data is not returned for all instruments in the list as some identifiers cannot be resolved at all, some are resolved to private companies or issues delisted so long ago that we don't have any price or volume history for them. But I am getting a response for all instruments in the list. The error message returned for instruments with no data at all is "Unable to collect data for the field 'TR.AvgDailyValTraded20D' and some specific identifier(s)."
What we would need from you to progress the issue is an exact replication procedure or an example that can be used to reproduce what you experienced. And since you say you reproduce the problem in Excel, I suggest you raise the support case with TR Helpdesk by either calling the Helpdesk number in your country or by using Contact Us capability in your Eikon application.1 -
Ok, let's try these:
1) Excel: I ran this formula for the whole universe.=TR($A$3:$A$2503,"TR.AvgDailyValTraded20D","curn=usd sdate="&12/31/2016&" edate="&12/31/2016&"",)
You will notice that not all of the outcomes are reported.2) Try to run this with python ('identifiers' variable is just the data readed from the ID's file I sent you):
ek.set_app_id('xxxxxx')
df = ek.get_data(identifiers, ['TR.AvgDailyValTraded20D'], {'curn'='usd' 'sdate'='12/31/2016' 'edate'='12/31/2016'})
df=df[0] #getting the dataframe
print(df)I get a Dataframe with pure NaN's, no "instrument" column.
0 -
I can replicate the issue. It could be a problem in Eikon Data APIs. I will contact the product team to verify it.
0 -
Hello @ec_20_inf,
You appear to say that this discrepancy exists in Excel as well as via Eikon python API.
Therefore, it appears to be accessed via API in a consistent manner.
I have tested with a couple of instruments, some from your list, and some not, the behavior appears to be as expected:
df = ek.get_data(['US345397VR12','IBM.N','JP3274200009','JP3414000004','TW0002852001'], ['TR.AvgDailyValTraded20D'], {'curn':'usd','sdate':'12/31/2016','edate':'12/31/2016'})
df=df[0]
print(df)Instrument Average Daily Value Traded - 20 Days
0 US345397VR12 NaN
1 IBM.N 1.468755e+08
2 JP3274200009 1.198922e+05
3 JP3414000004 8.368501e+05
4 TW0002852001 6.729630e+04And if the behavior is consistent, between API access and Excel access, the NaNs are expected to be returned, then the question is on content, why some RICs result in NaN.
Therefore, if the question is still relevant for you, the best way to get content questions answered is asking Refinitiv content helpdesk my.refinitiv.com.
0
Categories
- All Categories
- 6 AHS
- 39 Alpha
- 162 App Studio
- 4 Block Chain
- 5 Bot Platform
- 17 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
- 370 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
- 60 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛