Looking for an efficient way to download price quotes for a list of stock tickers into dataframe.

Using the following Python statement:

startdate = '2018-03-18'

enddate = '2018-03-20'

symbols = ['AAPL.OQ', 'CGNX.O', 'ABMD.OQ', 'PCO.V', 'BESI.AS']

mydata = ek.get_timeseries(symbols, fields=['OPEN', 'CLOSE', 'HIGH', 'LOW', 'VOLUME'], start_date=startdate, end_date=enddate)

I get a dataframe where the columns consist of the <Date>, <tickername+u'OPEN', tickername+u'CLOSE', tickername+u'HIGH', tickername+u'LOW', tickername+u'VOLUME', tickername+u'OPEN', next_tickername+u'OPEN', next_tickername+u'CLOSE', next_tickername+u'HIGH', next_tickername+u'LOW', next_tickername+u'VOLUME', third_tickername+u'OPEN', etc, etc. and rows consist of dates.The price quotes are filled accordingly.

Is there a way to issue a statement for multiple symbols and retrieve a dataframe where columns are:

'Date','RIC_Code', 'OPEN', 'CLOSE', 'HIGH', 'LOW', 'VOLUME'

and the rows are filled with dates and price quotes accordingly?

Find more posts tagged with

python

eikon

quote

workspace

eikon-data-api

refinitiv-dataplatform-eikon

workspace-data-api

Accepted answers

oalexandere

@vanderkroon Sounds like manipulating with pandas dataframe. May be there is a shorter way to do this, but hope this helps:

data = pd.DataFrame()
for ric in mydata.columns.levels[0]:
    temp = mydata[ric].reset_index()
    temp.insert(1, column='RIC_Code', value=ric)
    data = pd.concat((data,temp))
data = data.sort_values(by=['Date', 'RIC_Code']).reset_index(drop=True)

All comments

nick.zincone

Hi @vanderkroon,

I just tried your exact (well almost) request in jupyter notebook and the result came back as:

That is, a dataframe as multiple rows filled with dates and prices where the columns are 'RIC', 'OPEN', 'CLOSE', etc. If this is not what you are referring to, perhaps you can provide a simple screenshot or representation of exactly what you need.

thanks.

vanderkroon

Is there a way to get the RIC Code in the rows? Such that I get: the following columns:

'Date','RIC_Code', 'OPEN', 'CLOSE', 'HIGH', 'LOW', 'VOLUME'

and the rows would then contain:

2018-03-19 AAPL.OQ 177.25 175.30 177.47 173.66 99566654

2018-03-19 CGNX.O 54.46 54.43 54.80 53.2869 2119828

2018-03-19 ABMD.OQ 291.27 287.98 294.03 285.03 257711.0

2018-03-20 AAPL.OQ 177.28 175.24 176.79 174.945 6644902

2018-03-20 CGNX.O 54.40 56.15 56.32 54.3900 1836111

etc.

Is there a commend that would result in a Dataframe layout as outlined above?

oalexandere

@vanderkroon Sounds like manipulating with pandas dataframe. May be there is a shorter way to do this, but hope this helps:

data = pd.DataFrame()
for ric in mydata.columns.levels[0]:
    temp = mydata[ric].reset_index()
    temp.insert(1, column='RIC_Code', value=ric)
    data = pd.concat((data,temp))
data = data.sort_values(by=['Date', 'RIC_Code']).reset_index(drop=True)

vanderkroon

You are a genius! Do yourself a favor: Buy this sign saying <"GENIUS AT WORK"> and have your boss pay for it!

May I ask one more question. When I run this code I get many more dates than the range of dates that I ask for, i.e. from startdate thru enddate. Is there an explanation as to why this happens? If I send in the request without startdate and enddate, as follows:

mydata = ek.get_timeseries(symbols, fields=['OPEN', 'CLOSE', 'HIGH', 'LOW', 'VOLUME'])

I end up with a little over three months of pricing quotes for each stock symbol. I can live it, but I really only need two or three days for my purposes.

Regards and hats off!

Gerard van der Kroon

Alex Putkov.1

The behavior you observed is due to the fact that there were no trades for the date range you requested for one of the instruments in your list. PCO.V is an illiquid stock and may not trade for days and weeks on end. When you request the date range that does not exist in the timeseries for a given instrument the entire price history is returned for that instrument.

Alex Putkov.1

You can limit the output using "count" parameter in get_timeseries method. E.g. if you want the latest available 3 days of price history for each instrument in the list irrespective of the dates for those 3 days use ek.get_timeseries(symbols, fields, count=3)

EXPLORE OUR SITES