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:
- Yahoo! Finance doesn’t allow retrieval of historical prices for currencies, so this function wouldn’t work with currency tickers such as USDEUR=X.
- 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)…