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

finance
finance Newcomer
in Eikon Data APIs

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?

Tagged:

Best Answer

  • pf
    pf LSEG
    Answer ✓

    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.

Categories