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?
Best Answer
-
@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)1
Answers
-
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.
0 -
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?
0 -
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
0 -
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.
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)
1
Categories
- All Categories
- 6 AHS
- 39 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 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
- 367 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 中文论坛