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.
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:35Application.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