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