Store JSON results into an array excel vba

Question

I have tried using API for get some information for Yahoo Finance And this is the UDF that I created

Sub Test()
'1 >> High & 2 >> Close
MsgBox YahooHigh("GOOG", "2019-07-18", 1)
MsgBox YahooHigh("GOOG", "2019-07-18", 2)
End Sub

Function YahooHigh(sTicker As String, sDate As String, idx As Integer)
Dim json As Object

With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "GET", "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & sTicker & "&outputsize=full&apikey=myapikey"
    .Send
    Set json = JsonConverter.ParseJson(.responseText)
End With

If idx = 1 Then
    YahooHigh = json("Time Series (Daily)")(sDate)("2. high")
ElseIf idx = 2 Then
    YahooHigh = json("Time Series (Daily)")(sDate)("4. close")
Else
    YahooHigh = Empty
End If
End Function

The UDF works fine but of course I will have to load the JSON result each time. As in my example, the UDF will run for twice the first for High value and the second for the Close value Is there a way to store the json results into an array then instead of loading the json, the array is called. I thought of static but I am stuck at this

What I would like to do is to store all the dates in the json results for specific ticker (High value and Close value only) then to recall the desired value from the static array .. Any ideas?

Another variation: I have tried using the HTML content and it works fine for me when using the link directly

Sub MyTest()
Dim html As Object, ele As Object

With CreateObject("MSXML2.ServerXMLHTTP")
                 'https://finance.yahoo.com/quote/GOOG/history?period1=1325566800&period2=1325566800&interval=1d&filter=history&frequency=1d
    '.Open "GET", "https://finance.yahoo.com/quote/GOOG/history?period1=1325566800&period2=1325566800&interval=1d&filter=history&frequency=1d", False
    Dim sTicker As String
    sTicker = Sheets(1).Range("B1").Value   'GOOG

    Dim period1 As Long, period2 As Long
    period1 = ToUnix(Sheets(1).Range("B2").Value) '3 Jan 2012
    period2 = ToUnix(Sheets(1).Range("B3").Value) '3 Jan 2012

    .Open "GET", "https://finance.yahoo.com/quote/" & sTicker & "/history?period1=" & period1 & "&period2=" & period2 & "&interval=1d&filter=history&frequency=1d", False
    .Send

    If .Status <> 200 Then MsgBox "Problem" & vbNewLine & .Status & " - " & .StatusText: Exit Sub

    Set html = CreateObject("htmlfile")
    html.body.innerHTML = .responseText

    'WriteTxtFile html.body.innerHTML

    'Stop

    Set ele = html.getElementsByTagName("table")(0).getElementsByTagName("tr")(1)

    Dim tCell As Object
    Dim cnt As Long

    For Each tCell In ele.Children
        cnt = cnt + 1

        If cnt = 3 Then Debug.Print "High: " & tCell.innerText
        If cnt = 5 Then Debug.Print "Close: " & tCell.innerText
    Next tCell
End With
End Sub

Public Function ToUnix(dt) As Long
ToUnix = DateDiff("s", "1/1/1970", dt)
End Function

When using this line .Open "GET", "https://finance.yahoo.com/quote/GOOG/history?period1=1325566800&period2=1325566800&interval=1d&filter=history&frequency=1d", False it works fine and returns values from High and Close But when trying to convert the dates from the worksheet to UNIX so as to use them in the link, it doesn't work This is the problem for me now

I'm voting to close this question as off-topic because the initial problem has completely changed, with a different quote provider now being referenced. — Ron Rosenfeld
– Ron Rosenfeld, Commented Jul 19, 2019 at 18:32
This is the same target. Using API returns the results from Yahoo Finance. — YasserKhalil
– YasserKhalil, Commented Jul 19, 2019 at 18:43
I am sorry for that... I have no great idea. I was just trying to learn something new. — YasserKhalil
– YasserKhalil, Commented Jul 19, 2019 at 19:00

Ron Rosenfeld · Accepted Answer · 2019-07-19 14:57:29Z

1

Just have your function return the json object, then parse it in your sub. The json object will contain all your data, and you can parse out what you want.

For example

In your function:

Function YahooHigh(sTicker As String) as object
Dim json As Object

With CreateObject("WinHttp.WinHttpRequest.5.1")
    .Open "GET", "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & sTicker & "&outputsize=full&apikey=myapikey"
    .Send
    Set YahooHigh = JsonConverter.ParseJson(.responseText)
End With

and in your Sub:

 Sub Test()
  Dim obj As Object
Set obj = YahooHigh("GOOG")

MsgBox obj("Time Series (Daily)")("2019-07-18")("2. high")
MsgBox obj("Time Series (Daily)")("2019-07-18")("4. close")

End Sub

answered Jul 19, 2019 at 14:57

Ron Rosenfeld

62k7 gold badges35 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

16 Comments

YasserKhalil Over a year ago

Thank you very much. I thought of that but I am not sure if this will be faster with searching for a lot of dates.

YasserKhalil Over a year ago

Another point when I tried MsgBox obj("Time Series (Daily)")("2009-04-21")("2. high") I got error, it seems that date is not in the JSON results. How can I fix that problem?

Ron Rosenfeld Over a year ago

@yasserkhalil you'll need to take that up with alphavantage. That may be a limitation as to how far back the time series goes. Or maybe a change in your API called convention that.

YasserKhalil Over a year ago

You mean this is because free API ..? And If I bought API I could get more results?!

Ron Rosenfeld Over a year ago

@YasserKhalil So far as searching for a lot of dates, it'll certainly be faster than multiple calls. Why would you think otherwise?

|

Collectives™ on Stack Overflow

Store JSON results into an array excel vba

1 Answer 1

16 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

16 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related