Bloomberg data doesn't populate until Excel VBA macro finishes

Question

I'm running a macro in a blank Excel 2007 workbook on a PC with a Bloomberg license. The macro inserts Bloomberg functions into sheet1 that pull yield curve data. Some additional functions' results are dependent on the first functions finishing and correctly displaying the Bberg data. When I step through the program it only displays '#N/A Requesting Data . . .' instead of the results of the query, no matter how slowly I go. Because some of the functions are dependent on string and numeric field results being populated, the program hits a run-time error at that code. When I stop debugging -- fully ending running the program -- all the Bberg values that should have populated then appear. I want these values to appear while the program is still running.

I've tried using a combination of DoEvents and Application.OnTime() to return control to the operating system and to get the program to wait for a long time for the data update, but neither worked. Any ideas would be helpful. My code is below. wb is a global-level workbook and ws1 is a global level worksheet.

Public Sub Run_Me()

    'Application.DisplayAlerts = False
    'Application.ScreenUpdating = False

    Call Populate_Me
    Call Format_Me

    'Application.DisplayAlerts = True
    'Application.ScreenUpdating = True

End Sub

Private Sub Populate_Me()

    Dim lRow_PM As Integer
    Dim xlCalc As XlCalculation

    Set wb = ThisWorkbook
    Set ws1 = wb.Sheets(1)

    'clear out any values from previous day
    If wb.Sheets(ws1.Name).Range("A1").Value <> "" Then
        wb.Sheets(ws1.Name).Select
        Selection.ClearContents
    End If


    xlCalc = Application.Calculation
    Application.Calculation = xlCalculationAutomatic

    Range("A1").Value = "F5"
    Range("B1").Value = "Term"
    Range("C1").Value = "PX LAST"

    Range("A2").Select
    ActiveCell.FormulaR1C1 = "=BDS(""YCCF0005 Index"",""CURVE_MEMBERS"",""cols=1;rows=15"")"
    BloombergUI.RefreshAllStaticData

    Range("B2").Select
    ActiveCell.FormulaR1C1 = "=BDS(""YCCF0005 Index"",""CURVE_TERMS"",""cols=1;rows=15"")"
    BloombergUI.RefreshAllStaticData

    Application.OnTime Now + TimeValue("00:00:10"), "HardCode"

    '******more code*******'
    End Sub

Sub HardCode()

    Range("C2").Select
    ActiveCell.FormulaR1C1 = "=BDP($A2,C$1)"
    BloombergUI.RefreshAllStaticData

End Sub

Solution 1

A way to get around this issue is to put all subs, etc that you want to run after pulling the bloomberg data into a different sub. You must do this each time you call Bloomberg information. If you call another sub in the "master" sub after the Application.OnTime Now +TimeValue("00:00:15"), it will fail- you must put all subs following into a new master sub.

For example: Instead of

Sub Master1()
Application.Run "RefreshAllStaticData"
Application.OnTime Now + TimeValue("00:00:15"), "OtherSub1"
'This will cause the Bloomberg Data to not refresh until OtherSub2 and 3 have run
OtherSub2
OtherSub3
End Sub

It should be

Sub Master1()
Application.Run "RefreshAllStaticData"
Application.OnTime Now + TimeValue("00:00:15"), "Master2"
End Sub

Sub Master2()
OtherSub1
OtherSub2
OtherSub3
End Sub

Hope that helps

Solution 2

I googled for BloombergUI.RefreshAllStaticData and was immediately taken to this Mr Excel page: http://www.mrexcel.com/forum/showthread.php?t=414626

We are not supposed post answers which are only links in case that link disappears and takes the answer with it. However, I am not sure I understand the question or the answer well enough to summarise it.

The Google link will probably exist for the forseeable future.

Within Mr Excel, the chain is: MrExcel Message Board > Question Forums > Excel Questions > Bloomberg links and macros.

The key information appears to be:

On your Bloomberg terminal if you type in WAPI < GO > you will find listings of the Bloomberg API and downloadable examples.

Using the helpfile information in that area we can build a more robust solution to this using the Bloomberg Data Type Library. Go to Tools | References and add a reference to this library. This code can then be used to populate the cells:

Sub Test2()
    Dim vResults, vSecurities, vFields
    Dim objBloomberg As BLP_DATA_CTRLLib.BlpData

    'fill our arrays - must be 1 dimension so we transpose from the worksheet
    With Application.WorksheetFunction
        vSecurities = .Transpose(Sheet1.Range("B2:B4").Value)
        vFields = .Transpose(.Transpose(Range("C1:H1").Value))
    End With

    Set objBloomberg = New BLP_DATA_CTRLLib.BlpData
    objBloomberg.AutoRelease = False

    objBloomberg.Subscribe _
            Security:=vSecurities, _
            cookie:=1, _
            Fields:=vFields, _
            Results:=vResults

    Sheet1.Range("C2:H4").Value = vResults
End Sub

Once you have tried out Mr Excel's solution, perhaps you could update this answer for the benefit of future visitors.

Solution 3

I gathered some information from around the web and wrote what imho is an improved version in comparison with everything I have found so far:

Private WaitStartedAt As Double
Private Const TimeOut As String = "00:02:00"

Public Function BloomCalc(Callback As String) As Boolean
    Dim rngStillToReceive As Range
    Dim StillToReceive As Boolean
    Dim ws As Worksheet
    StillToReceive = False
    If WaitStartedAt = 0 Then
        WaitStartedAt = TimeValue(Now())
    End If
    If TimeValue(Now()) >= WaitStartedAt + TimeValue(TimeOut) Then
        GoTo errTimeOut
    End If
    For Each ws In ActiveWorkbook.Worksheets
        Set rngStillToReceive = ws.UsedRange.Find("*Requesting Data*", LookIn:=xlValues)
        StillToReceive = StillToReceive Or (Not rngStillToReceive Is Nothing)
    Next ws
    If StillToReceive Then
        BloomCalc = False
        Application.OnTime Now + (TimeSerial(0, 0, 1)), Callback
    Else
        WaitStartedAt = 0
        BloomCalc = True
    End If
    Exit Function
errTimeOut:
    Err.Raise -1, , "BloomCalc: Timed Out. Callback = " & Callback
End Function

It should an arbitrary task by calling a sub like DoSomething()

Public Sub DoSomething() 
    DoSomethingCallback
End Function

That calls a "callback" function that will call itself until either the data has been refreshed or the time limit reached

Public Sub AutoRunLcbCallback()
    If BloomCalc("AutoRunLcbCallback") Then
        MsgBox "Here I can do what I need with the refreshed data"
        ' for instance I can close and save the workbook
        ActiveWorkbook.Close True
    End If
End Sub

Any comment is appreciated. A possible improvement might be to allow the workbook and / or worksheet to be an input of the function but I really didn't see the need for that.

Cheers

Solution 4

Hello there I think I have found a solution to this problem and I really want to share this with you guys.

Before starting with the real answer I want to make sure everyone understands how Application.OnTime actually works. And If you already know then you can safely skip to THE SOLUTION below.

Let's make a TOY EXAMLPE example with two subroutines Sub First() and Sub Second() and one variable x that is declared outside, so that it has scope inside the whole Module

Dim x as integer
Sub First()
    x = 3
    Application.OnTime Now + TimeSerial(0, 0, 2), "Sub2"
    x = 2*x
End Sub

Sub Second() 
    x = x + 1
End Sub

I thought that the commands were executed in the following order:

1. x = 3
2. Application.OnTime Now + TimeSerial(0, 0, 2), "Sub2"
3. Then after 2 seconds of wait, in Sub Second() x = x + 1, hence 4
4. Finally we go back to Sub First() where x = 2*x , so that in the end x is equal to 8.

It turns out that this is not the way VBA operates; what happens instead is:

1. x = 3
2. Application.OnTime Now + TimeSerial(0, 0, 2), "Sub2"
3. Here the remaing code in Sub First() is executed until THE END, before switching to Sub Second().
4. So x = 2*x is executed right away along with every line of code that appears until the end of Sub First(). Now x is equal to 6.
5. Finally, after 2 seconds of waiting it executes the instruction in Sub Second(), x = x + 1, so that in the end x is equal to 7

This happens independently of how much time you make the application wait. So for instance if in my example, after

Application.OnTime Now + TimeSerial(0, 0, 2), "Sub2" 

VBA took 10 seconds to execute the line

x = 2*x

it would still have to finish the execution of that command before switching to Sub Second().

WHY IS THIS IMPORTANT?

Because in the light of what I just explained I can now show you my solution to the OP question. Then you can adapt it to your needs.

And YES!!! This works with For Loops too!

THE SOLUTION

I have two subroutines:

1. BLPDownload() one where I refresh a workbook and I have to wait for the values to be dowloaded in order to execute some other code ...

2. BLPCheckForRefresh() where I check if all data have been downloaded

So just like before, I declare two variables with Module-Level Scope

Dim firstRefreshDone As Boolean, Refreshing As Boolean

Sub BLPDownload()

CHECK:

What I do right below is to:

• check if I already told VBA to Refresh the workbook. Of course the first time you run the macro you have not; hence firstRefreshDone = False and it steps into this block of the if statement.
• next it calls the other Sub BLPCheckForRefresh() and it exits the current Subroutine.

And this is the trick. To Exit the Subroutine after calling Application.OnTime*

Inside BLPCheckForRefresh() what happens is

• that I set the value of firstRefreshDone = True
• check if, in the UsedRange, I have cells wiht #N/A Requesting Data. If I have, the value of Refreshing = True.

• finally I call back the Sub BLPDownload()

  If Not firstRefreshDone Then
      Application.Run "RefreshEntireWorkbook"
      Application.Run "RefreshAllStaticData"
      Application.OnTime Now + TimeSerial(0, 0, 4), "BLPCheckForRefresh"
      Exit Sub
  

This time though, firstRefreshDone = True so, if also the refreshing is finished it goes to AFTER_REFRESH where you can put all the code you want, else ...

    ElseIf Not Refreshing Then
        GoTo AFTER_REFRESH

if the refresh is not finished, i.e. if I have cells wiht #N/A Requesting Data it calls the other Sub BLPCheckForRefresh() and it exits the current Subroutine again.

This funny game goes on and on until we have no more #N/A Requesting Data in our UsedRange

    Else
        Refreshing = False
        Application.OnTime Now + TimeSerial(0, 0, 4), "BLPCheckForRefresh"
        Exit Sub
    End If

AFTER:
    some code ...
End Sub

This is the sub where I check if refreshing is done.

Sub BLPCheckForRefresh()
    Dim rng As Range, cl As Range
    Set rng = Foglio1.UsedRange

As explained above here I set the value of firstRefreshDone = True

    firstRefreshDone = True

And this is the loop where i go through each cell in the usedrange looking for #N/A Requesting Data

    On Error Resume Next
    For Each cl In rng
        If InStr(cl.Value2, "#N/A Request") > 0 Then
            Refreshing = True
            Exit For
        End If
    Next cl
    On Error GoTo 0

Finally I call back the Sub BLPDownload()

    Call BLPDownload
End Sub

So this is my solution. I works for me and with another dirty trick that exploits always the GoTo statements and another Module-Level Scope variable that keeps count of the number of iteration it is possible to use this structure in For Loops too.

That being said I want to point out that in my opinion the best solution to this problem is to use Bloomberg API as suggested by Tony Dallimore.

Hope this helps!!

View more solutions

Comments

[rex]

I like that you recommend using the WAPI but I don't think it addresses the original question as to why spreadsheet BBG formula's don't calculate until the macros stop running.

[JohnAndrews]

What do you do when you have a for loop?

[mynameisJEFF]

The whole thing just didn't work when there is a for loop.

[Marco Guignard]

I like this implementation. But you should give more delay on the Application.OnTime, maybe 5 seconde instead of one. If the delay is too short, you could end up with some weird application error in vba.