1

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!

"data": [{
 "annualisedVolatility": "37.35",
 "bestBuy": "6.04",.
  etc                               
 }]

VBA Code: I put URL with data; hence you can remove variable while testing

Sub getNSEFutData()
    ExpiryDate = Range("TSys_ExpiryDate")
    xRow = 4
    Do Until wksNSE50.Cells(xRow, 1) = ""
    scripID = wksNSE50.Cells(xRow, 1).Value
    scripID = Replace(scripID, "&", "%26")
    'URL = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=" & scripID & "&instrument=FUTSTK&expiry=" & ExpiryDate
    URL = "http://www.nseindia.com/live_market/dynaContent/live_watch/get_quote/GetQuoteFO.jsp?underlying=ADANIPORTS&instrument=FUTSTK&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=" & Chr(34) & "responseDiv")
    Debug.Print xmlhttp.responseText
    Dim jsonStr As String
    jsonStr = Trim(Mid(xmlhttp.responseText, sNo, InStr(sNo, xmlhttp.responseText, "/div>") - sNo))
    Debug.Print jsonStr

    xRow = xRow + 1
    Loop

End Sub
Improve this question
4
  • Use a library to parse the JSON. E.g. github.com/VBA-tools/VBA-JSON Commented Sep 28, 2018 at 5:33
  • @TimWilliams I tried with github, I am getting error since the out put data comes in between many other line. I don't know how to store data part alone and then do the parsing. Will you check the actual output I get at last? Commented Sep 28, 2018 at 5:39
  • Why is the HTTP request inside the loop? Nothing seems to change in relation to this during the loop. Commented Sep 28, 2018 at 6:11
  • @QHarr I will loop through the list of stock, I provided here just an example of one stock code, I am able to get data in between multiple HTTP output, I want to print data with in the range I specified in the question, Any help..pls Commented Sep 28, 2018 at 6:26

2 Answers 2

Reset to default
4

I'm not clear on whether you're having trouble pulling the data in the first place, or just extracting the part you need.

In the URL you provided, I don't see any data like your example. (The phrase bestBuy doesn't exist, and I see numbers like "337..." but not "37...".)

Regardless, if your issue is in getting data from the URL provided into Excel, it's easiest to use built-in functionality instead of re-writing existing features.

'Get External Data'

Go to the Data tab in Excel and click From Web. When prompted, enter the URL and hit Enter. Results will vary depending on which page you're loading, but in this case I clicked Table 0 in the left pane, and then clicked Load:

img

...then Excel loaded the data neatly into a table, which can be updated/refreshed automatically when required, or manually with only a click.

img

getHTTP

If there's a reason you need to do this with VBA, that can also be done although will take some extra work one way or another.

For loading JSON or HTML from any site I use this function:

Public Function getHTTP(ByVal sReq As String) As String
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", sReq, False
        .Send
        getHTTP = StrConv(.responseBody, vbUnicode)
    End With
End Function

Example Usage:

As I'm not sure where you're getting your JSON data from, here's an example that displays Stack Overflow's site status, using the Stack Exchange API:

Sub GetSiteInfo()
    Const url = "https://api.stackexchange.com/2.2/info?site=stackoverflow"
    Dim json As String

    json = getHTTP(url) 'get JSON response

    If InStr(json, """error_id""") > 0 Or json = "" Then 'check for error
        MsgBox "There was a problem." & vbLf & vbLf & json, vbCritical
        Exit Sub
    End If

    json = Mid(json, InStr(json, "[{""") + 3) 'tidy response with string functions
    json = Left(json, InStr(json, "}],") - 1)
    json = Replace(Replace(Replace(json, Chr(34), ""), ",", vbNewLine), "_", " ")
    json = Replace(StrConv(Replace(json, ":", " :" & vbTab & vbTab _
            & vbTab), vbProperCase), " Per ", "/")

    MsgBox json, vbInformation, "Site Statistics" 'display response
End Sub

Note how the extraction for a simple response like this can be managed with basic string functions.

Extract values with basic String functions

As another example, using the JSON data at the top of your question, if the string was in a variable called json and you wanted to extract the value for bestBuy, one way (of several possible methods) would be like this:

Sub jsonExtract_Demo()
    Const json = "aaaaaaa""bestBuy"": ""6.04"",."           'for demo

    Dim pStart As Long, pStop As Long, bestBuy As Single
    Dim prefix As String, suffix As String
    prefix = "bestBuy"": """                                'equivalent to:   "bestBuy": "
    suffix = """"                                           'equivalent to a single   "

    pStart = InStr(json, prefix) + Len(prefix)              'find beginning of value
    pStop = InStr(pStart, json, suffix)                     'find end of value
    bestBuy = CSng(Mid(json, pStart, pStop - pStart))       'extract & convert value
    MsgBox "The value for 'bestBuy` is : " & bestBuy, vbInformation
End Sub

WEBSERVICE Worksheet Function

Last thing I wanted to point out is an often overlooked Excel worksheet function that works fine for most plain-text responses (such as this JSON example):

Enter in a worksheet cell:

=WEBSERVICE("https://raw.githubusercontent.com/bahamas10/css-color-names/master/css-color-names.json")

...and you'll be instantly presented with the raw text result, ready to manipulate with worksheet functions as necessary. In the case of XML responses, WEBSERVICE can be combined with FILTERXML to extract specific pieces of data using XPath, which can be very handy for basic scraping needs.

Further information in the links included above.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

They are extracting the JSON string from the responseText. It's easier to find if you Ctrl + F with "data":[ as search term. +1
@QHarr and ashleedawg Thank you So much!
3

This is using JSONConvertor.bas and the whole JSON string (not just from Data). You would just adjust your current InStr method of determining the JSON extraction start and end points. I am reading in from a cell. "Data" is a collection. The first item is a dictionary which contains the values you are after. You simply loop the keys of the dictionary as shown below. It is easy enough to write these to a row, for example, by adding a cell reference using Cells with rowCounter and columnCounter variables to position. You would have the rowCounter increment in your outer loop to write to a new row each time.

VBA:

Public Sub GetInfoFromSheet()
    Dim jsonStr As String, json As Object, key As Variant, columnCounter As Long, rowCounter As Long
    jsonStr = ThisWorkbook.Worksheets("Sheet1").[a1]
    Set json = JsonConverter.ParseJson(jsonStr)("data")(1)
    rowCounter = rowCounter + 1
    For Each key In json
        columnCounter = columnCounter + 1
        ThisWorkbook.Worksheets("Sheet2").cells(rowCounter, columnCounter) = key & " : " & json(key)
    Next
End Sub

JSON treeview:

Here is a view of the path being traversed:

JSON string:

This is the JSON I am using:

{
  "valid": "true",
  "tradedDate": "28SEP2018",
  "eqLink": "/live_market/dynaContent/live_watch/get_quote/GetQuote.jsp?symbol=ADANIPORTS",
  "data": [
    {
      "annualisedVolatility": "37.35",
      "bestBuy": "8.22",
      "totalSellQuantity": "6,65,000",
      "vwap": "338.01",
      "clientWisePositionLimits": "7807220",
      "optionType": "-",
      "highPrice": "342.35",
      "dailyVolatility": "1.95",
      "bestSell": "9.22",
      "marketLot": "2500",
      "sellQuantity5": "7,500",
      "marketWidePositionLimits": "156144401",
      "sellQuantity4": "2,500",
      "sellQuantity3": "2,500",
      "sellQuantity2": "2,500",
      "underlying": "ADANIPORTS",
      "sellQuantity1": "5,000",
      "pChange": "0.55",
      "premiumTurnover": "-",
      "totalBuyQuantity": "4,95,000",
      "turnoverinRsLakhs": "15,227.35",
      "changeinOpenInterest": "3,35,000",
      "strikePrice": "-",
      "openInterest": "98,67,500",
      "buyPrice2": "338.10",
      "buyPrice1": "338.25",
      "openPrice": "339.80",
      "prevClose": "336.55",
      "expiryDate": "25OCT2018",
      "lowPrice": "333.75",
      "buyPrice4": "338.00",
      "buyPrice3": "338.05",
      "buyPrice5": "337.95",
      "numberOfContractsTraded": "1,802",
      "instrumentType": "FUTSTK",
      "sellPrice1": "338.50",
      "sellPrice2": "338.55",
      "sellPrice3": "338.70",
      "sellPrice4": "338.75",
      "sellPrice5": "338.80",
      "change": "1.85",
      "pchangeinOpenInterest": "3.51",
      "ltp": "8.82",
      "impliedVolatility": "-",
      "underlyingValue": "336.20",
      "buyQuantity4": "7,500",
      "buyQuantity3": "2,500",
      "buyQuantity2": "5,000",
      "buyQuantity1": "2,500",
      "buyQuantity5": "5,000",
      "settlementPrice": "336.55",
      "closePrice": "0.00",
      "lastPrice": "338.40"
    }
  ],
  "companyName": "Adani Ports and Special Economic Zone Limited",
  "lastUpdateTime": "28-SEP-2018 11:41:23",
  "isinCode": null,
  "ocLink": "/marketinfo/sym_map/symbolMapping.jsp?symbol=ADANIPORTS&instrument=-&date=-&segmentLink=17&symbolCount=2"
}

Note:

If you just want to extract the Data part, not the longer string, then consider adjusting your current Instr method to retrieve the following valid JSON string:

[{"annualisedVolatility":"37.35","bestBuy":"6.01","totalSellQuantity":"6,30,000","vwap":"337.78","clientWisePositionLimits":"7807220","optionType":"-","highPrice":"342.35","dailyVolatility":"1.95","bestSell":"7.80","marketLot":"2500","sellQuantity5":"2,500","marketWidePositionLimits":"156144401","sellQuantity4":"7,500","sellQuantity3":"2,500","sellQuantity2":"2,500","underlying":"ADANIPORTS","sellQuantity1":"2,500","pChange":"0.65","premiumTurnover":"-","totalBuyQuantity":"6,20,000","turnoverinRsLakhs":"24,370.83","changeinOpenInterest":"5,15,000","strikePrice":"-","openInterest":"1,00,47,500","buyPrice2":"338.30","buyPrice1":"338.35","openPrice":"339.80","prevClose":"336.55","expiryDate":"25OCT2018","lowPrice":"333.75","buyPrice4":"338.10","buyPrice3":"338.20","buyPrice5":"338.05","numberOfContractsTraded":"2,886","instrumentType":"FUTSTK","sellPrice1":"338.80","sellPrice2":"338.90","sellPrice3":"338.95","sellPrice4":"339.00","sellPrice5":"339.05","change":"2.20","pchangeinOpenInterest":"5.40","ltp":"7.60","impliedVolatility":"-","underlyingValue":"336.85","buyQuantity4":"7,500","buyQuantity3":"2,500","buyQuantity2":"5,000","buyQuantity1":"2,500","buyQuantity5":"5,000","settlementPrice":"336.55","closePrice":"0.00","lastPrice":"338.75"}]
Improve this answer

1 Comment

Good evening... there are several ways to skin a cat. I haven't seen that treeview before+

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.