http://bikle.com

oracle@zareason:~/vlsql/md/etfmas$ ./wget.sh
+ cd /oracle/vlsql/mkt_data/etfmas/
+ wget --output-document=DIA.csv 'http://ichart.finance.yahoo.com/table.csv?s=DIA'
--10:41:41--  http://ichart.finance.yahoo.com/table.csv?s=DIA
           => `DIA.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [   <=>                               ] 146,470      278.38K/s

10:41:42 (277.87 KB/s) - `DIA.csv' saved [146470]

+ wget --output-document=EFA.csv 'http://ichart.finance.yahoo.com/table.csv?s=EFA'
--10:41:42--  http://ichart.finance.yahoo.com/table.csv?s=EFA
           => `EFA.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [  <=>                                ] 96,792       250.84K/s

10:41:43 (250.11 KB/s) - `EFA.csv' saved [96792]

+ wget --output-document=MDY.csv 'http://ichart.finance.yahoo.com/table.csv?s=MDY'
--10:41:43--  http://ichart.finance.yahoo.com/table.csv?s=MDY
           => `MDY.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [   <=>                               ] 172,154      293.94K/s

10:41:43 (293.29 KB/s) - `MDY.csv' saved [172154]

+ wget --output-document=OIH.csv 'http://ichart.finance.yahoo.com/table.csv?s=OIH'
--10:41:43--  http://ichart.finance.yahoo.com/table.csv?s=OIH
           => `OIH.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [  <=>                                ] 104,036      295.36K/s

10:41:44 (294.96 KB/s) - `OIH.csv' saved [104036]

+ wget --output-document=SHY.csv 'http://ichart.finance.yahoo.com/table.csv?s=SHY'
--10:41:44--  http://ichart.finance.yahoo.com/table.csv?s=SHY
           => `SHY.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [  <=>                                ] 80,700       297.73K/s

10:41:44 (297.48 KB/s) - `SHY.csv' saved [80700]

+ wget --output-document=SPY.csv 'http://ichart.finance.yahoo.com/table.csv?s=SPY'
--10:41:44--  http://ichart.finance.yahoo.com/table.csv?s=SPY
           => `SPY.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [     <=>                             ] 212,759      249.86K/s

10:41:46 (249.10 KB/s) - `SPY.csv' saved [212759]

+ wget --output-document=XLB.csv 'http://ichart.finance.yahoo.com/table.csv?s=XLB'
--10:41:46--  http://ichart.finance.yahoo.com/table.csv?s=XLB
           => `XLB.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [   <=>                               ] 124,685      293.89K/s

10:41:46 (293.59 KB/s) - `XLB.csv' saved [124685]

+ wget --output-document=XLE.csv 'http://ichart.finance.yahoo.com/table.csv?s=XLE'
--10:41:46--  http://ichart.finance.yahoo.com/table.csv?s=XLE
           => `XLE.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [   <=>                               ] 126,026      251.35K/s

10:41:47 (250.50 KB/s) - `XLE.csv' saved [126026]

+ wget --output-document=XLF.csv 'http://ichart.finance.yahoo.com/table.csv?s=XLF'
--10:41:47--  http://ichart.finance.yahoo.com/table.csv?s=XLF
           => `XLF.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [   <=>                               ] 126,354      304.52K/s

10:41:47 (304.35 KB/s) - `XLF.csv' saved [126354]

+ wget --output-document=XLI.csv 'http://ichart.finance.yahoo.com/table.csv?s=XLI'
--10:41:47--  http://ichart.finance.yahoo.com/table.csv?s=XLI
           => `XLI.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [   <=>                               ] 123,884      268.63K/s

10:41:48 (268.06 KB/s) - `XLI.csv' saved [123884]

+ wget --output-document=XLK.csv 'http://ichart.finance.yahoo.com/table.csv?s=XLK'
--10:41:48--  http://ichart.finance.yahoo.com/table.csv?s=XLK
           => `XLK.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [   <=>                               ] 125,357      293.65K/s

10:41:48 (293.29 KB/s) - `XLK.csv' saved [125357]

+ wget --output-document=XLV.csv 'http://ichart.finance.yahoo.com/table.csv?s=XLV'
--10:41:48--  http://ichart.finance.yahoo.com/table.csv?s=XLV
           => `XLV.csv'
Resolving ichart.finance.yahoo.com... 69.147.86.173
Connecting to ichart.finance.yahoo.com|69.147.86.173|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: unspecified [text/csv]

    [   <=>                               ] 123,517      276.35K/s

10:41:49 (275.77 KB/s) - `XLV.csv' saved [123517]

oracle@zareason:~/vlsql/md/etfmas$
oracle@zareason:~/vlsql/md/etfmas$

oracle@zareason:~/vlsql/md/etfmas$ ll *csv
-rw-r--r-- 1 oracle dba 146470 2009-04-02 10:41 DIA.csv
-rw-r--r-- 1 oracle dba  96792 2009-04-02 10:41 EFA.csv
-rw-r--r-- 1 oracle dba 172154 2009-04-02 10:41 MDY.csv
-rw-r--r-- 1 oracle dba 104036 2009-04-02 10:41 OIH.csv
-rw-r--r-- 1 oracle dba  80700 2009-04-02 10:41 SHY.csv
-rw-r--r-- 1 oracle dba 212759 2009-04-02 10:41 SPY.csv
-rw-r--r-- 1 oracle dba 124685 2009-04-02 10:41 XLB.csv
-rw-r--r-- 1 oracle dba 126026 2009-04-02 10:41 XLE.csv
-rw-r--r-- 1 oracle dba 126354 2009-04-02 10:41 XLF.csv
-rw-r--r-- 1 oracle dba 123884 2009-04-02 10:41 XLI.csv
-rw-r--r-- 1 oracle dba 125357 2009-04-02 10:41 XLK.csv
-rw-r--r-- 1 oracle dba 123517 2009-04-02 10:41 XLV.csv
oracle@zareason:~/vlsql/md/etfmas$

oracle@zareason:~/vlsql/md/etfmas$
oracle@zareason:~/vlsql/md/etfmas$
oracle@zareason:~/vlsql/md/etfmas$ date; ./etfmas.sh ; date
Fri Apr  3 09:29:31 PDT 2009
+ cd /oracle/vlsql/mkt_data/etfmas/
+ cat DIA.csv
+ awk -F, '{print "DIA,"$1",DIA"$1","$6","$7}'
+ grep 0
+ cat EFA.csv
+ awk -F, '{print "EFA,"$1",EFA"$1","$6","$7}'
+ grep 0
+ cat MDY.csv
+ awk -F, '{print "MDY,"$1",MDY"$1","$6","$7}'
+ grep 0
+ cat OIH.csv
+ awk -F, '{print "OIH,"$1",OIH"$1","$6","$7}'
+ grep 0
+ cat SHY.csv
+ awk -F, '{print "SHY,"$1",SHY"$1","$6","$7}'
+ grep 0
+ cat SPY.csv
+ awk -F, '{print "SPY,"$1",SPY"$1","$6","$7}'
+ grep 0
+ cat XLB.csv
+ awk -F, '{print "XLB,"$1",XLB"$1","$6","$7}'
+ grep 0
+ cat XLE.csv
+ awk -F, '{print "XLE,"$1",XLE"$1","$6","$7}'
+ grep 0
+ cat XLF.csv
+ awk -F, '{print "XLF,"$1",XLF"$1","$6","$7}'
+ grep 0
+ cat XLI.csv
+ awk -F, '{print "XLI,"$1",XLI"$1","$6","$7}'
+ grep 0
+ cat XLK.csv
+ awk -F, '{print "XLK,"$1",XLK"$1","$6","$7}'
+ grep 0
+ cat XLV.csv
+ awk -F, '{print "XLV,"$1",XLV"$1","$6","$7}'
+ grep 0
+ sqlplus trade/t

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 3 09:29:31 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Session altered.

09:29:31 SQL> SET ECHO ON TIME ON TIMING ON LINES 155 PAGES 99
09:29:31 SQL>
Table dropped.

Elapsed: 00:00:00.10
09:29:32 SQL>
Table created.

Elapsed: 00:00:00.03
09:29:32 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
+ sqlldr trade/t bindsize=20971520 readsize=20971520 rows=123456 control=etfmas.ctl

SQL*Loader: Release 11.1.0.6.0 - Production on Fri Apr 3 09:29:32 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Commit point reached - logical record count 16256
Commit point reached - logical record count 31464
+ grep loaded etfmas.log
Table ETFMAS, loaded from every logical record.
  31464 Rows successfully loaded.
  0 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.
+ sqlplus trade/t

SQL*Plus: Release 11.1.0.6.0 - Production on Fri Apr 3 09:29:32 2009

Copyright (c) 1982, 2007, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Session altered.

09:29:32 SQL> SET ECHO ON TIME ON TIMING ON LINES 155 PAGES 99
09:29:32 SQL> 09:29:32 SQL> --
09:29:32 SQL> -- etfmas.sql
09:29:32 SQL> --
09:29:32 SQL>
09:29:32 SQL> SELECT tkr,COUNT(*),MIN(ydate),MAX(ydate)FROM etfmas GROUP BY tkr ORDER BY tkr ;

TKR    COUNT(*) MIN(YDATE) MAX(YDATE)
---- ---------- ---------- ----------
DIA        2821 1998-01-20 2009-04-02
EFA        1910 2001-08-27 2009-04-02
MDY        3430 1995-08-18 2009-04-02
OIH        2037 2001-02-26 2009-04-02
SHY        1681 2002-07-31 2009-04-02
SPY        4075 1993-01-29 2009-04-02
XLB        2585 1998-12-22 2009-04-02
XLE        2585 1998-12-22 2009-04-02
XLF        2585 1998-12-22 2009-04-02
XLI        2585 1998-12-22 2009-04-02
XLK        2585 1998-12-22 2009-04-02
XLV        2585 1998-12-22 2009-04-02

12 rows selected.

Elapsed: 00:00:00.06
09:29:32 SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Fri Apr  3 09:29:32 PDT 2009
oracle@zareason:~/vlsql/md/etfmas$
oracle@zareason:~/vlsql/md/etfmas$

--
-- mas.sql
--

CONNECT trade/t

-- Creates a view full of much of the data needed to drive my Moving-Avg-Study (MAS).
-- Some of the thinking behind this script is written down here:
-- vlsql/notes/notes2008_1209xp.txt

-- MAS depends on Moving Average so I use an inline view to get 3 types of Moving Averages.
CREATE OR REPLACE VIEW etfmai AS SELECT tkr_date,tkr,ydate,yprice,yvol
,yprice_avg5
,yprice_avg10
,yprice_avg20
-- Get MAS from past 5days, 10days, 20days.  Use LAG() to get yesterday's Moving Average
,yprice_avg5-LAG(yprice_avg5)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice_avg5slp
,yprice_avg10-LAG(yprice_avg10)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice_avg10slp
,yprice_avg20-LAG(yprice_avg20)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice_avg20slp
-- Get PG for future 5days, 10days, 20days
,100 * (yprice5 - yprice)/yprice pct_gain5
,100 * (yprice10 - yprice)/yprice pct_gain10
,100 * (yprice20 - yprice)/yprice pct_gain20
FROM
(
  SELECT tkr_date,tkr,ydate,yprice,yvol
  -- To get MAS, I 1st need Moving Avg.  I like 3 types: 5day, 10day, 20day
  ,AVG(yprice)OVER(PARTITION BY tkr ORDER BY tkr_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) yprice_avg5
  ,AVG(yprice)OVER(PARTITION BY tkr ORDER BY tkr_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) yprice_avg10
  ,AVG(yprice)OVER(PARTITION BY tkr ORDER BY tkr_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)yprice_avg20
  -- To get future PG I use LEAD()
  ,LEAD(yprice,5,yprice)OVER(PARTITION BY tkr ORDER BY tkr_date) yprice5
  ,LEAD(yprice,10,yprice)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice10
  ,LEAD(yprice,20,yprice)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice20
  FROM etfmas WHERE yprice>0
  ORDER BY tkr_date
)
ORDER BY tkr_date
/

-- Create a view which will help create the mas table.
CREATE OR REPLACE VIEW mas10 AS SELECT tkr_date,sysdate - ydate dayspast,yprice,yvol
,yprice_avg5
,yprice_avg10
,yprice_avg20
,yprice_avg5slp
,yprice_avg10slp
,yprice_avg20slp
-- The columns below should not propagate to the SVM-build-score tables.
-- They need to be dropped or filtered out later.  For now, I need them.
,pct_gain5
,pct_gain10
,pct_gain20
,tkr
,ydate
--
FROM etfmai ORDER BY tkr_date
/

-- The mas table contains a super-set of the columns which will go into the SVM-build-score tables.
-- Add slope columns from SHY, and XLE under assumption that interest rates and energy prices affect ETF prices.
DROP TABLE mas;
CREATE TABLE mas AS SELECT m.tkr_date,sysdate - m.ydate dayspast,m.yprice,m.yvol
,m.yprice_avg5
,m.yprice_avg10
,m.yprice_avg20
,m.yprice_avg5slp
,m.yprice_avg10slp
,m.yprice_avg20slp
,'nup' aa_targ_att
--
,shy.yprice_avg5slp shy_yprice_avg5slp
,xle.yprice_avg5slp xle_yprice_avg5slp
--
,shy.yprice_avg10slp shy_yprice_avg10slp
,xle.yprice_avg10slp xle_yprice_avg10slp
--
,shy.yprice_avg20slp shy_yprice_avg20slp
,xle.yprice_avg20slp xle_yprice_avg20slp
--
-- The columns below should not propagate to the SVM-build-score tables.
-- They need to be dropped or filtered out later.  For now, I need them.
,m.pct_gain5
,m.pct_gain10
,m.pct_gain20
,m.tkr
,m.ydate
FROM mas10 m, mas10 shy, mas10 xle
WHERE m.ydate=shy.ydate AND m.ydate=xle.ydate
AND shy.tkr='SHY'
AND xle.tkr='XLE'
ORDER BY m.tkr_date
/

-- The model-builder chokes on wide target attributes. Slim down to 3.
ALTER TABLE mas MODIFY aa_targ_att VARCHAR2(3);

21:56:19 SQL>
21:56:20 SQL> @mas.sql
21:56:25 SQL> --
21:56:25 SQL> -- mas.sql
21:56:25 SQL> --
21:56:25 SQL>
21:56:25 SQL> CONNECT trade/t
Connected.

Session altered.

Elapsed: 00:00:00.00
21:56:25 SQL> SET ECHO ON TIME ON TIMING ON LINES 155 PAGES 99
21:56:25 SQL>
21:56:25 SQL> -- Creates a view full of much of the data needed to drive my Moving-Avg-Study (MAS).
21:56:25 SQL> -- Some of the thinking behind this script is written down here:
21:56:25 SQL> -- vlsql/notes/notes2008_1209xp.txt
21:56:25 SQL>
21:56:25 SQL> -- MAS depends on Moving Average so I use an inline view to get 3 types of Moving Averages.
21:56:25 SQL> CREATE OR REPLACE VIEW etfmai AS SELECT tkr_date,tkr,ydate,yprice,yvol
21:56:25   2  ,yprice_avg5
21:56:25   3  ,yprice_avg10
21:56:25   4  ,yprice_avg20
21:56:25   5  -- Get MAS from past 5days, 10days, 20days.  Use LAG() to get yesterday's Moving Average
21:56:25   6  ,yprice_avg5-LAG(yprice_avg5)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice_avg5slp
21:56:25   7  ,yprice_avg10-LAG(yprice_avg10)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice_avg10slp
21:56:25   8  ,yprice_avg20-LAG(yprice_avg20)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice_avg20slp
21:56:25   9  -- Get PG for future 5days, 10days, 20days
21:56:25  10  ,100 * (yprice5 - yprice)/yprice pct_gain5
21:56:25  11  ,100 * (yprice10 - yprice)/yprice pct_gain10
21:56:25  12  ,100 * (yprice20 - yprice)/yprice pct_gain20
21:56:25  13  FROM
21:56:25  14  (
21:56:25  15    SELECT tkr_date,tkr,ydate,yprice,yvol
21:56:25  16    -- To get MAS, I 1st need Moving Avg.  I like 3 types: 5day, 10day, 20day
21:56:25  17    ,AVG(yprice)OVER(PARTITION BY tkr ORDER BY tkr_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) yprice_avg5
21:56:25  18    ,AVG(yprice)OVER(PARTITION BY tkr ORDER BY tkr_date ROWS BETWEEN 9 PRECEDING AND CURRENT ROW) yprice_avg10
21:56:25  19    ,AVG(yprice)OVER(PARTITION BY tkr ORDER BY tkr_date ROWS BETWEEN 19 PRECEDING AND CURRENT ROW)yprice_avg20
21:56:25  20    -- To get future PG I use LEAD()
21:56:25  21    ,LEAD(yprice,5,yprice)OVER(PARTITION BY tkr ORDER BY tkr_date) yprice5
21:56:25  22    ,LEAD(yprice,10,yprice)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice10
21:56:25  23    ,LEAD(yprice,20,yprice)OVER(PARTITION BY tkr ORDER BY tkr_date)yprice20
21:56:25  24    FROM etfmas WHERE yprice>0
21:56:25  25    ORDER BY tkr_date
21:56:25  26  )
21:56:25  27  ORDER BY tkr_date
21:56:25  28  /

View created.

Elapsed: 00:00:00.79
21:56:25 SQL>
21:56:25 SQL> -- Create a view which will help create the mas table.
21:56:25 SQL> CREATE OR REPLACE VIEW mas10 AS SELECT tkr_date,sysdate - ydate dayspast,yprice,yvol
21:56:25   2  ,yprice_avg5
21:56:25   3  ,yprice_avg10
21:56:25   4  ,yprice_avg20
21:56:25   5  ,yprice_avg5slp
21:56:25   6  ,yprice_avg10slp
21:56:25   7  ,yprice_avg20slp
21:56:25   8  -- The columns below should not propagate to the SVM-build-score tables.
21:56:25   9  -- They need to be dropped or filtered out later.  For now, I need them.
21:56:25  10  ,pct_gain5
21:56:25  11  ,pct_gain10
21:56:25  12  ,pct_gain20
21:56:25  13  ,tkr
21:56:25  14  ,ydate
21:56:25  15  --
21:56:25  16  FROM etfmai ORDER BY tkr_date
21:56:25  17  /

View created.

Elapsed: 00:00:00.04
21:56:25 SQL>
21:56:25 SQL> -- The mas table contains a super-set of the columns which will go into the SVM-build-score tables.
21:56:25 SQL> -- Add slope columns from SHY, and XLE under assumption that interest rates and energy prices affect ETF prices.
21:56:25 SQL> DROP TABLE mas;

Table dropped.

Elapsed: 00:00:00.13
21:56:26 SQL> CREATE TABLE mas AS SELECT m.tkr_date,sysdate - m.ydate dayspast,m.yprice,m.yvol
21:56:26   2  ,m.yprice_avg5
21:56:26   3  ,m.yprice_avg10
21:56:26   4  ,m.yprice_avg20
21:56:26   5  ,m.yprice_avg5slp
21:56:26   6  ,m.yprice_avg10slp
21:56:26   7  ,m.yprice_avg20slp
21:56:26   8  ,'nup' aa_targ_att
21:56:26   9  --
21:56:26  10  ,shy.yprice_avg5slp shy_yprice_avg5slp
21:56:26  11  ,xle.yprice_avg5slp xle_yprice_avg5slp
21:56:26  12  --
21:56:26  13  ,shy.yprice_avg10slp shy_yprice_avg10slp
21:56:26  14  ,xle.yprice_avg10slp xle_yprice_avg10slp
21:56:26  15  --
21:56:26  16  ,shy.yprice_avg20slp shy_yprice_avg20slp
21:56:26  17  ,xle.yprice_avg20slp xle_yprice_avg20slp
21:56:26  18  --
21:56:26  19  -- The columns below should not propagate to the SVM-build-score tables.
21:56:26  20  -- They need to be dropped or filtered out later.  For now, I need them.
21:56:26  21  ,m.pct_gain5
21:56:26  22  ,m.pct_gain10
21:56:26  23  ,m.pct_gain20
21:56:26  24  ,m.tkr
21:56:26  25  ,m.ydate
21:56:26  26  FROM mas10 m, mas10 shy, mas10 xle
21:56:26  27  WHERE m.ydate=shy.ydate AND m.ydate=xle.ydate
21:56:26  28  AND shy.tkr='SHY'
21:56:26  29  AND xle.tkr='XLE'
21:56:26  30  ORDER BY m.tkr_date
21:56:26  31  /

Table created.

Elapsed: 00:00:01.30
21:56:27 SQL>
21:56:27 SQL> -- The model-builder chokes on wide target attributes. Slim down to 3.
21:56:27 SQL> ALTER TABLE mas MODIFY aa_targ_att VARCHAR2(3);

Table altered.

Elapsed: 00:00:00.01

21:56:27 SQL>
21:56:32 SQL>

--
-- cr_time_dimension.sql
--

-- Start by creating a table full of dates.
DROP   TABLE dropme_soon;
CREATE TABLE dropme_soon(ydate DATE);
BEGIN
  FOR i IN 1..21234 LOOP
    INSERT INTO dropme_soon SELECT TRUNC(TO_DATE('2020-01-01','YYYY-MM-DD')) - i FROM dual;
  END LOOP;
END;
/
SELECT MIN(ydate),MAX(ydate),COUNT(*)FROM dropme_soon;

-- Next create a table full of days which exist between the endpoints of the data-dates in table: mas.
DROP   TABLE day_dimension ;
CREATE TABLE day_dimension AS
SELECT
0+TO_CHAR(ydate,'YYYYMMDD')day_id
,0+TO_CHAR(ydate,'YYYYMM')month_id
,TO_CHAR(ydate,'YYYY_MM_DD')day_dsc
,ydate day_end_date
,1 day_time_span
FROM dropme_soon
WHERE ydate BETWEEN (SELECT MIN(ydate)FROM mas)AND(SELECT MAX(ydate)FROM mas)
/

-- Months next.
DROP   TABLE month_dimension ;
CREATE TABLE month_dimension AS
SELECT
0+TO_CHAR(day_end_date,'YYYYMM')month_id
,0+TO_CHAR(day_end_date,'YYYY')year_id
,TO_CHAR(day_end_date,'YYYY_MM')month_dsc
,MAX(day_end_date) month_end_date
,1 + MAX(day_end_date) - MIN(day_end_date) month_time_span
FROM day_dimension
GROUP BY
0+TO_CHAR(day_end_date,'YYYYMM')
,0+TO_CHAR(day_end_date,'YYYY')
,TO_CHAR(day_end_date,'YYYY_MM')
/

SELECT COUNT(*)FROM month_dimension ORDER BY month_id;
-- SELECT * FROM month_dimension ORDER BY month_id;

-- Years next.
DROP   TABLE year_dimension ;
CREATE TABLE year_dimension AS
SELECT
0+TO_CHAR(day_end_date,'YYYY')year_id
,1 all_time_id
,TO_CHAR(day_end_date,'YYYY')year_dsc
,MAX(day_end_date) year_end_date
,1 + MAX(day_end_date) - MIN(day_end_date) year_time_span
FROM day_dimension
GROUP BY
0+TO_CHAR(day_end_date,'YYYY')
,1
,TO_CHAR(day_end_date,'YYYY')
/

-- All time next
DROP   TABLE all_time_dimension ;
CREATE TABLE all_time_dimension AS
SELECT
1 all_time_id
,'All_Time'all_time_dsc
,MAX(day_end_date) all_time_end_date
,1 + MAX(day_end_date) - MIN(day_end_date) all_time_time_span
FROM day_dimension
/

SELECT * FROM year_dimension ORDER BY year_id;
SELECT * FROM all_time_dimension ;

-- See how well they join:
SELECT COUNT(*)FROM day_dimension;

SELECT COUNT(*)FROM day_dimension d, month_dimension m, year_dimension y, all_time_dimension a
WHERE d.month_id = m.month_id AND m.year_id = y.year_id AND y.all_time_id = a.all_time_id
/

--
-- cr_yfact.sql
--

-- This script creates yfact from the mas table.
-- We call it yfact because it contains data from yahoo.

-- We use a series of SELECTs and INSERTs.
-- Each item in the series corresponds to a unique combination of dimensions for the yfact table.

-- The dimensions and their values are listed below:

-- strategy:       bh, loc, los
-- pivot_point:    5,10,20
-- holding_period: 5,10,20

-- tkr:
--  - DIA
--  - EFA
--  - MDY
--  - OIH
--  - SHY
--  - SPY
--  - XLB
--  - XLE
--  - XLF
--  - XLI
--  - XLK
--  - XLV

-- DROP and CREATE yfact first
-- pg (percentage gain, is the measure)
DROP   TABLE yfact;
CREATE TABLE yfact
(
pg              NUMBER
,day_id         NUMBER -- fk to day_dimension
,tkr            VARCHAR2(11) -- DIA, EFA, MDY, ...
,strategy       VARCHAR2(11) -- bh, loc, los
,pivot_point    NUMBER -- 5,10,20
,holding_period NUMBER -- 5,10,20
)
/

-- SELECT, INSERT rows where strategy == bh
-- SELECT, INSERT rows where pivot_point == 5,10,20
-- SELECT, INSERT rows where holding_period == 5
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)SELECT pct_gain5,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'bh', 5, 5 FROM mas;
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)SELECT pct_gain5,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'bh',10, 5 FROM mas;
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)SELECT pct_gain5,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'bh',20, 5 FROM mas;

-- SELECT, INSERT rows where strategy == bh
-- SELECT, INSERT rows where pivot_point == 5,10,20
-- SELECT, INSERT rows where holding_period == 10
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)SELECT pct_gain10,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'bh', 5,10 FROM mas;
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)SELECT pct_gain10,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'bh',10,10 FROM mas;
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)SELECT pct_gain10,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'bh',20,10 FROM mas;

-- SELECT, INSERT rows where strategy == bh
-- SELECT, INSERT rows where pivot_point == 5,10,20
-- SELECT, INSERT rows where holding_period == 20
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)SELECT pct_gain20,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'bh', 5,20 FROM mas;
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)SELECT pct_gain20,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'bh',10,20 FROM mas;
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)SELECT pct_gain20,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'bh',20,20 FROM mas;

--
-- loc below
--

-- SELECT, INSERT rows where strategy == loc
-- SELECT, INSERT rows where pivot_point == 5
-- SELECT, INSERT rows where holding_period == 5
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
-- loc logic: If mvg-avg slope < 0 hold cash (which gives a pg of 0) ELSE accept pg from long position
CASE WHEN yprice_avg5slp<0 THEN 0 ELSE pct_gain5 END -- loc logic implemented here
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'loc', 5, 5 FROM mas;

-- strategy == loc, pivot_point == 10, holding_period == 5
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg10slp<0 THEN 0 ELSE pct_gain5 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'loc', 10, 5 FROM mas;

-- strategy == loc, pivot_point == 20, holding_period == 5
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg20slp<0 THEN 0 ELSE pct_gain5 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'loc', 20, 5 FROM mas;

-- bump holding_period to 10

-- strategy == loc, pivot_point == 5, holding_period == 10
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg5slp<0 THEN 0 ELSE pct_gain10 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'loc', 5, 10 FROM mas;

-- strategy == loc, pivot_point == 10, holding_period == 10
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg10slp<0 THEN 0 ELSE pct_gain10 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'loc', 10, 10 FROM mas;

-- strategy == loc, pivot_point == 20, holding_period == 10
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg20slp<0 THEN 0 ELSE pct_gain10 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'loc', 20, 10 FROM mas;

-- bump holding_period to 20

-- strategy == loc, pivot_point == 5, holding_period == 20
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg5slp<0 THEN 0 ELSE pct_gain20 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'loc', 5, 20 FROM mas;

-- strategy == loc, pivot_point == 10, holding_period == 20
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg10slp<0 THEN 0 ELSE pct_gain20 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'loc', 10, 20 FROM mas;

-- strategy == loc, pivot_point == 20, holding_period == 20
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg20slp<0 THEN 0 ELSE pct_gain20 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'loc', 20, 20 FROM mas;


--
-- los below
--

-- SELECT, INSERT rows where strategy == los
-- SELECT, INSERT rows where pivot_point == 5
-- SELECT, INSERT rows where holding_period == 5
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
-- los logic: If mvg-avg slope < 0 accept -pg from short position ELSE accept pg from long position
CASE WHEN yprice_avg5slp<0 THEN -pct_gain5 ELSE pct_gain5 END -- los logic implemented here
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'los', 5, 5 FROM mas;

-- strategy == los, pivot_point == 10, holding_period == 5
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg10slp<0 THEN -pct_gain5 ELSE pct_gain5 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'los', 10, 5 FROM mas;

-- strategy == los, pivot_point == 20, holding_period == 5
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg20slp<0 THEN -pct_gain5 ELSE pct_gain5 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'los', 20, 5 FROM mas;

-- bump holding_period to 10

-- strategy == los, pivot_point == 5, holding_period == 10
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg5slp<0 THEN -pct_gain10 ELSE pct_gain10 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'los', 5, 10 FROM mas;

-- strategy == los, pivot_point == 10, holding_period == 10
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg10slp<0 THEN -pct_gain10 ELSE pct_gain10 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'los', 10, 10 FROM mas;

-- strategy == los, pivot_point == 20, holding_period == 10
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg20slp<0 THEN -pct_gain10 ELSE pct_gain10 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'los', 20, 10 FROM mas;

-- bump holding_period to 20

-- strategy == los, pivot_point == 5, holding_period == 20
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg5slp<0 THEN -pct_gain20 ELSE pct_gain20 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'los', 5, 20 FROM mas;

-- strategy == los, pivot_point == 10, holding_period == 20
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg10slp<0 THEN -pct_gain20 ELSE pct_gain20 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'los', 10, 20 FROM mas;

-- strategy == los, pivot_point == 20, holding_period == 20
INSERT INTO yfact(pg,day_id,tkr,strategy,pivot_point,holding_period)
SELECT
CASE WHEN yprice_avg20slp<0 THEN -pct_gain20 ELSE pct_gain20 END
,0+TO_CHAR(ydate,'YYYYMMDD'),tkr,'los', 20, 20 FROM mas;

--
-- Verify that the dimension tables can be joined
--

SELECT COUNT(*)FROM yfact;

SELECT COUNT(*)
FROM yfact y
,day_dimension d
,tkr_dimension t
,strategy_dimension s
,pivot_point_dimension p
,holding_period_dimension h
WHERE y.day_id        =d.day_id
AND   y.tkr           =t.tkr
AND   y.strategy      =s.strategy
AND   y.pivot_point   =p.pivot_point
AND   y.holding_period=h.holding_period
/

--
-- cr_yfact_ru.sql
--

-- Creates a set of fact tables which have dimensions "rolled-up" using a simple average.

-- Average-Roll-Up tkr
DROP   TABLE yfact_rut;
CREATE TABLE yfact_rut AS SELECT AVG(pg)pg,day_id,strategy,pivot_point,holding_period FROM yfact GROUP BY day_id,strategy,pivot_point,holding_period;

-- Average-Roll-Up strategy
DROP   TABLE yfact_rus ;
CREATE TABLE yfact_rus AS SELECT AVG(pg)pg,day_id,tkr,pivot_point,holding_period FROM yfact GROUP BY day_id,tkr,pivot_point,holding_period;

-- Average-Roll-Up pivot_point
DROP   TABLE yfact_rup ;
CREATE TABLE yfact_rup AS SELECT AVG(pg)pg,day_id,tkr,strategy,holding_period FROM yfact GROUP BY day_id,tkr,strategy,holding_period;

-- Average-Roll-Up holding_period
DROP   TABLE yfact_ruh;
CREATE TABLE yfact_ruh AS SELECT AVG(pg)pg,day_id,tkr,strategy,pivot_point FROM yfact GROUP BY day_id,tkr,strategy,pivot_point;

-- Average-Roll-Up tkr and strategy
DROP   TABLE yfact_ruts;
CREATE TABLE yfact_ruts AS SELECT AVG(pg)pg,day_id,pivot_point,holding_period FROM yfact GROUP BY day_id,pivot_point,holding_period;

-- Average-Roll-Up tkr and pivot_point
DROP   TABLE yfact_rutp;
CREATE TABLE yfact_rutp AS SELECT AVG(pg)pg,day_id,strategy,holding_period FROM yfact GROUP BY day_id,strategy,holding_period;

-- Average-Roll-Up tkr and holding_period
DROP   TABLE yfact_ruth;
CREATE TABLE yfact_ruth AS SELECT AVG(pg)pg,day_id,strategy,pivot_point FROM yfact GROUP BY day_id,strategy,pivot_point;

oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ cat mas_bh.rb
#!/usr/bin/env ruby

# mas_bh.rb

# Demo: ruby -r mas_bh.rb -e 'MasBh.new.write_score_sql_scripts'

# Ruby script which uses nested loops to generate several large SQL scripts.
# Each SQL script is a wrapper for the SVM algorithm resident within Oracle Data Miner.

class MasBh
require 'rubygems'
require 'oci8'
  def write_score_sql_scripts
    # tkrs_a will hold a list of unique tkr names resident within the stock-price-time-series data.
    tkrs_a = []
    (oci8conn = OCI8.new('trade','t')).exec("SELECT DISTINCT tkr FROM etfmas ORDER BY tkr"){ |r| tkrs_a << r.to_s }
    # See em.
    p(tkrs_a)

    # holding_periods_a holds integers representing days a tkr is held before its percentage gain is measured.
    holding_periods_a = ["5","10","20"]

    # pp is acronym for Pivot Point.  It is not used by the BH strategy but we set it here so it will propagate into the mas_predictions table.
    # Having it in that table makes it easier to compare BH strategy to LOC and LOS strategies.
    pp="20"

    # For bh strategy, build-model, and then score.
    # The score will give us the probability that the pct-gain will be >= (the average + 1 std. deviation).
    strategy="bh"
    tkrs_a.each do |tkr|
      sqlscript_name = "mas_#{strategy}_#{tkr}_score.sql"
      p(sqlscript_name)
      (fhw = File.open(sqlscript_name,"w")).write("--\n-- #{sqlscript_name}\n--\n\n"); fhw.close
      holding_periods_a.each do |hp|
        some_dates_a = []
        oci8conn.exec("SELECT TO_CHAR(ydate,'YYYY-MM-DD')FROM etfmas WHERE tkr='#{tkr}'AND ydate>TO_DATE('2002-09-01','YYYY-MM-DD')AND ydate<TO_DATE('2009-04-01','YYYY-MM-DD')ORDER BY ydate"){ |r| some_dates_a << r.to_s }
        some_dates_a.each do |adate|

sqlplus_syntax = <<END
--
CREATE OR REPLACE view sme AS SELECT
tkr_date
,NULL aa_targ_att
,dayspast
,yprice
,yvol
,yprice_avg5
,yprice_avg10
,yprice_avg20
,yprice_avg5slp
,yprice_avg10slp
,yprice_avg20slp
,shy_yprice_avg5slp
,xle_yprice_avg5slp
,shy_yprice_avg10slp
,xle_yprice_avg10slp
,shy_yprice_avg20slp
,xle_yprice_avg20slp
FROM mas WHERE tkr='#{tkr}'AND ydate='#{adate}'
/

CREATE OR REPLACE view bme AS SELECT
tkr_date
,CASE WHEN pct_gain#{hp}>(SELECT AVG(pct_gain#{hp})+STDDEV(pct_gain#{hp})FROM mas WHERE tkr='#{tkr}')THEN'up'ELSE aa_targ_att END aa_targ_att
,dayspast
,yprice
,yvol
,yprice_avg5
,yprice_avg10
,yprice_avg20
,yprice_avg5slp
,yprice_avg10slp
,yprice_avg20slp
,shy_yprice_avg5slp
,xle_yprice_avg5slp
,shy_yprice_avg10slp
,xle_yprice_avg10slp
,shy_yprice_avg20slp
,xle_yprice_avg20slp
FROM mas WHERE tkr='#{tkr}'AND 5+#{hp}+ydate<'#{adate}'
/


@cr_bme.sql #{strategy} #{hp} #{pp}
PURGE RECYCLEBIN;
--
END
          (fha = File.open(sqlscript_name,"a")).write(sqlplus_syntax); fha.close
        end # tkrs_a.each
      end # some_dates_a.each
    end # holding_periods_a.each
  end # def write_score_sql_scripts
end # class MasBh

MasBh.new.write_score_sql_scripts
oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ 

#!/usr/bin/env ruby

# mas.rb

# Demo: ruby -r mas.rb -e 'Mas.new.write_score_sql_scripts'

# Ruby script which uses nested loops to generate several SQL scripts.
# Each script is a wrapper for the SVM algorithm resident within Oracle Data Miner.

# This ruby script is a tool to study possible strategies for trading individual ETFs.
# Also it aims to answer the question,
# "Can SVM effectively tell us to abandon all strategies and move to cash during certain conditions?"

# This Ruby script helps me deal with a cartesian product of these attributes:
# Strategy (Long Or Cash, Long Or Short)
# Strategy Pivot Points (5day-Moving-Avg-Slope, 10day-Moving-Avg-Slope, 20day-Moving-Avg-Slope)
# Holding Period (days a tkr is held before its percentage gain is measured)
# Dates in a time-series (ydate)
# Stock Ticker (tkr).  Currently some ETF symbols: SPY, DIA, EFA, MDY, XLE, ...

class Mas
require 'rubygems'
require 'oci8'
  def write_score_sql_scripts
    # tkrs_a will hold a list of unique tkr names resident within the stock-price-time-series data.
    tkrs_a = []
    (oci8conn = OCI8.new('trade','t')).exec("SELECT DISTINCT tkr FROM etfmas ORDER BY tkr"){ |r| tkrs_a << r.to_s }
    # See em.
    p(tkrs_a)

    # pivot_points_a holds integers representing a type of moving average slope.
    # Some strategies (loc, los) depend upon a pivot point.
    # For example, The loc-Trader will watch the moving average slope.  Once it moves above zero, he will go Long.
    # Once it moves below zero, he will sell so that he holds cash.
    pivot_points_a = ["5","10","20"]

    # holding_periods_a holds integers representing days a tkr is held before its percentage gain is measured.
    holding_periods_a = ["5","10","20"]

    # strategies_a holds a list of acronyms for strategies.
    # Some of them are:
    # bh = Buy and Hold aka Long
    # loc = Long Or Cash
    # los = Long Or Short
    strategies_a = ['loc','los']

    # For each strategy, build-model, and then score.
    # The score will give us the probability that the pct-gain will be above a threshold dependent on std. deviation.
    strategies_a.each do |strategy|
      tkrs_a.each do |tkr|
        sqlscript_name = "mas_#{strategy}_#{tkr}_score.sql"
        (fhw = File.open(sqlscript_name,"w")).write("--\n-- #{sqlscript_name}\n--\n\n"); fhw.close
        pivot_points_a.each do |pp|
          holding_periods_a.each do |hp|
            p("strategy: #{strategy}  tkr: #{tkr} pivot_point: #{pp} holding_period: #{hp}")
            some_dates_a = []
            oci8conn.exec("SELECT TO_CHAR(ydate,'YYYY-MM-DD')FROM etfmas WHERE tkr='#{tkr}'AND ydate>TO_DATE('2002-09-01','YYYY-MM-DD')AND ydate<TO_DATE('2009-04-01','YYYY-MM-DD')ORDER BY ydate"){ |r| some_dates_a << r.to_s }
            some_dates_a.each do |adate|


sqlplus_syntax1 = <<ENDsqlplus_syntax1
CREATE OR REPLACE view sme AS SELECT
NULL aa_targ_att
,tkr_date
,dayspast
,yprice
,yvol
,yprice_avg5
,yprice_avg10
,yprice_avg20
,yprice_avg5slp
,yprice_avg10slp
,yprice_avg20slp
,shy_yprice_avg5slp
,xle_yprice_avg5slp
,shy_yprice_avg10slp
,xle_yprice_avg10slp
,shy_yprice_avg20slp
,xle_yprice_avg20slp
FROM mas WHERE tkr='#{tkr}'AND ydate='#{adate}'
/
CREATE OR REPLACE view bme AS SELECT
ENDsqlplus_syntax1

              # Here the aa_targ_att column depends on my choice of strategy
              case strategy
                when "loc"
aa_targ_att_syntax = <<ENDloc_syntax
CASE WHEN yprice_avg#{pp}slp<0 THEN'nup'
ELSE
  CASE WHEN pct_gain#{hp}>(SELECT AVG(pct_gain#{hp})+STDDEV(pct_gain#{hp})FROM mas WHERE tkr='#{tkr}')THEN'up'ELSE'nup'END
END aa_targ_att
ENDloc_syntax
                when "los"
aa_targ_att_syntax = <<ENDlos_syntax
CASE WHEN yprice_avg#{pp}slp<0 THEN
  CASE WHEN pct_gain#{hp}<(SELECT AVG(pct_gain#{hp})-STDDEV(pct_gain#{hp})FROM mas WHERE tkr='#{tkr}')THEN'up'ELSE'nup'END
ELSE
  CASE WHEN pct_gain#{hp}>(SELECT AVG(pct_gain#{hp})+STDDEV(pct_gain#{hp})FROM mas WHERE tkr='#{tkr}')THEN'up'ELSE'nup'END
END aa_targ_att
ENDlos_syntax
              end

              # Mix together the syntax
sqlplus_syntax = <<ENDsqlplus_syntax
#{sqlplus_syntax1}#{aa_targ_att_syntax},tkr_date
,dayspast
,yprice
,yvol
,yprice_avg5
,yprice_avg10
,yprice_avg20
,yprice_avg5slp
,yprice_avg10slp
,yprice_avg20slp
,shy_yprice_avg5slp
,xle_yprice_avg5slp
,shy_yprice_avg10slp
,xle_yprice_avg10slp
,shy_yprice_avg20slp
,xle_yprice_avg20slp
FROM mas WHERE tkr='#{tkr}'AND 5+#{hp}+ydate<'#{adate}'
/

@cr_bme.sql #{strategy} #{hp} #{pp}
PURGE RECYCLEBIN;

--
ENDsqlplus_syntax
              #Use cr_bme.sql to build a model from bme table and then score the sme table.
              #Since we are scoring only 1 tkr for 1 day, we expect only 1 prediction.
              (fha = File.open(sqlscript_name,"a")).write(sqlplus_syntax); fha.close
            end # tkrs_a.each
          end # some_dates_a.each
        end # holding_periods_a.each
      end # pivot_points_a.each
    end # strategies_a.each
  end # def write_score_sql_scripts
end # class Mas

Mas.new.write_score_sql_scripts
# end of ruby script

oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ cat cr_bme.sql
--
-- cr_bme.sql
--

-- usage: cr_bme.sql strategy holding_period pivot_point

-- A template script which accepts a pattern on the command line which is used at various places in the script to name related things.
-- Demo: @cr_bme.sql los 20 10

DEFINE strategy       = '&&1'
DEFINE holding_period = '&&2'
DEFINE pivot_point    = '&&3'

DEFINE target     = 'aa_targ_att'
DEFINE model_name = 'mas_model'
DEFINE bldtable   = 'bme'
DEFINE scoretable = 'sme'
DEFINE case_id    = 'tkr_date'

-- Define a variable to help me exclude some columns from some data mining calls
DEFINE exclude1 = "'&target','&case_id'"

-- Builds an SVM model using pl/sql.

-----------------------------------------------------------------------
--                            BUILD THE MODEL
-----------------------------------------------------------------------

-- Cleanup old build data preparation objects for repeat runs
DELETE svmc_miss_num;
DELETE svmc_miss_cat;
DELETE svmc_clip;
DELETE svmc_norm;
DELETE svmc_settings;

BEGIN EXECUTE IMMEDIATE 'DROP VIEW svmc_winsor';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP VIEW svmc_build_prep';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP VIEW xformed_build_miss_num';
EXCEPTION WHEN OTHERS THEN NULL; END;
/

BEGIN EXECUTE IMMEDIATE 'DROP VIEW xformed_build_miss_cat';
EXCEPTION WHEN OTHERS THEN NULL; END;
/


--------------------------------
-- PREPARE BUILD (TRAINING) DATA
--

-- 1. Missing Value treatment for all Predictors and
-- 2. Outlier Treatment and
-- 3. Normalization are performed below.
--    NOTE: that unlike SVM regression, the classification target is NOT
--    normalized here.

BEGIN
  -- Perform missing value treatment for all predictors
  -- create miss tables
--  DBMS_DATA_MINING_TRANSFORM.CREATE_MISS_NUM (miss_table_name => 'svmc_miss_num');
--  DBMS_DATA_MINING_TRANSFORM.CREATE_MISS_CAT (miss_table_name => 'svmc_miss_cat');

  -- populate miss tables
  DBMS_DATA_MINING_TRANSFORM.INSERT_MISS_NUM_MEAN (
    miss_table_name => 'svmc_miss_num',
    data_table_name => '&bldtable',
    exclude_list    => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST (&exclude1));

  DBMS_DATA_MINING_TRANSFORM.INSERT_MISS_CAT_MODE (
    miss_table_name => 'svmc_miss_cat',
    data_table_name => '&bldtable',
    exclude_list    => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST (&exclude1));

  -- xform input data to replace missing values
  DBMS_DATA_MINING_TRANSFORM.XFORM_MISS_NUM(
    miss_table_name => 'svmc_miss_num',
    data_table_name => '&bldtable',
    xform_view_name => 'xformed_build_miss_num');
  DBMS_DATA_MINING_TRANSFORM.XFORM_MISS_CAT(
    miss_table_name => 'svmc_miss_cat',
    data_table_name => '&bldtable',
    xform_view_name => 'xformed_build_miss_cat');

  -- Perform outlier treatment.
  -- create clip table
  --  DBMS_DATA_MINING_TRANSFORM.CREATE_CLIP (clip_table_name => 'svmc_clip');

  -- populate clip table
  DBMS_DATA_MINING_TRANSFORM.INSERT_CLIP_WINSOR_TAIL (
    clip_table_name => 'svmc_clip',
    data_table_name => '&bldtable',
    tail_frac       => 0.025,
    exclude_list    => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST (&exclude1));

  -- xform input data to winsorized data
  DBMS_DATA_MINING_TRANSFORM.XFORM_CLIP(
    clip_table_name => 'svmc_clip',
    data_table_name => '&bldtable',
    xform_view_name => 'svmc_winsor');

  -- create normalization table
  -- DBMS_DATA_MINING_TRANSFORM.CREATE_NORM_LIN (norm_table_name => 'svmc_norm');

  -- populate normalization table based on winsorized data
  DBMS_DATA_MINING_TRANSFORM.INSERT_NORM_LIN_MINMAX (
    norm_table_name => 'svmc_norm',
    data_table_name => 'svmc_winsor',
    exclude_list    => DBMS_DATA_MINING_TRANSFORM.COLUMN_LIST (&exclude1));

  -- normalize the original data
  DBMS_DATA_MINING_TRANSFORM.XFORM_NORM_LIN (
    norm_table_name => 'svmc_norm',
    data_table_name => '&bldtable',
    xform_view_name => 'svmc_build_prep');
END;
/

-- CREATE AND POPULATE A SETTINGS TABLE
--
-- DROP   TABLE svmc_settings ;
-- CREATE TABLE svmc_settings (
--   setting_name  VARCHAR2(30),
--   setting_value VARCHAR2(30));

-- The default classification algorithm is Naive Bayes. So override
-- this choice to SVM using a settings table.
-- SVM chooses a kernel type automatically. This choice can be overriden
-- by the user. Linear kernel is preferred high dimensional data, and
-- Gaussian kernel for low dimensional data. Here we use linear kernel
-- to demonstrate the get_model_details_svm() API, which applies only for
-- models.
--
BEGIN
-- Populate settings table
  INSERT INTO svmc_settings (setting_name, setting_value) VALUES
  (dbms_data_mining.algo_name, dbms_data_mining.algo_support_vector_machines);
  INSERT INTO svmc_settings (setting_name, setting_value) VALUES
  (dbms_data_mining.svms_kernel_function, dbms_data_mining.svms_gaussian);
  -- 99123123 is 99,123,123 is 99mb
  INSERT INTO svmc_settings (setting_name, setting_value) VALUES
  (dbms_data_mining.svms_kernel_cache_size,222123123);
--  (dbms_data_mining.svms_kernel_cache_size,99123123);
--  (dbms_data_mining.svms_kernel_cache_size,333123123);
  -- Examples of other possible overrides are:
  -- (dbms_data_mining.svms_kernel_function, dbms_data_mining.svms_linear);
  --(dbms_data_mining.svms_kernel_function, dbms_data_mining.svms_complexity_factor);
  COMMIT;
END;
/

---------------------
-- CREATE A NEW MODEL
--
-- Cleanup old model with the same name for repeat runs
BEGIN DBMS_DATA_MINING.DROP_MODEL('&model_name');
EXCEPTION WHEN OTHERS THEN NULL; END;
/

-- Build a new SVM Model
BEGIN
  DBMS_DATA_MINING.CREATE_MODEL(
    model_name          => '&model_name',
    mining_function     => dbms_data_mining.classification,
    data_table_name     => 'svmc_build_prep',
    case_id_column_name => '&case_id',
    target_column_name  => '&target',
    settings_table_name => 'svmc_settings');
END;
/

-----------------------------------------------------------------------
--                               APPLY/score THE MODEL
-----------------------------------------------------------------------

-- Cleanup old scoring data preparation objects for repeat runs
BEGIN EXECUTE IMMEDIATE 'DROP VIEW xformed_apply_miss_num';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP VIEW xformed_apply_miss_cat';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
BEGIN EXECUTE IMMEDIATE 'DROP VIEW svmc_apply_prep';
EXCEPTION WHEN OTHERS THEN NULL; END;
/
-----------------------
-- PREPARE SCORING DATA
--
-- If the data for model creation has been prepared, then the data
-- to be scored using the model must be prepared in the same manner
-- in order to obtain meaningful results.
--
-- 1. Missing Value treatment for all Predictors and
-- 2. Normalization
-- No outlier treatment will be performed during test and apply. The
-- normalization step is sufficient, since the normalization parameters
-- already capture the effects of outlier treatment done with build data.
--
BEGIN
  -- Xform Test data to replace missing values
  DBMS_DATA_MINING_TRANSFORM.XFORM_MISS_NUM(
    miss_table_name => 'svmc_miss_num',
    data_table_name => '&scoretable',
    xform_view_name => 'xformed_apply_miss_num');

  DBMS_DATA_MINING_TRANSFORM.XFORM_MISS_CAT(
    miss_table_name => 'svmc_miss_cat',
    data_table_name => '&scoretable',
    xform_view_name => 'xformed_apply_miss_cat');

  -- Normalize the data to be scored
  DBMS_DATA_MINING_TRANSFORM.XFORM_NORM_LIN (
    norm_table_name => 'svmc_norm',
    data_table_name => '&scoretable',
    xform_view_name => 'svmc_apply_prep');
END;
/

-- Get prob and the prediction:
INSERT INTO mas_predictions (tkr_date,strategy,holding_period,pivot_point,prediction,prob_up,prob_nup,run_date)
SELECT
tkr_date,'&strategy','&holding_period','&pivot_point'
,PREDICTION(            &model_name   USING *)prediction
,PREDICTION_PROBABILITY(&model_name,'up' USING *)prob_up
,PREDICTION_PROBABILITY(&model_name,'nup' USING *)prob_nup
,sysdate
FROM svmc_apply_prep
/
oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ 

13:22:27 SQL> 
13:22:28 SQL> 
13:22:28 SQL> 
13:22:29 SQL> 
13:22:33 SQL> 
13:22:33 SQL> @qry_mas_predictions.sql
13:22:39 SQL> SET ECHO ON
13:22:39 SQL> --
13:22:39 SQL> -- qry_mas_predictions.sql
13:22:39 SQL> --
13:22:39 SQL> 
13:22:39 SQL> -- This script displays interesting data from mas_predictions table.
13:22:39 SQL> -- The mas_predictions table contains predictions of classifications of the mas table.
13:22:39 SQL> -- The two classifications are 'up' and 'nup'.
13:22:39 SQL> -- The original classification values reside in the column: bme.aa_targ_att
13:22:39 SQL> -- The predicted classification values reside in the column: mas_predictions.prediction
13:22:39 SQL> -- Each prediction comes with a confidence level which resides in two columns:
13:22:39 SQL> -- -- prob_up and prob_nup
13:22:39 SQL> -- Be aware that prob_up + prob_nup is equal to 1.
13:22:39 SQL> 
13:22:39 SQL> SET LINES 55
13:22:39 SQL> DESCRIBE mas_predictions
 Name                       Null?    Type
 -------------------------- -------- ------------------
 TKR_DATE                            VARCHAR2(19)
 STRATEGY                            VARCHAR2(9)
 HOLDING_PERIOD                      NUMBER
 PIVOT_POINT                         NUMBER
 PREDICTION                          VARCHAR2(11)
 PROB_UP                             NUMBER
 PROB_NUP                            NUMBER
 RUN_DATE                            DATE

13:22:39 SQL> 
13:22:39 SQL> -- Count the tkrs
13:22:39 SQL> SELECT tkr,COUNT(tkr)FROM
13:22:39   2  (
13:22:39   3  SELECT
13:22:39   4  REGEXP_SUBSTR(tkr_date,'...')tkr
13:22:39   5  ,strategy
13:22:39   6  ,holding_period
13:22:39   7  ,pivot_point
13:22:39   8  ,prediction
13:22:39   9  ,prob_up
13:22:39  10  ,prob_nup
13:22:39  11  ,run_date
13:22:39  12  FROM mas_predictions
13:22:39  13  )
13:22:39  14  GROUP BY tkr ORDER BY tkr
13:22:39  15  /

TKR COUNT(TKR)
--- ----------
DIA      69108
EFA      68611
MDY      69078
OIH      68739
SHY      69140
SPY      69254
XLB      68648
XLE      67646
XLF      68918
XLI      69056
XLK      68985
XLV      69552

12 rows selected.

Elapsed: 00:00:00.00
13:22:39 SQL> 
13:22:39 SQL> -- Drill down into counts of strategy, holding_period, and pivot_point
13:22:39 SQL> SET LINES 77 PAGES 55
13:22:39 SQL> COLUMN tkr FORMAT A3
13:22:39 SQL> SELECT tkr, strategy, holding_period, pivot_point, COUNT(tkr)FROM
13:22:39   2  (
13:22:39   3  SELECT
13:22:39   4  REGEXP_SUBSTR(tkr_date,'...')tkr
13:22:39   5  ,strategy
13:22:39   6  ,holding_period
13:22:39   7  ,pivot_point
13:22:39   8  ,prediction
13:22:39   9  ,prob_up
13:22:39  10  ,prob_nup
13:22:39  11  ,run_date
13:22:39  12  FROM mas_predictions
13:22:39  13  )
13:22:39  14  GROUP BY tkr,strategy,holding_period,pivot_point
13:22:39  15  ORDER BY tkr,strategy,holding_period,pivot_point
13:22:39  16  /

TKR STRATEGY  HOLDING_PERIOD PIVOT_POINT COUNT(TKR)
--- --------- -------------- ----------- ----------
DIA bh                     5          20       3312
DIA bh                    10          20       3312
DIA bh                    20          20       3244
DIA loc                    5           5       3312
DIA loc                    5          10       3312
DIA loc                    5          20       3312
DIA loc                   10           5       3312
DIA loc                   10          10       3312
DIA loc                   10          20       3312
DIA loc                   20           5       3234
DIA loc                   20          10       3220
DIA loc                   20          20       3202
DIA los                    5           5       3312
DIA los                    5          10       3312
DIA los                    5          20       3312
DIA los                   10           5       3312
DIA los                   10          10       3312
DIA los                   10          20       3312
DIA los                   20           5       3284
DIA los                   20          10       3284
DIA los                   20          20       3272
EFA bh                     5          20       3312
EFA bh                    10          20       3312
EFA bh                    20          20       3244
EFA loc                    5           5       3312
EFA loc                    5          10       3244
EFA loc                    5          20       3214
EFA loc                   10           5       3312
EFA loc                   10          10       3312
EFA loc                   10          20       3218
EFA loc                   20           5       3214
EFA loc                   20          10       3224
EFA loc                   20          20       2996
EFA los                    5           5       3312
EFA los                    5          10       3308
EFA los                    5          20       3299
EFA los                   10           5       3312
EFA los                   10          10       3312
EFA los                   10          20       3284
EFA los                   20           5       3284
EFA los                   20          10       3284
EFA los                   20          20       3302
MDY bh                     5          20       3312
MDY bh                    10          20       3312
MDY bh                    20          20       3312
MDY loc                    5           5       3312
MDY loc                    5          10       3312
MDY loc                    5          20       3312
MDY loc                   10           5       3312
MDY loc                   10          10       3312
MDY loc                   10          20       3194
MDY loc                   20           5       3192

TKR STRATEGY  HOLDING_PERIOD PIVOT_POINT COUNT(TKR)
--- --------- -------------- ----------- ----------
MDY loc                   20          10       3312
MDY loc                   20          20       3192
MDY los                    5           5       3312
MDY los                    5          10       3312
MDY los                    5          20       3312
MDY los                   10           5       3312
MDY los                   10          10       3312
MDY los                   10          20       3274
MDY los                   20           5       3284
MDY los                   20          10       3312
MDY los                   20          20       3262
OIH bh                     5          20       3312
OIH bh                    10          20       3312
OIH bh                    20          20       3312
OIH loc                    5           5       3312
OIH loc                    5          10       3312
OIH loc                    5          20       3294
OIH loc                   10           5       3312
OIH loc                   10          10       3312
OIH loc                   10          20       3214
OIH loc                   20           5       2966
OIH loc                   20          10       3230
OIH loc                   20          20       3200
OIH los                    5           5       3312
OIH los                    5          10       3312
OIH los                    5          20       3303
OIH los                   10           5       3312
OIH los                   10          10       3312
OIH los                   10          20       3280
OIH los                   20           5       3292
OIH los                   20          10       3264
OIH los                   20          20       3264
SHY bh                     5          20       3309
SHY bh                    10          20       3310
SHY bh                    20          20       3280
SHY loc                    5           5       3309
SHY loc                    5          10       3308
SHY loc                    5          20       3308
SHY loc                   10           5       3306
SHY loc                   10          10       3274
SHY loc                   10          20       3274
SHY loc                   20           5       3280
SHY loc                   20          10       3280
SHY loc                   20          20       3280
SHY los                    5           5       3312
SHY los                    5          10       3308
SHY los                    5          20       3308
SHY los                   10           5       3306
SHY los                   10          10       3274
SHY los                   10          20       3274
SHY los                   20           5       3280
SHY los                   20          10       3280

TKR STRATEGY  HOLDING_PERIOD PIVOT_POINT COUNT(TKR)
--- --------- -------------- ----------- ----------
SHY los                   20          20       3280
SPY bh                     5          20       3312
SPY bh                    10          20       3312
SPY bh                    20          20       3312
SPY loc                    5           5       3312
SPY loc                    5          10       3312
SPY loc                    5          20       3312
SPY loc                   10           5       3312
SPY loc                   10          10       3312
SPY loc                   10          20       3194
SPY loc                   20           5       3312
SPY loc                   20          10       3312
SPY loc                   20          20       3202
SPY los                    5           5       3312
SPY los                    5          10       3312
SPY los                    5          20       3312
SPY los                   10           5       3312
SPY los                   10          10       3312
SPY los                   10          20       3284
SPY los                   20           5       3312
SPY los                   20          10       3312
SPY los                   20          20       3270
XLB bh                     5          20       3312
XLB bh                    10          20       3312
XLB bh                    20          20       3244
XLB loc                    5           5       3242
XLB loc                    5          10       3312
XLB loc                    5          20       3214
XLB loc                   10           5       3234
XLB loc                   10          10       3312
XLB loc                   10          20       3190
XLB loc                   20           5       3192
XLB loc                   20          10       3202
XLB loc                   20          20       3194
XLB los                    5           5       3304
XLB los                    5          10       3312
XLB los                    5          20       3306
XLB los                   10           5       3306
XLB los                   10          10       3312
XLB los                   10          20       3280
XLB los                   20           5       3294
XLB los                   20          10       3290
XLB los                   20          20       3284
XLE bh                     5          20       3312
XLE bh                    10          20       3312
XLE bh                    20          20       3312
XLE loc                    5           5       3312
XLE loc                    5          10       3312
XLE loc                    5          20       2940
XLE loc                   10           5       3312
XLE loc                   10          10       3312
XLE loc                   10          20       2944

TKR STRATEGY  HOLDING_PERIOD PIVOT_POINT COUNT(TKR)
--- --------- -------------- ----------- ----------
XLE loc                   20           5       2946
XLE loc                   20          10       3312
XLE loc                   20          20       2944
XLE los                    5           5       3312
XLE los                    5          10       3312
XLE los                    5          20       3298
XLE los                   10           5       3312
XLE los                   10          10       3312
XLE los                   10          20       3280
XLE los                   20           5       3294
XLE los                   20          10       3312
XLE los                   20          20       2944
XLF bh                     5          20       3312
XLF bh                    10          20       3312
XLF bh                    20          20       3312
XLF loc                    5           5       3312
XLF loc                    5          10       3312
XLF loc                    5          20       3194
XLF loc                   10           5       3312
XLF loc                   10          10       3312
XLF loc                   10          20       3194
XLF loc                   20           5       3312
XLF loc                   20          10       3312
XLF loc                   20          20       3012
XLF los                    5           5       3312
XLF los                    5          10       3312
XLF los                    5          20       3284
XLF los                   10           5       3312
XLF los                   10          10       3312
XLF los                   10          20       3284
XLF los                   20           5       3312
XLF los                   20          10       3312
XLF los                   20          20       3270
XLI bh                     5          20       3312
XLI bh                    10          20       3312
XLI bh                    20          20       3230
XLI loc                    5           5       3312
XLI loc                    5          10       3312
XLI loc                    5          20       3312
XLI loc                   10           5       3312
XLI loc                   10          10       3312
XLI loc                   10          20       3312
XLI loc                   20           5       3220
XLI loc                   20          10       3194
XLI loc                   20          20       3194
XLI los                    5           5       3312
XLI los                    5          10       3312
XLI los                    5          20       3312
XLI los                   10           5       3312
XLI los                   10          10       3312
XLI los                   10          20       3312
XLI los                   20           5       3284

TKR STRATEGY  HOLDING_PERIOD PIVOT_POINT COUNT(TKR)
--- --------- -------------- ----------- ----------
XLI los                   20          10       3282
XLI los                   20          20       3284
XLK bh                     5          20       3312
XLK bh                    10          20       3312
XLK bh                    20          20       3252
XLK loc                    5           5       3312
XLK loc                    5          10       3312
XLK loc                    5          20       3294
XLK loc                   10           5       3312
XLK loc                   10          10       3312
XLK loc                   10          20       3224
XLK loc                   20           5       3234
XLK loc                   20          10       3220
XLK loc                   20          20       3214
XLK los                    5           5       3312
XLK los                    5          10       3312
XLK los                    5          20       3303
XLK los                   10           5       3312
XLK los                   10          10       3312
XLK los                   10          20       3284
XLK los                   20           5       3284
XLK los                   20          10       3284
XLK los                   20          20       3272
XLV bh                     5          20       3312
XLV bh                    10          20       3312
XLV bh                    20          20       3312
XLV loc                    5           5       3312
XLV loc                    5          10       3312
XLV loc                    5          20       3312
XLV loc                   10           5       3312
XLV loc                   10          10       3312
XLV loc                   10          20       3312
XLV loc                   20           5       3312
XLV loc                   20          10       3312
XLV loc                   20          20       3312
XLV los                    5           5       3312
XLV los                    5          10       3312
XLV los                    5          20       3312
XLV los                   10           5       3312
XLV los                   10          10       3312
XLV los                   10          20       3312
XLV los                   20           5       3312
XLV los                   20          10       3312
XLV los                   20          20       3312

252 rows selected.

Elapsed: 00:00:00.01

13:22:39 SQL> 
13:22:39 SQL> -- Take a quick look at the predictions
13:22:39 SQL> 
13:22:39 SQL> -- Verify that prob_up + prob_nup is 1
13:22:39 SQL> SELECT AVG(prob_up + prob_nup)FROM mas_predictions;

AVG(PROB_UP+PROB_NUP)
---------------------
                    1

Elapsed: 00:00:00.00
13:22:39 SQL> -- Verify with a ROUND of 4 significant digits
13:22:39 SQL> SELECT COUNT(*)FROM mas_predictions WHERE ROUND((prob_up+prob_nup),4) !=1 ;

  COUNT(*)
----------
         0

Elapsed: 00:00:00.00
13:22:39 SQL> 
13:22:39 SQL> -- Count the predictions with confidence level rounded to 1 significant digit
13:22:39 SQL> SELECT
13:22:39   2  ROUND(prob_up,1)prob_up
13:22:39   3  ,ROUND(prob_nup,1)prob_nup
13:22:39   4  ,prediction
13:22:39   5  ,COUNT(prediction)
13:22:39   6  FROM
13:22:39   7  (
13:22:39   8    SELECT
13:22:39   9    REGEXP_SUBSTR(tkr_date,'...')tkr
13:22:39  10    ,strategy
13:22:39  11    ,holding_period
13:22:39  12    ,pivot_point
13:22:39  13    ,prediction
13:22:39  14    ,prob_up
13:22:39  15    ,prob_nup
13:22:39  16    ,run_date
13:22:39  17    FROM mas_predictions
13:22:39  18  )
13:22:39  19  GROUP BY prediction,ROUND(prob_up,1),ROUND(prob_nup,1)
13:22:39  20  ORDER BY prediction,ROUND(prob_up,1),ROUND(prob_nup,1)
13:22:39  21  /

   PROB_UP   PROB_NUP PREDICTION  COUNT(PREDICTION)
---------- ---------- ----------- -----------------
         0          1 nup                      1800
        .1         .9 nup                    154948
        .2         .8 nup                    614280
        .3         .7 nup                     35103
        .4         .6 nup                     10727
        .5         .5 nup                      2873
        .5         .5 up                       2033
        .6         .4 up                       2530
        .7         .3 up                       1417
        .8         .2 up                        775
        .9         .1 up                        247
         1          0 up                          2

12 rows selected.

Elapsed: 00:00:00.00
13:22:39 SQL> 
13:22:40 SQL> 
13:22:41 SQL> 
13:22:41 SQL> 

15:06:37 SQL> @cr_mp_j_yfact.sql
15:07:02 SQL> --
15:07:02 SQL> -- cr_mp_j_yfact.sql
15:07:02 SQL> --
15:07:02 SQL> 
15:07:02 SQL> -- Creates a view, mp_j_yfact, which joins mas_predictions with
15:07:02 SQL> -- yfact.  This view helps us analyze the accuracy of SVM predictions
15:07:02 SQL> -- within the mas_predictions table.
15:07:02 SQL> 
15:07:02 SQL> CREATE OR REPLACE VIEW mp_j_yfact AS SELECT
15:07:02   2  m.prediction
15:07:02   3  ,m.prob_up
15:07:02   4  ,y.pg             -- pct_gain
15:07:02   5  ,y.day_id          -- fk to day_dimension
15:07:02   6  ,y.tkr             -- DIA, EFA, MDY, ...
15:07:02   7  ,y.strategy        -- bh, loc, los
15:07:02   8  ,y.pivot_point     -- 5,10,20
15:07:02   9  ,y.holding_period  -- 5,10,20
15:07:02  10  FROM yfact y, mas_predictions m
15:07:02  11  WHERE REPLACE(m.tkr_date,'-') = y.tkr||y.day_id
15:07:02  12  AND y.strategy = m.strategy
15:07:02  13  AND y.pivot_point = m.pivot_point
15:07:02  14  AND y.holding_period = m.holding_period
15:07:02  15  /

View created.

Elapsed: 00:00:00.22
15:07:02 SQL> 
15:07:02 SQL> -- Now query
15:07:02 SQL> SELECT COUNT(*)FROM mas_predictions;

  COUNT(*)
----------
    826735

Elapsed: 00:00:00.00
15:07:02 SQL> SELECT COUNT(*)FROM yfact;

  COUNT(*)
----------
    544644

Elapsed: 00:00:00.00
15:07:02 SQL> SELECT COUNT(*)FROM mp_j_yfact;

  COUNT(*)
----------
    826735

Elapsed: 00:00:03.78
15:07:06 SQL> 
15:07:06 SQL> -- Look at data rolled up to highest level
15:07:06 SQL> SELECT holding_period,AVG(pg)avg_pct_gain,COUNT(*)FROM mp_j_yfact
15:07:06   2  GROUP BY holding_period ORDER BY holding_period
15:07:06   3  /

HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
-------------- ------------ ----------
             5   -.01880193     277235
            10     .0529347     276588
            20   .153422813     272912

Elapsed: 00:00:03.24
15:07:09 SQL> 
15:07:09 SQL> -- Drill down into prediction
15:07:09 SQL> SELECT    prediction, holding_period,AVG(pg)avg_pct_gain,COUNT(*)FROM mp_j_yfact
15:07:09   2  GROUP BY prediction,holding_period ORDER BY prediction,holding_period
15:07:09   3  /

PREDICTION  HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- -------------- ------------ ----------
nup                      5   -.01533182     276021
nup                     10   .062276532     274374
nup                     20   .167687269     269336
up                       5   -.80778346       1214
up                      10    -1.104769       2214
up                      20   -.92094285       3576

6 rows selected.

Elapsed: 00:00:03.25
15:07:12 SQL> 
15:09:16 SQL> 
15:09:17 SQL> 

15:21:05 SQL> 
15:21:05 SQL> @qry_mp_j_yfact.sql
15:21:52 SQL> --
15:21:52 SQL> -- qry_mp_j_yfact.sql
15:21:52 SQL> --
15:21:52 SQL> 
15:21:52 SQL> -- Drills down into yfact dimensions of view: mp_j_yfact
15:21:52 SQL> 
15:21:52 SQL> SELECT
15:21:52   2  tkr,prediction,strategy,pivot_point,holding_period
15:21:52   3  ,ROUND(AVG(pg),3)avg_pct_gain
15:21:52   4  ,COUNT(*)FROM mp_j_yfact
15:21:52   5  GROUP BY tkr,prediction,strategy,pivot_point,holding_period
15:21:52   6  ORDER BY tkr,prediction,strategy,pivot_point,holding_period
15:21:52   7  /

TKR         PREDICTION  STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- ----------- -------------- ------------ ----------
DIA         nup         bh                   20              5         .046       3305
DIA         nup         bh                   20             10         .089       3287
DIA         nup         bh                   20             20         .119       3127
DIA         nup         loc                   5              5        -.037       3312
DIA         nup         loc                   5             10         -.02       3308
DIA         nup         loc                   5             20        -.002       3192
DIA         nup         loc                  10              5        -.016       3312
DIA         nup         loc                  10             10         .028       3304
DIA         nup         loc                  10             20         .102       3187
DIA         nup         loc                  20              5          .02       3312
DIA         nup         loc                  20             10         .043       3312
DIA         nup         loc                  20             20         .106       3180
DIA         nup         los                   5              5        -.115       3294
DIA         nup         los                   5             10         -.12       3305
DIA         nup         los                   5             20        -.082       3207
DIA         nup         los                  10              5        -.106       3257
DIA         nup         los                  10             10        -.003       3258
DIA         nup         los                  10             20         .158       3206
DIA         nup         los                  20              5        -.012       3307
DIA         nup         los                  20             10         .042       3292
DIA         nup         los                  20             20         .152       3192
DIA         up          bh                   20              5        1.952          7
DIA         up          bh                   20             10        -.039         25
DIA         up          bh                   20             20          -.4        117
DIA         up          loc                   5             10        1.309          4
DIA         up          loc                   5             20          .23         42
DIA         up          loc                  10             10        1.071          8
DIA         up          loc                  10             20         .145         33
DIA         up          loc                  20             20        -.291         22
DIA         up          los                   5              5       -1.754         18
DIA         up          los                   5             10       -2.364          7
DIA         up          los                   5             20       -2.724         77
DIA         up          los                  10              5         1.32         55
DIA         up          los                  10             10       -1.546         54
DIA         up          los                  10             20        -3.03         78
DIA         up          los                  20              5         .925          5
DIA         up          los                  20             10       -7.344         20
DIA         up          los                  20             20        -2.12         80
EFA         nup         bh                   20              5          .14       3289
EFA         nup         bh                   20             10          .24       3254
EFA         nup         bh                   20             20          .46       3156
EFA         nup         loc                   5              5        -.018       3311
EFA         nup         loc                   5             10         .108       3310
EFA         nup         loc                   5             20         .332       3187
EFA         nup         loc                  10              5         .107       3244
EFA         nup         loc                  10             10         .231       3305
EFA         nup         loc                  10             20         .503       3178
EFA         nup         loc                  20              5         .195       3210
EFA         nup         loc                  20             10         .389       3196
EFA         nup         loc                  20             20         .741       2938
EFA         nup         los                   5              5        -.155       3299
EFA         nup         los                   5             10        -.014       3302
EFA         nup         los                   5             20         .296       3190
EFA         nup         los                  10              5         .093       3262
EFA         nup         los                  10             10         .249       3272
EFA         nup         los                  10             20         .676       3170
EFA         nup         los                  20              5          .29       3240
EFA         nup         los                  20             10         .619       3167
EFA         nup         los                  20             20        1.072       3177
EFA         up          bh                   20              5       -2.645         23
EFA         up          bh                   20             10         .052         58
EFA         up          bh                   20             20        -.368         88
EFA         up          loc                   5              5       -1.006          1
EFA         up          loc                   5             10        -.168          2
EFA         up          loc                   5             20        -.387         27
EFA         up          loc                  10             10       -1.181          7
EFA         up          loc                  10             20        -.191         46
EFA         up          loc                  20              5         .019          4
EFA         up          loc                  20             10        -.044         22
EFA         up          loc                  20             20         .378         58
EFA         up          los                   5              5        -.714         13
EFA         up          los                   5             10       -2.401         10
EFA         up          los                   5             20       -3.033         94
EFA         up          los                  10              5         .318         46
EFA         up          los                  10             10       -2.229         40
EFA         up          los                  10             20       -3.074        114
EFA         up          los                  20              5       -1.955         59
EFA         up          los                  20             10       -2.766        117
EFA         up          los                  20             20       -2.353        125
MDY         nup         bh                   20              5         .104       3296
MDY         nup         bh                   20             10         .189       3259
MDY         nup         bh                   20             20         .372       3293
MDY         nup         loc                   5              5        -.062       3312
MDY         nup         loc                   5             10         .036       3312
MDY         nup         loc                   5             20         .175       3190
MDY         nup         loc                  10              5         .012       3312
MDY         nup         loc                  10             10           .1       3312
MDY         nup         loc                  10             20         .309       3303
MDY         nup         loc                  20              5           .1       3312
MDY         nup         loc                  20             10         .198       3187
MDY         nup         loc                  20             20         .341       3192
MDY         nup         los                   5              5        -.229       3312
MDY         nup         los                   5             10        -.118       3307
MDY         nup         los                   5             20        -.018       3265
MDY         nup         los                  10              5        -.058       3299
MDY         nup         los                  10             10            0       3300

TKR         PREDICTION  STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- ----------- -------------- ------------ ----------
MDY         nup         los                  10             20         .297       3277
MDY         nup         los                  20              5         .111       3278
MDY         nup         los                  20             10          .22       3240
MDY         nup         los                  20             20         .413       3225
MDY         up          bh                   20              5         .367         16
MDY         up          bh                   20             10         .646         53
MDY         up          bh                   20             20       -1.939         19
MDY         up          loc                   5             20       -3.671          2
MDY         up          loc                  10             20        2.428          9
MDY         up          loc                  20             10         .515          7
MDY         up          los                   5             10       -4.499          5
MDY         up          los                   5             20       -4.595         19
MDY         up          los                  10              5       -5.891         13
MDY         up          los                  10             10        1.156         12
MDY         up          los                  10             20       -2.158         35
MDY         up          los                  20              5       -1.426         34
MDY         up          los                  20             10       -4.365         34
MDY         up          los                  20             20       -5.573         37
OIH         nup         bh                   20              5         .293       3299
OIH         nup         bh                   20             10         .558       3310
OIH         nup         bh                   20             20        1.165       3263
OIH         nup         loc                   5              5        -.092       3312
OIH         nup         loc                   5             10          .11       3305
OIH         nup         loc                   5             20         .487       2954
OIH         nup         loc                  10              5         .043       3312
OIH         nup         loc                  10             10         .253       3312
OIH         nup         loc                  10             20         .549       3226
OIH         nup         loc                  20              5         .163       3294
OIH         nup         loc                  20             10         .482       3214
OIH         nup         loc                  20             20         .718       3200
OIH         nup         los                   5              5        -.475       3312
OIH         nup         los                   5             10        -.333       3303
OIH         nup         los                   5             20        -.151       3264
OIH         nup         los                  10              5        -.209       3310
OIH         nup         los                  10             10        -.055       3309
OIH         nup         los                  10             20         .096       3243
OIH         nup         los                  20              5         .025       3298
OIH         nup         los                  20             10         .414       3277
OIH         nup         los                  20             20         .445       3258
OIH         up          bh                   20              5        -.057         13
OIH         up          bh                   20             10        3.035          2
OIH         up          bh                   20             20       -3.981         49
OIH         up          loc                   5             10        4.303          7
OIH         up          loc                   5             20        4.917         12
OIH         up          loc                  10             20        4.357          4
OIH         up          los                   5             10        3.767          9
OIH         up          los                   5             20         6.88         28
OIH         up          los                  10              5        4.845          2
OIH         up          los                  10             10         .569          3
OIH         up          los                  10             20        6.468         21
OIH         up          los                  20              5        9.603          5
OIH         up          los                  20             10        6.899          3
OIH         up          los                  20             20       33.461          6
SHY         nup         bh                   20              5         .067       3222
SHY         nup         bh                   20             10         .132       3145
SHY         nup         bh                   20             20         .262       3112
SHY         nup         loc                   5              5         .041       3281
SHY         nup         loc                   5             10         .086       3269
SHY         nup         loc                   5             20         .178       3222
SHY         nup         loc                  10              5         .048       3276
SHY         nup         loc                  10             10         .095       3230
SHY         nup         loc                  10             20         .188       3221
SHY         nup         loc                  20              5         .056       3238
SHY         nup         loc                  20             10         .111       3201
SHY         nup         loc                  20             20         .211       3203
SHY         nup         los                   5              5         .017       3257
SHY         nup         los                   5             10         .037       3225
SHY         nup         los                   5             20         .096       3109
SHY         nup         los                  10              5         .031       3265
SHY         nup         los                  10             10         .057       3141
SHY         nup         los                  10             20         .113       3026
SHY         nup         los                  20              5         .045       3229
SHY         nup         los                  20             10         .094       3093
SHY         nup         los                  20             20         .167       3002
SHY         up          bh                   20              5         .036         87
SHY         up          bh                   20             10         .129        165
SHY         up          bh                   20             20         .227        168
SHY         up          loc                   5              5           .1         28
SHY         up          loc                   5             10         .151         37
SHY         up          loc                   5             20         .305         58
SHY         up          loc                  10              5         .133         32
SHY         up          loc                  10             10         .114         44
SHY         up          loc                  10             20          .31         59
SHY         up          loc                  20              5         .031         70
SHY         up          loc                  20             10         .107         73
SHY         up          loc                  20             20         .347         77
SHY         up          los                   5              5        -.033         55
SHY         up          los                   5             10         .208         81
SHY         up          los                   5             20         .166        171
SHY         up          los                  10              5         .027         43
SHY         up          los                  10             10         .143        133
SHY         up          los                  10             20         .222        254
SHY         up          los                  20              5        -.008         79
SHY         up          los                  20             10         .058        181
SHY         up          los                  20             20         .176        278
SPY         nup         bh                   20              5         .051       3280

TKR         PREDICTION  STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- ----------- -------------- ------------ ----------
SPY         nup         bh                   20             10          .08       3292
SPY         nup         bh                   20             20         .121       3276
SPY         nup         loc                   5              5        -.093       3312
SPY         nup         loc                   5             10        -.036       3312
SPY         nup         loc                   5             20            0       3304
SPY         nup         loc                  10              5        -.042       3312
SPY         nup         loc                  10             10          .01       3307
SPY         nup         loc                  10             20         .128       3302
SPY         nup         loc                  20              5         .031       3312
SPY         nup         loc                  20             10         .042       3188
SPY         nup         loc                  20             20         .107       3196
SPY         nup         los                   5              5        -.235       3308
SPY         nup         los                   5             10        -.145       3295
SPY         nup         los                   5             20          -.1       3276
SPY         nup         los                  10              5        -.131       3281
SPY         nup         los                  10             10        -.035       3260
SPY         nup         los                  10             20         .179       3254
SPY         nup         los                  20              5         .021       3309
SPY         nup         los                  20             10         .061       3258
SPY         nup         los                  20             20         .175       3222
SPY         up          bh                   20              5        -.732         32
SPY         up          bh                   20             10        -.514         20
SPY         up          bh                   20             20         .333         36
SPY         up          loc                   5             20        1.118          8
SPY         up          loc                  10             10            0          5
SPY         up          loc                  10             20        1.826         10
SPY         up          loc                  20             10         .809          6
SPY         up          loc                  20             20        2.149          6
SPY         up          los                   5              5        4.898          4
SPY         up          los                   5             10        -.701         17
SPY         up          los                   5             20       -1.797         36
SPY         up          los                  10              5         .314         31
SPY         up          los                  10             10       -1.308         52
SPY         up          los                  10             20       -1.832         58
SPY         up          los                  20              5       -3.927          3
SPY         up          los                  20             10       -6.439         26
SPY         up          los                  20             20        -2.27         48
XLB         nup         bh                   20              5         .147       3300
XLB         nup         bh                   20             10         .265       3305
XLB         nup         bh                   20             20         .508       3193
XLB         nup         loc                   5              5        -.035       3241
XLB         nup         loc                   5             10        -.035       3233
XLB         nup         loc                   5             20         .029       3183
XLB         nup         loc                  10              5        -.012       3309
XLB         nup         loc                  10             10        -.007       3311
XLB         nup         loc                  10             20         .139       3194
XLB         nup         loc                  20              5         .071       3214
XLB         nup         loc                  20             10         .145       3190
XLB         nup         loc                  20             20         .292       3192
XLB         nup         los                   5              5        -.232       3295
XLB         nup         los                   5             10        -.381       3300
XLB         nup         los                   5             20        -.418       3262
XLB         nup         los                  10              5        -.172       3300
XLB         nup         los                  10             10        -.285       3310
XLB         nup         los                  10             20        -.185       3274
XLB         nup         los                  20              5        -.002       3281
XLB         nup         los                  20             10         .028       3259
XLB         nup         los                  20             20         .137       3222
XLB         up          bh                   20              5        -.926         12
XLB         up          bh                   20             10        3.869          7
XLB         up          bh                   20             20         .167         51
XLB         up          loc                   5              5         .417          1
XLB         up          loc                   5             10        2.879          1
XLB         up          loc                   5             20         .116          9
XLB         up          loc                  10              5       -1.311          3
XLB         up          loc                  10             10       -1.514          1
XLB         up          loc                  10             20       -3.931          8
XLB         up          loc                  20             20        -4.59          2
XLB         up          los                   5              5         .513          9
XLB         up          los                   5             10         .075          6
XLB         up          los                   5             20         .009         32
XLB         up          los                  10              5         .417         12
XLB         up          los                  10             10       -5.423          2
XLB         up          los                  10             20       -5.155         16
XLB         up          los                  20              5       -1.638         25
XLB         up          los                  20             10        -3.95         21
XLB         up          los                  20             20       -1.296         62
XLE         nup         bh                   20              5         .315       3301
XLE         nup         bh                   20             10         .606       3255
XLE         nup         bh                   20             20        1.111       3255
XLE         nup         loc                   5              5         .048       3312
XLE         nup         loc                   5             10         .307       3304
XLE         nup         loc                   5             20         .519       2939
XLE         nup         loc                  10              5         .113       3312
XLE         nup         loc                  10             10         .314       3302
XLE         nup         loc                  10             20         .492       3295
XLE         nup         loc                  20              5         .132       2939
XLE         nup         loc                  20             10          .33       2938
XLE         nup         loc                  20             20         .337       2930
XLE         nup         los                   5              5        -.212       3311
XLE         nup         los                   5             10        -.011       3284
XLE         nup         los                   5             20         -.12       3280
XLE         nup         los                  10              5        -.061       3292
XLE         nup         los                  10             10        -.003       3255
XLE         nup         los                  10             20         -.18       3292
XLE         nup         los                  20              5        -.047       3269

TKR         PREDICTION  STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- ----------- -------------- ------------ ----------
XLE         nup         los                  20             10        -.003       3250
XLE         nup         los                  20             20        -.442       2927
XLE         up          bh                   20              5       -1.435         11
XLE         up          bh                   20             10         .712         57
XLE         up          bh                   20             20        5.482         57
XLE         up          loc                   5             10       -1.892          8
XLE         up          loc                   5             20        4.135          7
XLE         up          loc                  10             10        -1.82         10
XLE         up          loc                  10             20        4.272         17
XLE         up          loc                  20              5        3.619          1
XLE         up          loc                  20             10       -1.908          6
XLE         up          loc                  20             20         6.04         14
XLE         up          los                   5              5       -2.513          1
XLE         up          los                   5             10         .709         28
XLE         up          los                   5             20        4.471         14
XLE         up          los                  10              5       -3.915         20
XLE         up          los                  10             10         .533         57
XLE         up          los                  10             20        2.625         20
XLE         up          los                  20              5        -.982         29
XLE         up          los                  20             10         1.45         30
XLE         up          los                  20             20        5.629         17
XLF         nup         bh                   20              5        -.106       3304
XLF         nup         bh                   20             10        -.254       3294
XLF         nup         bh                   20             20        -.669       3289
XLF         nup         loc                   5              5        -.286       3312
XLF         nup         loc                   5             10        -.389       3312
XLF         nup         loc                   5             20        -.518       3312
XLF         nup         loc                  10              5        -.166       3312
XLF         nup         loc                  10             10        -.299       3312
XLF         nup         loc                  10             20        -.315       3312
XLF         nup         loc                  20              5        -.054       3194
XLF         nup         loc                  20             10        -.147       3194
XLF         nup         loc                  20             20        -.228       3012
XLF         nup         los                   5              5        -.455       3312
XLF         nup         los                   5             10         -.51       3312
XLF         nup         los                   5             20         -.29       3273
XLF         nup         los                  10              5        -.204       3302
XLF         nup         los                  10             10        -.244       3283
XLF         nup         los                  10             20          .16       3261
XLF         nup         los                  20              5         .019       3280
XLF         nup         los                  20             10         .064       3238
XLF         nup         los                  20             20         .354       3207
XLF         up          bh                   20              5       -4.541          8
XLF         up          bh                   20             10       -2.898         18
XLF         up          bh                   20             20        1.511         23
XLF         up          los                   5             20       -8.051         39
XLF         up          los                  10              5       -3.605         10
XLF         up          los                  10             10      -10.092         29
XLF         up          los                  10             20       -8.629         51
XLF         up          los                  20              5        -9.54          4
XLF         up          los                  20             10       -7.469         46
XLF         up          los                  20             20       -4.214         63
XLI         nup         bh                   20              5         .044       3293
XLI         nup         bh                   20             10         .075       3280
XLI         nup         bh                   20             20         .108       3180
XLI         nup         loc                   5              5        -.032       3310
XLI         nup         loc                   5             10          .07       3312
XLI         nup         loc                   5             20         .098       3219
XLI         nup         loc                  10              5          .01       3312
XLI         nup         loc                  10             10         .089       3311
XLI         nup         loc                  10             20          .23       3188
XLI         nup         loc                  20              5         .056       3312
XLI         nup         loc                  20             10         .132       3311
XLI         nup         loc                  20             20         .215       3190
XLI         nup         los                   5              5        -.082       3280
XLI         nup         los                   5             10         .051       3295
XLI         nup         los                   5             20         .122       3238
XLI         nup         los                  10              5        -.002       3292
XLI         nup         los                  10             10         .154       3275
XLI         nup         los                  10             20         .405       3231
XLI         nup         los                  20              5         .107       3280
XLI         nup         los                  20             10         .271       3253
XLI         nup         los                  20             20         .378       3234
XLI         up          bh                   20              5       -1.306         19
XLI         up          bh                   20             10       -1.004         32
XLI         up          bh                   20             20        -.588         50
XLI         up          loc                   5              5       -1.531          2
XLI         up          loc                   5             20        1.111          1
XLI         up          loc                  10             10        1.671          1
XLI         up          loc                  10             20         .277          6
XLI         up          loc                  20             10        1.671          1
XLI         up          loc                  20             20        2.307          4
XLI         up          los                   5              5       -2.206         32
XLI         up          los                   5             10        4.694         17
XLI         up          los                   5             20       -5.369         46
XLI         up          los                  10              5       -2.224         20
XLI         up          los                  10             10       -3.346         37
XLI         up          los                  10             20       -5.929         51
XLI         up          los                  20              5       -3.248         32
XLI         up          los                  20             10       -3.738         59
XLI         up          los                  20             20       -5.085         50
XLK         nup         bh                   20              5         .167       3226
XLK         nup         bh                   20             10         .302       3225
XLK         nup         bh                   20             20          .31       3216
XLK         nup         loc                   5              5        -.082       3312
XLK         nup         loc                   5             10         .022       3312

TKR         PREDICTION  STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- ----------- -------------- ------------ ----------
XLK         nup         loc                   5             20         .058       3234
XLK         nup         loc                  10              5         .009       3312
XLK         nup         loc                  10             10         .095       3304
XLK         nup         loc                  10             20         .121       3220
XLK         nup         loc                  20              5         .038       3289
XLK         nup         loc                  20             10         .065       3216
XLK         nup         loc                  20             20         .044       3214
XLK         nup         los                   5              5        -.237       3268
XLK         nup         los                   5             10         -.17       3280
XLK         nup         los                   5             20        -.074       3250
XLK         nup         los                  10              5        -.096       3309
XLK         nup         los                  10             10        -.019       3283
XLK         nup         los                  10             20          .06       3243
XLK         nup         los                  20              5        -.046       3296
XLK         nup         los                  20             10        -.011       3233
XLK         nup         los                  20             20        -.089       3240
XLK         up          bh                   20              5       -1.901         86
XLK         up          bh                   20             10       -3.131         87
XLK         up          bh                   20             20       -2.014         36
XLK         up          loc                  10             10       -4.783          8
XLK         up          loc                  20              5         .907          5
XLK         up          loc                  20             10       -5.372          8
XLK         up          los                   5              5       -3.279         44
XLK         up          los                   5             10         .113         32
XLK         up          los                   5             20       -6.663         34
XLK         up          los                  10              5        1.272          3
XLK         up          los                  10             10       -3.075         29
XLK         up          los                  10             20       -1.512         41
XLK         up          los                  20              5         .576          7
XLK         up          los                  20             10       -4.031         51
XLK         up          los                  20             20       -1.561         32
XLV         nup         bh                   20              5         .021       3297
XLV         nup         bh                   20             10         .041       3257
XLV         nup         bh                   20             20         .075       3279
XLV         nup         loc                   5              5        -.024       3312
XLV         nup         loc                   5             10         .063       3306
XLV         nup         loc                   5             20          .03       3308
XLV         nup         loc                  10              5         .056       3310
XLV         nup         loc                  10             10         .056       3308
XLV         nup         loc                  10             20         .061       3309
XLV         nup         loc                  20              5        -.001       3312
XLV         nup         loc                  20             10         -.03       3283
XLV         nup         loc                  20             20        -.078       3298
XLV         nup         los                   5              5        -.072       3309
XLV         nup         los                   5             10         .087       3305
XLV         nup         los                   5             20        -.024       3284
XLV         nup         los                  10              5         .089       3294
XLV         nup         los                  10             10         .072       3277
XLV         nup         los                  10             20         .041       3235
XLV         nup         los                  20              5        -.027       3310
XLV         nup         los                  20             10        -.099       3272
XLV         nup         los                  20             20        -.215       3257
XLV         up          bh                   20              5         .736         15
XLV         up          bh                   20             10         .246         55
XLV         up          bh                   20             20        -.165         33
XLV         up          loc                   5             10        1.076          6
XLV         up          loc                   5             20         .528          4
XLV         up          loc                  10              5            0          2
XLV         up          loc                  10             10            0          4
XLV         up          loc                  10             20        1.022          3
XLV         up          loc                  20             10         .436         29
XLV         up          loc                  20             20        -.641         14
XLV         up          los                   5              5        -.769          3
XLV         up          los                   5             10        -.259          7
XLV         up          los                   5             20        1.471         28
XLV         up          los                  10              5         .013         18
XLV         up          los                  10             10        -.343         35
XLV         up          los                  10             20         .484         77
XLV         up          los                  20              5         .032          2
XLV         up          los                  20             10         .195         40
XLV         up          los                  20             20       -1.343         55

455 rows selected.

Elapsed: 00:00:05.16
15:21:57 SQL> 
15:21:57 SQL> 

15:33:48 SQL> 
15:33:48 SQL> 
15:33:48 SQL> SELECT
15:33:48   2  tkr,prediction,strategy,pivot_point,holding_period
15:33:48   3  ,ROUND(AVG(pg),3)avg_pct_gain
15:33:48   4  ,COUNT(*)FROM mp_j_yfact
15:33:48   5  WHERE tkr IN('OIH','XLE')AND holding_period=20
15:33:48   6  GROUP BY tkr,prediction,strategy,pivot_point,holding_period
15:33:48   7  ORDER BY tkr,prediction,strategy,pivot_point,holding_period
15:33:48   8  /

TKR         PREDICTION  STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- ----------- -------------- ------------ ----------
OIH         nup         bh                   20             20        1.165       3263
OIH         nup         loc                   5             20         .487       2954
OIH         nup         loc                  10             20         .549       3226
OIH         nup         loc                  20             20         .718       3200
OIH         nup         los                   5             20        -.151       3264
OIH         nup         los                  10             20         .096       3243
OIH         nup         los                  20             20         .445       3258
OIH         up          bh                   20             20       -3.981         49
OIH         up          loc                   5             20        4.917         12
OIH         up          loc                  10             20        4.357          4
OIH         up          los                   5             20         6.88         28
OIH         up          los                  10             20        6.468         21
OIH         up          los                  20             20       33.461          6
XLE         nup         bh                   20             20        1.111       3255
XLE         nup         loc                   5             20         .519       2939
XLE         nup         loc                  10             20         .492       3295
XLE         nup         loc                  20             20         .337       2930
XLE         nup         los                   5             20         -.12       3280
XLE         nup         los                  10             20         -.18       3292
XLE         nup         los                  20             20        -.442       2927
XLE         up          bh                   20             20        5.482         57
XLE         up          loc                   5             20        4.135          7
XLE         up          loc                  10             20        4.272         17
XLE         up          loc                  20             20         6.04         14
XLE         up          los                   5             20        4.471         14
XLE         up          los                  10             20        2.625         20
XLE         up          los                  20             20        5.629         17

27 rows selected.

Elapsed: 00:00:00.92
15:33:49 SQL> 
15:33:49 SQL> 
15:33:49 SQL> 
15:35:12 SQL> 
15:35:12 SQL> 


15:37:32 SQL> 
15:37:32 SQL> -- Rollup
15:37:32 SQL> SELECT
15:37:32   2  prediction,strategy,pivot_point,holding_period
15:37:32   3  ,ROUND(AVG(pg),3)avg_pct_gain
15:37:32   4  ,COUNT(*)FROM mp_j_yfact
15:37:32   5  WHERE tkr IN('OIH','XLE')AND holding_period=20
15:37:32   6  GROUP BY prediction,strategy,pivot_point,holding_period
15:37:32   7  ORDER BY prediction,strategy,pivot_point,holding_period
15:37:32   8  /

PREDICTION  STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- -------------- ------------ ----------
nup         bh                   20             20        1.138       6518
nup         loc                   5             20         .503       5893
nup         loc                  10             20          .52       6521
nup         loc                  20             20         .536       6130
nup         los                   5             20        -.135       6544
nup         los                  10             20        -.043       6535
nup         los                  20             20         .025       6185
up          bh                   20             20        1.108        106
up          loc                   5             20        4.629         19
up          loc                  10             20        4.288         21
up          loc                  20             20         6.04         14
up          los                   5             20        6.077         42
up          los                  10             20        4.593         41
up          los                  20             20       12.889         23

14 rows selected.

Elapsed: 00:00:00.91
15:37:33 SQL> 
15:37:33 SQL> 
15:37:33 SQL> 

#!/usr/bin/env ruby

# mas_run3.rb

# Demo: ruby -r mas_run3.rb -e 'MasRun3.new.write_score_sql_scripts'

# Ruby script which uses nested loops to generate several SQL scripts.
# Each script is a wrapper for the SVM algorithm resident within Oracle Data Miner.

class MasRun3
require 'rubygems'
require 'oci8'
  def write_score_sql_scripts
    # pivot_points_a holds integers representing a type of moving average slope.
    # Some strategies (loc, los) depend upon a pivot point.
    # For example, The loc-Trader will watch the moving average slope.  Once it moves above zero, he will go Long.
    # Once it moves below zero, he will sell so that he holds cash.
    pivot_points_a = ["5","10","20"]

    # holding_periods_a holds integers representing days a tkr is held before its percentage gain is measured.
    # holding_periods_a = ["5","10","20"]
    # Now, We only like 20 day holding period.
    holding_periods_a = ["20"]

    # strategies_a holds a list of acronyms for strategies.
    # Some of them are:
    # bh = Buy and Hold aka Long
    # loc = Long Or Cash
    # los = Long Or Short
    strategies_a = ['loc','los']

    # For each strategy, build-model, and then score.
    # The score will give us the probability that the pct-gain will be above a threshold dependent on std. deviation.
    strategies_a.each do |strategy|
      pivot_points_a.each do |pp|
        holding_periods_a.each do |hp|
          p("strategy: #{strategy} pivot_point: #{pp} holding_period: #{hp}")
          sqlscript_name = "mas_run3_#{strategy}_#{pp}_#{hp}_score.sql"
          (fhw = File.open(sqlscript_name,"w")).write("--\n-- #{sqlscript_name}\n--\n\n"); fhw.close
          some_dates_a = []
          dates_sql = "SELECT DISTINCT TO_CHAR(ydate,'YYYY-MM-DD')FROM etfmas WHERE ydate>TO_DATE('2002-09-01','YYYY-MM-DD')AND ydate<TO_DATE('2009-04-01','YYYY-MM-DD')"
	  OCI8.new('trade','t').exec(dates_sql){ |r| some_dates_a << r.to_s }
          some_dates_a.sort.each do |adate|

sqlplus_syntax1 = <<ENDsqlplus_syntax1
CREATE OR REPLACE view sme AS SELECT
NULL aa_targ_att
,m.tkr_date
,m.dayspast
,m.yprice
,m.yvol
,m.yprice_avg5
,m.yprice_avg10
,m.yprice_avg20
,m.yprice_avg5slp
,m.yprice_avg10slp
,m.yprice_avg20slp
,m.shy_yprice_avg5slp
,m.xle_yprice_avg5slp
,m.shy_yprice_avg10slp
,m.xle_yprice_avg10slp
,m.shy_yprice_avg20slp
,m.xle_yprice_avg20slp
,p.prob_up12
,t.tkr_int
FROM mas m, mas_predictions_avg p, tkr_int t
WHERE ydate='#{adate}'
AND m.tkr_date = p.tkr_date
AND p.strategy = '#{strategy}'
AND p.holding_period = #{hp}
AND p.pivot_point = #{pp}
AND m.tkr = t.tkr
/
CREATE OR REPLACE view bme AS SELECT
ENDsqlplus_syntax1

            # Here the aa_targ_att column depends on my choice of strategy
            case strategy
              when "loc"
aa_targ_att_syntax = <<ENDloc_syntax
CASE WHEN yprice_avg#{pp}slp<0 THEN'nup'
ELSE
  CASE WHEN pct_gain#{hp}>(SELECT AVG(pct_gain#{hp})+STDDEV(pct_gain#{hp})FROM mas)THEN'up'ELSE'nup'END
END aa_targ_att
ENDloc_syntax
              when "los"
aa_targ_att_syntax = <<ENDlos_syntax
CASE WHEN yprice_avg#{pp}slp<0 THEN
  CASE WHEN pct_gain#{hp}<(SELECT AVG(pct_gain#{hp})-STDDEV(pct_gain#{hp})FROM mas)THEN'up'ELSE'nup'END
ELSE
  CASE WHEN pct_gain#{hp}>(SELECT AVG(pct_gain#{hp})+STDDEV(pct_gain#{hp})FROM mas)THEN'up'ELSE'nup'END
END aa_targ_att
ENDlos_syntax
            end # case strategy
            # Mix together the syntax
sqlplus_syntax = <<ENDsqlplus_syntax
#{sqlplus_syntax1}#{aa_targ_att_syntax},m.tkr_date
,m.dayspast
,m.yprice
,m.yvol
,m.yprice_avg5
,m.yprice_avg10
,m.yprice_avg20
,m.yprice_avg5slp
,m.yprice_avg10slp
,m.yprice_avg20slp
,m.shy_yprice_avg5slp
,m.xle_yprice_avg5slp
,m.shy_yprice_avg10slp
,m.xle_yprice_avg10slp
,m.shy_yprice_avg20slp
,m.xle_yprice_avg20slp
,p.prob_up12
,t.tkr_int
FROM mas m, mas_predictions_avg p, tkr_int t
WHERE 5+#{hp}+ydate<'#{adate}'
AND m.tkr_date = p.tkr_date
AND p.strategy = '#{strategy}'
AND p.holding_period = #{hp}
AND p.pivot_point = #{pp}
AND m.tkr = t.tkr
/
ALTER SYSTEM FLUSH SHARED_POOL;
@cr_bme_run3.sql #{strategy} #{hp} #{pp}
PURGE RECYCLEBIN;

--
ENDsqlplus_syntax
            #Use cr_bme_run3.sql to build a model from bme table and then score the sme table.
            (fha = File.open(sqlscript_name,"a")).write(sqlplus_syntax); fha.close
          end # some_dates_a.each
        end # holding_periods_a.each
      end # pivot_points_a.each
    end # strategies_a.each
  end # def write_score_sql_scripts
end # class Mas

MasRun3.new.write_score_sql_scripts
# end of ruby script

19:18:49 SQL> 
19:26:53 SQL> 
19:26:53 SQL> @cr_mp3_j_yfact.sql 
19:27:04 SQL> --
19:27:04 SQL> -- cr_mp3_j_yfact.sql
19:27:04 SQL> --
19:27:04 SQL> 
19:27:04 SQL> -- Creates a view, mp3_j_yfact, which joins mas_predictions_run3 with
19:27:04 SQL> -- yfact.  This view helps us analyze the accuracy of SVM predictions
19:27:04 SQL> -- within the mas_predictions_run3 table.
19:27:04 SQL> 
19:27:04 SQL> CREATE OR REPLACE VIEW mp3_j_yfact AS SELECT
19:27:04   2  m.prediction
19:27:04   3  ,m.prob_up
19:27:04   4  ,y.pg		-- pct_gain
19:27:04   5  ,y.day_id 	 -- fk to day_dimension
19:27:04   6  ,y.tkr		 -- DIA, EFA, MDY, ...
19:27:04   7  ,y.strategy	 -- bh, loc, los
19:27:04   8  ,y.pivot_point	 -- 5,10,20
19:27:04   9  ,y.holding_period  -- 5,10,20
19:27:04  10  FROM yfact y, mas_predictions_run3 m
19:27:04  11  WHERE REPLACE(m.tkr_date,'-') = y.tkr||y.day_id
19:27:04  12  AND y.strategy = m.strategy
19:27:04  13  AND y.pivot_point = m.pivot_point
19:27:04  14  AND y.holding_period = m.holding_period
19:27:04  15  /

View created.

Elapsed: 00:00:00.08
19:27:04 SQL> 
19:27:04 SQL> -- Now query
19:27:04 SQL> SELECT COUNT(*)FROM mas_predictions_run3;

  COUNT(*)
----------
    115280

Elapsed: 00:00:00.02
19:27:04 SQL> SELECT COUNT(*)FROM yfact;

  COUNT(*)
----------
    544644

Elapsed: 00:00:00.45
19:27:04 SQL> SELECT COUNT(*)FROM mp3_j_yfact;

  COUNT(*)
----------
    115280

Elapsed: 00:00:02.79
19:27:07 SQL> 
19:27:07 SQL> -- Look at data rolled up to highest level
19:27:07 SQL> SELECT holding_period,AVG(pg)avg_pct_gain,COUNT(*)FROM mp3_j_yfact
19:27:07   2  GROUP BY holding_period ORDER BY holding_period
19:27:07   3  /

HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
-------------- ------------ ----------
	    20	 .133084439	115280

Elapsed: 00:00:03.22
19:27:10 SQL> 
19:27:10 SQL> -- Drill down into prediction
19:27:10 SQL> SELECT	prediction, holding_period,AVG(pg)avg_pct_gain,COUNT(*)FROM mp3_j_yfact
19:27:10   2  GROUP BY prediction,holding_period ORDER BY prediction,holding_period
19:27:10   3  /

PREDICTION  HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- -------------- ------------ ----------
nup			20   .153713598     113280
up			20   -1.0353511       2000

Elapsed: 00:00:03.11
19:27:13 SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@zareason:~/vlsql/md/etfmas$ 
oracle@zareason:~/vlsql/md/etfmas$ 

20:40:27 SQL> @qry_mp3_j_yfact.sql
20:40:37 SQL> --
20:40:37 SQL> -- qry_mp3_j_yfact.sql
20:40:37 SQL> --
20:40:37 SQL> 
20:40:37 SQL> -- Drills down into yfact dimensions of view: mp3_j_yfact
20:40:37 SQL> 
20:40:37 SQL> SELECT
20:40:37   2  tkr,prediction,strategy,pivot_point,holding_period
20:40:37   3  ,ROUND(AVG(pg),3)avg_pct_gain
20:40:37   4  ,COUNT(*)FROM mp3_j_yfact
20:40:37   5  GROUP BY tkr,prediction,strategy,pivot_point,holding_period
20:40:37   6  ORDER BY tkr,prediction,strategy,pivot_point,holding_period
20:40:37   7  /

TKR	    PREDICTION	STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- ----------- -------------- ------------ ----------
DIA	    nup 	loc		      5 	    20	       .006	  1616
DIA	    nup 	loc		     10 	    20	       .103	  1611
DIA	    nup 	loc		     20 	    20	       .132	  1488
DIA	    nup 	los		      5 	    20	      -.131	  1622
DIA	    nup 	los		     10 	    20	       .084	  1598
DIA	    nup 	los		     20 	    20	       .097	  1592
DIA	    up		loc		      5 	    20	     -7.506	     1
DIA	    up		loc		     20 	    20	       .808	     8
DIA	    up		los		      5 	    20	       .545	    16
DIA	    up		los		     10 	    20	       .953	    41
DIA	    up		los		     20 	    20	       .886	    42
EFA	    nup 	loc		      5 	    20	       .331	  1597
EFA	    nup 	loc		     10 	    20	       .493	  1616
EFA	    nup 	loc		     20 	    20	       .718	  1494
EFA	    nup 	los		      5 	    20	       .194	  1625
EFA	    nup 	los		     10 	    20	       .588	  1585
EFA	    nup 	los		     20 	    20	      1.011	  1583
EFA	    up		loc		      5 	    20	      -.672	    13
EFA	    up		loc		     10 	    20	       -.91	     2
EFA	    up		loc		     20 	    20	       .896	    10
EFA	    up		los		      5 	    20	       .501	    28
EFA	    up		los		     10 	    20	     -1.027	    68
EFA	    up		los		     20 	    20	      -.824	    70
MDY	    nup 	loc		      5 	    20	       .177	  1596
MDY	    nup 	loc		     10 	    20		 .3	  1610
MDY	    nup 	loc		     20 	    20		.31	  1488
MDY	    nup 	los		      5 	    20	      -.012	  1623
MDY	    nup 	los		     10 	    20	       .255	  1586
MDY	    nup 	los		     20 	    20	       .358	  1593
MDY	    up		loc		      5 	    20	     -6.925	     2
MDY	    up		loc		     10 	    20		.36	     3
MDY	    up		loc		     20 	    20	      2.158	     8
MDY	    up		los		      5 	    20	     -1.881	    19
MDY	    up		los		     10 	    20	      -.601	    53
MDY	    up		los		     20 	    20	      -.135	    39
OIH	    nup 	loc		      5 	    20	       .509	  1463
OIH	    nup 	loc		     10 	    20	       .549	  1578
OIH	    nup 	loc		     20 	    20	       .826	  1457
OIH	    nup 	los		      5 	    20	       .047	  1587
OIH	    nup 	los		     10 	    20	       .241	  1519
OIH	    nup 	los		     20 	    20		.56	  1527
OIH	    up		loc		      5 	    20	       .223	    20
OIH	    up		loc		     10 	    20	       .418	    34
OIH	    up		loc		     20 	    20	      1.918	    39
OIH	    up		los		      5 	    20	     -2.423	    52
OIH	    up		los		     10 	    20	     -1.336	   114
OIH	    up		los		     20 	    20	      -.293	   105
SHY	    nup 	loc		      5 	    20	       .183	  1617
SHY	    nup 	loc		     10 	    20	       .195	  1613
SHY	    nup 	loc		     20 	    20	       .219	  1497
SHY	    nup 	los		      5 	    20		 .1	  1644
SHY	    nup 	los		     10 	    20	       .124	  1640
SHY	    nup 	los		     20 	    20	       .169	  1645
SHY	    up		los		      5 	    20	      -.158	     4
SHY	    up		los		     10 	    20	       .032	    13
SHY	    up		los		     20 	    20	       .095	    15
SPY	    nup 	loc		      5 	    20		  0	  1617
SPY	    nup 	loc		     10 	    20	       .104	  1612
SPY	    nup 	loc		     20 	    20	       .101	  1489
SPY	    nup 	los		      5 	    20	      -.083	  1622
SPY	    nup 	los		     10 	    20	       .128	  1605
SPY	    nup 	los		     20 	    20	       .148	  1573
SPY	    up		loc		     20 	    20	      1.231	     7
SPY	    up		los		      5 	    20	     -2.386	    15
SPY	    up		los		     10 	    20	      -.076	    34
SPY	    up		los		     20 	    20	       .375	    59
XLB	    nup 	loc		      5 	    20	       .028	  1598
XLB	    nup 	loc		     10 	    20	       .114	  1604
XLB	    nup 	loc		     20 	    20	       .266	  1483
XLB	    nup 	los		      5 	    20	      -.334	  1605
XLB	    nup 	los		     10 	    20	      -.077	  1570
XLB	    nup 	los		     20 	    20	       .224	  1581
XLB	    up		loc		      5 	    20	      1.148	     3
XLB	    up		loc		     10 	    20	      7.558	     1
XLB	    up		loc		     20 	    20		1.6	    15
XLB	    up		los		      5 	    20	     -3.297	    40
XLB	    up		los		     10 	    20	     -3.584	    73
XLB	    up		los		     20 	    20	     -1.717	    65
XLE	    nup 	loc		      5 	    20	       .525	  1463
XLE	    nup 	loc		     10 	    20	       .554	  1594
XLE	    nup 	loc		     20 	    20	       .387	  1453
XLE	    nup 	los		      5 	    20	      -.015	  1591
XLE	    nup 	los		     10 	    20	      -.117	  1573
XLE	    nup 	los		     20 	    20	      -.264	  1432
XLE	    up		loc		      5 	    20	       .483	    11
XLE	    up		loc		     10 	    20	     -1.312	    19
XLE	    up		loc		     20 	    20	      1.702	    31
XLE	    up		los		      5 	    20	     -1.631	    53
XLE	    up		los		     10 	    20	      -.785	    76
XLE	    up		los		     20 	    20	     -2.345	    55
XLF	    nup 	loc		      5 	    20	      -.529	  1617
XLF	    nup 	loc		     10 	    20	      -.347	  1611
XLF	    nup 	loc		     20 	    20	      -.277	  1490
XLF	    nup 	los		      5 	    20	      -.315	  1601
XLF	    nup 	los		     10 	    20	       .076	  1566
XLF	    nup 	los		     20 	    20	       .405	  1578

TKR	    PREDICTION	STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- ----------- -------------- ------------ ----------
XLF	    up		loc		     10 	    20	       .867	     1
XLF	    up		loc		     20 	    20	      2.489	     6
XLF	    up		los		      5 	    20	     -2.931	    39
XLF	    up		los		     10 	    20	     -1.537	    78
XLF	    up		los		     20 	    20	     -1.205	    59
XLI	    nup 	loc		      5 	    20	       .099	  1611
XLI	    nup 	loc		     10 	    20		.23	  1597
XLI	    nup 	loc		     20 	    20	       .206	  1487
XLI	    nup 	los		      5 	    20	      -.055	  1615
XLI	    nup 	los		     10 	    20	       .305	  1572
XLI	    nup 	los		     20 	    20	       .286	  1572
XLI	    up		loc		     20 	    20	       .386	     9
XLI	    up		los		      5 	    20	      8.292	    22
XLI	    up		los		     10 	    20	       .507	    66
XLI	    up		los		     20 	    20	       1.25	    60
XLK	    nup 	loc		      5 	    20	       .058	  1617
XLK	    nup 	loc		     10 	    20	       .121	  1610
XLK	    nup 	loc		     20 	    20	       .101	  1491
XLK	    nup 	los		      5 	    20	       .009	  1606
XLK	    nup 	los		     10 	    20	       .353	  1562
XLK	    nup 	los		     20 	    20		.06	  1592
XLK	    up		loc		     20 	    20	      1.018	     5
XLK	    up		los		      5 	    20	     -5.807	    35
XLK	    up		los		     10 	    20	     -5.534	    76
XLK	    up		los		     20 	    20	     -4.506	    40
XLV	    nup 	loc		      5 	    20	       .026	  1617
XLV	    nup 	loc		     10 	    20	       .044	  1612
XLV	    nup 	loc		     20 	    20	      -.072	  1496
XLV	    nup 	los		      5 	    20	       .067	  1611
XLV	    nup 	los		     10 	    20	       .136	  1583
XLV	    nup 	los		     20 	    20	      -.159	  1591
XLV	    up		los		      5 	    20	     -2.745	    26
XLV	    up		los		     10 	    20	     -1.936	    58
XLV	    up		los		     20 	    20	     -1.348	    44

130 rows selected.

Elapsed: 00:00:03.20
20:40:40 SQL> 
20:40:40 SQL> 
20:40:40 SQL> SELECT
20:40:40   2  tkr,prediction,strategy,pivot_point,holding_period
20:40:40   3  ,ROUND(AVG(pg),3)avg_pct_gain
20:40:40   4  ,COUNT(*)FROM mp3_j_yfact
20:40:40   5  WHERE tkr IN('OIH','XLE')AND holding_period=20
20:40:40   6  GROUP BY tkr,prediction,strategy,pivot_point,holding_period
20:40:40   7  ORDER BY tkr,prediction,strategy,pivot_point,holding_period
20:40:40   8  /

TKR	    PREDICTION	STRATEGY    PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- ----------- -------------- ------------ ----------
OIH	    nup 	loc		      5 	    20	       .509	  1463
OIH	    nup 	loc		     10 	    20	       .549	  1578
OIH	    nup 	loc		     20 	    20	       .826	  1457
OIH	    nup 	los		      5 	    20	       .047	  1587
OIH	    nup 	los		     10 	    20	       .241	  1519
OIH	    nup 	los		     20 	    20		.56	  1527
OIH	    up		loc		      5 	    20	       .223	    20
OIH	    up		loc		     10 	    20	       .418	    34
OIH	    up		loc		     20 	    20	      1.918	    39
OIH	    up		los		      5 	    20	     -2.423	    52
OIH	    up		los		     10 	    20	     -1.336	   114
OIH	    up		los		     20 	    20	      -.293	   105
XLE	    nup 	loc		      5 	    20	       .525	  1463
XLE	    nup 	loc		     10 	    20	       .554	  1594
XLE	    nup 	loc		     20 	    20	       .387	  1453
XLE	    nup 	los		      5 	    20	      -.015	  1591
XLE	    nup 	los		     10 	    20	      -.117	  1573
XLE	    nup 	los		     20 	    20	      -.264	  1432
XLE	    up		loc		      5 	    20	       .483	    11
XLE	    up		loc		     10 	    20	     -1.312	    19
XLE	    up		loc		     20 	    20	      1.702	    31
XLE	    up		los		      5 	    20	     -1.631	    53
XLE	    up		los		     10 	    20	      -.785	    76
XLE	    up		los		     20 	    20	     -2.345	    55

24 rows selected.

Elapsed: 00:00:00.34
20:40:41 SQL> 
20:40:41 SQL> -- Rollup
20:40:41 SQL> SELECT
20:40:41   2  prediction,strategy,pivot_point,holding_period
20:40:41   3  ,ROUND(AVG(pg),3)avg_pct_gain
20:40:41   4  ,COUNT(*)FROM mp3_j_yfact
20:40:41   5  WHERE tkr IN('OIH','XLE')AND holding_period=20
20:40:41   6  GROUP BY prediction,strategy,pivot_point,holding_period
20:40:41   7  ORDER BY prediction,strategy,pivot_point,holding_period
20:40:41   8  /

PREDICTION  STRATEGY	PIVOT_POINT HOLDING_PERIOD AVG_PCT_GAIN   COUNT(*)
----------- ----------- ----------- -------------- ------------ ----------
nup	    loc 		  5		20	   .517       2926
nup	    loc 		 10		20	   .552       3172
nup	    loc 		 20		20	   .607       2910
nup	    los 		  5		20	   .016       3178
nup	    los 		 10		20	   .059       3092
nup	    los 		 20		20	   .161       2959
up	    loc 		  5		20	   .315 	31
up	    loc 		 10		20	  -.202 	53
up	    loc 		 20		20	  1.822 	70
up	    los 		  5		20	 -2.023        105
up	    los 		 10		20	 -1.116        190
up	    los 		 20		20	  -.998        160

12 rows selected.

Elapsed: 00:00:00.14
20:40:41 SQL> 
20:40:41 SQL> 
20:40:41 SQL>