question

Upvotes
Accepted
rajanraju avatar image
63 ●10 ●17 ●19

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.

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

2 Answers

· Write an Answer
Upvote
Accepted
Jirapongse avatar image
79.2k ●251 ●52 ●74

@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


1646025010267.png (26.3 KiB)
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.

Upvotes
rajanraju avatar image
63 ●10 ●17 ●19

@Jirapongse Many thanks.


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.