Here’s the way to go if you need to query financial data at Yahoo!Finance via Oracle SQL.
Pre-Requirements:
* Database version must be >= 10g
* HTTP access to *.yahoo.com. (You’ll need an appropriate ACL when you are on 11g!)
* Access to UTL_HTTP, DBMS_LOB, SYS.ANYTYPE and SYS.ANYDATASET
* STR Package (because of the Split functions -> can be found here)
Implementation:
The solution is implemented with the Oracle pipelined table function feature, so it’s possible to use it in a SQL statement with the TABLE keyword:
select * from table(finyho([REQUEST]))
Furthmore the returned resultset of FINYHO is dynamic (ANYDATASET) and only contains the requested data fields. This is accomplished with the interface implementation approach of the pipelined table function.
There are two possible request types:
* a single row result for each security. Returns the latest data (= GET Request)
* a historical data request which returns a predefined set of data fields for a specific time frame.
(= HISTORY Request)
Syntax of the GET Request:
Get|[SECURITIES]|[FIELDS]
SECURITIES is a + seperated list of security tickers (eg: MSFT or GOOG+APPL etc.)
FIELDS is a list of special tags which represents the data fields you would like to query
Here are the possible values for the FIELDS parameter:
| a | Ask | a2 | Average Daily Volume | a5 | Ask Size |
| b | Bid | b2 | Ask (Real-time) | b3 | Bid (Real-time) |
| b4 | Book Value | b6 | Bid Size | c | Change & Percent Change |
| c1 | Change | c3 | Commission | c6 | Change (Real-time) |
| c8 | After Hours Change (Real-time) | d | Dividend/Share | d1 | Last Trade Date |
| d2 | Trade Date | e | Earnings/Share | e1 | Error Indication |
| 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 |
| g3 | Annualized Gain | g4 | Holdings Gain | g5 | Holdings Gain Percent (Real-time) |
| g6 | Holdings Gain (Real-time) | i | More Info | i5 | Order Book (Real-time) |
| j1 | Market Capitalization | j3 | Market Cap (Real-time) | j4 | EBITDA |
| j5 | Change From 52-week Low | j6 | Percent Change From 52-week Low | k1 | Last Trade (Real-time) With Time |
| k2 | Change Percent (Real-time) | 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 | l3 | Low Limit | m | Day’s Range |
| m2 | Day’s Range (Real-time) | m3 | 50-day Moving Average | m4 | 200-day Moving Average |
| m5 | Change From 200-day Moving Average | m6 | PCT Change From 200-day Moving Average | m7 | Change From 50-day Moving Average |
| m8 | PCT Change From 50-day Moving Average | n | Name | n4 | Notes |
| o | Open | p | Previous Close | p1 | Price Paid |
| 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) | r5 | PEG Ratio | r6 | Price/EPS Estimate Current Year |
| r7 | Price/EPS Estimate Next Year | s | Symbol | s1 | Shares Owned |
| s7 | Short Ratio | t1 | Last Trade Time | t6 | Trade Links |
| t7 | Ticker Trend | t8 | 1 yr Target Price | v | Volume |
| v1 | Holdings Value | v7 | Holdings Value (Real-time) | w | 52-week Range |
| w1 | Day’s Value Change | w4 | Day’s Value Change (Real-time) | x | Stock Exchange |
| y | Dividend Yield |
Examples:
Get the Ask, Bid and Last Trade of Microsoft:
select * from table(finyho('Get|MSFT|abl1'))
Get the Symbol, Name, Last Trade Date, Last Trade (Price only), Dividend Yield and P/E Ratio of Google and GE:
select * from table(finyho('Get|GOOG+GE|snd1l1yr'))
Syntax of the HISTORY Request:
History|[SECURITY]|[STARTDATE]|[ENDDATE]
SECURITY is the security ticker (only one allowed!)
STARTDATE is the beginning of the time frame (Syntax: YYYYMMDD)
ENDDATE is the end of the time frame (Syntax: YYYYMMDD)
The History Request returns the following data fields:
DATE, OPEN, HIGH, LOW, CLOSE, VOLUME and ADJ CLOSE
Examples:
Historical Data of Microsoft from 01/02/2009 till 01/20/2010:
select * from table(finyho('History|MSFT|20090102|20100120'))
Historical Data of Google from 01/02/2009 till 01/20/2010:
select * from table(finyho('History|GOOG|20090102|20100120'))
Code:
The YAHOO package contains two functions. The ParseRequest function parses the Request parameter and creates the Yahoo URL. The Query function executes the HTTP request and stores the CSV response in a CLOB. The YHO_IMPL object is the interface implementation of the pipelined table function FINYHO and creates the ANYDATASET from the CLOB data.
References:
Using Pipelined and Parallel Table Functions
Yahoo Data Download
SQL Script:
Attention:
This code-snippet is for educational purpose only! I don’t know anything about possible Yahoo usage restrictions or licenses, copyrights etc. Use at your own risk!
Enjoy!
