How to get data using LIST# function for changing Instruments as one time series data

Question

question

bob.kim

66 ●3 ●5 ●7

How to get data using LIST# function for changing Instruments as one time series data

I'm trying to get the sum of all assets of stocks in the ASX 200 Index. However, the constituent stocks of the index change over time. Therefore, i tried running a while loop for the following DFO function.

=DSGRID("LASX200I0900","LIST#(X,X(WC02999),SUM)","2000-09-01","2000-09-21","W","RowHeader=true;ColHeader=true;DispSeriesDescription=false;YearlyTSFormat=false;QuarterlyTSFormat=false","")

- where the instrument "LASX200I0900" gets updated for every loop, i.e. constituents for Sep 2000 becomes LASX200I0900, for Oct 2000 becomes LASX200I1000 (So LASX200I+mmyy).

My guess is that as Instruments change, DatastreamDSWS class cannot contain it as one time series and causing issue.

Would appreciate if you can assist - whether it be resolving the error, or advise on a different approach.

PYTHON CODE: You can copy paste the following into Jupyter notebook (for some reason cannot upload the file itself)

import pandas as pd

import datetime as dt

import DatastreamDSWS as DSWS

# ds = DSWS.Datastream(username ="username", password="password")

ds = DSWS.Datastream(username ="username", password="password")

Index = 'LASX200I' # 1. Input your index root mnemonic

Data_Type = 'WC02999' # 2. Input your Datatype

End_Date = dt.datetime.now()

End_Date = dt.datetime(End_Date.year,End_Date.month-1,End_Date.day)

Date = dt.datetime(2000,9,1) # 3. Input your start year and date. Day is set as 1st of month. Please check start date from DS

Start_Year = Date.year

Start_Month = Date.month

while Start_Month <= 12 and Date < End_Date:

Index_t = Index + Date.strftime("%m") + Date.strftime("%y")

Output_Total =[]

Field1 = 'LIST#(X,X(' + Data_Type + '),SUM)'

Start = Date.strftime("%Y") + '-' + Date.strftime("%m") + '-' + Date.strftime("%d")

End = Date.strftime("%Y") + '-' + Date.strftime("%m") + '-' + str(Date.day+21)

# print(Start,End)

Output_Total.append(ds.post_user_request(tickers=Index_t, fields=[Field1], start=Start, end=End, freq = "w"))#Timeseries data

#Output_Total.append(ds.post_user_request(tickers=Index_t, fields=['LIST#(X,X(WC02999),SUM)'], start='2000-09-01', end='2000-09-21', freq = "W"))#Timeseries data

#'LIST#(X,X('+Data_Type+')*X(MV),SUM)'

# print(Index_t)#checking ticker

Start_Month = Start_Month%12 + 1

if Start_Month == 1:

Start_Year+=1

Date = dt.datetime(Start_Year,Start_Month,1)

else:

Date = dt.datetime(Start_Year,Start_Month,1)

ds.get_bundle_data(bundleRequest=Output_Total)

datastream-api dsws-api

1582791651995.png (69.4 KiB)

Feb 26, 2020 at 06:58 AM

10 |1500

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Jirapongse Feb 27, 2020 at 08:26 AM

@bob.kim

I have removed the username and password from the question. However, this question is in the public forum for almost a day. You must reset the password of your DataStream account.

pimchaya.wongrukun01 Mar 12, 2020 at 04:09 AM

Hello @bob.kim

Thank you for your participation in the forum. Is the reply below satisfactory in resolving your query?

If yes, please click the 'Accept' text next to the reply. This will guide all community members who have a similar question. Otherwise please post again offering further insight into your question.

Thanks,

AHS

Answer 1 · 2020-03-02T07:11:19Z

Jirapongse

79.2k ●251 ●52 ●74

@bob.kim

Therefore, we need to concatenate all data frames into one. The code could be:

Index = 'LASX200I' # 1. Input your index root mnemonic
Data_Type = 'WC02999' # 2. Input your Datatype
End_Date = dt.datetime.now()
End_Date = dt.datetime(End_Date.year,End_Date.month-1,End_Date.day)
Date = dt.datetime(2000,9,1) # 3. Input your start year and date. Day is set as 1st of month. Please check start date from DS
Start_Year = Date.year
Start_Month = Date.month
Output_Total =[]
while Start_Month <= 12 and Date < End_Date:
    Index_t = Index + Date.strftime("%m") + Date.strftime("%y")
    Field1 = 'LIST#(X,X(' + Data_Type + '),SUM)'
    Start = Date.strftime("%Y") + '-' + Date.strftime("%m") + '-' + Date.strftime("%d")
    End = Date.strftime("%Y") + '-' + Date.strftime("%m") + '-' + str(Date.day+21)
    Output_Total.append(ds.post_user_request(tickers=Index_t, fields=[Field1], start=Start, end=End, freq = "W"))#Timeseries data  
    Start_Month = Start_Month%12 + 1
    if Start_Month == 1:
        Start_Year+=1
        Date = dt.datetime(Start_Year,Start_Month,1)
    else:
        Date = dt.datetime(Start_Year,Start_Month,1)
        
#Split array to size of 20
dfList=[]
n=20
final_list = [Output_Total[i * n:(i + 1) * n] for i in range((len(Output_Total) + n - 1) // n )]  
for _list in final_list:    
   #dataList.append(ds.get_bundle_data(bundleRequest=_list))
    outList = ds.get_bundle_data(bundleRequest=_list)
    for _df in outList:
        if(isinstance(_df.columns, pd.core.indexes.multi.MultiIndex)):
            _df.columns = _df.columns.droplevel()
            dfList.append(_df)

result = pd.concat(dfList)
result

The result is:

1583133054348.png (11.0 KiB)

Mar 02, 2020 at 07:11 AM

10 |1500

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bob.kim Mar 12, 2020 at 05:22 AM

Awesome! Very much appreciated!

Answer 2 · 2020-02-27T09:19:30Z

Jirapongse

79.2k ●251 ●52 ●74

@bob.kim

The code should be:

Index = 'LASX200I' # 1. Input your index root mnemonic
Data_Type = 'WC02999' # 2. Input your Datatype
End_Date = dt.datetime.now()
End_Date = dt.datetime(End_Date.year,End_Date.month-1,End_Date.day)
Date = dt.datetime(2000,9,1) # 3. Input your start year and date. Day is set as 1st of month. Please check start date from DS
Start_Year = Date.year
Start_Month = Date.month
Output_Total =[]
while Start_Month <= 12 and Date < End_Date:
    Index_t = Index + Date.strftime("%m") + Date.strftime("%y")
    Field1 = 'LIST#(X,X(' + Data_Type + '),SUM)'
    Start = Date.strftime("%Y") + '-' + Date.strftime("%m") + '-' + Date.strftime("%d")
    End = Date.strftime("%Y") + '-' + Date.strftime("%m") + '-' + str(Date.day+21)
    Output_Total.append(ds.post_user_request(tickers=Index_t, fields=[Field1], start=Start, end=End, freq = "W"))#Timeseries data  
    Start_Month = Start_Month%12 + 1
    if Start_Month == 1:
        Start_Year+=1
        Date = dt.datetime(Start_Year,Start_Month,1)
    else:
        Date = dt.datetime(Start_Year,Start_Month,1)
        
#Split array to size of 20
n=20
final_list = [Output_Total[i * n:(i + 1) * n] for i in range((len(Output_Total) + n - 1) // n )]  
for _list in final_list:    
    print(ds.get_bundle_data(bundleRequest=_list))

The maximum size of a bundle request is 20.

Feb 27, 2020 at 09:19 AM

10 |1500

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

bob.kim Mar 02, 2020 at 12:36 AM

Thanks so much for this!! However, i have one follow up question.

As you can see, each loop will display Instrument, Field, etc...which does not present the time series into one set (pls see below screen shot). However, i want to get it as one time series so that i can seamlessly plot it in a chart. In other words, i don't want the time series to be divided into different time series. I only need it as one, and don't need instrument information.

To do this, i tried to change the Output_Total list into a data frame, and then printing it. However, this does not work. What would be the best way to go about this?

Code attached in next comment section

untitled.jpg (94.9 KiB)

Answer 3 · 2020-03-02T04:10:49Z

bob.kim

66 ●3 ●5 ●7

Thanks However, I'm trying to save this as one '1' timeseries without dividing it by instruments. Can we not use Bundle? I tried changing the Output_Total list into Data Frame but seems like it is incorrect. My code is below:

import pandas as pd

import datetime as dt

import DatastreamDSWS as DSWS

import IPython as myIP

ds = DSWS.Datastream(username ="USERNAME", password="PASSWORD")

Index = 'LASX200I' # 1. Input your index root mnemonic

Data_Type = 'WC02999' # 2. Input your Datatype

#End_Date = dt.datetime.now()

End_Date = dt.datetime(2001,3,30)

Date = dt.datetime(2000,9,1) # 3. Input your start year and date. Day is set as 1st of month. Please check start date from DS

Start_Year = Date.year

Start_Month = Date.month

Output_Total =[]

while Start_Month <= 12 and Date < End_Date:

Index_t = Index + Date.strftime("%m") + Date.strftime("%y")

Field1 = 'LIST#(X,X(' + Data_Type + '),SUM)'

Start = Date.strftime("%Y") + '-' + Date.strftime("%m") + '-' + Date.strftime("%d")

End = Date.strftime("%Y") + '-' + Date.strftime("%m") + '-' + str(Date.day+14)

Output_Total.append(ds.post_user_request(tickers=Index_t, fields=[Field1], start=Start, end=End, freq = "W"))#Timeseries data

Start_Month = Start_Month%12 + 1

if Start_Month == 1:

Start_Year+=1

Date = dt.datetime(Start_Year,Start_Month,1)

else:

Date = dt.datetime(Start_Year,Start_Month,1)

DF_Output_Total = pd.DataFrame(Output_Total)

print(DF_Output_Total)

Mar 02, 2020 at 04:10 AM

10 |1500

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Q&A Forum

question

How to get data using LIST# function for changing Instruments as one time series data

3 Answers

Write an Answer

question

How to get data using LIST# function for changing Instruments as one time series data

3 Answers

Write an Answer

Related Questions