Error converting json to VBA

[cpampas]

hello, I am trying to retrieve quotes from into my database, som I ve used the following code that I came across online, but it returns error 424 (object requiered)

Public Sub GetYahooData()
    '<  VBE > Tools > References > Microsoft Scripting Runtime
    Dim json As Object, re As Object, s As String, xhr As Object
    Dim startDate As String, endDate As String, stock As String

    Set re = CreateObject("VBScript.RegExp")
    Set xhr = CreateObject("MSXML2.XMLHTTP")

    stock = "DLX"
    startDate = "1534809600"
    endDate = "1566345600"

    With xhr
        .Open "GET", "https://finance.yahoo.com/quote/" & stock & "/history?period1=" & startDate & "&period2=" & endDate & "&interval=1d&filter=history&frequency=1d&_guc_consent_skip=" & GetCurrentUnix(Now()), False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .send
        s = .responseText
    End With

    s = GetJsonString(re, s)
    If s = "No match" Then Exit Sub
    
    Set json = JsonConverter.ParseJson(s) ' returns error 424 (object requiered)
 
  For Each item In json
        Debug.Print item("date")
        Debug.Print item("close")
        Debug.Print item("volume")
        
      
  Next

end sub

the string s, looks like the following :

[{"date":1566307800,"open":45.40999984741211,"high":45.40999984741211,"low":44.66999816894531,"close":44.970001220703125,"volume":344600,"adjclose":41.86009979248047},{"date":1566221400,"open":45.880001068115234,"high":46,"low":45.380001068115234,"close":45.52000045776367,"volume":301200,"adjclose":42.3720703125},{"date":1565962200,"open":44.31999969482422,"high":45.810001373291016,"low":44.31999969482422,"close":45.220001220703125,"volume":426100,"adjclose":42.092811584472656},{"amount":0.3,"date":1565962200,"type":"DIVIDEND","data":0.3},{"date":1565875800,"open":44.130001068115234,"high":44.54999923706055,"low":43.84000015258789,"close":44.33000183105469,"volume":419700,"adjclose":40.985111236572266},{"date":1534858200,"open":59.470001220703125,"high":60.470001220703125,"low":59.119998931884766,"close":60.13999938964844,"volume":421400,"adjclose":54.53030014038086}]

Any ideas ?

 

[theDBguy]

Few things:

What does the code for GetJsonString() look like? What is the result of doing a Debug.Print s? Did you verify that you're getting a valid JSON string from the web server?

 

[isladogs]

I don't have time to look at your code right now.
However you might find it useful to look at my JSON app JSON Analyse and Transform for Access
It is a commercial app but there is a free pre-release version which you can try.
That will import the JSON file into Access and analyse its structure suggesting a suitable design for Access tables.
On a quick glance, the JSON structure looks very straightforward.

Hope that helps

 

[cpampas]

debug.print s returns (part of the string):

[{"date":1566307800,"open":45.40999984741211,"high":45.40999984741211,"low":44.66999816894531,"close":44.970001220703125,"volume":344600,"adjclose":41.86009979248047},{"date":1566221400,"open":45.880001068115234,"high":46,"low":45.380001068115234,"close":45.52000045776367,"volume":301200,"adjclose":42.3720703125},{"date":1565962200,"open":44.31999969482422,"high":45.810001373291016,"low":44.31999969482422,"close":45.220001220703125,"volume":426100,"adjclose":42.092811584472656},{"amount":0.3,"date":1565962200,"type":"DIVIDEND","data":0.3},{"date":1565875800,"open":44.130001068115234,"high":44.54999923706055,"low":43.84000015258789,"close":44.33000183105469,"volume":419700,"adjclose":40.985111236572266},{"date":1534858200,"open":59.470001220703125,"high":60.470001220703125,"low":59.119998931884766,"close":60.13999938964844,"volume":421400,"adjclose":54.53030014038086}]

Public Function GetJsonString(ByVal re As Object, ByVal responseText As String) As String
    With re
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "HistoricalPriceStore"":{""prices"":(.*?\])" 'regex pattern to get json string
        If .test(responseText) Then
            GetJsonString = .Execute(responseText)(0).SubMatches(0)
        Else
            GetJsonString = "No match"
        End If
    End With
End Function

 

[theDBguy]

debug.print s returns (part of the string):

[{"date":1566307800,"open":45.40999984741211,"high":45.40999984741211,"low":44.66999816894531,"close":44.970001220703125,"volume":344600,"adjclose":41.86009979248047},{"date":1566221400,"open":45.880001068115234,"high":46,"low":45.380001068115234,"close":45.52000045776367,"volume":301200,"adjclose":42.3720703125},{"date":1565962200,"open":44.31999969482422,"high":45.810001373291016,"low":44.31999969482422,"close":45.220001220703125,"volume":426100,"adjclose":42.092811584472656},{"amount":0.3,"date":1565962200,"type":"DIVIDEND","data":0.3},{"date":1565875800,"open":44.130001068115234,"high":44.54999923706055,"low":43.84000015258789,"close":44.33000183105469,"volume":419700,"adjclose":40.985111236572266},{"date":1534858200,"open":59.470001220703125,"high":60.470001220703125,"low":59.119998931884766,"close":60.13999938964844,"volume":421400,"adjclose":54.53030014038086}]

Public Function GetJsonString(ByVal re As Object, ByVal responseText As String) As String
    With re
        .Global = True
        .MultiLine = True
        .IgnoreCase = False
        .Pattern = "HistoricalPriceStore"":{""prices"":(.*?\])" 'regex pattern to get json string
        If .test(responseText) Then
            GetJsonString = .Execute(responseText)(0).SubMatches(0)
        Else
            GetJsonString = "No match"
        End If
    End With
End Function

Hi. Just a guess, but if the returned string has double quotes in them, you may have to clean them up first before you can parse them.

 

[isladogs]

Hi. Just a guess, but if the returned string has double quotes in them, you may have to clean them up first before you can parse them.

No. The quotes indicate field names

 

[theDBguy]

No. The quotes indicate field names

But when I do the following, I get an error.

JsonConverter.ParseJson("[{"date":123}]")

 

[isladogs]

But when I do the following, I get an error.

JsonConverter.ParseJson("[{"date":123}]")

Even the simplest JSON structures can sometimes defeat Tim Hall's ParseJson code!

This is an extract from the help file supplied with my JATFA app:

This is part of the JSON file supplied by the OP ... expanded with 'whitespace' to improve legibility:

All field names are surrounded by quotes as they are strings. All field values shown here are numbers so no quotes are used

This is the detected table structure:

However, I've noticed a 'rogue' item in the middle of the file which needs looking at further

NOTE: the app automatically handles field names that use reserved words
e.g. Date renamed here as cpampasDate (based on the file name I used)

 

[arnelgp]

you can also do "scraping".

 

[cpampas]

ArnelGP and Isladogs,
Thanks so much, that worked nicely !!

 

[arnelgp]

i also create another sub to process Json, no scraping.

 

[arnelgp]

one curious question, how did you convert the yahoo dates to real date/time?

Edit: so it was Unix time?
Excel VBA: Convert a date string to a Unix timestamp - Stack Overflow

 

[cpampas]

Arnelgp, i followed the suggestion in your link that seems tork fine:

UPDATE scraped SET scraped.dateC = DateAdd("s",[yahoodate],#1/1/1970#);