Getting Yahoo! Finance data into Excel

Here’s a little VBA snippet that allows to retrieve Yahoo! Finance data into an Excel spreadsheet.

Usage:

=yfQuote(ticker, date, field), where

• ticker (required) is ticker symbol used by Yahoo! Finance. Examples: MSFT, ^GSPC, VXX.
• date (optional) is the date for which quotes are sought. If omitted, the most recent available historical quote is retrieved. Example: DATE(2014, 10, 11).
• field (optional) is the data field requested. If omitted, close price is returned. Allowed values: 1 (open price), 2 (high price), 3 (low price), 4 (сlose price), 5 (volume), and 6 (adjusted close price).

Public Function yfQuote(strTicker As String, _
    Optional dtDate As Variant, _
    Optional intField As Variant)

    ' dtDate is optional. If omitted, use today. 
    ' If value is not a date, throw an error.

    If IsMissing(dtDate) Then
        dtDate = Date
    Else
        If Not (IsDate(dtDate)) Then
            yfQuote = CVErr(xlErrNum)
        End If
    End If
    
    ' intField is optional. 
    ' If omitted, use 4 to retrieve closing price.

    If IsMissing(intField) Then
        intField = 4
    Else
        If Not (IsNumeric(intField)) _
               Or (intField > 6) _ 
               Or (intField < 0) Then
            yfQuote = CVErr(xlErrNum)
        End If
    End If
    
    Dim dtStartDate As Date
    Dim strURL As String 
    Dim strCSV As String 
    Dim strRows() As String 
    Dim strColumns() As String
    Dim dblResult As Double
    
    dtStartDate = dtDate - 7
    
    ' Compose the request URL with start date and end date

    strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & _ 
      strTicker & _
      "&a=" & Month(dtStartDate) - 1 & _
      "&b=" & Day(dtStartDate) & _
      "&c=" & Year(dtStartDate) & _
      "&d=" & Month(dtDate) - 1 & _
      "&e=" & Day(dtDate) & _
      "&f=" & Year(dtDate) & _
      "&g=d&ignore=.csv"
    
    Set objHTTP = CreateObject("MSXML2.XMLHTTP")
    objHTTP.Open "GET", strURL, False
    objHTTP.Send
    strCSV = objHTTP.responseText
    
    ' The most recent price information is in the second row; 
    ' the first row is the table headings.
    ' Order of fields:
    ' 0 -- Date
    ' 1 -- Open
    ' 2 -- High
    ' 3 -- Low
    ' 4 -- Close
    ' 5 -- Volume
    ' 6 -- Adj Close

    ' split the CSV into rows
    strRows() = Split(strCSV, Chr(10)) 

    ' split the most recent row into columns
    strColumns = Split(strRows(1), ",") 

    dblResult = strColumns(intField)
    yfQuote = dblResult
    
    Set objHTTP = Nothing

End Function

Notes:

1. Yahoo! Finance doesn’t allow retrieval of historical prices for currencies, so this function wouldn’t work with currency tickers such as USDEUR=X.
2. With some additional fiddling, the function could be persuaded to accept 0 to return the date for which quotes are retrieved. This could be useful when retrieving the most recent quote. As is, this wouldn’t work because of dblResult = strColumns(intField) (double value is expected, but a string is returned; there would have to be a conversion of that string into a date value)…