Yahoo Finance API
How to Import Share Price Data into Excel (with Yahoo! Finance)
IMPORTANT: On 2 November 2017, Yahoo blocked access to the CSV API. No solution is yet
known.
Editor's Note:
This software is non-commercial and for personal use only. No support is available.
Yahoo! and Google both unofficially provide a free way to download stock quotes using an API.
Yahoo! Finance
Financial data can be downloaded in a CSV, imported into an existing Excel Spreadsheet, or downloaded in XML /
JSON formatted data to display on a website using the Yahoo! Query Language (YQL) API.
Google Finance
Share price data can be imported into an existing Excel spreadsheet or downloaded in a JSON string for use on a
website.
This article will cover two uses of the Yahoo! Finance API.
1. How to download Share Price Data in a CSV
2. How to Import Share Price Data into an Excel Spreadsheet
Disclaimer
Displaying share price data on a website (even delayed data) usually requires a license from the relevant stock exchange/s.
The Google Finance API was officially deprecated in June 2011. It may cease to work at any time.
The Yahoo Finance API is not officially supported and prone to “glitches” and inaccurate data. Use for commercial reasons may result in a breach of Yahoo!’s T&Cs.
How to Download Share Price Data in a CSV
Yahoo! Finance provides the simplest way to import financial data into a spreadsheet. The data (including stock prices, indices and company fundamentals) can be automatically downloaded in a CSV by simply entering a URL into your browser’s address bar. The CSV can then be opened in Excel and manipulated as required.
Real-time data is limited to a few US exchanges and most countries have a 10-30min delay due to licensing restrictions.
Step 1: Call the Yahoo! Finance API
Start with the base URL: http://download.finance.yahoo.com/d/quotes.csv
Step 2: Add stock codes
Add ?s= to your base URL followed by the stock symbols you want to download.
To download multiple companies at once, simply use the “+” sign in between the company codes.
http://download.finance.yahoo.com/d/quotes.csv?s=^AORD+BHP.AX+BLT.L+AAPL
The above URL will call:
• Australian All Ordinaries Index
• BHP Billiton Ltd in Australia
• BHP Billiton Ltd in UK
• Apple Inc. in USA.
How to Find the Correct Stock Code
All listed companies have a stock ticker between 1 and 4 characters. E.g. Apple has the stock ticker AAPL.
As there are multiple exchanges around the world, you must specify which exchange your code relates to by adding a suffix.
• Australian listed companies require the suffix “.AX” to be added to the companies stock code (e.g. BHP.AX).
• UK listed companies require the suffix “.L” to be added to the companies stock code (e.g. BLT.L).
Note: Most US listed companies do not require a suffix
Most stock indices use a “^" prefix prior to the index code along with a country identifier.
• The Australian All Ordinaries index has the prefix “^A” (e.g. ^AORD).
• The NASDAQ Composite uses the code “^IXIC”.
To find the correct suffix for a company or prefix for an index, visit https://finance.yahoo.com and start typing the name of the company into the “Look Up” box.
Step 3: Specify the information you want to download using "Tags"
Add &f= to your URL string followed by the Yahoo! Tags for the data you want to download.
http://download.finance.yahoo.com/d/quotes.csv?s=^AORD+BHP.AX+BLT.L+AAPL&f=nd1l1v
The above URL will fetch:
• Name
• Last Trade Date
• Last Trade Price
• Volume
Yahoo! Tags
Company Details
| n | Name |
| x | Stock Exchange |
| j1 | Market Capitalisation |
| f6 | Float Shares |
| b4 | Book Value |
Trading Data
Applicable to the most recent trading day (including today).
| a | Ask |
| b | Bid |
| a5 | Ask Size |
| b6 | Bid Size |
| d1 | Last trade Date |
| t1 | Last trade Time |
| l1 | Last trade Price |
| k3 | Last trade Size |
| c1 | Change |
| p2 | Change in Percent |
| o | Open |
| h | Day's High |
| g | Day's Low |
| m | Day's Range |
| v | Volume |
| a2 | Average Daily Volume |
| p | Previous Close |
Historical Performance
52-week data only.
| w | 52-week Range |
| k | 52-week High |
| j | 52-week Low |
| k4 | Change From 52-week High |
| j5 | Change From 52-week Low |
| k5 | % Change From 52-week High |
| j6 | % Change From 52-week Low |
Fundamental Analysis
Dividend and Target Price data are not always available and often restricted to US listed companies.
| e | Earnings/Share |
| j4 | EBITDA |
| r | P/E Ratio |
| r5 | PEG Ratio |
| d | Dividend/Share |
| q | Ex-Dividend Date |
| r1 | Dividend Pay Date |
| y | Dividend Yield |
| t8 | 1 yr Target Price |
Technical Analysis
The 50 and 200 day moving averages are two of the most commonly used indicators in the finance industry.
| m3 | 50-day Moving Average |
| m4 | 200-day Moving Average |
| m5 | Change From 200-day Moving Average |
| m6 | Percent Change From 200-day Moving Average |
| m7 | Change From 50-day Moving Average |
| m8 | Percent Change From 50-day Moving Average |
Step 4: Download the CSV
Enter the above URL strong into your browser’s address bar and hit return/enter. The CSV will be automatically downloaded to your computer.
How to Import Share Price Data into an Excel Spreadsheet
Excel provides a simple way to download financial data into a preconfigured spreadsheet at the click of a button.
Only advanced users can create a spreadsheet as Macros are required to download the data. For the less tech-savvy, here's a spreadsheet that will do the hard work for you.
You will get two warnings when you open the file:
1. The workbook contains Marcos (to process the data)
2. The workbook requires a data connection (to download data from the internet - duh!)
You must accept/enable both for the spreadsheet to work. The Macros are not password protected so you are free to view and manipulate all code anyway you like.
STEP 1: Open the Spreadsheet
The Spreadsheet is in XLS so it’s backwards compatible to MS Excel 97. Saving it as an .XLSX will break the spreadsheet.
STEP 2: Enter Stock Codes into Column A
Don’t forget to add the correct suffix or prefix as detailed above.
STEP 3: Click the “Download Data” Button
A few caveats:
• Dow Jones data will not import due to licensing restrictions.
• Financial data (e.g. dividends, ratios and target prices) are often only available for US listed companies.
• Most exchanges have a delay on their data of 10 - 30mins.
• You’re limited to calling 200 codes at a time.
Want to select your own column headers?
No problem.
Enter the Yahoo! Tags for the information you want to download into cell C2. The data will be displayed in the order you specify in the Yahoo! Tag string. You will need to rename the column headers to match the order of the tags you specified in C2.
Using Google Docs/Sheets?
You can use the Yahoo Finance API to import data into Google Docs (Sheets) by using the =IMPORTDATA function. For example:
=IMPORTDATA("http://download.finance.yahoo.com/d/quotes.csv ?s=AAPL&f=nd1l1")