Excel VBA JSON array url import and parse

Question

I'm trying to import and parse the JSON data from the following link into excel using VBA:

https://www.alphavantage.co/query?fu...N5&symbol=MSFT

Unfortunately, i'm not able to complete it as it keeps giving an error: Object doesn't support this property or method. Can someone please help me resolve?

All i need is to get the date that is listed a long with the SMA that is provided for it. The URL for the JSON file is actually in Sheet2 and is referenced in the code. The reason for this is because i will have multiple URLs that the code will need to loop through and import.

Here is a screenshot of expected out put.

https://i.stack.imgur.com/74wKU.jpg

Here is the code that i'm using:

Sub test()
Dim objHTTP As Object
Dim MyScript As Object
Dim x As Integer, NoA As Integer, NoC As Integer
Dim myData As Object
Set MyScript = CreateObject("MSScriptControl.ScriptControl")
MyScript.Language = "JScript"

Set objHTTP = CreateObject("MSXML2.XMLHTTP")
For x = 1 To Application.CountA(Sheet2.Columns(1))
Sheets("Sheet1").Activate
Sheets(1).Cells.Clear
Sheets(1).Range("A1:D1").Font.Bold = True
Sheets(1).Range("A1:D1").Font.Color = vbRed
Sheets(1).Range("A1") = "DATE"
Sheets(1).Range("B1") = "SMA"

URL = Sheets(2).Cells(x, 1)
objHTTP.Open "GET", URL, False
objHTTP.Send

If objHTTP.ReadyState = 4 Then
If objHTTP.Status = 200 Then

Set RetVal = MyScript.Eval("(" & objHTTP.responseText & ")")
objHTTP.abort

Set MyList1 = RetVal.result.buy
NoA = Sheet1.Cells(65536, 1).End(xlUp).Row + 1

For Each myData In MyList1
Sheets(1).Cells(NoA, 1).Value = myData.Last_Refreshed
Sheets(1).Cells(NoA, 2).Value = myData.SMA
NoA = NoA + 1
Next
End If
End If

Next

Set MyList2 = Nothing
Set MyList = Nothing
Set objHTTP = Nothing
Set MyScript = Nothing
End Sub

Please provide an example URL (you can obscure the API key bit) and indicate on which line the error occurs. — QHarr, Mar 23 '18 at 07:43
Sorry not sure why the url is showing up wierd in the question: https://www.alphavantage.co/query?function=SMA&interval=daily&time_period=90&series_type=close&apikey=ES1RXJ7VF1C1L9N5&symbol=MSFT — Yousuf, Mar 23 '18 at 15:39

QHarr · Accepted Answer · 2018-03-25T08:10:29.043

1

This will do it. Uses VBA JSON module and you need to add a reference to microsoft scripting runtime in vbe > tools >references

Option Explicit

Public Sub test()

    Dim objHTTP As Object
    Dim URL As String
    Dim Json As Object

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

    URL = "https://www.alphavantage.co/query?function=SMA&interval=daily&time_period=90&series_type=close&apikey=ES1RXJ7VF1C1L9N5&symbol=MSFT"
    objHTTP.Open "GET", URL, False
    objHTTP.setRequestHeader "If-Modified-Since", "Sat, 1 Jan 2000 00:00:00 GMT"
    objHTTP.Send

    Set Json = JsonConverter.ParseJson(objHTTP.ResponseText)("Technical Analysis: SMA")

    Dim key As Variant
    Dim counter As Long

    counter = 1

    For Each key In Json                         'loop items of collection which returns dictionaries of dictionaries

      Dim innerKey As Variant

      For Each innerKey In Json(key).Keys
          counter = counter + 1
         ActiveSheet.Cells(counter, 1) = key '
         ActiveSheet.Cells(counter, 2) = Json(key)(innerKey) ' innerKey
      Next innerKey

    Next key

End Sub

Results:

To test a list of URLs to see if valid see answer by @FlorentB here

Excel VBA script to find 404 errors in a list of URLs?

edited Mar 25 '18 at 08:10

answered Mar 23 '18 at 15:54

QHarr

83,427
12
54
101

Awesome, that worked. If i have a list of URLs and some of them cause errors, is there a way i first run a macro to show me which URLs cause an error? Or even if we can just print out "ERROR" onto Cells A1 and B1, that would work for me too. – Yousuf Mar 25 '18 at 03:58
Yes though that is a different question. If the above is answered please mark as accepted and/or upvote. I will see if I can dig out a prior answer I have given on the URL point. Also, pretty sure I have seen it answered elsewhere on SO. – QHarr Mar 25 '18 at 08:06
See answer by @FlorentB here https://stackoverflow.com/questions/36064315/excel-vba-script-to-find-404-errors-in-a-list-of-urls OR https://stackoverflow.com/questions/41576604/vba-script-to-to-get-urls-status-code – QHarr Mar 25 '18 at 08:09
Sorry for the delay, i forgot i needed to press the check mark. – Yousuf Mar 26 '18 at 02:05

Excel VBA JSON array url import and parse

1 Answers1