Python: Eikon is generating unrequested dates for some ID's, using get_data.

I have the following code that requests local returns for a list of companies.

import pandas as pd
import eikon as ek #import Eikon module
import datetime
import time

start_time=time.time()
ek.set_app_id('someId') #setting AppID
#Getting a list of dates
start=datetime.date(2017,11,27)
end=datetime.date.today() - datetime.timedelta(days=1)
row_dates=[x.strftime('%m/%d/%Y') for x in pd.bdate_range(start,end).tolist()]
#getting identifiers to be used on Eikon
csv_data=pd.read_csv('identifiers_test.csv', header=None)
identifiers=csv_data[0].tolist()

This is a list with 2500 identifiers or so (all SEDOLS).

To request the returns, I do this:

df=ek.get_data(identifiers,["TR.TotalReturn.Date","TR.TotalReturn"], {'SDate': row_dates[0], 'EDate': row_dates[len(row_dates)-1], 'Frq':'D'})[0]

To pivot the table, and re-arrange it in a way that I can have dates as column headers and identifiers as indexes without being repeated, I do this:

df=pd.crosstab(df.Instrument, df.Date,values=df['Total Return'], aggfunc='mean')

But the outcome is really strange, it places columns with dates that I didn't even requested, filled with NaN's, and I would like to know how to filter that info. I already tried a couple of approaches with dropna() and other Pandas functions but I can't seem to get them to work :(, I'll attach 2 photos so you can see what I'm talking about).

image

And it properly shows dates I've requested, like this (shown 28-Nov, 29-Nov and 30-Nov. Not displaying in the picture but appeared properly in the dataframe, are 27-Nov and 01-Dec).

image

How can I get rid of those dates? Obviously these dates were produced by Eikon's API, somehow. I printed the original dataframe into a CSV file, I should have only 5 dates, I obviously found more. I can't upload the screencapture for this since this forum doesn't allow more than 2 image uploads.


Why Eikon does this? Is there a way to ellude it? A way to fix it? I'd like to know.

Thanks in advance!

Welcome!

It looks like you're new here. Sign in or register to get started.

Best Answer

  • Zhenya Kovalyov
    Answer ✓

    Ok, I was able to get the list of the instruments, that are generating incorrect dates, you can use the following code:

    instruments = {}


    for ix, row in df.iterrows():
    instruments.setdefault(row[1], set()).add(row[0])

    instrument

    A quick manual check shows that the incorrect date is when the asset traded last, for example:

    B2Q1MC6 (PGD.P) - 5th of September
    B4TDMX4 (SBV.P) - 19th of September

    Etc.

    So, when you are requesting the latest Total return value, what it can give you is the last possible estimate.

Answers

Welcome!

It looks like you're new here. Sign in or register to get started.

Welcome!

It looks like you're new here. Sign in or register to get started.