AlphaVantage Quote Retrieval

So I'm trying to pull closing stock quotes from Alpha Vantage which seems pretty simple. They provide nice API examples. The problem is that my VBA code fails with:

VBAErrorMsg.png

Cut down to a minimum toy example the code below is what I'm trying to do. You'll note that for debug purposes, at one point I save my "qurl" query out to the query sheet. And if I copy/paste that URL into a web browser it works fine so I'm assuming there is no problem in the URL itself, but with the VBA call for the data. Yet the code always crashes pointing to the "Refresh" line. (Changing it to "true" doesn't help - that keeps it from crashing but it spins its wheels indefinitely then.)

Any suggestions to make this run? Thanks.

Bill

Sub AlphaCurrentQuote(Symb As String, AdjCloseQ As Single)
'Retrieve the current closing quote from Alpha Vantage
    Dim APIkey      As String
    Dim QuerySht    As Worksheet
    Dim DataSht     As Worksheet
    Dim qurl        As String
    
    Set DataSht = ActiveSheet
    Set QuerySht = Sheets.Add
    APIkey = Range("Notes!C2")
    
    qurl = "http://www.alphavantage.co/query?function=TIME_SERIES_DAILY_ADJUSTED&symbol=" & Symb & _
            "&outputsize=compact&apikey=" & APIkey & "&datatype=csv"
            
    QuerySht.Cells(1, "K") = qurl        'Debug info: Save the url for examination
            
    With ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl, Destination:=QuerySht.Range("A1"))
        .BackgroundQuery = True     
        .TablesOnlyFromHTML = False
        .Refresh BackgroundQuery:=False  
        .SaveData = True
    End With

Here is an alternative way to get stock prices

https://office-watch.com/2016/excel-...oogle-finance/

I'm also looking for a way to use Alpha Vantage as described in the original post. I got the same error message, which I think indicates that it cannot be done. There are a few reasons I don't want to use Google.

I'll post here if I find a solution, but I may end up downloading the csv file, opening the new file, connecting to a database with VBA, transfering the data, and deleting the recently-downloaded file.

Yahoo cut us off from their API and I'm concerned Google will do the same. I think Alpha Vantage will be more reliable over a longer period of time.

I've seen that code before --- I think it's applicable to json outputs, correct? I tried the following and got no results.

Public Sub TEST_httprequest()

Dim http As Object

    Set http = CreateObject("MSXML2.XMLHTTP")
    With http
        .Open "GET", BuildURLfromAPIParameters("BA", DailyAdjusted), True
        .Send
    End With

End Sub

...where BuildURLfromAPIParameters is a function that returned a URL (I tested to verify it returns the proper URL).

I also tried .Open "GET", BuildURLfromAPIParameters("BA", DailyAdjusted), False (changing the last boolean argument).

How can I dump several historical values into excel?

Thanks. I am following WiseOwl tutorials on Youtube and I'll post any meaningful results.