0
$\begingroup$

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.

Improve this question
$\endgroup$
2
  • 1
    $\begingroup$ 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. $\endgroup$ – assylias Oct 13 '20 at 14:35
  • $\begingroup$ 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. $\endgroup$ – Friedrich Oct 13 '20 at 14:37
1
$\begingroup$

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.

Improve this answer
$\endgroup$
2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.