Replicate GRAVG function (Group Average in TR) in Python

Hello, i have this formula in Excel:

=@TR(&quot;CHRH.CO,.OMXC25CAP,.OMXCCAPGI","GRAVG(TR.PriceClose,universe=""univ"",SDate=2018-08-17 EDate=2021-08-17 Frq=WD Curn=DKK);TR.PriceClose","SDate=2018-08-17 EDate=2021-08-17 Frq=WD CH=IN RH=calcdate SORTA=calcdate NULL=blank Curn=DKK",A1)

However, i cannot implement this in Python. See below what I have tried.

Trying with SDate and EDate inside GRAVG:
I thought I would just get it with get_data. If I have EDate and SDate inside the GRAVG:

get_data("CHRH.CO,.OMXC25CAP,.OMXCCAPGI","GRAVG(TR.PriceClose,universe=""univ"",""SDate=2018-08-17 EDate=2021-08-17 Frq=WD Curn=DKK"");TR.PriceClose", {"SDate": "2018-08-17", "EDate": "2021-08-17", "Frq": "WD", "RH": "calculate", "SORTA": "calculate", "NULL": "blank", "Curn": "DKK"}))

It provides the error in the message section:

"The 'EDATE' is unexpected in formula. A delimiter is probably missing before the lexeme."

Trying without SDate and EDate inside of GRAVG:

Below is an example of when I put it outside, but here I only get one entry for the value of the group average but all of the TR.PriceClose values:

get_data("CHRH.CO,.OMXC25CAP,.OMXCCAPGI","GRAVG(TR.PriceClose,universe=""univ"",""Curn=DKK"");TR.PriceClose", {"SDate": "2018-08-17", "EDate": "2021-08-17", "Frq": "WD", "RH": "calculate", "SORTA": "calculate", "NULL": "blank", "Curn": "DKK"})

I'm stuck on this issue and any help is greatly appreciated.

Find more posts tagged with

python

eikon

workspace

eikon-data-api

refinitiv-dataplatform-eikon

workspace-data-api

Accepted answers

Jirapongse

@frederik.dyrmose

You can try the following code.

df, err = ek.get_data(["CHRH.CO",".OMXC25CAP",".OMXCCAPGI"],
                      ['GRAVG(TR.PriceClose,universe="univ",SDate=2018-08-17, EDate=2021-08-17, Frq=WD, Curn=DKK)',                       
                       'TR.PriceClose.Date',
                       'TR.PriceClose'], 
                      {"SDate": "2018-08-17", "EDate": "2021-08-17", "Frq": "WD", "RH": "calculate", "SORTA": "calculate", "NULL": "blank", "Curn": "DKK"})
df

It returns the following data frame.

There is no date returned for GRAVG so we need to reformat the dataframe.

The code looks like this:

gravg = df[df["Price Close"].isna()]
df_test = df[df["Price Close"].notna()].drop(df.columns[1], axis=1)
gravg["Instrument"] = "GRAVG"
gravg["Date"]=list(df_test.iloc[0:int(len(df_test)/3),]["Date"])  
gravg = gravg.drop(gravg.columns[3], axis=1)
gravg.rename(columns={gravg.columns.values[1]: 'Price Close'}, inplace=True)
df_total = df_test.append(gravg[['Instrument','Date', 'Price Close']])
df_total.drop_duplicates().pivot(index='Date', columns='Instrument', values='Price Close')

The output is:

However, this method is prone to error.

Therefore, I would like to propose another solution by getting the TR.PriceClose and TR.PriceClose.Date from Eikon Data API and then use Python to calculate the group average. The code is:

df1, err = ek.get_data(["CHRH.CO",".OMXC25CAP",".OMXCCAPGI"],
                      ['TR.PriceClose.Date',
                       'TR.PriceClose'], 
                      {"SDate": "2018-08-17", "EDate": "2021-08-17", "Frq": "WD", "RH": "calculate", "SORTA": "calculate", "NULL": "blank", "Curn": "DKK"})


df1 = df1.drop_duplicates().pivot(index='Date', columns='Instrument', values='Price Close')
col = df1.iloc[: , 0:3]
df1['GRAVG'] = col.mean(axis=1)
df1

All comments

Jirapongse

@frederik.dyrmose

You can try the following code.

df, err = ek.get_data(["CHRH.CO",".OMXC25CAP",".OMXCCAPGI"],
                      ['GRAVG(TR.PriceClose,universe="univ",SDate=2018-08-17, EDate=2021-08-17, Frq=WD, Curn=DKK)',                       
                       'TR.PriceClose.Date',
                       'TR.PriceClose'], 
                      {"SDate": "2018-08-17", "EDate": "2021-08-17", "Frq": "WD", "RH": "calculate", "SORTA": "calculate", "NULL": "blank", "Curn": "DKK"})
df

It returns the following data frame.

There is no date returned for GRAVG so we need to reformat the dataframe.

The code looks like this:

gravg = df[df["Price Close"].isna()]
df_test = df[df["Price Close"].notna()].drop(df.columns[1], axis=1)
gravg["Instrument"] = "GRAVG"
gravg["Date"]=list(df_test.iloc[0:int(len(df_test)/3),]["Date"])  
gravg = gravg.drop(gravg.columns[3], axis=1)
gravg.rename(columns={gravg.columns.values[1]: 'Price Close'}, inplace=True)
df_total = df_test.append(gravg[['Instrument','Date', 'Price Close']])
df_total.drop_duplicates().pivot(index='Date', columns='Instrument', values='Price Close')

The output is:

However, this method is prone to error.

Therefore, I would like to propose another solution by getting the TR.PriceClose and TR.PriceClose.Date from Eikon Data API and then use Python to calculate the group average. The code is:

df1, err = ek.get_data(["CHRH.CO",".OMXC25CAP",".OMXCCAPGI"],
                      ['TR.PriceClose.Date',
                       'TR.PriceClose'], 
                      {"SDate": "2018-08-17", "EDate": "2021-08-17", "Frq": "WD", "RH": "calculate", "SORTA": "calculate", "NULL": "blank", "Curn": "DKK"})


df1 = df1.drop_duplicates().pivot(index='Date', columns='Instrument', values='Price Close')
col = df1.iloc[: , 0:3]
df1['GRAVG'] = col.mean(axis=1)
df1

frederik.dyrmose

Hello @Jirapongse, thank you for the response.

The goal was to build GRAVG into an internal framework for report generating using your Eikon Data, however, it's unfortunate that your Python API don't work similarly to your Excel as otherwise was the impression I was given.

In Excel GRAVG provides dates, but not in Python, so the group average function is obsolete if I have to implement a specific sub-routine to handle the matter.

The reason this is a large problem is that I cannot tell the given user that the results will be consistent (that the TR function they used previously in Excel will provide the same results), because only some of your Excel functions works properly in Python, and then one would have to make exceptions in the Python code for all of your badly incorporated functions in Python.

Do you have a list of not well-incorporated functions, so I can make exceptions for these from the get-go instead of having report to me and say they want an exception when they face an error?

Jirapongse

@frederik.dyrmose

Eikon Data API can be used to retrieve the same content as the TR function in Eikon Excel. However, the output may be different.

Eikon Data API retrieves the JSON data from Eikon then converts the data to the data frame. Sometimes, we need to transform the data frame to make the output to be similar to the output from Eikon Excel.

Moreover, when requesting the time-series data via the get_data method, we need to specify the date fields, such as TR.PriceClose.Date, to retrieve the associated Date field. For now, it seems that we are unable to get the Date field for the GRAVG expression.

I will raise your concerns to the product team.

finance

@Jirapongse I am having the same problem with GRSUM and would like to reiterate the need for the Date filed on aggregated functions. Perhaps this will help push it up the priority list.

zoya faberov

Hi @frederik.dyrmose @finance @Jirapongse ,

I would like to reference a previous discussion thread - How do I server-side aggregate data with Python API as I think it is related to this discussion.

finance

Server-side aggregation is indeed possible and the answer is in the link that you provided. However...when I tried this solution (for GRSUM), the server ultimately could not handle the request and the query was timing out and giving other errors after a few successful runs, I think because of too much data. Pulling individual data points and then summing locally was the only thing that worked. I used a pandas pivot_table function which works quite well. An example is below. You will need to import both pandas and numpy, represented by pd and np below, respectively.

params = {'SDate': dtstart, 'EDate': dtend, 'Frq': 'C', 'Scale': 6, 'Curn': 'USD'}
fields = [ek.TR_Field('TR.CompanyMarketCap.CalcDate', params, 'desc', 0),
          ek.TR_Field('TR.CompanyMarketCap', params)]
dfm, err = ek.get_data(ricpass, fields)
print(dfm)
tbl = pd.pivot_table(dfm, values='Company Market Cap', index='Calc Date', columns='Instrument', aggfunc=np.sum)

EXPLORE OUR SITES