Forums  > Software  > download historical index component weights in Bloomberg  
     
Page 1 of 2Goto to page: [1], 2 Next
Display using:  

golftango


Total Posts: 33
Joined: Aug 2004
 
Posted: 2008-08-07 18:15

Index components and weights can be seen in Bloomberg (ex : SX5E Index WGT <GO>), even for past dates (Edit menu -> Change Date)

But I can't find how to download these data in Excel or using the API. Has anyone already done it?

ThanX !


"Price is what you pay. Value is what you get." (Warren Buffet)

FDAXHunter
Founding Member

Total Posts: 8333
Joined: Mar 2004
 
Posted: 2008-08-07 18:19
I hate Bloomberg so I can't help you. But in case it's not possible, Stoxx has this data available. For a fee, of course. (Although I suspect you're looking at more than just the EuroStoxx 50)

The Figs Protocol.

golftango


Total Posts: 33
Joined: Aug 2004
 
Posted: 2008-08-07 18:24

Off course I looked at stoxx.com. But past data are available on a daily basis only on the last 2 calendar months.

And off course I am looking at more than SX5E, and more than 2 months...


"Price is what you pay. Value is what you get." (Warren Buffet)

FDAXHunter
Founding Member

Total Posts: 8333
Joined: Mar 2004
 
Posted: 2008-08-07 18:54
Ha! Welcome to the world of index calculations. My experience has been that to get anything decent, you have to take the raw data (i.e. raw prices, divs and multipliers/divisors) and... roll your own. It's a fun project... for the 20 or so largest indices in the world Smiley And it costs a reasonable amount of money too.

Then again, maybe that WGT thing on Bloomberg can do the trick. If one trusts that thing enough.



The Figs Protocol.

signalseeker


Total Posts: 236
Joined: Oct 2006
 
Posted: 2008-08-07 19:13
As FDAX already alluded to, this is very tedious work. BBG is definitely not the best source of this data but I can't see why you can't download this data. Just do Output Results To - Excel. Use FPRP to see what data fields are exposed by the api. Good luck. Hell is getting data out of bloomberg.

The dark is light enough.

urnash


Total Posts: 558
Joined: Sep 2006
 
Posted: 2008-08-08 08:36

A quick look at an old Excel sheet showed that you should be able to do something like

=BLPB("sx5e index","indx mweight hist", "end dt",20051001,50,2)

20051001 is the date, 50 is the number of stocks in the index, and I have no idea what the 2 means Smiley

HTH


Assuming normality means never having to say you don’t have enough data. -- J. Michael Steele

Veegan


Total Posts: 684
Joined: Jun 2004
 
Posted: 2008-08-08 11:15

You can use:

=BDS("SX5E Index", "INDX_MWEIGHT_HIST", "aggregate=n", "end dt", 20051001,"cols=2;rows=50")

The 'aggregate = y' argument is supposed to return a string in a single cell containing all the data.

However, when I attempt to aggregate in this way BBMG returns only part of the data from the full array. Does anyone know how to return the full length string containing the data for all 50 members of the Eurostoxx 50, say?

V.


"The Stranger within my gates, He may be evil or good, But I cannot tell what powers control-- What reasons sway his mood; Nor when the Gods of his far-off land Shall repossess his blood." ~ Kipling

golftango


Total Posts: 33
Joined: Aug 2004
 
Posted: 2008-08-08 11:46

Thanks to everybody for your help !

However, when I attempt to aggregate in this way BBMG returns only part of the data from the full array. Does anyone know how to return the full length string containing the data for all 50 members of the Eurostoxx 50, say?

Excel has a limit of 255 characters in any cell. Annoying, isn't it ? Head against Wall

 


"Price is what you pay. Value is what you get." (Warren Buffet)

FDAXHunter
Founding Member

Total Posts: 8333
Joined: Mar 2004
 
Posted: 2008-08-08 11:57
255 characters per cell? Really? This is not the case for Excel for Mac 2004 and Excel 2007 for Windows. But even the older ones had more capacity than that (1024?). I think the restriction might come from the Bloomberg API itself?

The Figs Protocol.

Cheng


Total Posts: 2802
Joined: Feb 2005
 
Posted: 2008-08-08 12:20
My Excel 2003 starts puking at 12227 characters per cell. Dunno what you did, golf, but something is clearly wrong on your side.

"Everyday when the knife in my back starts to twinge 'n' turn / My eyes are catching fire and my heart starts to burn / A foot away from you is like being closer to heaven / Then again it's like being needled 24/7"

apine


Total Posts: 1008
Joined: Jun 2004
 
Posted: 2008-08-08 16:46
we had a problem with another dll where we couldnt transfer more than 255 characters. is this a generic dll issue with excel?

Too many people make decisions based on outcomes rather than process. -- Paul DePodesta

golftango


Total Posts: 33
Joined: Aug 2004
 
Posted: 2008-08-10 15:20

I think you are right : the 255 char limits seems to be a limit in Bloomberg dll (and maybe other dlls).

On my Vista PC with Excel 2003 the limit with formulas or VBA functions is 32767 characters.

Anyway, Excel (like other Micro$oft products) has built-in limits which are not defined by "natural" reasons (like amount of available memory).


"Price is what you pay. Value is what you get." (Warren Buffet)

sammyzee


Total Posts: 79
Joined: Jan 2008
 
Posted: 2008-08-10 15:54
If I remember correctly, the 255 character thing is a limitation of the XLL interface. Apparantly it's resolved in Excel 2007 (as well as the limit on the number of arguments)

flip


Total Posts: 21
Joined: Apr 2007
 
Posted: 2008-08-19 20:26
I don't know whether this is relevant to you, but we had a related problem these days.

We wanted to get all historical index constituents of the s&p500 index since 1990.

Historical index components of the sp500 are available on the s&p
homepage back to 2000, in bloomberg back to 1990 (a list with the
500 stocks of each single day).

The index additions and deletions are part of the report which includes
historical weight changes and is nearly 2000 bloomberg pages long,
but cannot be downloaded (we also asked the helpdesk which
confirmed that)

So what we did then was to write a matlab function to retrieve the
index members of each single day since 1990 (directly into matlab)
which compares the members of each day to deduce the additions and
deletions from this list.

This way we were able to get a list with each security in the s&p500
since 1990 and the corresponding date in the index (first day in index, last
day in index)

But I'm not sure whether this is also possible for weight changes (and for eurostoxx data in general)

HLCYG


Total Posts: 139
Joined: Dec 2004
 
Posted: 2008-08-20 21:48
It's a lot of work, but some data is in Bloomberg; if you use MEMB you can select 'Display'-> 'Index member changes'; select a period and generate report. Combine this with hist. price data and you get a relatively quick solution for an index like the AEX;
For an index like the SX5E you can do the same excersise but then its more work - you'll have to know the historical divisors etc.

IMAN answers some questions; Index ticker HMOV might be usefull as well.

On the problem of not being able to download/export - you can copy paste, or use MEMB and change the date manually and export the results to excel, but that's not really I solution I like. HTH

‘Scheppend nihilisme, agressief medelijden, totale misantropie’

dansmo


Total Posts: 119
Joined: Aug 2008
 
Posted: 2008-08-26 15:06

I did it this way:

 

Cell C10: SPX INDEX

Cell C11: indx mweight hist

C 12: end dt

C 13: date in YYYYDDMM

in D13: =BDS(C10;C11;C12;C13;"cols=2;rows=500")

This will import index members and their weightings.


I didn´t choose to trade - trading chose me

macrotrader


Total Posts: 353
Joined: May 2009
 
Posted: 2010-04-14 20:00
In case anyone wants to try this with Thomson Datastream.

http://extranet.datastream.com/news_events/newweb/JulyAug_2006/Infostream_julaug06.pdf

"
Thomson Financial has released four additional index constituent list datatypes to complement the existing coverage of constituent
weights (WTIDX), number of shares (NSIDX) and free-float factors (FFIDX) provided for the S&P/TSX Canadian constituent lists on
Datastream. These additional datatypes more importantly enable the user to see stored data for free-float number of shares on datatype
FNSIDX (previously stored on NSIDX), and total number of shares stored on NSIDX.
Currently, the following datatypes are available:
WTIDX – weights adjusted for free float
FFIDX – free-float factor of shares available for trading
NSIDX – the number of shares adjusted for free float used to calculate the market values and weights
These changes now allow for the storage of both the total and adjusted number of shares data.
New datatypes are as follows:
FNSIDX – adjusted number of shares
PIDX – closing stock price
MVIDX – total market value
FMVIDX – adjusted market value
NSIDX – total number of shares
This become effective at March month-end. This means that the month-end history pre-March 2007 on datatype NSIDX is adjusted for
free float, and from March 2007 is the full number of shares.
Daily constituent lists are provided, and monthly historical lists with the above new datatypes are stored from March 2007 onwards and
can be accessed using the form:
Index list mnemonic + mmyy
For example: LTTOCOMP0307 returns the March 2007 month-end constituents for the S&P/TSX Canadian Composite index.
History for the existing datatypes: constituent weights (WTIDX), number of shares (NSIDX) and free-float factors (FFIDX) is stored from November 2003."

"Risk comes from not knowing what you're doing" Warren Buffett

cordura21


Total Posts: 228
Joined: Aug 2009
 
Posted: 2010-08-19 17:56
Thanks to everybody who participated in this thread, it saved me a lot of time.
I am attaching an Excel spreadsheet that will retrieve historical components of any Bloomberg index that you have access to, and it will cycle and accumulate thru different dates. I made it using what I read in this thread. Cheers, Cord.

Attached File: Bloomberg Index Retriever.xls

EDIT: I will make a version that will also get non historical Bloomberg fields (like "BDP("AAPL Equity","NAME") since some old names are just numbers and they are hard to understand. But for the time being you can add it yourself on the historical results.

Vespertilio homo est cientificus

pj


Total Posts: 3305
Joined: Jun 2004
 
Posted: 2010-08-19 18:25
Thank you, Cord! Beer

вакансия "Программист Психологической службы" -але! у нас ошибко! не работает бля-бля-бля -вы хотите об этом поговорить?

bundy_al01


Total Posts: 1
Joined: Aug 2010
 
Posted: 2010-08-24 19:14

Hi,

in your spreadsheets, the weightings are in %, is there a way to get the number of shares in the index instead ?

basically, i tried to retreive those by using the followng code :

    Blp = DDEInitiate("Winblp", "bbk")
  
    Call DDEExecute(Blp, "<blp-2>dax<index>memb<go>")
    Call DDEExecute(Blp, "<tabr><tabr><tabr><tabr>")

but then i get stuck because i don't know how to simulate "the down arrow" so that i can choose the output excel option

Thank you.

Al


One won't buy the cow if one can get the milk for free

cordura21


Total Posts: 228
Joined: Aug 2009
 
Posted: 2010-08-25 15:56
The spreadsheet I made just downloads what your formula tells it to. In this case it's downloading a field called "INDX_MWEIGHT_HIST". You can change that to any field you want as long as it's there, overridable and historical.

I don't know of a field that will download the index shares, but if you find out about it please post it here. Cheers, Cord

Vespertilio homo est cientificus

NikEy


Total Posts: 76
Joined: Mar 2008
 
Posted: 2010-08-25 16:04
enjoy




edit: how the heck were you able to post "<" and ">" without the forum stripping the tags?

interrane


Total Posts: 1
Joined: Oct 2015
 
Posted: 2015-10-14 05:21
Hi Flip, I know this was posted 7 years ago but I was wondering if you still had that matlab function that you wrote? I am working on something similar and have been finding it really difficult to get index constituents

Nivrat


Total Posts: 1
Joined: Jun 2016
 
Posted: 2016-06-02 10:37
I am after the historical components (quarterly) of the Eurostoxx 50 for the last 10 years. Weights would also be useful. This is an excel sheet of 40x50 or 80x50 with weights. Has anyone got this data that could send me or sell me? Many thanks

contango_and_cash


Total Posts: 57
Joined: Sep 2015
 
Posted: 2016-06-06 22:43
FWIW the solution posted below should still work but i believe the screen is actually {MEMB} or {MEMBS}. They have made it quite easy now to extract given some manipulation.
Previous Thread :: Next Thread 
Page 1 of 2Goto to page: [1], 2 Next