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
finance avatar image
2 ●0 ●0 ●3

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

Jirapongse avatar image   Jirapongse

@finance

Hi,

Thank you for your participation in the forum.

Is the reply below satisfactory in answering your question?

If yes please click the 'Accept' text next to the reply. This will guide all community members who have a similar question.

Otherwise please post again offering further insight into your question.

Thanks,

AHS

1 Answer

· Write an 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 512.0 KiB each and 1.0 MiB total.

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.