@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

Hello @jirapongse.phuriphanvichai, 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?

@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.

Hi @frederik.dyrmose @finance @jirapongse.phuriphanvichai ,

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.