This Excel spreadsheet imports historical stock prices from Yahoo Finance (http://finance.yahoo.com). You don’t need to manually import data with text files. You simply need to specify a ticker symbol, your start and end dates, and then click a button.
Once you click the button, Excel (after calling a VBA function) connects to Yahoo Finance and retrieves daily stock quotes, placing the data into a sheet called “Data”
Currently, the spreadsheet imports the date (in day/month/year format), opening price, daily high, daily low, closing price, trading volume and the adjusted close. All prices are in US dollars.
You’re free to manipulate and analyze the data once it’s in Excel. As an example of some trivial analysis, clicking a button in the spreadsheet gives the arithmetic mean, the variance and the standard deviation of the daily returns (based upon the daily closing price).
You could also perform far more advanced analyses (which I’ll discuss in future blog posts). For example, I’ve used this variations of this spreadsheet to calculate investment ratios (e.g. Sharpe, Sortino and Treynor Ratios), plot histograms of daily returns to check if they’re normally distributed, identified stocks with my desired risk characteristics, and calculate mean-variance optimal portfolios.
The VBA that retrieves the data from Yahoo was adapted from code found at mathfinance.cn. It can be modified to download other data found on the web.
You’ll need to enable macros before you use the spreadsheet (Excel may warn you that you’re importing a macro-enabled worksheet).
UPDATE July 17th 2011: Here’s an Excel spreadsheet that downloads historical Forex rates for two currency pairs between two dates
UPDATE 18th August 2011: Here’s a Mathcad worksheet to download stock quotes from Yahoo Finance
UPDATE 14th February 2013: Download historical quotes into Excel for multiple stock tickers here
Download Excel spreadsheet to import historical stock prices from Yahoo
Thank-you for posting these. I wrote a VBA program to get Yahoo data into a spreadsheet a couple of years ago but it was lost in a hard drive disaster and I forgot how it worked.
I have spent this day trying to get straight information from the web about working with stocks and portfolios in excel but preoccupation with finance does not really bring out the generous side of people. Most things have a fee and behind that another fee if you want the good features.
I would like to see the download macros if you don’t mind.
The VBA in the spreadsheet is already exposed – you don’t need a password
Thank you for offering this download. Unfortunately I get an compile error and the debugger opens. I am using excel 2002 and am in the UK looking for LSE stocks. I was trying to use the spreadsheet for importing the historical stock prices.
Hi Gabriella, I’ve just tried the spreadsheet in Excel 2010 and it works as expected. I don’t have Excel 2002 so I can’t test whether it works in that version so I’ll take your word for it.
Do you have access to a later version of Excel?
Can you try this version?
Thanks Samir, Its perfect. I will enjoy trying it out and thank you for a quick response.
Is there a reason that the code for the 2002 version is passworded?
There’s no reason for it to have a password (can’t remember why I put one on there). Here’s a version with no password: Yahoo Stock Quotes Excel 2002
Is there a way that this can easily be modified so that it will:
1) run every day at 5 pm?
2) fetch historical data from a point from the past up to today’s date?
3) fetch data for multiple stocks at once?
4) and then insert that data into sheets titled as the stock’s ticker symbol?
Thanks! I’m new and hard at work trying to analyze stocks in my own way via excel. Any help helps!
Yes to questions 1 to 4. You just need to know some VBA to automate everything.
can you recommend the place to go to learn this specific concept??
Everything you need is on the web (I’ve never read a book). Just Google ‘vba tutorial’ (it’s how I learned). Remember to start small, and work your way up from there.
Hi, from spain, how can I get de vba password for this code,
Thanks
The Yahoo finance spreadsheet doesn’t have a password, you should be able to view the VBA.
Will this also work to download stock options? I have been having a terrible time locating a way to do this. Thank you.
- Fred
I’m currently preparing a spreadsheet that downloads option chains. Should be published soon
Thanks for the very nice macro to import historical stock quotes from Yahoo into Excel. You have made it very accessible. I am looking forward to your options spreadsheet – please post it here! (Note: the file I downloaded did not require a password. Any VBA error was due to commands not available in earlier versions of Excel – easy to fix.)
This is great! But how can I make it so that I do not need to click the “Get Data from Yahoo” button each time I enter a ticker symbol? I want to be able to run a bunch of ticker symbols through quickly without having to hit the button every time. Thanks!
Unfortunately I get a VBA error when I use the spreadsheet in MS Excel for Mac 2011. Is there a work around?
Hi… how can I have in Excel Spread Sheet format the open, close, high, low, time and date from 1992 to date. In other words, how can I import EUR/USD graph on MT4 Platform into Excel cells?
Thanks
I’m getting a ‘variable not defined’ error so the program breaks when I try to download yahoo finance date. Thoughts?
Many thanks for this! I notice that it’s not possible to get the data from Yahoo Finance for commodities – do you know if there is a way to get the exact equivalent for commodities from somewhere else?
Hello,
When i try to compute to cells, after I have gotten the stock data, it just says value. And when I try to compute some of the data with other cell, that I have put in, it doesent seems to calculate it right. What to do?
It’s not clear what the problem is. The spreadsheet works as expected for me, and I can perform calculations with the data
Hi, the file does not seem to be working for stock indices. Is there any way this could be made to work? Really desperate for this! Thank you!
Stock indices like the S&P 500? Try using the appropriate Yahoo Finance ticker symbol (for the S&P500 it’s ^GSPC).
I’d like to manipulate the code to run this with a list 18 stock symbols. How can I do this? Thanks!
Look at http://investexcel.net/3878/multiple-stock-quote-downloader-for-excel/
Samir,
Do you know why Yahoo historical prices are not up to date anymore? It usually updates at the end of every day but for some reason the last day of price for a symbol like jpm is 4/16/13.
Thanks