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:

31458

which is very similar to what you said you wanted:

31459

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

314613146131462

[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:

31463

then you can step through the query on the right:

31464

but I'm not sure what you want.

[xyz987]

thanks for your help. i already try successful. my code & setting like pic
31466 31468