Handling Asynchronous Bloomberg BDH Data in Excel Macros
Summary
The document describes a timing problem when an Excel VBA macro requests historical prices with Bloomberg’s BDH function. Because the data request completes asynchronously, a macro that moves on too quickly may capture only one value or encounter a pending-data message instead of the requested time series. The author reports that worksheet refresh calls and attempts to add waits did not reliably resolve the issue, and that concurrent requests across worksheets could reproduce the failure.
The accepted response recommends considering a synchronous COM wrapper or newer Bloomberg query tools. For workflows that retain BDH, it outlines polling the relevant range for the pending-data marker, refreshing and waiting briefly while it remains, and proceeding after it disappears. This is a practical data-retrieval workaround, not a trading method. The document gives no benchmark or reliability comparison, and polling still depends on detecting the correct range and interpreting request errors appropriately.
Key ideas
- Bloomberg BDH requests may still be processing after a VBA macro writes the formula.
- A macro can therefore read incomplete output or leave pending-data markers in cells.
- The response suggests synchronous interfaces or newer Bloomberg query tools as alternatives.
- For BDH, polling for pending requests and refreshing between checks can help establish readiness.
Tags
Full text
# Populating price data from Bloomberg in Excel with BDH via macro not returning time series
# Populating price data from Bloomberg in Excel with BDH via macro not returning time series
Though issue has been addressed before on stackoverflow and reddit, I was not able to find any useful answers.
I'm running a macro to populate price data with a macro via excel bloomberg API, say
```
=@BDH("AAPL US EQUITY"; "PX_LAST"; "ED-5AY"; BToday())
```
and
```
=@BDH("AAPL US EQUITY"; "PX_LAST"; "ED-3AY"; BToday())
```
My problem is that my macro is too fast and sometimes it does not return the proper time series. Sometimes it returns just one value in a cell or it will get stuck on `#N/A Requesting Data...`. I have been trying everything from trying to utilize `Application.Run "RefreshEntireWorksheet"` and `Application.Run "RefreshAllStaticData"` as well as all kinds of exotic waiting functions.
I could not figure out why this happens nor find a satisfying solution which would let me loop through all my securities and desired time frames. I found out that it seems to work better, if the function already knows the `cols` and `rows`, which can also be specified in `BDH`. But I am quite lost on this. So any advice on how to solve this annoying problem is much appreciated. You can use the macro I wrote.
```
Sub example()
Dim startRange As Range
Set startRange = Range("A1")
startRange.Formula = "=@BDH(" & Chr(34) & "AAPL US EQUITY" & Chr(34) & _
", " & Chr(34) & "PX_LAST" & Chr(34) & ", " & Chr(34) & "ED-5AY" & _
Chr(34) & ", BToday())"
startRange.Offset(0, 2).Formula = "=@BDH(" & Chr(34) & "AAPL US EQUITY" & Chr(34) & _
", " & Chr(34) & "PX_LAST" & Chr(34) & ", " & Chr(34) & "ED-5AY" & _
Chr(34) & ", BToday())"
End Sub
```
I was able to replicate my problem, by running my macro in two different worksheets within the same workbook.
## Answer by Dimitri Vulis (score 2, accepted)
https://quant.stackexchange.com/a/58647
It would be better not to use (old) BDP/BDH, but to use either the COM wrapper (which can be synchronous) or the new BQL or BQL.query calls (HELP BQLX). Some time ago I wrote a a few lines of code (similar to https://stackoverflow.com/questions/33661436/ ) to get synchronous behavior from BDP/BDH. In VBA, call Excel to search the range for value "#N/A Requesting Data". If something is still found, then refresh the calls, sleep 1/2 second and search again. else (if the search fails, then) all your data is ready.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.