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

Board Regular
Joined
May 30, 2015
Messages
241
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

Board Regular
Joined
May 30, 2015
Messages
241
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:

Code:
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?
 
Last edited:

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
16,666
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.
Code:
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
 

Forum statistics

Threads
1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top