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.

Yahoo Symbol Search 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.

Macro Instructions

Allow Data Connection Instructions

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.

Column A in Spreadsheet Example

STEP 3: Click the “Download Data” Button

Download Data Button Example

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.

Yahoo Tags Example



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

Video Instructions





Related Links

Yahoo! Query Language
https://developer.yahoo.com/yql/

How to import Yahoo stock data into a website
https://developer.yahoo.com/yql/console/