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, Jul 19 '19 at 18:32
This is the same target. Using API returns the results from Yahoo Finance. — YasserKhalil, Jul 19 '19 at 18:43
Have a look at this [LINK](https://stackoverflow.com/questions/52234348/excel-vba-web-source-code-how-to-extract-multiple-fields-to-one-sheet/52235053?noredirect=1#comment100731606_52235053) to find the relationship — YasserKhalil, Jul 19 '19 at 18:45
I am sorry for that... I have no great idea. I was just trying to learn something new. — YasserKhalil, Jul 19 '19 at 19:00

score 1 · Accepted Answer · answered Jul 19 '19 at 14:57

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 '19 at 14:57

Ron Rosenfeld

53,870
7
28
60

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 Jul 19 '19 at 15:03
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? – YasserKhalil Jul 19 '19 at 15:04
@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. – Ron Rosenfeld Jul 19 '19 at 15:07
You mean this is because free API ..? And If I bought API I could get more results?! – YasserKhalil Jul 19 '19 at 15:12
@QHarr this is the question that you told me to post as for the API restriction .. – YasserKhalil Jul 19 '19 at 16:22
1

@YasserKhalil So far as searching for a lot of dates, it'll certainly be faster than multiple calls. Why would you think otherwise? – Ron Rosenfeld Jul 19 '19 at 16:43
Thanks a lot. The problem is not with the search or calls. The problem is with the results of JSON that returned don't include all the dates. I have tried to read at the API website but I can't get a lot. And I am lost. – YasserKhalil Jul 19 '19 at 16:47
@YasserKhalil I suspect that the quotes of `GOOG` returned are probably only from around the time that Alphabet and Google merged. On at least one historical site, the quotes did not go back that far. But if you query for `MSFT`, you can go back quite a bit further -- 1998-01-02. The GOOG quotes only go back to 2014-03-27 – Ron Rosenfeld Jul 19 '19 at 17:02
@YasserKhalil AS I wrote above, you'll need to take it up with alphavantage about the historical quotes for GOOG, and why it only goes back to 2014. – Ron Rosenfeld Jul 19 '19 at 17:06
@YasserKhalil and Google did do a funny kind of split on 2014-03-27 which resulted in different classes of stock. That is probably related to the historical quote length of GOOG – Ron Rosenfeld Jul 19 '19 at 17:10
What about this [URL](https://finance.yahoo.com/quote/GOOG/history?period1=1325566800&period2=1325566800&interval=1d&filter=history&frequency=1d). The `GOOG` returns to 2012 – YasserKhalil Jul 19 '19 at 17:53
@YasserKhalil What about it? Your code is not accessing that URL. – Ron Rosenfeld Jul 19 '19 at 17:57
@ Ron Rosenfeld I am talking about the results of JSON files that are returned from the API >> this date 2012 for this quote GOOG is not there. That's the point I am talking about – YasserKhalil Jul 19 '19 at 18:06
@ Ron Rosenfeld I have updated the main post with new code that I have a problem in. The problem is with the unix format – YasserKhalil Jul 19 '19 at 18:24
@YasserKhalil And as I have written before, **this is an issue you will need to take up with the provider:** alphavantage – Ron Rosenfeld Jul 19 '19 at 18:31
@YasserKhalil Your new code is using an entirely different quote provider. Your new problem bears no relation to your original problem. You should be posting a new question. – Ron Rosenfeld Jul 19 '19 at 18:33

Store JSON results into an array excel vba

1 Answers1