0

When I run my code first time after opening excel I get updated and correct data from website but after that I get the same data when I run the code even after 1 hour. the website data change in every 5 minute interval.

I am using excel 2019. I have downloaded the json converter bas file from github, (https://github.com/Malekai/Downloading-Data/blob/master/population_data.json).

The code is here....

Sub N_Test_33()
Dim req As New MSXML2.XMLHTTP60
Dim URL As String, ws As Worksheet
Dim json As Object, r, r1 As String, i, j As Integer
Set ws = ThisWorkbook.Worksheets("Sheet3")

URL = "https://www.nseindia.com/api/option-chain-indices?symbol=BANKNIFTY"
req.Open "GET", URL, False
req.send
r = req.responseText
Set json = JsonConverter.ParseJson(r)

ws.Range("A1").Value = json("filtered")("data")(62)("strikePrice")
ws.Range("A2").Value = json("filtered")("data")(62)("CE")("openInterest")
ws.Range("A3").Value = json("filtered")("data")(62)("PE")("openInterest")

End Sub

The code has to be tested between Mon-Friday, 9:30AM to 3:30PM in Indian timing.

dimnnv
  • 678
  • 3
  • 8
  • 21
Jugal Kishor
  • 17
  • 7
  • When writing in English, please use capital I instead of small i for denoting "myself". – dimnnv Jun 03 '23 at 10:34
  • Ok I will remember by next time. – Jugal Kishor Jun 03 '23 at 10:37
  • Try adding `req.setRequestHeader "Cache-Control", "no-store"` to line before `req.send` – CDP1802 Jun 03 '23 at 11:07
  • I'm not too familiar with that api, but I'd check to make sure the time-stamp has been updated so that you can be certain the api output is matching the web output. Also, can you be certain that `item 62` will always be referring to the same contract? – Ron Rosenfeld Jun 03 '23 at 11:16
  • 1
    That's because a cached site is loaded - see for example https://stackoverflow.com/questions/16614167/microsoft-xmlhttp-how-to-prevent-caching It's not an VBA, Json or Git problem. – FunThomas Jun 03 '23 at 11:51
  • Yes this is most active strick price number. @RonRosenfeld – Jugal Kishor Jun 03 '23 at 12:12
  • Previously: https://stackoverflow.com/questions/5235464/how-to-make-microsoft-xmlhttprequest-honor-cache-control-directive – Tim Williams Jun 03 '23 at 17:03
  • But did you check that the time stamp for the data was actually updated? – Ron Rosenfeld Jun 03 '23 at 20:48

0 Answers0