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?

stock-test.txt

Find more posts tagged with

python

eikon

workspace

eikon-data-api

refinitiv-dataplatform-eikon

workspace-data-api

Accepted answers

Jirapongse

I have tested the code and found that the problem has been resolved.

All comments

Alex Putkov.1

@22ca8780-99e4-42fa-9ca2-b27abad3c0c4_deprecated_52290d28-5651-43dc-98eb-52c06ef37810
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.

ec_20_inf

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.

zoya faberov

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+04

And 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.

Jirapongse

I have tested the code and found that the problem has been resolved.

EXPLORE OUR SITES