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

UPDATE 5th January 2014: I’ve just evaluated a financial data add-in for Excel MarketXLS. It’s a rock-solid route to getting live stock data inside Excel. You just use one of 80 new functions together with your chosen ticker. Disclaimer: the link to MarketXLS is an affiliate link, but this has not influenced my opinion of the tool.

UPDATE July 6th 2017: The old Yahoo Finance API was discontinued, so the spreadsheet stopped working for a while. However, I’ve now uploaded a new working version

Download Excel spreadsheet to import historical stock prices from Yahoo


91 thoughts on “Importing Historical Stock Prices from Yahoo into Excel

  1. Hi Samir –

    Great piece of code, very helpful. While I’m updating stocks, I’m also trying to update the CADUSD=X exchange at the same time, similar to the stocks, why is this not working? Do you know if Yahoo Finance has specific codes for exchange rates (I thought the CADUSD=X would do it).

    Cheers, and thanks again

    Peter

  2. Hi Samir –

    Great code and thanks for making it available. I was wondering if there is a way to download an exchange rate historically through this code? I thought it would work to just use the code CADUSD=X, in place of one of the stock codes, but it didn’t. Can you advise? Does it require another code to just get an exchange rate? Is there a way to get it into the same format as this code/

    Thanks again,
    Peter

  3. Hi Samir,
    I used to love your API version of this spreadsheet, and I’m glad to see you’ve built a non-API version. You really know your excel kung-fu!
    Unfortunately this one doesn’t seem to work on mac though. It trips at the sub:

    For i = 0 To 5 ‘ask for a valid crumb 5 times
    Set objRequest = CreateObject(“WinHttp.WinHttpRequest.5.1”)

    Any suggestions?

    Robin

  4. Dear Samir, Your webpage is just amazing, and I was impressed. …

    II was however trying to retrieve financial data for various tickers and in some of them the macro failed (eg. SREN.VX did not work).

    Ticker SREN.VX works well in yahoo (https://finance.yahoo.com/quote/SREN.VX/history?p=SREN.VX)
    Clicking the data-download button we get the respective csv file, whose associated URL was
    https://query1.finance.yahoo.com/v7/finance/download/SREN.VX?period1=1515244396&period2=1517922796&interval=1d&events=history&crumb=LYzrW9HVaUc

    When inserting SREN.VX in your wbook and running the data-download, no data is retrieved… and the macro gets stuck. Your macro builds the following url :
    https://query1.finance.yahoo.com/v7/finance/download/SREN.VX?period1=1486339200&period2=1517875200&interval=1d&events=history&crumb=dsMCdw2AwRL
    .. while it looks perfectly fine, however when inserted in a browser, the output is no CSV file but the following error message:

    {
    “finance”: {
    “error”: {
    “code”: “Unauthorized”,
    “description”: “Invalid cookie”
    }
    }
    }

    I do not know exactly what may be the issue… it seems yahoo is not sourcing data as expected, and then you cannot load it in the nice template you build.

    (curiously SSREY works perfectly fine, and SSREY is the ADR-version of SREN.VX !)

  5. Hello Samir ,

    I was downloading some monthly stock data. I noticed the 1st month row download has been filled with an error message “null”.

    I use the endDate =today()

    Then I use the begDate = endDate – ( 5*365).

    I get the error message “null”

    However If I use begDate = 1-1-2013 then it works
    So it seems your code only works right if you set the begDate to the 1st day of whatever date you use ?

    I am only trying to be supportive in letting you know and to be helpful to you.

    Samir,

    Thank You.

  6. Hi Samir
    Your website and sheets seem great. I just wanted to ask if there had been a change in Yahoo API perhaps, as I have downloaded a few of your sheets, but non of them seem to be able to pull data from Yahoo. Perhaps there is a fault on my side, but would be great to know if it is still supposed to be working.

    cheers
    Nick

Leave a Reply

Your email address will not be published. Required fields are marked *

What is 4 + 5 ?
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) :-)