Some financial models need to reference stock quotes at certain dates. Instead of using those as hard-coded input data, you can create an Excel formula that will retrieve stock prices for a given date.
This tutorial uses the concept of user-defined functions (UDF) - for an introduction, read this article. Long story short, you can develop a VBA function that is usable as an Excel spreadsheet formula.
The code in this tutorial allows you to retrieve stock prices from Yahoo! Finance. Once included, you can use the formula as follows:
In this example, "MCD" is the stock exchange ticker symbol for McDonald's, and DATEVALUE("2011-11-30") means that you want the quote for 30 November 2011. (Instead of DATEVALUE, you can also just include a reference to a cell that contains a date.) The formula will return the actual price close, unadjusted for splits or dividends:
To find out which ticker symbol to use, go to the Yahoo! Finance website and type in the name of the company you would like to analyse. The ticker symbol is shown on the left:
The code, which you need to put in a VBA code module, is as follows:
Function StockQuote(strTicker As String, Optional dtDate As Variant)
' Date is optional - if omitted, use today. If value is not a date, throw error.
If IsMissing(dtDate) Then
dtDate = Date
If Not (IsDate(dtDate)) Then
StockQuote = CVErr(xlErrNum)
Dim dtPrevDate As Date
Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
Dim dbClose As Double
dtPrevDate = dtDate - 7
' Compile the request URL with start date and end date
strURL = "http://ichart.finance.yahoo.com/table.csv?s=" & strTicker & _
"&a=" & Month(dtPrevDate) - 1 & _
"&b=" & Day(dtPrevDate) & _
"&c=" & Year(dtPrevDate) & _
"&d=" & Month(dtDate) - 1 & _
"&e=" & Day(dtDate) & _
"&f=" & Year(dtDate) & _
' Debug.Print strURL
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
strCSV = http.responseText
' Debug.Print strCSV
' 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
' Debug.Print vbLf
' Debug.Print strRows(1)
' Debug.Print "dbClose: " & dbClose
StockQuote = dbClose
Set http = Nothing
As pointed out by readers, Yahoo will automatically only deliver prices with two decimals with the URL used in this code. This page provides documentation for some interesting additions you can use.
Furthermore, Financial-Modelling.net reader René has uploaded an instructional video that shows how to implement this script.