Excel Function to get stock price without Yahoo finance API

Yahoo finance API was free and helping so many people to get their things done. As per the internet forums many were doing business around that API. One of the best use case is to pull stock price to Excel spreadsheets.

Unfortunately the API is discontinued now. So what is the alternative? Its nothing but another free API. A promising one is Alphavantage. We have to get their API key before using the API. Its just free. No idea whether they throttle or make the service priced later. The web site is given below.

https://www.alphavantage.co/

Below goes the Excel VBA code for having an Excel Function which accept the stock symbol and returns the price.

Public Function StockQuote(strTicker As String) As String
    Dim key As String
    key = "<YOUR KEY FROM Alphavantage>"
    If IsMissing(strTicker) Then
        StockQuote = "No input"
        Exit Function
    End If
    Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
    Dim dbClose As Double

    'Compile the request URL with needed data.
    strURL = "https://www.alphavantage.co/query?function=TIME_SERIES_DAILY" & _
    "&symbol=" & strTicker & _
    "&interval=15min&outputsize=compact&datatype=csv&" & _
    "apikey=" & key
    
    On Error GoTo catch
        Set http = CreateObject("MSXML2.XMLHTTP")
        http.Open "GET", strURL, False
        http.Send
        strCSV = http.responseText
    
        ' The most recent information is in row 2, just below the table headings.
        ' The price close is the 5th entry
        strRows() = Split(strCSV, Chr(10)) ' split the CSV into rows
        strColumns = Split(strRows(1), ",") ' split the relevant row into columns. 1 means 2nd row, starting at index 0
        dbClose = strColumns(4) ' 4 means: 5th position, starting at index 0
        StockQuote = dbClose
        Set http = Nothing
        Exit Function
catch:
        MsgBox (Err.Description)
End Function

Thanks to the original coder who posted the snippet for Yahoo API.