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.
Best Answer
-
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"})
dfIt 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)
df10
Answers
-
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?
1 -
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.
0 -
@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.
0 -
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.
0 -
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)0
Categories
- All Categories
- 6 AHS
- 37 Alpha
- 161 App Studio
- 4 Block Chain
- 4 Bot Platform
- 16 Connected Risk APIs
- 47 Data Fusion
- 30 Data Model Discovery
- 608 Datastream
- 1.3K DSS
- 577 Eikon COM
- 4.9K Eikon Data APIs
- 7 Electronic Trading
- Generic FIX
- 7 Local Bank Node API
- Trading API
- 2.7K Elektron
- 1.3K EMA
- 236 ETA
- 519 WebSocket API
- 33 FX Venues
- 10 FX Market Data
- 1 FX Post Trade
- 1 FX Trading - Matching
- 12 FX Trading – RFQ Maker
- 5 Intelligent Tagging
- 2 Legal One
- 20 Messenger Bot
- 2 Messenger Side by Side
- 9 ONESOURCE
- 7 Indirect Tax
- 59 Open Calais
- 264 Open PermID
- 39 Entity Search
- 2 Org ID
- PAM
- PAM - Logging
- 8.4K Private Comments
- 6 Product Insight
- Project Tracking
- ProView
- ProView Internal
- 20 RDMS
- 1.4K Refinitiv Data Platform
- 367 Refinitiv Data Platform Libraries
- 3 Refinitiv Due Diligence
- LSEG Due Diligence Portal API
- 3 Refinitiv Due Dilligence Centre
- Rose's Space
- 1.1K Screening
- 18 Qual-ID API
- 13 Screening Deployed
- 23 Screening Online
- 10 World-Check Customer Risk Screener
- 990 World-Check One
- 44 World-Check One Zero Footprint
- 45 Side by Side Integration API
- Test Space
- 3 Thomson One Smart
- 1.2K TR Internal
- Global Hackathon 2015
- 2 Specialists Who Code
- 10 TR Knowledge Graph
- 150 Transactions
- 142 REDI API
- 1.7K TREP APIs
- 4 CAT
- 21 DACS Station
- 117 Open DACS
- 1.1K RFA
- 103 UPA
- 172 TREP Infrastructure
- 224 TRKD
- 886 TRTH
- 5 Velocity Analytics
- 5 Wealth Management Web Services
- 59 Workspace SDK
- 9 Element Framework
- 5 Grid
- 13 World-Check Data File
- Yield Book Analytics
- 46 中文论坛