Speeding up 900 Simultaneous Web Queries

[JML0328]

Hello:

I currently have a spreadsheet that is pulling pricing information from the Internet in 900 queries. Currently, each query returns a 4 x 7 table of data assigned to a unique worksheet.
The problem is that it is taking 1 hour, 20 minutes to update.

So far, I have disabled the background refresh, which has helped improve times. Now, I am looking to see if there is anything else.

I have read about Table.Buffer, but I'm not sure where to insert that code when dealing with web queries.

I have not done any VBA coding yet, but have a good amount of experience in areas outside of Power Query.

I have ideas involving moving the data to one worksheet, but I think the issue is the number of queries. Any help making the data refresh more efficient is greatly appreciated.

 

[offthelip]

I have done a very similar thing for the 350 stocks using XMLHTTP object instead of web queries. Using the XMLHTTP object is much faster than using web queries but does depend on you being able to code in VBA because the code needs to change depending on what the webiste is providing.
Here is a simplified version of my code. I have removed all the confidential bits

Sub Getdata()
Dim inarr As Variant
Dim outarr As Variant
Set FinHTTP = CreateObject("Microsoft.xmlHTTP")
Dim FinRows, FinCols
Sheets("httplist").Select
inarr = Range(Cells(2, 1), Cells(360, 3))
Sheets("Results").Select
Range(Cells(1, 1), Cells(360, 45)) = ""
outarr = Range(Cells(1, 1), Cells(360, 45))


initstring = "http://www.yourstring"



For i = 1 To 350
'For i = 1 To 45
endstring = inarr(i, 3)
urlstring = initstring & endstring
     outarr(i + 4, 1) = inarr(i, 1)
     outarr(i + 4, 2) = inarr(i, 2)


FinHTTP.Open "GET", urlstring, False
  FinHTTP.send
  
  textmess = FinHTTP.responseText
' MsgBox textmess
  FinRows = Split(FinHTTP.responseText, Chr(10))
  For j = 0 To UBound(FinRows) - 1
    FinCols = Split(FinRows(j), "")
    For k = 0 To UBound(FinCols)
    If j > 0 And k > 0 Then
     lk = InStr(FinCols(k), "")
     temp = Left(FinCols(k), lk - 1)
     outarr(i + 4, k + 5) = temp
    Else
    outarr(i + 4, k + 5) = FinCols(k)
    End If
    Next k
  Next j
If i = 5 Or i = 10 Or i = 25 Or i = 200 Or i = 250 Or i = 300 Then
  Sheets("Results").Select
 Range(Cells(1, 1), Cells(360, 45)) = outarr
End If
  
    Next i


  
  
  Sheets("Results").Select
 Range(Cells(1, 1), Cells(360, 45)) = outarr
End Sub

My code will download 350 stock prices in a couple of minutes from the web source that I am using

 

[JML0328]

Thanks so much! I am researching XMLHTTP objects as I have never used them before, so it will take some time to resolve this, but I appreciate your help.