Update Bloomberg API static data

Question

I want to copy and paste data from one sheet to another sheet. The original data is from Bloomberg API function. I found the Bloomberg data cell only updates after the macro finishes running. I tried to build a private function to let the static data update before I copy it. Here is my code:

Private Sub ProcesData()

Application.Run

Call Application.OnTime(Now + TimeValue("00:00:01"), "ProcessData")

End Sub


Sub Macro3()

Dim i As Integer
i = 1

Do Until i > 2
    Sheets("Sheet1").Activate

    Cells(1, 3).Value = Cells(i, 1)

    i = i + 1

    Call Application.OnTime(Now + TimeValue("00:00:01"), "ProcessData")

    Range("C4:E181").Select

    Selection.Copy

    Sheets("Sheet2").Select
    Range("A1").Select
    lMaxRows = Cells(Rows.Count, "A").End(xlUp).Row
    Range("A" & lMaxRows + 1).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Loop

End Sub

PaulG PaulG · Accepted Answer · 2016-02-03T08:43:50

Ah, Bloomberg API... many an issue.

So, when you do a Bloomberg request using a function call, the cell call site receives a call back and refreshes the data.

The problem is, w.r.t. automation, the data can come back in parts and you have to wait till it completes before continuing to process your data.

So, there are 3 approaches to this solution.

Last time I looked, this is the one that Bloomberg recommends. Have a routine that refreshes the call to the cell formula. This has a timer and recursively invokes itself until the data has been retrieved. The trick to this is to know when the data has been retrieved correctly. The data area is checked for errors which say it hasn't been completed yet.
The other solution is to invoke the call using Application.Run where you build the formula in code and invoke with VBA. This allows you to not deal with functions as such in the worksheet. But is effectively the same thing as 1.
You can set a reference to the Bloomberg API and use COM to do the request. This is a much cleaner approach and you can easier integrate this into your code effectively creating a Bloomberg data call API.

I would suggest, if possible going for option 3. Although a little more effort, te code becomes much cleaner. Options 1. and 2. can be tricky to debug when your workbooks become more complex in terms of structure etc.

If you search your Bloomberg help (the ever tremendous old 'F1 F1' help service should be able to point you in the right direction. Simply the most impressive help system ever done in my opinion!) Bloomberg does have some examples which demonstrate the methods highlighted above.

So, with the issue at hand and to finally answer your question :) I would restructure your code to actually do function call that initially does a Calcuation (which will invoke your requests to Bloomberg). Then enter into a recursive function which calls itself until there are no errors in the data. After this you then copy your data.

Hope that helps.

Update Bloomberg API static data

1 Answers