•  
  •  

How to Import Yahoo Finance Data into Excel

Unfortunately the Excel spreadsheets no longer work as market data has ceased to be available at the expected Yahoo URL.This post wil be removed soon.

Yahoo Finance is one of the biggest and most popular finance sites worldwide. This is in no small part due to their generosity in the amount of data they make available for free. They provide both historical and live market data although some live data is delayed for up to twenty minutes. A question often asked is “How do I get market data from Yahoo Finance into a spreadsheet?”. This article provides two example, free to download Excel files that show you how. Both use macros which must be enabled for them to work.

Live Updates

LiveUpdates.xls* downloads and displays a variety of live data fields (e.g., Open, High, Low, Last Trade (Price Only), Volume, 1 yr Target Price) for up to one hundred securities. Data can be downloaded as a one off or periodically. Click on the image below to view in high resolution.

LiveUpdates.xls

LiveUpdates.xls

How to Use

1. Populate the symbol column with the required stock tickers including exchange. e.g., BHP.AX. Note, the spreadsheet is limited to one hundred symbols.
2. Select up to six live data fields. To specify a data field click a blue column header and a drop down control will appear. Select the required field from the drop down. To see the full list of available fields click on the ‘Show Fields’ check box. Uncheck the check box to hide the fields.
3. To get a one off update click the ‘Manual Update’ button.
4. To get periodic updates first select the required ‘seconds to next update’ then click ‘Start’. Click the ‘Stop’ button to stop automatic updates.

LiveUpdates.xls can be downloaded via the following link. If you find it useful let us know using the comment form below. Please be aware of our comments policy.

Historical Data

HistoricalData.xls* downloads and displays historical market data. The data fields loaded are Date, Open, High, Low, Close, Volume, and Adjusted Close for the selected security and date range.

HistoricalData.xls

HistoricalData.xls

How to Use

1. Input the required stock ticker including exchange. e.g., BHP.AX.
2. Input the required start and end dates. Note, Start Data must be before End Date and End Date must be before today.
3. Click the ‘Load Yahoo Data’ button to download the data into the spreadsheet.
4. To save the data in comma delimited format click the “Save As’ button. You can then save the data to a location of your choice.

Note, Data may not be available for the entire input range.

HistoricalData.xls can be downloaded via the following link. If you find it useful let us know using the comment form below. Please be aware of our comments policy.

*LiveUpdates.xls and HistoricalData.xls are provided free of charge as examples only and come with no warranty. Yahoo Finance may change its interfaces at any time causing them to cease to work.

7 Responses to “How to Import Yahoo Finance Data into Excel”

  1. Richard Stack says:

    In the Live Updates spreadsheet, change https://finance.yahoo.com to the
    following in the macro……

    http://download.finance.yahoo.com

    Save and then exit and restart the spreadsheet, downloads should work OK

  2. Jorge Marquez says:

    Trying to run the historical worksheet, but am getting an error “can’t find project or library”, am I doing something wrong?

    • Mal Moore says:

      Hi Jorge, No you’re not doing anything wrong. It seems that Yahoo has moved their data. Unfortunately that means the spreadsheet will no longer work.

  3. mike says:

    When I change the blue drop down fields they don’t populate when I click on manual update. Any suggestions – thanks

    • Mal Moore says:

      Hi Mike,

      This usually happens when Yahoo either does not support that security or does not provide that specific data for the given security.

  4. Doreen says:

    Hi there,

    Love this spreadsheet. But .. how do I unprotect it so that I can add columns such as number of stock, date of purchase/sale etc?

    Cheers, D’

    Everyone you meet is fighting a battle you know nothing about.
    Be kind.

    • Mal Moore says:

      Hi D,

      To add extra columns to LiveUpdates.xls just show the row and column headers and unhide the hidden columns. You can then customize it to your requirements.

      To show row and column headers:

      In Excel 2003 select the Tools/Options menu, then on the View tab select the ‘Row & column headers’ check box.

      In Excel 2010 select the File tab, then Options, Advanced. Scroll down to find ‘Display options for this worksheet’. Select the ‘Show row and column headers’ check box.

      To unhide hidden columns:

      Left mouse click on the right most column (‘M’) and drag to the right. Then right mouse click on the selected column and select ‘unhide’ from the popup menu.

      Cheers

Leave a Reply to mike