|
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: 8335 |
| Joined: Mar 2004 |
| |
|
| 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. |
|
 |
|
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: 8335 |
| Joined: Mar 2004 |
| |
|
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  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. |
|
|
|
| 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 |
| |
|
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
HTH |
Assuming normality means never having to say you don’t have enough data. -- J. Michael Steele |
|
|
Veegan
|
|
| Total Posts: 684 |
| Joined: Jun 2004 |
| |
|
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 |
|
|
|
|
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 ? 
|
"Price is what you pay. Value is what you get." (Warren Buffet) |
|
|
FDAXHunter
|
| Founding Member |
| Total Posts: 8335 |
| Joined: Mar 2004 |
| |
|
| 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: 2818 |
| Joined: Feb 2005 |
| |
|
| 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 |
| |
|
| 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 |
|
|
|
|
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 |
| |
|
| 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 |
| |
|
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 |
| |
|
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 |
| |
|
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 |
|
|
|
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 |
|
|
|
|
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: 3317 |
| Joined: Jun 2004 |
| |
|
Thank you, Cord!  |
вакансия "Программист Психологической службы"
-але! у нас ошибко! не работает бля-бля-бля
-вы хотите об этом поговорить? |
|
|
|
|
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 |
|
|
|
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 |
| |
|
enjoy
edit: how the heck were you able to post "<" and ">" without the forum stripping the tags? |
|
|
|
|
| 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 |
| |
|
| 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 |
|
|
|
|
| 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. |
|
|
|
|
