Unable to clear a comma related confusion

shahin · Nov 3, 2017

I've written some code in vba to parse a price from json response. Although my code is successfully parsing the price, I'm having a hard time understanding an inverted comma related confusion. Hope somebody here would stretch a helping hand to clear the confusion.

This is the script:

Code:

Sub Get_price()
    Dim res As Variant
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.homedepot.ca/homedepotcacommercewebservices/v2/homedepotca/products/1000660019/localized/9999?catalogVersion=Online&lang=en", False
        .send
        res = .responseText
    End With
    res = Split(Split(Split(res, """displayPrice""")(1), """formattedValue"" : """)(1), """")(0)
    MsgBox res
End Sub

This is the total response you can see if you follow the link used within the above script:

Code:

{
   "bopis" : true,
   "buyable" : false,
   "onlineStock" : {
      "stockLevel" : 0,
      "stockLevelStatus" : "NoLongerAvailable"
   },
   "optimizedPrice" : {
      "availabilityMsg" : "<a href=\"#\" data-target=\"#myStoreModal\" data-toggle=\"modal\">Select store for availability</a>",
      "displayPrice" : {
         "currencyIso" : "CAD",
         "formattedValue" : "$11.29",
         "priceType" : "BUY",
         "value" : 11.29
      },
      "endDate" : "2017-11-03T23:59:59-04:00",
      "productId" : "1000660019",
      "productStatus" : "NA",
      "regprice" : {
         "currencyIso" : "CAD",
         "formattedValue" : "$11.29",
         "priceType" : "BUY",
         "value" : 11.29
      },
      "savingsAmount" : {
         "currencyIso" : "CAD",
         "formattedValue" : "$0.00",
         "priceType" : "BUY",
         "value" : 0.0
      }
   },
   "promotionMessages" : {
   },
   "shipToHome" : false
}

What I can't understand is:

I knew so far that if there is any name wrapped within inverted comma in the json file like "shahin", then it will be used like """shahin""" in the responsetext so that it can be split properly. Which means to diminish the effect of "" there should be used """". If I'm right and try to understand how the below expression worked:

Split(Split(Split(res, """displayPrice""")(1), """formattedValue"" : """)(1), """")(0)

1. """displayPrice""" ---there is nothing to complain according to how it looks in the json response.
2. """formattedValue"" : """ ---something to complain according to how it looks in the json response

Because the right one should be """formattedValue"""" : """ if the way i thought is right. However, it throws error. Where i'm going wrong? Thanks.

shrivallabha · Nov 3, 2017

Simple logic that I follow is below:

Original string

Code:

"formattedValue" : "

Step 1: Replace all quotes with pair of them

Code:

""formattedValue"" : ""

Step 2: Wrap the result in pair of double quotes

Code:

"""formattedValue"" : """

It doesn't answer your question.

Now coming back to your question, you just need to reverse above logic. Final Value (let's ignore it is not working for a moment)

Code:

"""formattedValue"""" : """

Step 1: Remove wrapping quotes

Code:

""formattedValue"""" : ""

Step 2: Replace all pairs with a double quote

Code:

"formattedValue"" : "

Does it look right with respect to what you want to pass?

shahin · Nov 3, 2017

Nothing clearer. Just love you man. Thanks a lot shrivallabha for your time and the clarity.

shahin · Nov 3, 2017

But, things become vague when i come across this example. It doesn't seem to comply the logic. Forgive my ignorance.

The working code:

Code:

Sub Torrent_data()
    Const URL = "https://www.yify-torrent.org/search/1080p/"
    Dim http As New XMLHTTP60, str As Variant
  
    With http
        .Open "GET", URL, False
        .send
        str = Split(.responseText, " class=""mv"">")
    End With
  
    y = UBound(str)
    For i = 1 To y
        Cells(i, 1) = Split(Split(str(i), " title=""")(1), """")(0)
        Cells(i, 2) = Split(Split(str(i), " href=""")(1), """")(0)
        Cells(i, 3) = Split(Split(str(i), " src=""")(1), """")(0)
        Cells(i, 4) = Split(Split(str(i), "Genre:</b>")(1), "</li>")(0)
    Next i
End Sub

Elements within the above items embedded:

Code:

<div class="mv"><h3><a href="/movie/56382/download-atomic-blonde-2017-1080p-mp4-yify-torrent.html" target="_blank" title="Atomic Blonde (2017) 1080p">Atomic Blonde (2017) 1080p</a></h3><div class="movie"><div class="movie-image"><a href="/movie/56382/download-atomic-blonde-2017-1080p-mp4-yify-torrent.html" target="_blank" title="Download Atomic Blonde (2017) 1080p"><span class="play"><span class="name">Atomic Blonde (2017) 1080p</span></span><img src="//pic.yify-torrent.org/20171103/56382/atomic-blonde-2017-1080p-poster.jpg" alt="Atomic Blonde (2017) 1080p"></a></div></div><div class="mdif"><ul><li><b>Genre:</b>Action</li> <li><b>Quality:</b>1080p</li> <li><b>Screen:</b>1920x804</li> <li><b>Size:</b>2.19G</li> <li><b>Rating:</b>6.9/10</li> <li><b>Peers:</b>292</li> <li><b>Seeds:</b>170</li> </ul><a href="/movie/56382/download-atomic-blonde-2017-1080p-mp4-yify-torrent.html" class="small button orange" target="_blank" title="Download Atomic Blonde (2017) 1080p YIFY Torrent">Download</a></div></div>

shrivallabha · Nov 4, 2017

Can you specify the line please? e.g.

Code:

Cells(i, 1) = Split(Split(str(i), " title=""")(1), """")(0)

shahin · Jan 3, 2018

Hi there!! Do you have any idea as to how I can achieve the same not using the hardcoded length what I have used in my below script to parse specific items from a link containing json data. While playing around with a link posted in SO, I thought to solve that. I did everything and it is fetching all the required values which are in this case "Name" and "Price" out of the below link. However, I can't fix the hardcoded part myself. Any help on this will be vastly appreciated.

The link containing json data: https://api.coinmarketcap.com/v1/ticker/

This is the script I'm trying with:

Code:

Sub json_data()
    Dim http As New XMLHTTP60, res As Variant

    With http
        .Open "GET", "https://api.coinmarketcap.com/v1/ticker/", False
        .send
        res = .responseText
    End With

    For r = 1 To 100   ''talking about this portion
        Cells(r, 1) = Split(Split(Split(res, "name"": """)(r), """symbol")(0), """")(0)
        Cells(r, 2) = Split(Split(Split(res, "price_usd"": """)(r), """price_btc")(0), """")(0)
    Next r
End Sub

shrivallabha · Jan 4, 2018

shahin said:
Hi there!! Do you have any idea as to how I can achieve the same not using the hardcoded length what I have used in my below script to parse specific items from a link containing json data. While playing around with a link posted in SO, I thought to solve that. I did everything and it is fetching all the required values which are in this case "Name" and "Price" out of the below link. However, I can't fix the hardcoded part myself. Any help on this will be vastly appreciated.

The link containing json data: https://api.coinmarketcap.com/v1/ticker/

This is the script I'm trying with:

Code:

Sub json_data() Dim http As New XMLHTTP60, res As Variant With http .Open "GET", "https://api.coinmarketcap.com/v1/ticker/", False .send res = .responseText End With For r = 1 To 100 ''talking about this portion Cells(r, 1) = Split(Split(Split(res, "name"": """)(r), """symbol")(0), """")(0) Cells(r, 2) = Split(Split(Split(res, "price_usd"": """)(r), """price_btc")(0), """")(0) Next r End Sub

The answer is hidden in your code.

You can use:

Code:

For r = 1 To UBound(Split(res, "name"": """)) 'Use size of split

shahin · Jan 4, 2018

Exactly this is what I tried to do but couldn't. This is so damn awesome!! Thanks a lot shrivallabha for your fruitful effort.

Unable to clear a comma related confusion

shahin

Active Member

shrivallabha

Excel Ninja

shahin

Active Member

shahin

Active Member

shrivallabha

Excel Ninja

shahin

Active Member

shrivallabha

Excel Ninja

shahin

Active Member