Upgrade from Eikon -> Workspace. Learn about programming differences.

For a deeper look into our Eikon Data API, look into:

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
16 1 1 3

Replicate GRAVG function (Group Average in TR) in Python

Hello, i have this formula in Excel:

=@TR("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.

eikoneikon-data-apipythonrefinitiv-dataplatform-eikonworkspaceworkspace-data-api
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
Accepted
78.1k 246 52 72

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

1629451295061.png

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:

1629451555602.png

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



1629451295061.png (34.4 KiB)
1629451555602.png (33.6 KiB)
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvote
16 1 1 3

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?

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Upvotes
78.1k 246 52 72

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

icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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

Upvotes
32.2k 40 11 19
icon clock
10 |1500

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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)

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.