Hi, I am try to replicate a formula in DFO/Excel
=DSGRID("CHCNYEZSQ,CHCNBQJHQ,CHCNXFSVQ"," ","-2Y","","Q","RowHeader=true;ColHeader=true;DispSeriesDescription=false;YearlyTSFormat=false;QuarterlyTSFormat=false","")
The dates shown are Q1 XXXX,Q2 XXXX, etc. and there are column headings.
When I use DSWS API, dates returned are yyyy-mm--dd and column headings are just the instruments.
Is there anything to add to the code in order to show the same dates and column headers as in DFO (Excel) ?
Hello @Alan Kar Lun.Tam
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
Hi,
Please be informed that a reply has been verified as correct in answering the question, and has been marked as such.
Thanks,
AHS
From my checking, the DataStreamDSWS library can't retrieve the data type's full name. Therefore, it is unable to show the column headers as in DFO.
However, I found the PyDatastream library can do it. The code is:
from pydatastream import Datastream
import pandas as pd
from dateutil.relativedelta import relativedelta
import datetime
DS = Datastream(username="username", password="password")
itemList = ['CHCNYEZSQ','CHCNBQJHQ','CHCNXFSVQ']
requestList = []
startDate = datetime.datetime.now() - relativedelta(years=2)
for item in itemList:
r = DS.construct_request(item, [''], date_from=startDate,freq='Q',return_names=True)
requestList.append(r)
response = DS.request_many(requestList)
dfs = DS.parse_response(response)
dfList = []
for i in range(len(dfs)):
df_temp = dfs[i].droplevel(level=0)
df_temp.columns = [response['DataResponses'][i]['DataTypeNames'][0]['Value']]
df_temp['Quarter'] = pd.PeriodIndex(df_temp.index, freq='Q')
df_temp = df_temp.set_index('Quarter')
dfList.append(df_temp)
result = pd.concat(dfList, axis=1)
result
The output is:
