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