Wait until Excel finishes populating Bloomberg data using VBA

Question

I have an Excel sheet with ~300,000 BDH formulas to download securities prices.

I want to

1. open the file and get all prices
2. paste them as values
3. save and close the file.

However, I do not know when Excel finishes populating Bloomberg data, so it's difficult to determine the time to do 2) and 3).

I have written VBA, but not sure if it works:

In Module1

Sub CheckFormulas()
If Application.CalculationState = xlDone Then
    With Worksheets("Sheet1").UsedRange
    .Value = .Value
End With
Else
    Application.OnTime Now + TimeValue("00:30:00"), "CheckFormulas"

ActiveWorkbook.Close Savechanges:=True
End If
End Sub

In 'ThisWorkbook'

Private Sub Run()
Call CheckFormulas
End Sub

So the way it should theoretically work is:

1. I open the file
2. the code checks every 30 minutes if all BDH formulas are done
3. if they are done, then paste as values, save and close 4) if not, check 30 minutes later again.

I am wondering if this is the best way to accomplish my goal and want to know if Application.CalculationState is compatible or works with Bloomberg formulas BDH?

Klotz

How would you know when Bloomberg is done populating? Do you just guess, and just pick a time to start working with the data? Or is there some marker somewhere that lets you know it's done? – Lithesome

@Lithesome Well, I do not know when Bloomberg is done populating, and that's exactly why I posted this question haha. One way to check is to select all cells in the worksheet and look at the numerical count value at the bottom. Once it's done populating, it stays constant. – Klotz

Answer 1

In our production environment we use a cell that checks if there are cells with "Req" in their formula:

=SUM(IFERROR(FIND("Req", _range_to_check_with_formulas_ ),0))

In a VBA Sub, we test the cell value with a WHILE LOOP:

while cell_to_test.value <> 0
    Application.Calculate
    Application.RTD.RefreshData
    DoEvents
Wend

Call _sub_to_do_some_stuff_

Arian

Yes, that's very smart. So for Bloomberg, it should be no: empty cell values / #N/A requesting data / #Values. Those are the common values I can think of that BDH gives. – Potherb

thanks. this is kinda what I want. My concern is that, every time you calculate and refresh data (in the while loop), wouldn't it affect the daily bloomberg limit? (my bbg daily machine is 500,000) – Klotz

How do I declare _range_to_check_with_formulas_? – Klotz

you replace _range_to_check_with_formulas_ to any range you want to check: A2:B10 – Potherb

@user10039434, ok I don't know exactly. In the while loop you can just wait some time...let me know – Arian

In latest version of Bloomberg Desktop API, cell_to_test needs to check if there are cells with "#N/A Requesting Data..." in their formula. For a single @BDH() request in cell A5, =IF(A5="#N/A Requesting Data...",1,0) will suffice. – Flogging

Answer 2

Built on @stexcec's answer, since I do not want to add a test cell in the worksheet, I use VBA to search for "requesting" based on values. The following function is used to find "requesting" in the Range containing Bloomberg formulae:

Function IsFinished(ws As Worksheet) As Boolean

Dim i, j, LastRow, LastCol As Integer
Dim r, c As Range

LastRow = ws.Range("A1").End(xlDown).Row
LastCol = ws.Range("A1").End(xlToRight).Column

Set r = ws.Range(ws.Cells(1, 1), ws.Cells(LastRow, LastCol))
Set c = r.Find("Requesting", LookIn:=xlValues)
If c Is Nothing Then
    IsFinished = True
Else
    IsFinished = False
End If

End Function

Then, similar to @stexcec's answer, the following code to used to wait until all cells are refreshed:

' Wait until updated
Do While True
    Application.Calculate
    Application.RTD.RefreshData
    DoEvents
    If IsFinished(Application.ActiveSheet) Then
        Exit Do
    End If
Loop

Kannan