Jan 29

Query Finance@Yahoo with SQL

PL/SQL No Comments »

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:

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!

Jan 26

Exception Line Number

PL/SQL No Comments »

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

BEGIN

 RAISE NO_DATA_FOUND;
 
EXCEPTION WHEN NO_DATA_FOUND THEN
/*
Parse a line with this format:
ORA-NNNNN: at "SCHEMA.PROGRAM", line NNN
and return the line number of the exception
*/

 DBMS_OUTPUT.put_line(
    TRIM(trailing CHR(10) FROM
       SUBSTR(DBMS_UTILITY.format_error_backtrace,
              INSTR(DBMS_UTILITY.format_error_backtrace,’ ‘,-1,1)+1
       )
    )
 );
   
END;

 

 
Download SQL Test Script

Jan 25

Parse a delimiter-seperated string / Split Function

PL/SQL No Comments »

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

CREATE OR REPLACE TYPE str_array IS TABLE OF VARCHAR2(4000)

 
2. Create a package with two functions

CREATE OR REPLACE PACKAGE Str IS

   FUNCTION Split(sText IN VARCHAR2, sDel IN VARCHAR2 := ‘,’) RETURN str_array;
   FUNCTION Split(sText IN clob, sDel IN VARCHAR2 := ‘,’) RETURN str_array;
   
END Str;
 

CREATE OR REPLACE PACKAGE BODY Str IS

FUNCTION Split(sText IN VARCHAR2, sDel IN VARCHAR2 := ‘,’) RETURN str_array IS
    nStartIdx PLS_INTEGER := 1;
    nEndIdx PLS_INTEGER := 1;
    oRet str_array := str_array();
BEGIN

    IF sText IS NULL THEN RETURN oRet; END IF;

    LOOP

       nEndIdx := INSTR(sText, sDel, nStartIdx);

       IF nEndIdx > 0 THEN

          oRet.Extend;
          oRet(oRet.LAST) := SUBSTR(sText, nStartIdx, nEndIdx – nStartIdx);
          nStartIdx := nEndIdx + 1;

       ELSE

          oRet.Extend;
          oRet(oRet.LAST) := SUBSTR(sText, nStartIdx);
          EXIT;

       END IF;

    END LOOP;

    RETURN oRet;

END Split;

FUNCTION Split(sText IN clob, sDel IN VARCHAR2 := ‘,’) RETURN str_array IS
    nStartIdx PLS_INTEGER := 1;
    nEndIdx PLS_INTEGER := 1;
    oRet str_array := str_array();
BEGIN

    IF sText IS NULL THEN RETURN oRet; END IF;
    IF DBMS_LOB.getlength(sText) = 0 THEN RETURN oRet; END IF;

    LOOP

       nEndIdx := DBMS_LOB.INSTR(sText, sDel, nStartIdx);

       IF nEndIdx > 0 THEN

          oRet.Extend;
          oRet(oRet.LAST) := DBMS_LOB.SUBSTR(sText, nEndIdx – nStartIdx, nStartIdx);
          nStartIdx := nEndIdx + LENGTH(sDel);

       ELSE

          oRet.Extend;
          oRet(oRet.LAST) := DBMS_LOB.SUBSTR(lob_loc => sText, offset => nStartIdx);
          EXIT;

       END IF;

    END LOOP;

    RETURN oRet;

END Split;

END Str;

 

 
3. Test it

DECLARE
  oStr str_array;
BEGIN
  oStr := Str.Split(‘one,two,three,four,five’);
  IF oStr.COUNT > 0 THEN
    FOR i IN oStr.FIRST .. oStr.LAST
    LOOP
        DBMS_OUTPUT.put_line(oStr(i));
    END LOOP;
  END IF;
END;
 

 
Download SQL Script

preload preload preload