How to pull "Last Trade Date" data from Yahoo! Finance API within Excel?

Question

Using Visual Basic, I am attempting to pull "Last Trade Date" data from the Yahoo! Finance API for CSV by referencing a stock's ticker symbol within Excel.

The Yahoo! Finance API last trade date is appended to the URL as "&f=d1" with ticker being substituted for whatever stock exchange symbol is passed to StockQuote() within Excel (i.e. GOOG, GE).

When entering a ticker symbol into a cell and, in turn, referencing that cell with StockQuote(), I am receiving a #VALUE! error.

#VALUE! error screenshot from Excel

I realize that using the As Double data type for the StockQuote() function may not be appropriate for retrieving date data. Replacing Double with the Integer, Long, and Date data types within my current block of code have presented a #VALUE! error as well.

I am working in a macro-enabled sheet and have placed the Visual Basic code inside a module.

Will someone explain the source of the #VALUE! error and how I might revise the code below to retrieve the last trade date for a given ticker symbol when utilizing the Yahoo! Finance API?

Option Explicit

Function StockQuote(ByVal ticker As String) As Double
Dim url As String
Dim lastDate As Double
Dim http As Object

url = "http://download.finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=d1"

Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", url, False
http.Send

StockQuote = http.responseText
Set http = Nothing
End Function

Tim Williams Tim Williams · Accepted Answer · 2015-11-08T23:49:59

Calling your function from a Sub instead of from the worksheet will give you a better idea of what's going wrong.

In this case the error is "Type Mismatch" on the line:

StockQuote = http.responseText

This is because (as you suspected) you've flagged the function's return type as Double, whereas http.responseText is of type String. Note that the return value actually contains quotes so you might want to remove those.

Function StockQuote(ByVal ticker As String) As Double
    Dim url As String
    Dim lastDate As Double
    Dim http As Object, rv

    url = "http://download.finance.yahoo.com/d/quotes.csv?s=" & ticker & "&f=d1"

    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", url, False
    http.Send

    rv = http.responseText
    StockQuote = CDate(Trim(Replace(rv, """", "")))

    Set http = Nothing
End Function

How to pull "Last Trade Date" data from Yahoo! Finance API within Excel?

#VALUE! error screenshot from Excel

1 Answers