Downloading stock data from www.alphavantage.co into excel

Question

since the change from yahoo finance to not support automatic downloading anymore, I checked other sources and www.alphavantage.co seems to fit my requirements. However, the data does not arrive in excel. Did anyone out there programmed it already? The test link I am using is https://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=MSFT&apikey=demo&datatype=csv. It downloads the data into a csv file when opening it in a browser, but there is no data arriving in excel.

Many thanks in advance, Jan

Answer 1

Within VBA, select Tools > References and select the following references:

• Microsoft Script Control 1.0
• Microsoft Scripting Runtime

The following function will retrieve the most recent data (open, high, low, close, and volume) for a specific symbol:

Public Function GetLastCloseData(symbol As String) As Dictionary
    Dim scriptControl As Object
    Dim json As Object
    Dim time_series As Object
    Dim date_data As Object
    Dim date_label As String
    Dim date_offset As Integer

    Set scriptControl = CreateObject("MSScriptControl.ScriptControl")
    scriptControl.Language = "JScript"

    'Retrieve historical price data in JSON format
    With CreateObject("MSXML2.XMLHTTP")
        .Open "GET", "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol=" & symbol & "&apikey=" & API_KEY, False
        .send
        Set json = scriptControl.Eval("(" + .responseText + ")")
        .abort
    End With

    'CallByName returns an error if it cannot find the requested selection.
    'The code is written to skip over those errors.
    On Error Resume Next

    Set time_series = CallByName(json, "Time Series (Daily)", VbGet)

    'If time series property was found...
    If Not time_series Is Nothing Then
        date_offset = 0
        'Retrieve the most recent closing price by looking for todays date. If it's not found,
        'iterate through the last week of dates, stopping whenever the most recent is found.
        While date_data Is Nothing And date_offset < 8
            date_label = Format(DateAdd("d", -date_offset, Date), "yyyy-mm-dd")
            Set date_data = CallByName(time_series, date_label, VbGet)
            date_offset = date_offset + 1
        Wend
    End If

    If Not date_data Is Nothing Then
        Set GetLastCloseData = New Dictionary
        With GetLastCloseData
            .Add "Open", CDbl(CallByName(date_data, "1. open", VbGet))
            .Add "High", CDbl(CallByName(date_data, "2. high", VbGet))
            .Add "Low", CDbl(CallByName(date_data, "3. low", VbGet))
            .Add "Close", CDbl(CallByName(date_data, "4. close", VbGet))
            .Add "Volume", CLng(CallByName(date_data, "5. volume", VbGet))
        End With
    End If

    'set error handling back to normal
    On Error GoTo 0

End Function

The following Sub demonstrates how to use the results:

Public Sub GetStockData()
    Dim daily_data As Dictionary

    Set daily_data = GetLastCloseData("SPY")
    Debug.Print daily_data("Open")
    Debug.Print daily_data("High")
    Debug.Print daily_data("Low")
    Debug.Print daily_data("Close")
    Debug.Print daily_data("Volume")
End Sub

Output:

260 
260.15 
259.57 
259.76 
45033392