I’m trying to get data from an index on Bloomberg terminal into excel with BDH command. Specifically I am trying to get all of the closing prices for each company in the Russell 2000 index for every day for 10 years. I’ve managed to export all of the company names into excel (all of the members of Russell 2000) and using =BDH(cell, “px_last”, “8/1/2006”, “8/1/2016”) I can even get the prices that I want for each company. The problem is that this formula generates two columns of data, one column or dates and another column of prices, one price for each date. Because there are two columns I can’t just use excel’s drag formula function to automatically run this for every one of the 2000 companies (If I drag it over, the new column or dates overwrites the previous column of prices). I don’t want to paste this in 2000 times by hand. Does anyone know of a code that, instead of generating two columns like this, one of dates and one of prices, would only return the prices column? That way I can drag the formula over. Also am happy to do this in Rstudio if anyone has code for that instead.
$\begingroup$
$\endgroup$
3
-
1$\begingroup$ I do not recommend working only with prices (no dates). You would not be able to tell if the dates are the same for different stocks or if there are mismatches (due for example for the price of one of the stocks not being available on a certain date). $\endgroup$– nbbo2Commented Jul 7, 2022 at 15:26
-
$\begingroup$ Bloomberg data license? $\endgroup$– Paul BrennanCommented Jul 7, 2022 at 16:03
-
1$\begingroup$ Best to ask F1F1 (help desk). They will not help with R (in fact, R is not even supported officially on WAPI but only a 3rd party wrapper is available). It makes no difference though as it will neither give you a different API nor different data. @nbbo2's point is spot on. There are ways to force the output to fill in missing data etc but you will need to decide what you need yourself (help desk can help with overrides). In BDH, using dts=h will do the trick, but give you all the problems that will arise with this. $\endgroup$– AKdemyCommented Jul 7, 2022 at 19:40
Add a comment
|
2 Answers
$\begingroup$
$\endgroup$
You can add "dates=H" to hide the dates, but you should then also specify which dates to include so that each query returns the exact same date set.
For example, to see all workdays, and fill the days with no price with "n.a.":
=BDH(cell, "px_last", 20060108, 20160108, "dates=H,days=W,fill=n.a.")
You can see more options in the help of the function in Excel.
$\begingroup$
$\endgroup$
1
Maybe you'll need to create a macro to set the 2000 tickers and formulas in each column. Then youll have to wait for it to query bbg. Then copy paste values in a different sheet and with another macro (unless you want to do it one by one) consolidate all the data to just one column with the dates.
-
$\begingroup$ That is overkill. All you need is to add dts=h to the BDH query - the overrides allow different ways to handle missing data too (or force to certain calendars etc). $\endgroup$– AKdemyCommented Jul 8, 2022 at 8:36