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

Overview |  Quickstart |  Documentation |  Downloads |  Tutorials |  Articles

question

Upvotes
Accepted
finance avatar image
2 ●0 ●0 ●2

How do I server-side aggregate data with Python API?

I am looking to pull the sum of market caps for a number of RIC's per day for a date range. I saw another post discussing a Pandas solution that pulls market cap for each RIC and date and then groups the data locally. Given the amount of data that I am looking for, this will be a slow process and I might hit data limits. For this reason, I am looking to aggregate the data on the server side.

The Eikon Excel expression builder offers the function below. I am not sure if this function is passed to the server or if it is handled locally.

=@TR($H$5:$H$9,"GRSUM(TR.CompanyMarketCap(Scale=6),universe=""univ"",SDate=2000-06-15 EDate=2021-06-15 Frq=C Curn=USD)","SDate=2000-06-15 EDate=2021-06-15 Frq=C Curn=USD RH=calcdate SORTD=calcdate")

I wrote the following code to pull individual market caps by RIC and date, which I could (but would rather not) process with Pandas:

import eikon as ek


dtstart='20000615'

dtend='20210615'

ric=[a whole bunch of RIC's passed in via csv file]


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(ric, fields)


Is there a way to modify this with a GRSUM (Excel expression builder) equivalent?

eikoneikon-data-apiworkspaceworkspace-data-apirefinitiv-dataplatform-eikon
icon clock
10 |1500

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

1 Answer

Upvote
Accepted
pierre.faurel avatar image
4.3k ●2 ●4 ●5

Hi @finance ,

A similar question was posted few days ago: https://community.developers.refinitiv.com/questions/82854/i-cannot-replicate-this-specific-formula-in-python.html

The result is matching with EXCEL but order is inverted and date isn't in the DataFrame.

 =@TR($A$1:$A$3,"GRSUM(TR.CompanyMarketCap(Scale=6),universe=""univ"",SDate=2000-06-15 EDate=2021-06-15 Frq=C Curn=USD)","SDate=2000-06-15 EDate=2021-06-15 Frq=C Curn=USD RH=calcdate SORTD=calcdate")

TR result:

RENA.PA    15/06/2021    4122228.178969
AAPL.O     14/06/2021    4147983.034847
MSFT.O     13/06/2021    4080697.767199
           12/06/2021    4080697.767199
             ...             ...
           20/06/2000    421305.575404
           19/06/2000    414223.771335
           18/06/2000    407333.721552
           17/06/2000    407333.721552
           16/06/2000    407333.721552
           15/06/2000    406617.755897

The mapping in Python is:

    rics = ["RENA.PA", "AAPL.O", "MSFT.O"]
    df, err = ek.get_data(
        rics,
        ['GRSUM(TR.CompanyMarketCap(Scale=6),universe="univ", SDate=2000-06-15, EDate=2021-06-15, Frq=C, Curn=USD)'],
        {"SDate": "2000-06-15", "EDate": "2021-06-15", "Frq": "C", "Curn":"USD", "RH": "calcdate", "SORTD": "calcdate"}
    )
    print(df)
            Instrument      GRSUM(TR.CompanyMarketCap(Scale=6),universe="univ", SDate=2000-06-15, EDate=2021-06-15, Frq=C, Curn=USD)
0           n/a             406617.755897                                                        1           n/a             407333.721552                                                        2           n/a             407333.721552                                                        3           n/a             407333.721552                                                        4           n/a             414223.771335                                                        ...         ...             ...                                                        7666        n/a             4080697.767199                                                        7667        n/a             4080697.767199                                                        7668        n/a             4080697.767199                                                        7669        n/a             4147983.034847                                                        7670        n/a             4122228.178969                                                         [7671 rows x 2 columns]

From this point, you need to remove Instrument column, change the index with the range of dates and invert the order.

icon clock
10 |1500

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

Click below to post an Idea Post Idea