Excel: Retrieve Stock Quotes with a Spreadsheet Formula
| Posted by financial-modelling.net under Excel |
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:
=StockQuote("MCD";DATEVALUE("2011-11-30"))
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:
Excel: "StockQuote" Formula
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:
Yahoo! Finance
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
Else
If Not (IsDate(dtDate)) Then
StockQuote = CVErr(xlErrNum)
End If
End IfDim dtPrevDate As Date
Dim strURL As String, strCSV As String, strRows() As String, strColumns() As String
Dim dbClose As DoubledtPrevDate = 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) & _
"&g=d&ignore=.csv"' Debug.Print strURL
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
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: " & dbCloseStockQuote = dbClose
Set http = Nothing
End Function
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.
Hello,
I ama private investor and used Excel Stock quote add-in which has now been discontinued.
I have copied your function StockQuote into an Excel file and it seems to work as a User defined Fuction but it returns #VALUE.
Is it still up to date?
Many Thanks for your time
Christopher Brewer
Thank you for mentioning this! There was indeed an issue with request dates, e.g., the following won't work:
http://ichart.finance.yahoo.com/table.csv?s=MCD&a=10&b=3&c=2012&d=10&e=4&f=2012&g=d&ignore=.csv
The reason seems to be that this date range only includes a weekend. This is unexpected, in a way, because the Yahoo! website itself automatically corrects such a request and shows data for the preceding Friday. The CSV web service apparently does not do that.
Hence, in the macro, it makes sense to look back for a full week:
dtPrevDate = dtDate - 7
That way you will download data for a full week and still just take the quote from the most recent date.
The article above is adjusted accordingly, and the code should work fine again.
If you encounter other errors, try to un-comment the lines with "Debug.Print"; that would help identifying errors.
Hello,
Thanks for the code, it works very well.
However some stock prices have three or more decimals, like ticker UCG.MI, but your code retrieves two decimals only.
Can the code be modified to get more decimals?
thanks
Rob
Not sure whether this is possible via the ichart-based URL without the API. It has nothing to do with the code, this is only a question of what Yahoo provides and what you need to enter in the URL parameters. If you find out how to retrieve 3 or more decimals, do post it here!
Found it and replaced your function as follows.
For more details, see webpage
http://www.gummy-stuff.org/Yahoo-data.htm
Function Shareprice(YahooTicker As String)
Dim strURL As String, strCSV As Double
' Compile the request URL with selected Yahoo Ticker & Yahoo Tag
' Example for Apple Inc. share price
' http://download.finance.yahoo.com/d/quotes.csv?s=AAPL&f=l1
strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & YahooTicker & "&f=l1"
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText
Shareprice = strCSV
Set http = Nothing
End Function
For an Excel VBA function that returns any parameter in the Google Finance XML stream try
http://www.philadelphia-reflections.com/blog/2385.htm
I have not been able to get either function to run.
The function StockQuote come up with a formula error (I cut and pasted so no typos).
For Shareprice, all I get is #VALUE! error. Thoughts? I am a newbie creating VBA scripts in Excel.
Running Office 2011 Mac. Thanks for any guidance. My goal is just to get today's share price into a cell.
Thank you for posting this. I was able to make it work with just one problem I can't seem to solve. When I get the price for the date NOW() and for NOW()-1, I get the same value (both return the correct value for yesterday's price). Other calls with NOW()-7, NOW()-30, etc. seem to retrieve correct values. Why am I unable to get today's price?
Thanks.
Works great! Thanks!!
It works for me! You are the best.
Hey thanks so much! This saved my day buddy
[...] process immediately in Excel without having to download each file seperately. From the website Financial-Modelling I modified the code to perform this fairly simple vba code. You can download the file which is [...]
This is pulling in the previous close if I put in today's date. It is 10PM eastern time currently. I am using date 4/24/2013.
AAPL comes in at 406.13, but that was the close on 4/23. It closed today at 405.46.
More noticeable, AT&T (ticker T) was down 5% today. Running =StockQuote("T",DATEVALUE("4/24/2013")) returns 39. T closed at 37.04 on 4/24. Previous close (on 4/23) was 39.
Is it possible for it to retrieve the current live price? I find like Mike above if I use eg. StockQuote("Goog") it returns the previous day's close price.
This is great!
Is there anyway to pull the yield and full name of the stock too?
How do make it auto-update / refresh eg when I open the file in Excel. Also how do I implement a button which I can click to update manually?
Hope someone can please help!
hello,
i had created a portfolio in Yahoo.http://in.finance.yahoo.com/portfolio/....1/view/..
in the past i could import the data into excel by the 'import from web' button. Now the querry returns a prompt saying that no data could be found. May be the data is now in a different format or java or something ?
Could you help?
thanks
This works great. Thank you very much for posting the module to accomplish this.
hi, I saved the macro as "StockQuote", but when I return to Excel sheet and put in the formula, it doesn't recognize the macro and says #NAME in the cell where I tried to call the function? In VB, I was able to name the project and of course the function to StockQuote, but it does still say that the Module is called Module1...Back in the worksheet where I called it, I don't see "StockQuote" or any macros under "View Macros"...thanks
Thanks! This is awesome! I seem to be getting a problem with historical splits. For instance, for AAPL on 1/30/2004, I get 22.56 when it should be half that for the split adjustment. Is this a Yahoo! thing? Or is there maybe a way to query historical splits?
Can someone explain why i cannot run the module? I've coppied the code into the vba module but when i try to run it, the module isn't visible in de MACRO's finder. Can someone give me a quick tutorial how to make it run?
tnx in advance