Data Missing in API, available in Excel

wesley.ng

Here's some sample working code, just have to set your variable for the Eikon API key:

from typing import Union, List 
import pandas as pd 
import eikon as ek
import os

os.environ['HTTP_PROXY'] = ''
os.environ['HTTPS_PROXY'] = ''

def get_bid_ask(id_list: Union[List[str], str], start_date: str, end_date: str, api_key: str) -> pd.DataFrame:                          

  ek.set_app_key(api_key)

  if isinstance(id_list, str):
      id_list = [id_list]

  fields = ['TR.BIDPRICE', 'TR.ASKPRICE', 'TR.ASKPRICE.calcdate', 'TR.FiCurrency']
  parameters = {'Frq': 'D', 'Sdate': start_date, 'Edate': end_date}

  df, _ = ek.get_data(instruments = id_list, fields = fields, parameters = parameters)

  # Broadcast currency since currency is only returned on first date
  df['Currency'] = df.groupby('Instrument')['Currency'].transform('first')

  # Drop rows with NA
  df = df.dropna()

  # Filter for dates - there is a bug where more dates than those inputted are returned.
  # Example: id_list = 'XS2078642183', start_date = '2022-07-22', end_date = '2022-07-22'
  df = df.loc[ (df['Calc Date'] >= start_date) & (df['Calc Date'] <= end_date) ]

  return df


id_list = "XS1713193586"
asofdate = "2022-10-05"
test = get_bid_ask(id_list, start_date=asofdate, end_date=asofdate, api_key=api_key)

Here's the output:

Here's the available Bid/Ask price in Excel:

Using the formula:

=@TR("HK171319358=","TR.BIDPRICE; TR.ASKPRICE","SDate=2022-10-05 CH=Fd RH=IN",B2)

Might be a mapping issue, please advice.

Many thanks.

aramyan.h

Hi @wesley.ng ,

Just received confirmation from the Dev team that this is indeed an issue, and will be solved in 1.65 release that goes live in mid Feb 2023.

Best regards,

Haykaz

aramyan.h

Dear @wesley.ng ,

Thank you for your question. I have tried to debug your code by adding a couple of prints in the function and what I have found is that the request is returning the value you are looking for. The data is then lost when you filter for dates (df = df.loc[ (df['Calc Date'] >= start_date) & (df['Calc Date'] <= end_date) ]). The Ask.Calcdate is 2022-09-28 while you are filtering for 2022-10-05.

Please find the details below:

Hope this was helpful and let me know should you have any further questions.

Best regards,

Haykaz

wesley.ng

Hi @h.aramyan01,

Thanks for debugging - apologies that the issue seems to be caused on my side. But the results begs the question of why the bid / ask data is not updated, both in the Excel and the API?

I can clearly see the price history beyond 28 Sept 22 for the security in question:

Many thanks.

Cheers,
Wesley

aramyan.h

Hi @wesley.ng ,

It does shoe the newest values when you provide a range in parameters, rather than using same value for Sdate and Edate. See below:

However, I believe it should show the latest when a same date is specified, which is not the case for this instrument. It works for other instruments (eg AAPL.O) for instance. Let me check this with the Product team and I'll come back to you with an answer.

In the meantime you could probably work with range values.

Best regards,

Haykaz

wesley.ng

Hi @h.aramyan01,

It doesn't make much sense to work with range values for my use cases, or even put in a range just to filter it down again, which increases query time and the number of requests. Also would like to highlight another bug present in the example you showed, which is that the returned date rate (2022-09-29 to 2022-10-05) is larger than the queried date range.

Appreciate you following up with your product team, will await your answer.

Many thanks.

Cheers,
Wesley

m.bunkowski

Hi @h.aramyan01

I think that your inputs are a bit different. You are comparing RIC HK171319358= or directly ISIN XS1713193586 with the HK171319358=RRPS as seen on your screenshot from Eikon. Please use the RRPS RIC to get the same through API or Excel.

aramyan.h

Hi @marcin.bunkowski01 ,

Thanks for your input. In my comments (code examples) I am using only XS1713193586 though and still can spot the problem with the date range and incompatibility between requested and returned dates.

m.bunkowski

Hi @h.aramyan01

The question between data availability between two RICs should be raised to suport team via Contact Us functionality in Eikon.
Looking at your initial example you should rather use .data suffix instead of .calcdate to get the datestamp of a datapoint.

fields = ['TR.BIDPRICE', 'TR.ASKPRICE', 'TR.ASKPRICE.date', 'TR.FiCurrency']

aramyan.h

Hi @wesley.ng ,

I have already raised the issue related to the difference in requested and returned dates to the respective team and will update the thread accordingly.

In the meantime, please note that to get the datestamp of a datapoint you should use .date suffix as noted by @marcin.bunkowski01 .

Best regards,

Haykaz

frank.ling1

You may use the ric 'HK171319358=RRPS' instead of 'HK171319358= ' to replicate what you saw on price history page.
The bid/ask yield are the same as above displayed.

wesley.ng

Thanks Haykaz - do you have a rough estimate for how long it will take to roll out the fix?

wesley.ng

Hi @h.aramyan01,

Just wanted to add, I'm getting similar issues (i.e. correct data not being returned when it's available in the desktop application, weird date ranges) for the following ISINs as well:

• US88032XBB91
• USG6S94TAG83
• XS2193950271
• XS1713193586
• XS1955077596

Please forward these to your product team.

Many thanks.

Cheers,
Wesley

aramyan.h

Hi @wesley.ng ,

Followed up the team, will come back to you as soon as get an answer. In the meantime, have you checked the response from @frank.ling1 ?

Best regards,

Haykaz

wesley.ng

Hi
@h.aramyan01,

The workaround provided by @frank.ling1 will not work because I'm working with thousands of ISINs here, and would not be able to map the RICs individually for each security that doesn't return the requested fields.

Cheers,
Wesley

frank.ling1

When there is no market price available, you can use pricing data from Refinitiv Price Service by adding "RRPS" (contributor code) behind the RIC as the case.

wesley.ng

Hi @h.aramyan01,

Just to check - is the team able to provide an estimate of how long this will take to fix?

Cheers,
Wesley

wesley.ng

Hi
@h.aramyan01,

Is it possible for me to follow up on this enquiry outside of the forum?

Would very much appreciate an update on this.

Cheers,
Wesley

aramyan.h

Hi @wesley.ng ,

I am in communication with the Dev Team and we are trying to understand if it is actually a bug or an expected behavior which might be handled differently. In any case, if that would be a bug, you can expect the fix 2023 Q1, if not, I will provide an option to handle it.

Thank you very much for your patience.

Best regards,

Haykaz

wesley.ng

Hi
@h.aramyan01, thanks for the update. Appreciated.