Yahoo Finance API
Yahoo provides a convenient access to worldwide stock market data. There are two popular Yahoo URLs:1. Quotes History
This URL download stocks' previous years of OHLCV quotes. When I first setup my stock history database, I used this link to read previous years quotes for my universe of stocks. One draw back of this URL is that the latest record is delayed for 1 day. Now I need to read the latest quotes after trade time form other financial links(see below). I only use Yahoo's ichart URL for first time database setup and then I'll read other financial links to retrieve latest quotes on daily bases.URL Format:
http://ichart.finance.yahoo.com/table.csv?s=<Symbol>
Example - Load Google's (OHLCV) quote history
http://ichart.finance.yahoo.com/table.csv?s=goog
2. Quote.csv - 85 Tags
This is an unofficial link from Yahoo Finance. There are 85 Tags(stock information, see next section) that allow users to add into the link and let them to download the specific stock information as needed. The popular information is the PE, EPS and DPS.This URL also provide Delayed (15 mins) real time stock quotes. It may have little use because there are a number of data providers provide real time quotes in Hong Kong. Please check http://finance.yahoo.com/exchanges all the Yahoo Finance stock quotes delay time. I only use the delayed time quotes after trade time, say 4:20pm, and then add the latest day close price and volume to my database. I'm using the delay data to carry out a daily market performance analysis soon after the stock market is closed.
A details test on the Quote.csv URL can be found in my other article in this blog.
URL Format:
http://download.yahoo.com/d/quotes.csv?s=<Symbol>&f=<Tag1><Tag2>
Example - The following tags download Latest trade day(d1), Symbol(s), 52-Week low(j), 52-Week high(k), PE(r), DPS(d)
http://download.finance.yahoo.com/d/quotes.csv?s=GOOG&f=d1sjkrd
NASDAQ
http://download.finance.yahoo.com/d/quotes.csv?s=^IXIC&f=d1sjkrd
HSBC HOLDINGS
http://download.finance.yahoo.com/d/quotes.csv?s=0005.hk&f=d1sjkrd
HANG SENG INDEX
http://download.finance.yahoo.com/d/quotes.csv?s=^hsi&f=d1sjkrd
BANK OF CHINA
http://download.finance.yahoo.com/d/quotes.csv?s=601988.ss&f=d1sjkrd
SHANGHAI COMPOSITE INDEX
http://download.finance.yahoo.com/d/quotes.csv?s=000001&f=d1sjkrd
FOXCONN
http://download.finance.yahoo.com/d/quotes.csv?s=2354.tw&f=d1sjkrd
TWSE TWAIN 50
http://download.finance.yahoo.com/d/quotes.csv?s=^TWII&f=d1sjkrd
3. Quote.csv Tags
I did a test to download NASDAQ, Hang Seng Index, Shanghai Composite Index and TWSE Twain 50 and found there are 31 tags out of 85 don't return any data. It is either the information are not available to these stock/ markets, or the information is no longer available. I'll provide details on my test results in my other article.
Tags
|
Name
|
CalvinSir's Comment
|
a
|
Ask
|
|
a2
|
Average Daily Volume
|
3 month's average volume
|
a5
|
Ask Size
|
|
b
|
Bid
|
|
b2
|
Ask (real-time)
|
Not Available
|
b3
|
Bid
(real-time)
|
Not Available
|
b4
|
Book Value
|
|
b6
|
Bid Size
|
|
c
|
Change & Percent Change
|
|
c1
|
Change
|
|
c3
|
Commission
|
Not Available
|
c6
|
Change (real-time)
|
Not Available
|
c8
|
After Hours Change
|
Not Available
|
d
|
Dividend/Share
|
|
d1
|
Last Trade Date
|
|
d2
|
Trade Date
|
Not Available
|
e
|
Earnings/Share
|
|
e1
|
Error Indication
(returned for symbol changed / invalid) |
Not Available
|
e7
|
EPS Estimate Current Year
|
|
e8
|
EPS Estimate Next Year
|
|
e9
|
EPS Estimate Next Quarter
|
|
f6
|
Float Shares
|
|
g
|
Day’s Low
|
|
h
|
Day’s High
|
|
j
|
52-week Low
|
|
k
|
52-week High
|
|
g1
|
Holdings Gain Percent
|
Not Available
|
g3
|
Annualized Gain
|
Not Available
|
g4
|
Holdings Gain
|
Not Available
|
g5
|
Holdings Gain Percent (Real-time)
|
Not Available
|
g6
|
Holdings Gain (Real-time)
|
Not Available
|
i
|
More Info
|
Not Available
|
i5
|
Order Book (Real-time)
|
Not Available
|
j1
|
Market Capitalization
|
|
j3
|
Market Cap (Real-time)
|
Not Available
|
j4
|
EBITDA
|
|
j5
|
Change From 52-week Low
|
|
j6
|
Percent Change From 52-week Low
|
|
k1
|
Last Trade (Real-time) With Time
|
Not Available
|
k2
|
Change Percent (Real-time)
|
Not Available
|
k3
|
Last Trade Size
|
|
k4
|
Change From 52-week High
|
|
k5
|
Percebt Change From 52-week High
|
|
l
|
Last Trade (With Time)
|
|
l1
|
Last Trade (Price Only)
|
|
l2
|
High Limit
|
Not Available
|
l3
|
Low Limit
|
Not Available
|
m
|
Day’s Range
|
|
m2
|
Day’s Range (Real-time)
|
Not Available
|
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
|
|
n
|
Name
|
|
n4
|
Notes
|
Not Available
|
o
|
Open
|
Not Available
|
p
|
Previous Close
|
|
p1
|
Price Paid
|
Not Available
|
p2
|
Change in Percent
|
|
p5
|
Price/Sales
|
|
p6
|
Price/Book
|
|
q
|
Ex-Dividend Date
|
|
r
|
P/E Ratio
|
|
r1
|
Dividend Pay Date
|
|
r2
|
P/E Ratio (Real-time)
|
Not Available
|
r5
|
PEG Ratio
|
|
r6
|
Price/EPS Estimate Current Year
|
|
r7
|
Price/EPS Estimate Next Year
|
|
s
|
Symbol
|
|
s1
|
Shares Owned
|
Not Available
|
s7
|
Short Ratio
|
|
t1
|
Last Trade Time
|
Delay
|
t6
|
Trade Links
|
Not Available
|
t7
|
Ticker Trend
|
Not Available
|
t8
|
1 yr Target Price
|
|
v
|
Volume
|
Delay
|
v1
|
Holdings Value
|
Not Available
|
v7
|
Holdings Value (Real-time)
|
Not Available
|
w
|
52-week Range
|
|
w1
|
Day’s Value Change
|
Not Available
|
w4
|
Day’s Value Change (Real-time)
|
Not Available
|
x
|
Stock Exchange
|
|
y
|
Dividend Yield
|
4. Downloading Hong Kong Exchanges Main Board Stock History
I tried to download Hong Kong Exchanges Main Board securities (total 1634 stocks) but there are 16 stocks aren't available from Yahoo! Finance API. The following stocks history are missing:1265, 1341, 1499, 1561, 1683, 2928, 2930, 2936, 2937, 4601,4603,4604,4605,4606,80737,84602
5. Using Excel VBA to download stock data
I use Excel's QueryTables object to download the Yahoo Finance data directly into my worksheet.The following is the Excel VBA code Example on using iChart URL:
The following is the Excel VBA code Example on using Quote.csv URL:
6. Finance VBA, C++ and SQL Training
We're financial experts to provide the following services:- Personal and company training on Finance VBA and SQL (both local & remote learning)
- Financal modelling
- Bloomberg plug-in and Excel VBA applications development
- Database system development
Please contact us for more information.
7. Further Study
How the program QueryTables object in Excel so that I can download any HTML web page information from the internet?How to save my universe of stocks' quote history into a database?
How to download 100 stocks' PE in one go?
How to perform stock market daily performance analysis?
How to calculate SMA, Bollinger Bands, RSI, MACD and Beta, etc
How to use Excel VBA to screen potential stocks automatically?
How to draw all kinds of stock charts in Excel (e.g. Candlestick charts)?
Contact us to learn more
Other Reference:
C# example : http://www.jarloo.com/yahoo
Java example: http://markhall.com/blog/?q=content/retrieving-stock-price-data
