Macro to paste formulas to a 'batch' of 500 cells, wait for results to populate, then clear those formulas and paste to the NEXT batch of 500.

[d0rian]

I'm trying to take a snapshot of 10,000 different stock quotes using my brokerage's RTD API, *but* they limit me to 500 concurrent requests at any one time. If I exceed that, then I get booted / the API connection gets blocked.

So my inelegant-but-probably-simple-solution (that I need help executing) is to:

1) Request 500 quotes;​

​

2) Wait for those values to populate (about 8 seconds)​

​

3) Copy / paste-as-values to an adjacent column​

​

4) Then clear the 500 stock-requesting formulas, and paste them to 500 cells below the most recently requested ones in order to pull the next 'batch' of 500.​

​

And repeat this process until I have requested and essentially taken a snapshot of all 10,000 price quotes. Doing 500 at a time (for 10K total), this process will essentially have to repeat ~20 times. At around 10 seconds each, it should take around 3-4 minutes to get through.

TLDR: I want a process to "cycle" through batches of cells, copying a formula to that batch, waiting for the results to populate and then copy-pasting-as-values to adjacent cells, then clearing that batch and copying it to the NEXT batch of cells.


I made the attached picture showing my cell setup.

***It should be obvious, but to keep it simple, the attached image shows a hypothetical "batch size" of just 10 (instead of the actual desired size of 500).

 

[d0rian]

I've actually made some progress trying to figure this out on my own, BUT have run into a weird problem: the stock values I'm pulling in via the RTD API will NOT populate while VBA code is running. They just stay at 0.00.

So my currently-crude code right now looks like this:

Call paste_formula_for next_batch
Application.Wait (Now + TimeValue("0:00:5"))
Call Hardcode_values_to_target_cells
Call paste_formula_for next_batch
Application.Wait (Now + TimeValue("0:00:5"))
Call Hardcode_values_to_target_cells

So there's 2 macros above:
paste_formula_for next_batch >> pastes the formula to my desired range of cells
Hardcode_values_to_target_cells >> Hard-codes (copies) the populated stock values to adjacent cells and clears the formula cells.

The problem is that despite my code above dictating a 5-second wait time (usually more than enough for the stock values to populate via the API), the values just stay at 0.00...they apparently will NOT populate while VBA code is running. So the end result is my code is just pasting a bunch of 0.00 values.

Is there any command that lets me tell Excel to go ahead and populate the RTD/API values via the API despite the ongoing VBA?

 

[JoeMo]

You can't have a parallel external activity running while the macro executes. Here's some code you can test on small batch size (currently set at 10 quotes per batch) to see if it is able to do what you want. Completely untested so I have no idea if it allow your API calls to complete.

Sub BeatTheBroker()
'assumes formulas are in the first batch D cells before macro runs
Dim R As Range, CycleNum As Long, TkrCt As Long, LastRow As Long, Ct As Long
Const BatchSize As Long = 10   'change to suit
Const StartRow As Long = 2     'row first ticker symbol is in - change to suit
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set R = Range("D" & StartRow, "D" & BatchSize + StartRow - 1)
Application.ScreenUpdating = False
With R
    .Calculate
    .Offset(0, 1).Value = .Value
End With
CycleNum = 2
TkrCt = LastRow - StartRow + 1
Do
    R.Copy Destination:=Range("D" & StartRow + (CycleNum - 1) * BatchSize, "D" & CycleNum * BatchSize + StartRow - 1)
    R.ClearContents
    Set R = Range("D" & StartRow + (CycleNum - 1) * BatchSize, "D" & CycleNum * BatchSize + StartRow - 1)
    With R
        .Calculate
        .Offset(0, 1).Value = .Value
    End With
    CycleNum = CycleNum + 1
    Ct = Ct + BatchSize
Loop While Ct < TkrCt - BatchSize
With R
    .Copy Destination:=Range("D" & StartRow, "D" & BatchSize + StartRow - 1)
    .ClearContents
End With
Application.ScreenUpdating = True
End Sub