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.
Furthermore, Financial-Modelling.net reader René has uploaded an instructional video that shows how to implement this script.
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
Hi,
Is there anyway we can get closing price for a particular date which is adjusted to split?
Hi,
With American numeric settings this macro works fantastic!
Unfortunately with my "," as decimal separator setting I tend to get the share prices x100. I think dividing every price by 100 is not always a perfect solution. Does anyone have a VBA solution?
thanks
Very nice. This function returns the actual close. I prefer to use the adjusted close (adjusted for splits and dividends paid). The change was pretty simple. Find the line
dbClose = strColumns(4)
change to
dbClose = strColumns(6)
Hope that helps
Thanks for this great macro! I simplified the program and removed the date input. So my version only outputs the current stock price ("l1"). I guess that is what Rupesh and Mike asked for further above. The input has to look like this: =StockQuote("ticker_symbol").
I had difficulties with this macro to show exchange rates (e.g. EURUSD=X). That's why I changed the url in my version to "download.finance.yahoo.com". Somehow there it works.
Here is the code:
Function StockQuote(strTicker As String)
Dim strURL As String, strCSV As String
Dim dbClose As Double
' Compile the request URL
strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & strTicker & _
"&f=l1"
' Debug.Print strURL
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", strURL, False
http.Send
strCSV = http.responseText
' Debug.Print strCSV
dbClose = strCSV
' Debug.Print vbLf
' Debug.Print "dbClose: " & dbClose
StockQuote = dbClose
Set http = Nothing
End Function
Another thing that I learned: The line
dbClose = strCSVshould be replaced with
dbClose = Val(strCSV)then the output value is independent of your locale, i.e. no matter whether you use a comma or dot as a number separator, the value will be as expected.
Rene, great edit! That is exactly what I was looking for out of this macro. Rene, what about refreshing?
Hi Mike,
I added some code (all the credit goes to Nick - http://excelexperts.com/vba-tips-run-code-every-hour-minute-or-second).
You can change the update interval (Line 8. It's set to 5 minutes right now). I wouldn't recommend to set a a very small time though. Yahoo seems to block too many requests from the same user. So if you have many ticker symbols and/or a short update interval the macro hangs for quite some time.
Another note: After copying the code into your Excel sheet, it might be necessary to save, close and reopen the file in order to start the auto update.
I uploaded a video on youtube for users, who are not so familiar with VBA. It's a step-by-step instruction on how to implement this macro into your Excel workbook:
http://youtu.be/3gfgZWy5778
Brilliant! I included the link in the article.
Rene, Thank you...Thank you... Thank you. I've been trying to replace an Open Office plug-in called "GetHistory" which also retrieved Yahoo historical stock prices as it quit working some time ago. Thank you also Jeff O for posting how to retrieve the adjusted closing prices also. Those are the only historical prices that mean anything to me. With much struggle I got it working on Excel 2013... a program that I hate along with Windows 8. Anyway, it works and it has simplified my life.
Thanks again,
Merle
Why is there no simple function to pull a stock price into excel such as google provides with its online spreadsheet (=finance(MSFT)).
I'm getting a #VALUE error when I use this...anyone know why? Im' using Rene's modified version.
Can't figure it out.. I added the Val(...)
the code works great, thank you! One question, I cant seem to get SPX to work. Yahoo finance has spx as the ticker ^SPXPM, and it errors out every time. Other futures such as ^VIX work just fine. Anybody have any idea how to fix this?
Thanks
Yeah, this doesn't seem to work on Mac Office 2011. I'm getting the same problem as Jerry Z, where the something is going wrong in the http connection & response, resulting in #VALUE.
[…] […]
This is great! I'm trying to modify the code to get either the High or the Close price without much luck.
Thanks
Dan, Use ^GSPC.
Rene, Thanks for the macro! It works great and was exactly what i needed.
Would you have any idea how to retrieve another piece of Yahoo Finance data for comparison?
There is a Field called "Mean Target" on the Analyst Opinion Tab within Yahoo Finance (http://finance.yahoo.com/q/ao?s=AAPL+Analyst+Opinion) that i'd like to compare to the stock prices to see which stocks have the most and least theoretical upside.
My Macro/VBA understanding is insufficient to figure out how to adapt your code to perform this additional function.
If you have any ideas, i'd be grateful.
(I have also been an investor for a long time and think it will help you to make money in stocks.)
Hello Michael,
there is just a limited amount of data which can be extracted from Yahoo (have a look at http://www.gummy-stuff.org/Yahoo-data.htm). Your "mean target" is not in this list, so I am afraid it won't be possible to get it into Excel (at least not with this type of macro).
Awesome Rene!
A couple of indexes I have tried don't work either like ^DJI and ^SSEC.
I'm trying to replace MyYahoo's stock reporting. How do I figure out what the fields like Daily change and percentage are, from yahoo finance?
Thanks ............... Bill
I like the function but was a little leery of using a function on my spreadsheet where I track historical info. I wouldn’t want to update a sheet that has values for a previous period. So I wrote a little macro that I will run once, it’ll update my sheet with the current values for the stocks. Then I can save the spreadsheet and if I open it six months later it won’t try to recalculate by sending the command to yahoo again.
The macro is based on the assumption that the ticker symbols start at cell A5; there can be as many as 40 symbols (blanks are skipped). The value returned by the function is put in the cell three columns to the right of the ticker symbol.
Here’s some sample data (so you can see the format I have):
Ticker Name Shares Price Total
CAH Cardinal Health, Inc. 100 69.73 $6,973.00
Z Zillow, Inc. 200 92.27 $18,454.00
T AT&T, Inc 300 35.09 $10,527.00
Sub UpdatePrice()
'
' Go down list of symbols and update price column
'
Dim Symbol As String
OrigAdd = ActiveCell.Address
Range("A5").Select
Symbol = ActiveCell.Value
For Count = 1 To 40
Do While Symbol ""
x = StockQuote(Symbol)
ActiveCell.Offset(0, 3).Value = x
ActiveCell.Offset(1, 0).Activate
Symbol = ActiveCell.Value
Loop
ActiveCell.Offset(1, 0).Activate
Symbol = ActiveCell.Value
Next Count
Range(OrigAdd).Activate
End Sub
Hope that helps someone.
-Larry
Sub UpdatePrice()
'
' Go down list of symbols and update price column
'
Dim Symbol As String
OrigAdd = ActiveCell.Address
Range("A5").Select
Symbol = ActiveCell.Value
For Count = 1 To 40
Do While Symbol ""
x = StockQuote(Symbol)
ActiveCell.Offset(0, 3).Value = x
ActiveCell.Offset(1, 0).Activate
Symbol = ActiveCell.Value
Loop
ActiveCell.Offset(1, 0).Activate
Symbol = ActiveCell.Value
Next Count
Range(OrigAdd).Activate
End Sub
Hello - would it be possible to use this data set to download TTM EPS data?
Hi Rene,
Your code works cool with NYSE, however i follow National stock exchange (NSE) and would like to get the quote for NSE.
The stock codes are available at Yahoo finance with the code names suffixed by .NS
eg "IDFC.NS" or "AXISBANK.NS" etc.
now the code does not work for them (maybe because of a dot in the code) so can you update the code to work with the other exchange codes available on Yahoo finance but suffixed by a DOT
iam using the below code for pulling historical data into excel from moneycontrol.com . for each page of the website its taking one sheet and a total of 8 to 10 sheets in excel because of the code written like that. i need little help from you, i want you to modify a little, that all the pages containing data should be imported in one sheet in excel only.
Private Const URL_TEMPLATE As String = “URL;http://www.moneycontrol.com/stocks/hist_stock_result.php?sc_id=RI&pno={0}&hdn=daily&fdt=2000-01-01&todt=2013-11-01″
Private Const NUMBER_OF_PAGES As Byte = 7
Sub test()
Dim page As Byte
Dim queryTableObject As QueryTable
Dim url As String
For page = 1 To NUMBER_OF_PAGES
url = VBA.Strings.Replace(URL_TEMPLATE, “{0}”, page)
Set queryTableObject = ActiveSheet.QueryTables.Add(Connection:=url, Destination:=ThisWorkbook.Worksheets.Add.[a1])
queryTableObject.WebSelectionType = xlSpecifiedTables
queryTableObject.WebTables = “3″
queryTableObject.Refresh
Next page
End Sub
I like Renee's idea with the auto-updater and Larry's idea of utilising an x=stockquote(symbol) and x=value of specified cell
But I would like to know if it is possible to somewhat combine the two and use prices fetched from yahoo...without having to type the full formula into excel each time.
E.g. I want to have the formula built into the cell, and all I have to type is the code AAPL which converts into the price (rather than typing: stockquote("aapl")
Is this possible??
Thanks so much guys, this macro has already made my days much easier!
Thank you. Both for the original article and code and for Rene's mods.
Microsoft really should provide a built-in function for such a commonly used data set as stock prices. And not just for the open close high low. But for much of the other parameters as well. Like P/E, PEG, bid, ask etc. For both current live and historical data.
Again thank you all for this very useful VBA macro.
Added option for specifying which value to return(strFieldName As String):
Date
Open
High
Low
Close
Volume
Adj Close
Usage:
=StockQuoteHistory("INTC","Adj Close",DATEVALUE("7-10-2014"))
Or using cells references to provide the parameters.
=StockQuoteHistory($A2,G$1,$B2)
Where:
Cell G$1 is a field name such as "Adj Close" (typically the column header)
Cell $A2 is a ticker symbol such as "INTC" (typically the row header)
Cell $B2 is a date such as 7/10/2014
Field Name defaults to "Close" if the parameter is missing or empty, like so...
=StockQuoteHistory("INTC")
=StockQuoteHistory("INTC",,DATEVALUE("7-10-2014"))
=StockQuoteHistory("INTC","",DATEVALUE("7-10-2014"))
Option Compare Text
Function StockQuoteHistory(strTicker As String, Optional strFieldName 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
StockQuoteHistory = CVErr(xlErrNum)
End If
End If
If IsMissing(strFieldName) Or strFieldName = "" Then
strFieldName = "Close"
End If
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) & _
"&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
' Field Positions Index
If (strFieldName = "Date") Then intFieldIndex = 0
If (strFieldName = "Open") Then intFieldIndex = 1
If (strFieldName = "High") Then intFieldIndex = 2
If (strFieldName = "Low") Then intFieldIndex = 3
If (strFieldName = "Close") Then intFieldIndex = 4
If (strFieldName = "Volume") Then intFieldIndex = 5
If (strFieldName = "Adj Close") Then intFieldIndex = 6
' 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 = Val(strColumns(intFieldIndex)) ' 4 means: 5th position, starting at index 0
' Debug.Print vbLf
' Debug.Print strRows(1)
' Debug.Print "dbClose: " & dbClose
StockQuoteHistory = dbClose
Set http = Nothing
End Function