download historical index component weights in Bloomberg

[apine]

we had a problem with another dll where we couldnt transfer more than 255 characters. is this a generic dll issue with excel?

[golftango]

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

[sammyzee]

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]

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]

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

[dansmo]

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.

[macrotrader]

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

[cordura21]

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.



[url=/User%20Files/5921/Bloomberg%20Index%20Retriever.xls]Attached File: Bloomberg Index Retriever.xls[/url]



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.

[pj]

Thank you, Cord! Beer

[bundy_al01]

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, "daxmemb")

    Call DDEExecute(Blp, "")



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