Aligning Bloomberg Historical Price Queries Across Securities
Summary
The document addresses exporting daily closing prices for many Russell 2000 constituents from Bloomberg into Excel. A historical data query returns dates alongside prices, which can interfere with copying formulas across adjacent columns. One response suggests hiding the date column and explicitly setting the date schedule and missing-value behavior so each security query returns a consistent set of observations. Another response proposes using macros to populate ticker formulas, wait for data retrieval, copy values, and consolidate the results.
The key practical lesson is that hiding dates is safe only when the requested series share an identical date index; otherwise, values can be misaligned across securities. The example specifies workdays and a placeholder for missing prices, but the discussion does not validate the approach, address Bloomberg limits or licensing, or provide a complete macro. It is a narrow data-extraction workflow, not an investment analysis.
Key ideas
- Bloomberg historical queries can return both dates and price values.
- Hiding dates can make adjacent price columns easier to populate in Excel.
- Use a consistent date schedule and missing-value convention to reduce cross-security misalignment.
- Macros can automate ticker formulas, retrieval, and consolidation, though no complete implementation is given.
Tags
Full text
# How do you extract only prices for an index from Bloomberg? # How do you extract only prices for an index from Bloomberg? 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. ## Answer by assylias (score 2) https://quant.stackexchange.com/a/71533 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. ## Answer by Aldo Shumway (score 1) https://quant.stackexchange.com/a/71529 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.
Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.