Bloomberg Data taking time to load in Excel- VBA

Question

I currently have this Excel VBA code written using Bloomberg API. I am trying to pull data from Bloomberg into Excel using VBA. That is working fine but the problem is that I have another macro that then copies the data from Bloomberg and pastes it in another sheet.

If the data hasn't all been output from Bloomberg then I end up having insufficient data to copy then paste into the other sheet.

Currently, I am using this line of code:

Application.OnTime Now + TimeValue("00:01:45"), "RunAll"

which waits after running the first macro for 1 min 45s till it runs the remaining macros. It is useful but way too much time. Issue is that this is around how long the data takes to output.

Is there any other more efficient way for me to pull in the bloomberg data faster by ensuring that the data gets output into excel faster?

do loading cells contain a particular string? Maybe you could run a loop or intercept the Worksheet.Change event to check for this string. — silentsurfer
Please provide example code. I don't know what bloomberg data is, so please make the question more generic. Are you automating IE to get the data? Are you making a database connection? — Cody Ｇ
How do you retrieve the data? If you do it programmatically you won't have to wait like this... — assylias
I've attached a photo of the code above. First, I load the bloomberg data into excel. After that, when people reopen the file thhey can clear the sheets contents and refresh the sheet to extract the data in case it has changed. After refreshing, I have put a timer that waits for 1min 45 s till it runs the remaining macros. This is where I would like ton try and find a way to make sure that the data refreshes and outputs fully at a faster pace but I don't know how. Thanks! — markos

pheeper pheeper · Accepted Answer · 2016-07-08T01:11:04

One way to handle it would be when you start your second macro that copies the data, check to see to see if a mid-point cell is empty (something like A100?? Seeing your code would help here...). If so, wait 10 seconds and check again. This will force that second macro to stay in a holding pattern while the first one catches up.

Word of caution though, I would set a max number of loops otherwise if that data doesn't download for some reason it won't hang up your machine.

UPDATE 1:

I wrote out the code below to accomplish what you're trying to do. There are a couple of things you'll need to work into your current code, but it I've purposely made it comment heavy so you should be able to follow. Let me know if this works for you.

Public boolBloombergCompleted As Boolean

Sub GetBloombergData()

    'add this line after the data grab is complete
    boolBloombergCompleted = True
End Sub


Sub WriteData()
    Dim iRow As Integer
    Dim boolTimeOut As Boolean

    'change the last number as fit, make sure it's larger than the number of rows of data you're pulling in though
    For iRow = 1 To 1000  

        ' Check to see if the cell is blank
        If Sheet1.Cells(iRow, 1) = vbNullString Then
            ' If the cell is blank and GetBloombergData has completed then exit sub
            If boolBloombergCompleted = True Then
                Exit Sub: Debug.Print "WriteData completed"
            Else
                ' Call the wait function below
                boolTimeOut = WaitForDataGrabToCatchUp(Sheet1.Cells(iRow, 1))
                If boolTimeOut = True Then GoTo TimeOutErr:
            End If
        End If

        ' < Add your code to write data in here >

    Next iRow

    Exit Sub

TimeOutErr:
    MsgBox "The write sub timed out while waiting for data to load", vbExclamation

End Sub

Function WaitForDataGrabToCatchUp(rng As Range) As Boolean
    Dim StartTime1 As Long
    Dim StartTime2 As Long
    Dim PauseTime As Long
    Dim StopTime As Long

    ' Set the amount of time to pause between checking the spreadsheet for updates
    PauseTime = 5 'seconds

    ' Set the maximum amount of time to wait before timing out
    StopTime = 60 'seconds

    ' StartTime1 is used for calculating overall time
    StartTime1 = Timer

    Do While rng = vbNullString
        ' check if the StopTime has been reached
        If Timer - StartTime1 > StopTime Then
            WaitForDataGrabToCatchUp = True
            Exit Function
        Else
           ' loop for amount of PausedTime (the DoEvents part here is key to keep the data grab moving)
            StartTime2 = Timer
            Do While Timer < StartTime2 + PauseTime
                Debug.Print Timer - StartTime1
                DoEvents
            Loop
        End If
    Loop

    WaitForDataGrabToCatchUp = False ' means it did not time out

End Function

Bloomberg Data taking time to load in Excel- VBA

1 Answers