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.

• Best option is to request the data using the COM addin from VBA directly, you may find examples or libraries online. Alternatively you can try using Application.onTime to check at regular intervals if the data has been populated and continue your program once it's the case. Oct 13 '20 at 14:35
• I saw the Application.onTime method in the reddit link. I literally copied the routine and call but it did bring any improvements. I will take a look at the COM addin. Oct 13 '20 at 14:37

1 Answer

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.

• I ended up using this approach mikejuniperhill.blogspot.com/2013/06/… Oct 15 '20 at 15:01
• I used this one too, it works well. Oct 15 '20 at 15:52