I am executing the following code in VBA through a button in excel. However the website data is updating, and excel doesn't seem to update, no matter how long I wait in between button clicks. Only when I close excel and reopen then the data updates. How do I get my code to pull in live data with each click?
Public Sub exceljson()
Dim http As Object
Dim JSON As Object
Dim inst As String
Dim arrInst() As String
Dim arrRatio() As Double
Dim arrBid() As Double
Dim arrAsk() As Double
Dim arrMin() As Double
Dim arrMaxk() As Double
Dim size As Integer
Dim i As Integer, x As Integer
Set http = CreateObject("MSXML2.XMLHTTP")
'Counts the number of cells that are not empty and the values within the list of arguments.
Set myRange = Range("A2:A100")
size = WorksheetFunction.CountA(myRange)
'Sets the array of instruments to the size determined above
'Loops through each row and inputs that instrument name into the array
ReDim arrInst(size)
For i = 1 To size
arrInst(i) = UCase(Range("E1").Offset(i).Value)
Next i
'Get data needed from API for each leg and print in sheet
For i = 1 To UBound(arrInst)
http.Open "GET", ("https://testapp.deribit.com/api/v2/public/ticker?instrument_name=" & arrInst(i)), False
http.Send
Set JSON = ParseJson(http.ResponseText)
Cells(i, 7).Offset(1).Value = JSON("result")("min_price")
Cells(i, 8).Offset(1).Value = JSON("result")("best_bid_price")
Cells(i, 9).Offset(1).Value = JSON("result")("mark_price")
Cells(i, 10).Offset(1).Value = JSON("result")("best_ask_price")
Cells(i, 11).Offset(1).Value = JSON("result")("max_price")
Next i
End Sub
