title: Fetching data from xmlhttp.responseText into excel
body: <p>I am writing a macro to fetch some data from website. I got struck in the following step. I want to take data part and print it in excel. I provided below the data structure and code I used to print data. I am trying various methods for three days. No luck!</p>
  
  <pre><code>"data": [{
   "annualisedVolatility": "37.35",
   "bestBuy": "6.04",.
    etc                               
   }]
  </code></pre>
  
  <p>VBA Code: I put URL with data; hence you can remove variable while testing</p>
  
  <pre><code>Sub getNSEFutData()
      ExpiryDate = Range("TSys_ExpiryDate")
      xRow = 4
      Do Until wksNSE50.Cells(xRow, 1) = ""
      scripID = wksNSE50.Cells(xRow, 1).Value
      scripID = Replace(scripID, "&amp;", "%26")
      'URL = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=" &amp; scripID &amp; "&amp;instrument=FUTSTK&amp;expiry=" &amp; ExpiryDate
      URL = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=ADANIPORTS&amp;instrument=FUTSTK&amp;expiry=25OCT2018"
      Set xmlhttp = CreateObject("MSXML2.ServerXMLHTTP.6.0")
      xmlhttp.Open "GET", URL, False
      xmlhttp.setRequestHeader "Content-Type", "text/JSON"
      xmlhttp.Send
      sNo = InStr(1, xmlhttp.responseText, "id=" &amp; Chr(34) &amp; "responseDiv")
      Debug.Print xmlhttp.responseText
      Dim jsonStr As String
      jsonStr = Trim(Mid(xmlhttp.responseText, sNo, InStr(sNo, xmlhttp.responseText, "/div&gt;") - sNo))
      Debug.Print jsonStr
  
      xRow = xRow + 1
      Loop
  
  End Sub
  </code></pre>