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) ?
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:
