52

Importing Historical Stock Prices from Yahoo into Excel

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


52 Responses to "Importing Historical Stock Prices from Yahoo into Excel"

  1. Ann says:

    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.

  2. Gabriella says:

    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.

  3. Ed says:

    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!

  4. 85nora says:

    Hi, from spain, how can I get de vba password for this code,
    Thanks

  5. Fred says:

    Will this also work to download stock options? I have been having a terrible time locating a way to do this. Thank you.
    - Fred

    • Samir says:

      I’m currently preparing a spreadsheet that downloads option chains. Should be published soon

      • Edith says:

        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.)

  6. John Nordeen says:

    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!

  7. Bruno says:

    Unfortunately I get a VBA error when I use the spreadsheet in MS Excel for Mac 2011. Is there a work around?

  8. rozak says:

    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

  9. Bill Lucas says:

    I’m getting a ‘variable not defined’ error so the program breaks when I try to download yahoo finance date. Thoughts?

  10. PJH says:

    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?

  11. ME says:

    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?

  12. Shravan Shah says:

    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!

  13. A.B. says:

    I’d like to manipulate the code to run this with a list 18 stock symbols. How can I do this? Thanks!

  14. Alex says:

    Samir,
    how can I modify the code to be able to add frequency (d,w,m) to this single stock download spreadsheet (the way you realized it in your Multiple Stock Quote Downloader spreadsheet)?

    Thanks

  15. William says:

    Hi everybody,
    the data I import is always in the first cell.
    This is A1 for me: Date,Open,High,Low,Close,Volume,Adj Close
    A2: 2013-08-29,2.68,2.77,2.68,2.75,8200,2.75
    and so on.
    Did anybody encounter this problem / do you know how to solve it?

    Thanks a lot,
    William

    • Samir Khan says:

      I’m using Excel 2010. The spreadsheet works fine for me. The data is separated into columns and rows, not just squeezed into one cell.

      What version of Excel do you have?

  16. Gagan says:

    Hi Samir,
    The excel sheet doesn’t work anymore with yahoo finance. I am looking for stock quotes in excel in the Indian Market. It looks like stock quotes can now no longer be downloaded as csv from yahoo finance. Would appreciate if you can help

  17. Larry says:

    Samir,
    You are obviously a king at this. Back in 2012 (September 9), you said you were preparing a spreadsheet that would import option data.

    I was wondering if you had gone one further: using the multiple stock quote downloader, add code before the next ticker iteration that pulls the option data for that ticker and places it in columns H-O (Calls) and P-V (Puts). I have found code that will bring the data in, but am not savvy enough in VBA to make it mesh with the code you have already developed, which I really believe is a work of art.

    Thanks in advance for taking the time to read this and consider it for use.

  18. Bill says:

    Hi Samir,

    I am interested in downloading end of month exit prices for ALL Australian unlisted managed funds on a recurring basis. Do you know if this is possible? If so, how?

  19. Christoffer says:

    Hello!

    Very nice spreadsheet. But im looking for something much easier.
    I would like to get the only the last price in a cell for a stock.

    If anyone could help me out i be happy.

    Im looking for a function
    =getPrice(“ticker”)
    that would return the current price for the stock in that cell.

    Thanks!

  20. Ali Hassan says:

    Hi Samir,

    I wonder if you’ve developed a spreadsheet for retrieving option prices in Excel 2013? If yes, can you point me to where I can download it?

    Many thanks.
    Ali

  21. Michael says:

    Hello Samir:

    I noticed that the data is sorted in ascending order. Is there an easy way to put it into defending order by date?

    I also noticed one quirk, the end date data is appearing at the very end of the ascending data? Only the last record.

    Thanks you, I have found several others yahoo data downloaders on the web but none of them are as stable and thus reliable as yours.

    Thanks, Michael

  22. james says:

    Hi Samir,
    can you tell me how to change the code from daily price intervals to weekly and monthly price intervals? I am after weekly open and close date and monthly open and close data from yahoo. Your help is much appreciated.

    James

  23. James says:

    Hi Samir, on your multiple stock download spreadsheet I am trying to reorder teh output to show date, volume, open, high, low, close. I am new to this game, any help would be much appreciated.

    Thanks,

    James

  24. Gustav says:

    Hello Samir

    First let me say thanks, for making this spreadsheet. It is very useful for my mate and I when we analyse various companies.
    However, after i got the new version of Excel, the 2013 one, I havent been able to get the spreadsheet working. I get and error code, saying something like:

    Run-time error ’1004′:

    Unable to open
    http://chart.yahoo.com/table.csv=s=MSFT&a=10&b=22&c=2013&d=(
    The internet site reports that the item you requested could not be found. (HTTP/1.0 404)

    Do you have any idea how I can fix this? It would be such a loss to miss out on your spreadsheet!

  25. Lance says:

    Download utility for historical data is not working – some type of server error. I don’t think the VBA code for the actual qurl is matching the server from YAHOO’s webiste…

  26. Chris K says:

    Samir,

    As mentioned above, great job on all your spreadsheets. And thank you for posting.

    I was wondering why some tickers don’t work within your spreadsheets, for example: $DJUSCX (S&P Specialty Chemicals). Along with other sector/industry tickers.

    Is there any way to get the sector historical data on your spreadsheets?

    Best Wishes,
    Chris

  27. Theresa says:

    Hi Samir- This is so helpful. THANK YOU. I was able to download most of the indices I needed with your spreadsheet. However, I was not able to down historical prices for example for IYK. This is the ishares US consumer goods. Do you know why?

Leave a Reply

Submit Comment
What is 9 + 2 ?
Please leave these two fields as-is:
IMPORTANT! To be able to proceed, you need to solve the following simple math (so we know that you are a human) :-)
© 2014 Invest Excel. All rights reserved. XHTML / CSS Valid.

Facebook