JsonConverter question

[xyz987]

Hi sirs

i want to catch data from "https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date=20240327&type=ALLBUT0999&response=json&_=1711564048882"

i try to use JsonConverte, but it show run-time error "450", but i check ResponseText correct. what mistake in this case ?? code like list, thanks

Sub FetchAndParseData_1()
    Dim xhr As Object
    Dim URl As String
    Dim response As String
    Dim json As Object
    Dim tables As Object
    Dim data As Object
URl = "https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date=20240327&type=ALLBUT0999&response=json&_=1711564048882"
Set xhr = CreateObject("MSXML2.XMLHTTP")
xhr.Open "GET", URl, False
    xhr.send
response = xhr.ResponseText
' Parse JSON response
    Set json = JsonConverter.ParseJson(response)
    Debug.Print json("tables")  'this show run-time error
    ' Check if JSON contains "tables" key
    If json.Exists("tables") Then
    Set tables = json("tables")
    Set data = tables(8)("data")
    Debug.Print json("tables")
    Else
    MsgBox "No tables found in JSON response."
    End If
' Clean up
    Set xhr = Nothing
End Sub

[xyz987]

sorry, i find some error with my code

Sub FetchAndParseData_1()
    Dim xhr As Object
    Dim URl As String
    Dim response As String
    Dim json As Object
    Dim tables As Object
    Dim data As Object
URl = "https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date=20240327&type=ALLBUT0999&response=json&_=1711564048882"
Set xhr = CreateObject("MSXML2.XMLHTTP")
xhr.Open "GET", URl, False
    xhr.send
response = xhr.ResponseText
    'Debug.Print response
' Parse JSON response
    Set json = JsonConverter.ParseJson(response)
    ' Check if JSON contains "tables" key
    'If json.Exists("tables") Then
        'Set tables = json("tables")
        'Set data = tables(8)("data")
    Debug.Print json("tables")(8)("data")
    'Else
        'MsgBox "No tables found in JSON response."
    'End If
' Clean up
    Set xhr = Nothing
End Sub

[Aussiebear]

Does this give you any assistance?

Function ParseJSON(json$, Optional key$ = "obj") As Object
    p = 1
    token = Tokenize(json)
    Set dic = CreateObject("Scripting.Dictionary")
    If token(p) = "{" Then ParseObj key Else ParseArr key
    Set ParseJSON = dic
End Function

Function ParseObj(key$)
    Do: p = p + 1
    Select Case token(p)
        Case "]"
        Case "[":  ParseArr key
        Case "{"
        If token(p + 1) = "}" Then
            p = p + 1
           dic.Add key, "null"
        Else
            ParseObj key
        End If
        Case "}":  key = ReducePath(key): Exit Do
        Case ":":  key = key & "." & token(p - 1)
        Case ",":  key = ReducePath(key)
        Case Else: If token(p + 1) <> ":" Then dic.Add key, token(p)
    End Select
    Loop
End Function

Function ParseArr(key$)
    Dim e&
    Do: p = p + 1
    Select Case token(p)
        Case "}"
        Case "{":  ParseObj key & ArrayID(e)
        Case "[":  ParseArr key
        Case "]":  Exit Do
        Case ":":  key = key & ArrayID(e)
        Case ",":  e = e + 1
        Case Else: dic.Add key & ArrayID(e), token(p)
    End Select
    Loop
End Function

[xyz987]

thanks for your help, but i really not very know vba, i copy your coed into my code, but it shows not defined, or should i open vba tools/reference function
https://ibb.co/2FKtJb4


attach code file

error message
https://ibb.co/Jsybym4


i want to catch this data
https://ibb.co/s9mZP0x

[arnelgp]

i think what you have on that website is an Image and not a table or json string.

[p45cal]

I can get this:

2024-03-30_103300.jpg

which is very similar to what you said you wanted:

2024-03-30_103947.jpg

with Power Query and no vba, everything built-in to Excel already, so no add-ins or special JSON 'thingies' (libraries, whatever).
See attached workbook.
At the moment, it's very hard-coded in that

• it looks at one specific url:

  https://www.twse.com.tw/rwd/zh/afterTrading/MI_INDEX?date=20240327&type=ALLBUT0999&response=json&_=1711564048882

• takes the 9th table
• takes the 1st list from the lists in the data field.

All 3 of the above could be made more flexible.

[xyz987]

Yes, this is i want to get, are you useing query to get??
i think it is normal data, because i use python can get it in normal method.
maybe it is two tables, so i need json twice tables?? but when i use request to get and debug.print, it can show all data, and next row use json then only can get any thing from request data

python.jpgpython.jpganalysis.jpg

[p45cal]

Yes, this is i want to get, are you useing query to get??

Yes, it Power Query. You can see what's going on if you:

2024-03-30_170113.jpg

then you can step through the query on the right:

2024-03-30_165700.jpg

but I'm not sure what you want.

[xyz987]

thanks for your help. i already try successful. my code & setting like pic
2.JPG 1.JPG