Annual Average Market Capitalisation From Datastream

I was looking to get a time series of the average annual market cap of the STI (Straits Time Index) companies for the past ten years. As of June 30 every year from 2011, I wanted to get the average annual market cap for each constituent. The Refinitiv Content Team have shared this formula.

=@TR($A$1,"AVG(TR.CompanyMarketCap(Scale=6))","Frq=D SDate=#1 EDate=#2",,$B13,$B14)

SDate and EDates are each year's start dates and end dates (Jul 1 of the previous year and Jun 30 current year, respectively). Is there an effective way of getting this information via the Datastream python API? I tried to avoid looping each year and used the DataStream MAV/AVG function. I got myself tangled up and reaching out for some help! :-)

I've tried

ds.get_data(tickers='ticker', fields=['MAV#(X(MV),-1Y)'], start='2011-06-30', end='2021-06-30', freq='Y')

Which unfortunately doesn't work. If the freq = 'D', I get the moving average, but for every day! I was only hoping to get the ten records for each year.

Tagged:

Best Answer

  • Jirapongse
    Jirapongse ✭✭✭✭✭
    Answer ✓

    @rajanraju

    You can use multiple fields. For example:

    df = ds.get_data('LSNGPORI|L',['AVG#(X(MV)~U$,1/1/2021,31/12/2021)',
                              'AVG#(X(MV)~U$,1/1/2020,31/12/2020)',
                              'AVG#(X(MV)~U$,1/1/2019,31/12/2019)',
                              'AVG#(X(MV)~U$,1/1/2018,31/12/2018)',],kind=0)

    The output after pivot is:

    1646025010267.png

Answers