Date format and column headings

Alan Tam123
Alan Tam123
in Datastream

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","")

image

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.

image

Is there anything to add to the code in order to show the same dates and column headers as in DFO (Excel) ?

Tagged:

Best Answer

  • Jirapongse
    Jirapongse admin
    Answer ✓

    @Alan Kar Lun.Tam

    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:

    image

Categories