geek.soosoo.at

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:

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:

Download

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!

Sometimes it’s quite useful to know the line number of an exception:

 
Download SQL Test Script

This is a simple function which parses a delimiter-seperated string (varchar2 or clob) and returns a string array.
 
1. Create the string array type

 
2. Create a package with two functions

 
3. Test it

 
Download SQL Script