Parsing Json array via VBA

Question

I am getting a json response from an api and parse it to update in excel. Below is the code. I am not able to parse further to get the price info.

Dim strResult As String
Dim objHTTP As Object
Dim URL As String
Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
URL = "https://bitbns.com/order/getTickerAll"
objHTTP.Open "GET", URL, False
objHTTP.Send
Set JSON = JsonConverter.ParseJson(objHTTP.ResponseText)
'strResult = objHTTP.ResponseText
'MsgBox JSON(1)("BTC")("sellPrice")
baseCol = 9
buyCol = 10
sellCol = 11
i = 1
Dim keyCurr As String
For Each Item In JSON
    ActiveSheet.Cells(i + 2, baseCol).Value = Item.Keys
    i = i + 1
Next

Kinly help. As you could see in a comment above, I am able to get data as hard coded

MsgBox JSON(1)("BTC")("sellPrice")

But when I try getting that in loop, I am unable to. Below are the ones I tried but did not work.

ActiveSheet.Cells(i + 2, baseCol).Value = JSON(i)(Item.Keys)("sellPrice") 
ActiveSheet.Cells(i + 2, baseCol).Value = JSON(i)(" + Item.Keys + ")("sellPrice")
ActiveSheet.Cells(i + 2, baseCol).Value = JSON(i)(Item(0))("sellPrice")
ActiveSheet.Cells(i + 2, baseCol).Value = JSON(i)(Item(1))("sellPrice")

For parsing JSON, I use vbaJSON library. It seem to return proper object (as could see am able to access hard coded way, but could not access in loop)

Update: As per Vityata's hint, below code seem to be working fine. Thank you all for such immediate help. :)

For Each Item In JSON
    ActiveSheet.Cells(i + 2, baseCol).Value = Item.Keys
    For Each curr In Item
        ActiveSheet.Cells(i + 2, buyCol).Value = JSON(i)(curr)("buyPrice")
        ActiveSheet.Cells(i + 2, sellCol).Value = JSON(i)(curr)("sellPrice")
        i = i + 1
    Next curr
Next Item

Vityata · Accepted Answer · 2018-02-15T15:52:41.183

3

It you hard-code the "sellPrice", you can come up with something like this:

Dim something, someItem, cnt&
For Each something In JSON
    For Each someItem In something
        cnt = cnt + 1
        Debug.Print someItem
        Debug.Print JSON(cnt)(someItem)("sellPrice")
    Next someItem
Next something

And in the immediate window:

BTC
 623900 
XRP
 70,35 
NEO
 7699,5 
GAS
 2848,97 
ETH
 59500 
XLM
 28,38

The keys and the items are collection, which can be looped through:

Dim something, someItem, cnt&, obj, iO
For Each something In JSON
    For Each someItem In something
        cnt = cnt + 1
        Debug.Print someItem
        Set obj = JSON(cnt)(someItem)
        For Each iO In obj.Keys
            Debug.Print iO
            Debug.Print obj.item(iO)
        Next iO
    Next someItem
Next something

In the immediate window:

BTC
sellPrice
 625000 
buyPrice
 624000 
lastTradePrice
 625000 
XRP
sellPrice
 70,2 
buyPrice
 70,1 
lastTradePrice
 70,2

edited Feb 15 '18 at 15:52

answered Feb 15 '18 at 14:45

Vityata

42,633
8
55
100

I get this same result using the OP's code. Had to add declarations. Am I missing something? – QHarr Feb 15 '18 at 14:47
I am wondering if they are trying to access the array associated with each key that has the different prices? – QHarr Feb 15 '18 at 14:49
Item.Keys is getting printed. I should be able to access further. for eg, sellerPrice etc. if you look at json response, it would be clear. – Parthiban Rajendran Feb 15 '18 at 14:49
@QHarr = is this line from the OP's code working for you - `ActiveSheet.Cells(i + 2, baseCol).Value = Item.Keys`? – Vityata Feb 15 '18 at 14:50
Erm..Amazingly yes. After declaring the types. But it doesn't access the arrays. Only the keys. – QHarr Feb 15 '18 at 14:50
@QHarr - I have tried to `debug.Print` the `item.Keys` instead of writing to Excel, got an error and decided that this was the problem of the OP. Now I see that it works actually... – Vityata Feb 15 '18 at 14:53
I would be interested in how you access the array though!! – QHarr Feb 15 '18 at 14:55
@PaariVendhan - 1 step further :) – Vityata Feb 15 '18 at 15:10
@QHarr - which array? – Vityata Feb 15 '18 at 15:10
Yes. I just nicked your code to have a spin..... I was stuck with my own in swopping out ("sellPrice") for something like (1) or .Name..... – QHarr Feb 15 '18 at 15:24
@QHarr - I see what you mean. Managed to do somethign with the collections. – Vityata Feb 15 '18 at 15:39
1

@Vityata Yes I upvoted. Apparently [] means array and {} means object which means setting objects and looping arrays.... Still exploring here. – QHarr Feb 15 '18 at 15:41
@QHarr - I see. There should be a way to combine the two `obj.Items` and `obj.Keys` loops into a single one. – Vityata Feb 15 '18 at 15:45
1

I was looking at the structure of this though the JSON is a little different https://stackoverflow.com/questions/48766360/how-can-i-parse-the-array-objects-inside-this-json-list-in-vba – QHarr Feb 15 '18 at 15:46
1

Means should be able to access array of prices via their index if can get level up from that correct. Pretty much your obj = JSON(cnt)(someItem). That said, without knowing the number of indices then your way is much more sensible. – QHarr Feb 15 '18 at 15:48
1

@Vityata I haven't managed to do a one liner. I am not sure how you can return an array of all the inner dictionaries items without looping each of the outer. I put a very messy answer below which is the long winded route. Once I started thinking [] = Collection and {} = Dictionary , which may be wrong overall? , how to access elements seemed to be clear. – QHarr Feb 16 '18 at 08:19

QHarr · Answer 2 · 2018-02-16T09:32:09.047

Not a perfectly tidy, yet, version but here goes:

Version 2 (1 less loop) - I switched to reading the JSON from file due to API call time outs

Option Explicit

Public Sub test3()

    Dim fso As FileSystemObject
    Dim JsonTS As TextStream
    Dim JsonText As String

    Set fso = New FileSystemObject
    Set JsonTS = fso.OpenTextFile(ThisWorkbook.Path & Application.PathSeparator & "newFile.txt", ForReading)

    JsonText = JsonTS.ReadAll
    JsonTS.Close

    Dim JSON As Object
    Dim Dict As Dictionary
    Dim key As Variant

    Set JSON = ParseJson(JsonText)

    For Each Dict In JSON                 'loop items of collection which returns dictionaries of dictionaries

        For Each key In Dict(Dict.Keys(0))
            Debug.Print Dict.Keys(0) & " - " & key & ":" & Dict(Dict.Keys(0))(key)
        Next key

    Next Dict

End Sub

Version 1:

Option Explicit

Public Sub test()

    Dim strResult As String
    Dim objHTTP As Object
    Dim URL As String
    Dim JSON As Object

    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")

    URL = "https://bitbns.com/order/getTickerAll"
    objHTTP.Open "GET", URL, False
    objHTTP.Send

    Set JSON = JsonConverter.ParseJson(objHTTP.ResponseText)

    Dim currItem As Dictionary
    Dim DictKey As Variant
    Dim targetValue As Variant

    For Each currItem In JSON                         'loop items of collection which returns dictionaries of dictionaries

        For Each DictKey In currItem.Keys 'currItem is a dictionary; dictKey is a key

            For Each targetValue In currItem(DictKey).Keys 'currItem(DictKey) returns a dictionary

                Debug.Print DictKey & "-" & targetValue & ": " & currItem(DictKey)(targetValue)

            Next targetValue

        Next DictKey

    Next currItem

End Sub

1 nested loop less is quite ok :) – Vityata Feb 16 '18 at 11:33 — Vityata, Feb 16 '18 at 11:33

Parsing Json array via VBA

2 Answers2

Linked