preso.txt This file serves as an index to some ideas presented here: http://www.meetup.com/batsig/ http://www.meetup.com/batsig/events/30191551/ Description: At this Meetup,I start off with a discussion about downloading a copy of Oracle RDBMS software and installing it on a laptop. Next I pick a stock we love or hate and I show how to download many years of CSV data for it from Yahoo to a laptop. Then I demonstrate loading that data into an Oracle table using a utility called SQL-Loader. Next I decide on a holding-period which we want to simulate. Probably one week would be a good choice. Then I demonstrate how to create a table from the CSV data which contains useful columns: * tkr * date * date1week_later * price1 * price2 * mvg_avg_4wk_slope * gain1week Next I demonstrate how to visualize gain1week as a distribution (hint: it will look like a bell curve). Then I demonstrate how to visualize mvg_avg_4wk_slope as a distribution (hint: it will look like a bell curve). Next I demonstrate how to visualize mvg_avg_4wk_slope and gain1week as points in a scatter plot. Next I answer the question, when mvg_avg_4wk_slope, was more than two standard deviations from the mean, how did gain1week usually react? (hint: we will use Pearson's Correlation here) Next I discuss the question, what besides mvg_avg_4wk_slope could we mix with gain1week in a scatter plot? My goal is to find some calculated attribute of stock price which has a large correlation with gain1week (and thus be predictive). oooooooooo Download Oracle: - oracle.com - downloads - Database 11g Enterprise (11.2.0.1.0) Download Microsoft Windows (32-bit) File 1, File 2 (2GB) See All Download Microsoft Windows (x64) File 1, File 2 (2GB) See All Download Linux x86 File 1, File 2 (2GB) See All Download Linux x86-64 File 1, File 2 (2GB) See All - Oracle installation documentation is ok for windows. - Linux installation help: - google: pythian install oracle 11g on ubuntu - http://www.pythian.com/news/13291/installing-oracle-11gr2-enterprise-edition-on-ubuntu-10-04-lucid-lynx/ ooooooooooooo Download CSV for SPY: - wget --output-document=HL.csv http://ichart.finance.yahoo.com/table.csv?s=SPY Add a column to the CSV: - cat SPY.csv | awk '{print "SPY,"$0}' | grep 0 > ystk_stage.csv Inspect it: oracle@h2:/pt/s/rluck/svmd/cf$ oracle@h2:/pt/s/rluck/svmd/cf$ head ystk_stage.csv SPY,2011-09-09,117.68,119.06,115.28,115.92,380056000,115.92 SPY,2011-09-08,119.57,120.94,118.77,119.04,250568200,119.04 SPY,2011-09-07,118.76,120.34,118.36,120.29,209803200,120.29 SPY,2011-09-06,114.39,117.16,114.38,116.99,285130500,116.99 SPY,2011-09-02,118.42,120.87,117.43,117.85,255517200,117.85 SPY,2011-09-01,122.29,123.40,120.78,120.94,254585900,120.94 SPY,2011-08-31,122.46,123.51,121.30,122.22,301828400,122.22 SPY,2011-08-30,120.83,122.43,119.26,121.68,241315700,121.68 SPY,2011-08-29,119.56,121.43,118.06,121.36,190977200,121.36 SPY,2011-08-26,115.69,118.51,113.85,117.97,314495900,117.97 oracle@h2:/pt/s/rluck/svmd/cf$ tail ystk_stage.csv SPY,1993-02-11,44.78,45.13,44.78,44.94,19500,32.37 SPY,1993-02-10,44.66,44.75,44.53,44.72,379600,32.22 SPY,1993-02-09,44.81,44.81,44.56,44.66,122100,32.17 SPY,1993-02-08,44.97,45.13,44.91,44.97,596100,32.40 SPY,1993-02-05,44.97,45.06,44.72,44.97,492100,32.40 SPY,1993-02-04,44.97,45.09,44.47,45.00,531500,32.42 SPY,1993-02-03,44.41,44.84,44.38,44.81,529400,32.28 SPY,1993-02-02,44.22,44.38,44.13,44.34,201300,31.94 SPY,1993-02-01,43.97,44.25,43.97,44.25,480500,31.88 SPY,1993-01-29,43.97,43.97,43.75,43.94,1003200,31.65 oracle@h2:/pt/s/rluck/svmd/cf$ Create a table: CREATE TABLE ystk_stage( tkr VARCHAR2(11) ,ydate DATE ,opn NUMBER ,mx NUMBER ,mn NUMBER ,clse0 NUMBER ,vol NUMBER ,clse NUMBER); Create a CTL file: LOAD DATA INFILE 'ystk_stage.csv' replace INTO TABLE ystk_stage FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( tkr CHAR NULLIF (tkr=BLANKS) ,ydate DATE "YYYY-MM-DD" NULLIF (ydate=BLANKS) ,opn DECIMAL EXTERNAL NULLIF (opn=BLANKS) ,mx DECIMAL EXTERNAL NULLIF (mx=BLANKS) ,mn DECIMAL EXTERNAL NULLIF (mn=BLANKS) ,clse0 DECIMAL EXTERNAL NULLIF (clse0=BLANKS) ,vol DECIMAL EXTERNAL NULLIF (vol=BLANKS) ,clse DECIMAL EXTERNAL NULLIF (clse=BLANKS) ) Call SQL*Loader. Screen dump: oracle@h2:/pt/s/rluck/svmd$ sqlldr trade/t bindsize=20971520 readsize=20971520 rows=123456 control=ystk_stage.ctl SQL*Loader: Release 11.2.0.1.0 - Production on Mon Sep 12 15:37:40 2011 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Commit point reached - logical record count 4690 oracle@h2:/pt/s/rluck/svmd$ Inspect the data: 15:43:09 SQL> 15:43:10 SQL> SELECT tkr,ydate,clse FROM ystk_stage WHERE ydate > sysdate - 9 ORDER BY ydate; TKR YDATE CLSE ----------- ---------- ---------- SPY 2011-09-06 116.99 SPY 2011-09-07 120.29 SPY 2011-09-08 119.04 SPY 2011-09-09 115.92 Elapsed: 00:00:00.13 15:43:11 SQL> 15:43:11 SQL> SELECT tkr,MIN(ydate),MAX(ydate),MIN(clse),MAX(clse)FROM ystk_stage GROUP BY tkr; TKR MIN(YDATE) MAX(YDATE) MIN(CLSE) MAX(CLSE) ----------- ---------- ---------- ---------- ---------- SPY 1993-01-29 2011-09-09 31.27 144.94 Elapsed: 00:00:00.01 15:43:47 SQL> Create a table named batsig: oracle@h2:/pt/s/rluck/svmd/bp$ sqt @cr_batsig.sql SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 13 17:19:15 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 17:19:15 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; Session altered. Elapsed: 00:00:00.00 17:19:15 SQL> 17:19:15 SQL> -- 17:19:15 SQL> -- cr_batsig.sql 17:19:15 SQL> -- 17:19:15 SQL> 17:19:15 SQL> -- Get prices from past and future in same row as price1: 17:19:15 SQL> CREATE OR REPLACE VIEW batsigv1 AS 17:19:15 2 SELECT 17:19:15 3 tkr 17:19:15 4 ,ydate 17:19:15 5 ,LEAD(ydate,5,NULL)OVER(PARTITION BY tkr ORDER BY ydate) date1week_later 17:19:15 6 ,LAG(clse,1,NULL)OVER(PARTITION BY tkr ORDER BY ydate) price0 17:19:15 7 ,clse price1 17:19:15 8 ,LEAD(clse,5,NULL)OVER(PARTITION BY tkr ORDER BY ydate) price2 17:19:15 9 FROM ystk_stage 17:19:15 10 -- Prevent divide by 0 later on: 17:19:15 11 WHERE clse>0 17:19:15 12 / View created. Elapsed: 00:00:00.17 17:19:16 SQL> 17:19:16 SQL> -- Get mvg avg for today and yesterday. Their Difference is slope. 17:19:16 SQL> CREATE OR REPLACE VIEW batsigv2 AS 17:19:16 2 SELECT 17:19:16 3 tkr 17:19:16 4 ,ydate 17:19:16 5 ,date1week_later 17:19:16 6 ,price1 17:19:16 7 ,price2 17:19:16 8 ,(price2 - price1)/price1 gain1week 17:19:16 9 ,AVG(price1)OVER(PARTITION BY tkr ORDER BY ydate ROWS BETWEEN 4*5 PRECEDING AND CURRENT ROW) 17:19:16 10 -AVG(price0)OVER(PARTITION BY tkr ORDER BY ydate ROWS BETWEEN 4*5 PRECEDING AND CURRENT ROW)mas1 17:19:16 11 FROM batsigv1 17:19:16 12 / View created. Elapsed: 00:00:00.03 17:19:16 SQL> 17:19:16 SQL> DROP TABLE batsig; Table dropped. Elapsed: 00:00:00.11 17:19:16 SQL> CREATE TABLE batsig COMPRESS AS 17:19:16 2 SELECT 17:19:16 3 tkr 17:19:16 4 ,ydate 17:19:16 5 ,date1week_later 17:19:16 6 ,price1 17:19:16 7 ,price2 17:19:16 8 ,gain1week 17:19:16 9 ,mas1/price1 mvg_avg_slope 17:19:16 10 FROM batsigv2 17:19:16 11 WHERE price2 > 0 17:19:16 12 / Table created. Elapsed: 00:00:02.60 17:19:18 SQL> 17:19:18 SQL> SELECT 17:19:18 2 tkr 17:19:18 3 ,ydate 17:19:18 4 ,date1week_later 17:19:18 5 ,price1 17:19:18 6 ,price2 17:19:18 7 ,mvg_avg_slope 17:19:18 8 ,gain1week 17:19:18 9 FROM batsig 17:19:18 10 WHERE ydate > sysdate - 22 17:19:18 11 / TKR YDATE DATE1WEEK_ PRICE1 PRICE2 MVG_AVG_SLOPE GAIN1WEEK ----------- ---------- ---------- ---------- ---------- ------------- ---------- SPY 2011-08-23 2011-08-30 116.44 121.68 -.00711178 .045001718 SPY 2011-08-24 2011-08-31 118.08 122.22 -.00614999 .035060976 SPY 2011-08-25 2011-09-01 116.28 120.94 -.00586433 .040075679 SPY 2011-08-26 2011-09-02 117.97 117.85 -.00494476 -.00101721 SPY 2011-08-29 2011-09-06 121.36 116.99 -.00312726 -.03600857 SPY 2011-08-30 2011-09-07 121.68 120.29 -.00277856 -.01142341 SPY 2011-08-31 2011-09-08 122.22 119.04 -.00127405 -.02601865 SPY 2011-09-01 2011-09-09 120.94 115.92 -.00205927 -.04150819 8 rows selected. Elapsed: 00:00:00.01 17:19:18 SQL> 17:19:18 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options oracle@h2:/pt/s/rluck/svmd/bp$ Next I demonstrate how to visualize gain1week as a distribution (hint: it will look like a bell curve). oracle@h2:/pt/s/rluck/svmd/bp$ sqt @gdist SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 13 17:20:53 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 17:20:53 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; Session altered. Elapsed: 00:00:00.00 17:20:53 SQL> 17:20:53 SQL> -- 17:20:53 SQL> -- gdist.sql 17:20:53 SQL> -- 17:20:53 SQL> 17:20:53 SQL> -- Demonstrate how to visualize gain1week as a distribution. 17:20:53 SQL> 17:20:53 SQL> -- Model a Normal Distribution: 17:20:53 SQL> 17:20:53 SQL> SELECT 17:20:53 2 MIN(gain1week) 17:20:53 3 ,AVG(gain1week) 17:20:53 4 ,MAX(gain1week) 17:20:53 5 ,STDDEV(gain1week) 17:20:53 6 FROM batsig 17:20:53 7 / MIN(GAIN1WEEK) AVG(GAIN1WEEK) MAX(GAIN1WEEK) STDDEV(GAIN1WEEK) -------------- -------------- -------------- ----------------- -.19785379 .001711593 .194059128 .025146525 Elapsed: 00:00:00.01 17:20:53 SQL> 17:20:53 SQL> CREATE OR REPLACE VIEW batsigv10 AS 17:20:53 2 SELECT 17:20:53 3 tkr 17:20:53 4 ,ydate 17:20:53 5 ,price1 17:20:53 6 ,gain1week 17:20:53 7 ,ROUND(gain1week,1)g1wr1 17:20:53 8 ,ROUND(gain1week,2)g1wr2 17:20:53 9 FROM batsig 17:20:53 10 / View created. Elapsed: 00:00:00.04 17:20:53 SQL> 17:20:53 SQL> -- Gather some points for a spreadsheet. 17:20:53 SQL> 17:20:53 SQL> -- Start with a very rough histogram: 17:20:53 SQL> 17:20:53 SQL> SELECT 17:20:53 2 g1wr1 17:20:53 3 ,COUNT(g1wr1) gcount 17:20:53 4 FROM batsigv10 17:20:53 5 GROUP BY g1wr1 17:20:53 6 ORDER BY g1wr1 17:20:53 7 / G1WR1 GCOUNT ---------- ---------- -.2 5 -.1 114 0 4462 .1 102 .2 2 Elapsed: 00:00:00.01 17:20:53 SQL> 17:20:53 SQL> -- Drill Down: 17:20:53 SQL> 17:20:53 SQL> SELECT 17:20:53 2 g1wr2 17:20:53 3 ,COUNT(g1wr2) gcount 17:20:53 4 FROM batsigv10 17:20:53 5 GROUP BY g1wr2 17:20:53 6 ORDER BY g1wr2 17:20:53 7 / G1WR2 GCOUNT ---------- ---------- -.2 1 -.19 1 -.17 1 -.16 1 -.15 1 -.14 1 -.13 1 -.12 2 -.11 5 -.1 3 -.09 7 -.08 12 -.07 26 -.06 29 -.05 74 -.04 137 -.03 229 -.02 409 -.01 627 0 968 .01 937 .02 621 .03 296 .04 149 .05 70 .06 32 .07 16 .08 11 .09 6 .1 5 .11 4 .14 1 .16 1 .19 1 34 rows selected. Elapsed: 00:00:00.00 17:20:53 SQL> 17:20:53 SQL> 17:20:53 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options oracle@h2:/pt/s/rluck/svmd/bp$ google images: histogram of stock market gains Then I demonstrate how to visualize mvg_avg_4wk_slope as a distribution (hint: it will look like a bell curve). oracle@h2:/pt/s/rluck/svmd/bp$ ll *l -rw-r--r-- 1 oracle oracle 328 2011-09-13 03:00 corr_spy.sql -rw-r--r-- 1 oracle oracle 1484 2011-09-13 03:40 corr_tkr.sql -rw-r--r-- 1 oracle oracle 966 2011-09-13 01:29 cr_batsig.sql -rw-r--r-- 1 oracle oracle 608 2011-09-13 03:47 gdist.sql -rw-r--r-- 1 oracle oracle 107 2011-09-12 16:00 login.sql -rw-r--r-- 1 oracle oracle 947 2011-09-13 02:45 ma_gdist.sql oracle@h2:/pt/s/rluck/svmd/bp$ oracle@h2:/pt/s/rluck/svmd/bp$ oracle@h2:/pt/s/rluck/svmd/bp$ sqt @ma_gdist SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 13 17:27:54 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 17:27:54 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; Session altered. Elapsed: 00:00:00.00 17:27:54 SQL> 17:27:54 SQL> -- 17:27:54 SQL> -- ma_gdist.sql 17:27:54 SQL> -- 17:27:54 SQL> 17:27:54 SQL> -- Demonstrate how to visualize gain1week as a distribution. 17:27:54 SQL> -- Demonstrate how to visualize mvg_avg_slope as a distribution. 17:27:54 SQL> 17:27:54 SQL> -- Model a Normal Distribution: 17:27:54 SQL> 17:27:54 SQL> SELECT 17:27:54 2 -- MIN(gain1week) 17:27:54 3 -- ,AVG(gain1week) 17:27:54 4 -- ,MAX(gain1week) 17:27:54 5 -- ,STDDEV(gain1week) 17:27:54 6 -- ,MIN(mvg_avg_slope) 17:27:54 7 MIN(mvg_avg_slope) 17:27:54 8 ,AVG(mvg_avg_slope) 17:27:54 9 ,MAX(mvg_avg_slope) 17:27:54 10 ,STDDEV(mvg_avg_slope) 17:27:54 11 FROM batsig 17:27:54 12 / MIN(MVG_AVG_SLOPE) AVG(MVG_AVG_SLOPE) MAX(MVG_AVG_SLOPE) STDDEV(MVG_AVG_SLOPE) ------------------ ------------------ ------------------ --------------------- -.02093415 .0002229 .009014257 .002370261 Elapsed: 00:00:00.00 17:27:54 SQL> 17:27:54 SQL> CREATE OR REPLACE VIEW batsigv10 AS 17:27:54 2 SELECT 17:27:54 3 tkr 17:27:54 4 ,ydate 17:27:54 5 ,price1 17:27:54 6 ,gain1week 17:27:54 7 ,ROUND(gain1week,1)g1wr1 17:27:54 8 ,ROUND(gain1week,2)g1wr2 17:27:54 9 ,mvg_avg_slope 17:27:54 10 ,ROUND(mvg_avg_slope,1)masr1 17:27:54 11 ,ROUND(mvg_avg_slope,2)masr2 17:27:54 12 ,ROUND(mvg_avg_slope,3)masr3 17:27:54 13 FROM batsig 17:27:54 14 / View created. Elapsed: 00:00:00.10 17:27:55 SQL> 17:27:55 SQL> -- Visualize a histogram: 17:27:55 SQL> 17:27:55 SQL> SELECT 17:27:55 2 masr3 17:27:55 3 ,COUNT(masr3) gcount 17:27:55 4 FROM batsigv10 17:27:55 5 GROUP BY masr3 17:27:55 6 ORDER BY masr3 17:27:55 7 / MASR3 GCOUNT ---------- ---------- -.021 1 -.02 1 -.018 1 -.017 2 -.015 1 -.014 1 -.013 2 -.012 6 -.011 3 -.01 7 -.009 13 -.008 12 -.007 19 -.006 40 -.005 57 -.004 104 -.003 204 -.002 366 -.001 544 0 882 .001 1116 .002 794 .003 329 .004 104 .005 45 .006 18 .007 7 .008 3 .009 2 0 30 rows selected. Elapsed: 00:00:00.03 17:27:55 SQL> 17:27:55 SQL> 17:27:55 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options oracle@h2:/pt/s/rluck/svmd/bp$ oracle@h2:/pt/s/rluck/svmd/bp$ Next I answer the question, when mvg_avg_4wk_slope, was more than two standard deviations from the mean, how did gain1week usually react? (hint: we will use Pearson's Correlation here) oracle@h2:/pt/s/rluck/svmd/bp$ oracle@h2:/pt/s/rluck/svmd/bp$ oracle@h2:/pt/s/rluck/svmd/bp$ sqt @corr_spy SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 13 18:20:43 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 18:20:43 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; Session altered. Elapsed: 00:00:00.00 18:20:43 SQL> 18:20:43 SQL> -- 18:20:43 SQL> -- corr_spy.sql 18:20:43 SQL> -- 18:20:43 SQL> 18:20:43 SQL> -- Start by showing a count of all the trading days: 18:20:43 SQL> SELECT COUNT(ydate)FROM batsig; COUNT(YDATE) ------------ 4684 Elapsed: 00:00:00.04 18:20:43 SQL> 18:20:43 SQL> -- Now go on a quest for correlation 18:20:43 SQL> -- between extreme 4 wk moving avg slopes and resulting 1 week gain: 18:20:43 SQL> 18:20:43 SQL> SELECT 18:20:43 2 CASE WHEN SIGN(mvg_avg_slope)>0 THEN'slope_up'ELSE'slope_down'END up_down 18:20:43 3 ,CORR(mvg_avg_slope,gain1week)corr_mas_g1w 18:20:43 4 ,COUNT(mvg_avg_slope) occurrences 18:20:43 5 FROM batsig 18:20:43 6 WHERE ABS(mvg_avg_slope)>= 0.004 18:20:43 7 GROUP BY SIGN(mvg_avg_slope) 18:20:43 8 / UP_DOWN CORR_MAS_G1W OCCURRENCES ---------- ------------ ----------- slope_up .065718225 112 slope_down -.28819814 211 Elapsed: 00:00:00.02 18:20:43 SQL> 18:20:43 SQL> SELECT 18:20:43 2 CASE WHEN SIGN(mvg_avg_slope)>0 THEN'slope_up'ELSE'slope_down'END up_down 18:20:43 3 ,CORR(mvg_avg_slope,gain1week)corr_mas_g1w 18:20:43 4 ,COUNT(mvg_avg_slope) occurrences 18:20:43 5 FROM batsig 18:20:43 6 WHERE ABS(mvg_avg_slope)>= 0.0045 18:20:43 7 GROUP BY SIGN(mvg_avg_slope) 18:20:43 8 / UP_DOWN CORR_MAS_G1W OCCURRENCES ---------- ------------ ----------- slope_up .09507026 75 slope_down -.27568424 166 Elapsed: 00:00:00.00 18:20:43 SQL> 18:20:43 SQL> SELECT 18:20:43 2 CASE WHEN SIGN(mvg_avg_slope)>0 THEN'slope_up'ELSE'slope_down'END up_down 18:20:43 3 ,CORR(mvg_avg_slope,gain1week)corr_mas_g1w 18:20:43 4 ,COUNT(mvg_avg_slope) occurrences 18:20:43 5 FROM batsig 18:20:43 6 WHERE ABS(mvg_avg_slope)>= 0.005 18:20:43 7 GROUP BY SIGN(mvg_avg_slope) 18:20:43 8 / UP_DOWN CORR_MAS_G1W OCCURRENCES ---------- ------------ ----------- slope_up .201801711 45 slope_down -.26419987 134 Elapsed: 00:00:00.01 18:20:43 SQL> 18:20:43 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options oracle@h2:/pt/s/rluck/svmd/bp$ Next I discuss the question, what besides mvg_avg_4wk_slope could we mix with gain1week in a scatter plot? Ans: More tkrs! oracle@h2:/pt/s/rluck/svmd/bp$ sqt @corr_tkr SQL*Plus: Release 11.2.0.1.0 Production on Tue Sep 13 03:57:25 2011 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options 03:57:25 SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD'; Session altered. Elapsed: 00:00:00.00 03:57:25 SQL> 03:57:25 SQL> -- 03:57:25 SQL> -- corr_tkr.sql 03:57:25 SQL> -- 03:57:25 SQL> 03:57:25 SQL> -- Helps me find tkrs where CORR-tween MAS and g1w is strong. 03:57:25 SQL> 03:57:25 SQL> -- Get prices from past and future in same row as price1: 03:57:25 SQL> CREATE OR REPLACE VIEW batsigv20 AS 03:57:25 2 SELECT 03:57:25 3 tkr 03:57:25 4 ,ydate 03:57:25 5 ,LAG(clse,1,NULL)OVER(PARTITION BY tkr ORDER BY ydate) price0 03:57:25 6 ,clse price1 03:57:25 7 ,LEAD(clse,5,NULL)OVER(PARTITION BY tkr ORDER BY ydate) price2 03:57:25 8 FROM ystk 03:57:25 9 -- Prevent divide by 0 later on: 03:57:25 10 WHERE clse>0 03:57:25 11 / View created. Elapsed: 00:00:00.03 03:57:25 SQL> 03:57:25 SQL> 03:57:25 SQL> -- Get mvg avg for today and yesterday. Their Difference is slope. 03:57:25 SQL> CREATE OR REPLACE VIEW batsigv22 AS 03:57:25 2 SELECT 03:57:25 3 tkr 03:57:25 4 ,ydate 03:57:25 5 ,price1 03:57:25 6 ,price2 03:57:25 7 ,(price2 - price1)/price1 gain1week 03:57:25 8 ,AVG(price1)OVER(PARTITION BY tkr ORDER BY ydate ROWS BETWEEN 4*5 PRECEDING AND CURRENT ROW) 03:57:25 9 -AVG(price0)OVER(PARTITION BY tkr ORDER BY ydate ROWS BETWEEN 4*5 PRECEDING AND CURRENT ROW)mas1 03:57:25 10 FROM batsigv20 03:57:25 11 / View created. Elapsed: 00:00:00.03 03:57:25 SQL> 03:57:25 SQL> 03:57:25 SQL> 03:57:25 SQL> CREATE OR REPLACE VIEW batsigv24 AS 03:57:25 2 SELECT 03:57:25 3 tkr 03:57:25 4 ,ydate 03:57:25 5 ,price1 03:57:25 6 ,price2 03:57:25 7 ,gain1week 03:57:25 8 ,mas1/price1 mvg_avg_slope 03:57:25 9 ,STDDEV(mas1/price1)OVER(PARTITION BY tkr)stddev4tkr 03:57:25 10 FROM batsigv22 03:57:25 11 / View created. Elapsed: 00:00:00.04 03:57:25 SQL> 03:57:25 SQL> DROP TABLE batsig2; Table dropped. Elapsed: 00:00:00.07 03:57:25 SQL> CREATE TABLE batsig2 COMPRESS AS 03:57:25 2 SELECT 03:57:25 3 tkr 03:57:25 4 ,ydate 03:57:25 5 ,price1 03:57:25 6 ,price2 03:57:25 7 ,gain1week 03:57:25 8 ,mvg_avg_slope 03:57:25 9 ,stddev4tkr 03:57:25 10 FROM batsigv24 03:57:25 11 / Table created. Elapsed: 00:00:18.46 03:57:44 SQL> 03:57:44 SQL> SELECT 03:57:44 2 tkr 03:57:44 3 ,CASE WHEN SIGN(mvg_avg_slope)>0 THEN'slope_up'ELSE'slope_down'END up_down 03:57:44 4 ,CORR(mvg_avg_slope,gain1week)corr_mas_g1w 03:57:44 5 ,COUNT(mvg_avg_slope) occurrences 03:57:44 6 FROM batsig2 03:57:44 7 WHERE ABS(mvg_avg_slope)> 2*stddev4tkr 03:57:44 8 GROUP BY SIGN(mvg_avg_slope),tkr 03:57:44 9 HAVING COUNT(mvg_avg_slope)>10 03:57:44 10 AND ABS(CORR(mvg_avg_slope,gain1week)) > 0.3 03:57:44 11 ORDER BY ABS(CORR(mvg_avg_slope,gain1week))DESC 03:57:44 12 / TKR UP_DOWN CORR_MAS_G1W OCCURRENCES --------- ---------- ------------ ----------- SINA slope_up -.85996624 12 LVS slope_down -.77302025 59 EFA slope_down .713658434 26 AXP slope_up -.58643497 24 IAG slope_down -.57996787 37 DNDN slope_up -.56742989 38 FXI slope_down .553772886 25 EGO slope_down -.54875409 35 MVG slope_down -.54092657 47 GOOG slope_down -.532438 51 HAL slope_up .526437385 15 AXU slope_down -.52112439 46 GDX slope_up .510045234 14 MT slope_up -.5026734 31 GDX slope_down -.49894063 28 BIDU slope_down -.48231978 21 CREE slope_up -.48175097 15 APOL slope_up .469113021 27 IOC slope_down -.46826535 46 PEP slope_down .456697968 91 XOM slope_down .453063752 116 WYNN slope_down -.44769247 64 ARG slope_up -.44331489 12 UA slope_up -.44302496 11 SWC slope_down -.43783286 187 V slope_down -.43481517 35 MET slope_down -.43038829 57 XLU slope_down -.42859917 108 CMG slope_up -.42715384 22 C slope_down -.42316725 260 PRU slope_down -.41812509 58 GLD slope_up -.4158026 50 PM slope_down -.41472197 47 EGO slope_up -.41285315 17 STT slope_up -.41246182 15 BBT slope_up -.39829984 39 ABX slope_up .393578482 30 DTV slope_down -.38713246 47 GFI slope_down -.38076471 165 FSLR slope_down -.37918789 40 DTV slope_up .377711183 30 CELG slope_down -.37748948 137 GS slope_down -.3749512 90 SLW slope_down -.37145991 35 CEO slope_down -.36387817 50 AMGN slope_up -.36264683 15 QCOM slope_up -.36015301 12 YUM slope_down .358714342 82 TM slope_down -.35810336 119 APC slope_up .354584354 17 PALL slope_down -.34124265 14 AUY slope_down -.3408188 57 MET slope_up .338596677 26 SLV slope_down -.33150545 21 VMW slope_down .330759136 58 TKR slope_up -.32943567 35 RIG slope_down -.32793158 169 AMZN slope_up -.32781593 25 VLO slope_up -.32236941 28 AU slope_down -.32093453 120 EBAY slope_up .31844767 32 HMY slope_up -.31562862 62 QQQ slope_down .312809496 78 MDT slope_down .312269013 161 SCCO slope_down .31119895 59 IYR slope_up -.31085718 11 MOS slope_down -.30870009 31 VECO slope_down -.30859293 182 CAT slope_up -.30785237 39 WDC slope_up -.30168735 29 70 rows selected. Elapsed: 00:00:01.24 03:57:45 SQL> 03:57:45 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options oracle@h2:/pt/s/rluck/svmd/bp$ How do I trade using this idea? #!/bin/bash # getload_tkrs.bash . /pt/s/rluck/svmd/.orcl # Use wget to download csv files full of pricing data from finance.yahoo.com set -x # cd to the right place cd /pt/s/rluck/svmd/bp/cf/ # avoid bumping into old data rm -f *csv # wget em wget --output-document=ABX.csv http://ichart.finance.yahoo.com/table.csv?s=ABX wget --output-document=AMGN.csv http://ichart.finance.yahoo.com/table.csv?s=AMGN wget --output-document=AMZN.csv http://ichart.finance.yahoo.com/table.csv?s=AMZN wget --output-document=APC.csv http://ichart.finance.yahoo.com/table.csv?s=APC wget --output-document=APOL.csv http://ichart.finance.yahoo.com/table.csv?s=APOL wget --output-document=ARG.csv http://ichart.finance.yahoo.com/table.csv?s=ARG wget --output-document=AU.csv http://ichart.finance.yahoo.com/table.csv?s=AU wget --output-document=AUY.csv http://ichart.finance.yahoo.com/table.csv?s=AUY wget --output-document=AXP.csv http://ichart.finance.yahoo.com/table.csv?s=AXP wget --output-document=AXU.csv http://ichart.finance.yahoo.com/table.csv?s=AXU wget --output-document=BBT.csv http://ichart.finance.yahoo.com/table.csv?s=BBT wget --output-document=BIDU.csv http://ichart.finance.yahoo.com/table.csv?s=BIDU wget --output-document=C.csv http://ichart.finance.yahoo.com/table.csv?s=C wget --output-document=CAT.csv http://ichart.finance.yahoo.com/table.csv?s=CAT wget --output-document=CELG.csv http://ichart.finance.yahoo.com/table.csv?s=CELG wget --output-document=CEO.csv http://ichart.finance.yahoo.com/table.csv?s=CEO wget --output-document=CMG.csv http://ichart.finance.yahoo.com/table.csv?s=CMG wget --output-document=CREE.csv http://ichart.finance.yahoo.com/table.csv?s=CREE wget --output-document=DNDN.csv http://ichart.finance.yahoo.com/table.csv?s=DNDN wget --output-document=DTV.csv http://ichart.finance.yahoo.com/table.csv?s=DTV wget --output-document=EBAY.csv http://ichart.finance.yahoo.com/table.csv?s=EBAY wget --output-document=EFA.csv http://ichart.finance.yahoo.com/table.csv?s=EFA wget --output-document=EGO.csv http://ichart.finance.yahoo.com/table.csv?s=EGO wget --output-document=EGO.csv http://ichart.finance.yahoo.com/table.csv?s=EGO wget --output-document=FSLR.csv http://ichart.finance.yahoo.com/table.csv?s=FSLR wget --output-document=FXI.csv http://ichart.finance.yahoo.com/table.csv?s=FXI wget --output-document=GDX.csv http://ichart.finance.yahoo.com/table.csv?s=GDX wget --output-document=GFI.csv http://ichart.finance.yahoo.com/table.csv?s=GFI wget --output-document=GLD.csv http://ichart.finance.yahoo.com/table.csv?s=GLD wget --output-document=GOOG.csv http://ichart.finance.yahoo.com/table.csv?s=GOOG wget --output-document=GS.csv http://ichart.finance.yahoo.com/table.csv?s=GS wget --output-document=HAL.csv http://ichart.finance.yahoo.com/table.csv?s=HAL wget --output-document=HMY.csv http://ichart.finance.yahoo.com/table.csv?s=HMY wget --output-document=IAG.csv http://ichart.finance.yahoo.com/table.csv?s=IAG wget --output-document=IOC.csv http://ichart.finance.yahoo.com/table.csv?s=IOC wget --output-document=IYR.csv http://ichart.finance.yahoo.com/table.csv?s=IYR wget --output-document=LVS.csv http://ichart.finance.yahoo.com/table.csv?s=LVS wget --output-document=MDT.csv http://ichart.finance.yahoo.com/table.csv?s=MDT wget --output-document=MET.csv http://ichart.finance.yahoo.com/table.csv?s=MET wget --output-document=MOS.csv http://ichart.finance.yahoo.com/table.csv?s=MOS wget --output-document=MT.csv http://ichart.finance.yahoo.com/table.csv?s=MT wget --output-document=MVG.csv http://ichart.finance.yahoo.com/table.csv?s=MVG wget --output-document=PALL.csv http://ichart.finance.yahoo.com/table.csv?s=PALL wget --output-document=PEP.csv http://ichart.finance.yahoo.com/table.csv?s=PEP wget --output-document=PM.csv http://ichart.finance.yahoo.com/table.csv?s=PM wget --output-document=PRU.csv http://ichart.finance.yahoo.com/table.csv?s=PRU wget --output-document=QCOM.csv http://ichart.finance.yahoo.com/table.csv?s=QCOM wget --output-document=QQQ.csv http://ichart.finance.yahoo.com/table.csv?s=QQQ wget --output-document=RIG.csv http://ichart.finance.yahoo.com/table.csv?s=RIG wget --output-document=SCCO.csv http://ichart.finance.yahoo.com/table.csv?s=SCCO wget --output-document=SINA.csv http://ichart.finance.yahoo.com/table.csv?s=SINA wget --output-document=SLV.csv http://ichart.finance.yahoo.com/table.csv?s=SLV wget --output-document=SLW.csv http://ichart.finance.yahoo.com/table.csv?s=SLW wget --output-document=STT.csv http://ichart.finance.yahoo.com/table.csv?s=STT wget --output-document=SWC.csv http://ichart.finance.yahoo.com/table.csv?s=SWC wget --output-document=TKR.csv http://ichart.finance.yahoo.com/table.csv?s=TKR wget --output-document=TM.csv http://ichart.finance.yahoo.com/table.csv?s=TM wget --output-document=UA.csv http://ichart.finance.yahoo.com/table.csv?s=UA wget --output-document=V.csv http://ichart.finance.yahoo.com/table.csv?s=V wget --output-document=VECO.csv http://ichart.finance.yahoo.com/table.csv?s=VECO wget --output-document=VLO.csv http://ichart.finance.yahoo.com/table.csv?s=VLO wget --output-document=VMW.csv http://ichart.finance.yahoo.com/table.csv?s=VMW wget --output-document=WDC.csv http://ichart.finance.yahoo.com/table.csv?s=WDC wget --output-document=WYNN.csv http://ichart.finance.yahoo.com/table.csv?s=WYNN wget --output-document=XLU.csv http://ichart.finance.yahoo.com/table.csv?s=XLU wget --output-document=XOM.csv http://ichart.finance.yahoo.com/table.csv?s=XOM wget --output-document=YUM.csv http://ichart.finance.yahoo.com/table.csv?s=YUM # clobber ystk_stage.csv: cat ABX.csv | awk '{print "ABX,"$0}' | grep 0 >ystk_stage.csv # Now append to it: cat AMGN.csv | awk '{print "AMGN,"$0}'| grep 0 >>ystk_stage.csv cat AMZN.csv | awk '{print "AMZN,"$0}' | grep 0 >>ystk_stage.csv cat APC.csv | awk '{print "APC,"$0}' | grep 0 >>ystk_stage.csv cat APOL.csv | awk '{print "APOL,"$0}' | grep 0 >>ystk_stage.csv cat ARG.csv | awk '{print "ARG,"$0}' | grep 0 >>ystk_stage.csv cat AU.csv | awk '{print "AU,"$0}' | grep 0 >>ystk_stage.csv cat AUY.csv | awk '{print "AUY,"$0}' | grep 0 >>ystk_stage.csv cat AXP.csv | awk '{print "AXP,"$0}' | grep 0 >>ystk_stage.csv cat AXU.csv | awk '{print "AXU,"$0}' | grep 0 >>ystk_stage.csv cat BBT.csv | awk '{print "BBT,"$0}' | grep 0 >>ystk_stage.csv cat BIDU.csv | awk '{print "BIDU,"$0}' | grep 0 >>ystk_stage.csv cat C.csv | awk '{print "C,"$0}' | grep 0 >>ystk_stage.csv cat CAT.csv | awk '{print "CAT,"$0}' | grep 0 >>ystk_stage.csv cat CELG.csv | awk '{print "CELG,"$0}' | grep 0 >>ystk_stage.csv cat CEO.csv | awk '{print "CEO,"$0}' | grep 0 >>ystk_stage.csv cat CMG.csv | awk '{print "CMG,"$0}' | grep 0 >>ystk_stage.csv cat CREE.csv | awk '{print "CREE,"$0}' | grep 0 >>ystk_stage.csv cat DNDN.csv | awk '{print "DNDN,"$0}' | grep 0 >>ystk_stage.csv cat DTV.csv | awk '{print "DTV,"$0}' | grep 0 >>ystk_stage.csv cat EBAY.csv | awk '{print "EBAY,"$0}' | grep 0 >>ystk_stage.csv cat EFA.csv | awk '{print "EFA,"$0}' | grep 0 >>ystk_stage.csv cat EGO.csv | awk '{print "EGO,"$0}' | grep 0 >>ystk_stage.csv cat EGO.csv | awk '{print "EGO,"$0}' | grep 0 >>ystk_stage.csv cat FSLR.csv | awk '{print "FSLR,"$0}' | grep 0 >>ystk_stage.csv cat FXI.csv | awk '{print "FXI,"$0}' | grep 0 >>ystk_stage.csv cat GDX.csv | awk '{print "GDX,"$0}' | grep 0 >>ystk_stage.csv cat GFI.csv | awk '{print "GFI,"$0}' | grep 0 >>ystk_stage.csv cat GLD.csv | awk '{print "GLD,"$0}' | grep 0 >>ystk_stage.csv cat GOOG.csv | awk '{print "GOOG,"$0}' | grep 0 >>ystk_stage.csv cat GS.csv | awk '{print "GS,"$0}' | grep 0 >>ystk_stage.csv cat HAL.csv | awk '{print "HAL,"$0}' | grep 0 >>ystk_stage.csv cat HMY.csv | awk '{print "HMY,"$0}' | grep 0 >>ystk_stage.csv cat IAG.csv | awk '{print "IAG,"$0}' | grep 0 >>ystk_stage.csv cat IOC.csv | awk '{print "IOC,"$0}' | grep 0 >>ystk_stage.csv cat IYR.csv | awk '{print "IYR,"$0}' | grep 0 >>ystk_stage.csv cat LVS.csv | awk '{print "LVS,"$0}' | grep 0 >>ystk_stage.csv cat MDT.csv | awk '{print "MDT,"$0}' | grep 0 >>ystk_stage.csv cat MET.csv | awk '{print "MET,"$0}' | grep 0 >>ystk_stage.csv cat MOS.csv | awk '{print "MOS,"$0}' | grep 0 >>ystk_stage.csv cat MT.csv | awk '{print "MT,"$0}' | grep 0 >>ystk_stage.csv cat MVG.csv | awk '{print "MVG,"$0}' | grep 0 >>ystk_stage.csv cat PALL.csv | awk '{print "PALL,"$0}' | grep 0 >>ystk_stage.csv cat PEP.csv | awk '{print "PEP,"$0}' | grep 0 >>ystk_stage.csv cat PM.csv | awk '{print "PM,"$0}' | grep 0 >>ystk_stage.csv cat PRU.csv | awk '{print "PRU,"$0}' | grep 0 >>ystk_stage.csv cat QCOM.csv | awk '{print "QCOM,"$0}' | grep 0 >>ystk_stage.csv cat QQQ.csv | awk '{print "QQQ,"$0}' | grep 0 >>ystk_stage.csv cat RIG.csv | awk '{print "RIG,"$0}' | grep 0 >>ystk_stage.csv cat SCCO.csv | awk '{print "SCCO,"$0}' | grep 0 >>ystk_stage.csv cat SINA.csv | awk '{print "SINA,"$0}' | grep 0 >>ystk_stage.csv cat SLV.csv | awk '{print "SLV,"$0}' | grep 0 >>ystk_stage.csv cat SLW.csv | awk '{print "SLW,"$0}' | grep 0 >>ystk_stage.csv cat STT.csv | awk '{print "STT,"$0}' | grep 0 >>ystk_stage.csv cat SWC.csv | awk '{print "SWC,"$0}' | grep 0 >>ystk_stage.csv cat TKR.csv | awk '{print "TKR,"$0}' | grep 0 >>ystk_stage.csv cat TM.csv | awk '{print "TM,"$0}' | grep 0 >>ystk_stage.csv cat UA.csv | awk '{print "UA,"$0}' | grep 0 >>ystk_stage.csv cat V.csv | awk '{print "V,"$0}' | grep 0 >>ystk_stage.csv cat VECO.csv | awk '{print "VECO,"$0}' | grep 0 >>ystk_stage.csv cat VLO.csv | awk '{print "VLO,"$0}' | grep 0 >>ystk_stage.csv cat VMW.csv | awk '{print "VMW,"$0}' | grep 0 >>ystk_stage.csv cat WDC.csv | awk '{print "WDC,"$0}' | grep 0 >>ystk_stage.csv cat WYNN.csv | awk '{print "WYNN,"$0}' | grep 0 >>ystk_stage.csv cat XLU.csv | awk '{print "XLU,"$0}' | grep 0 >>ystk_stage.csv cat XOM.csv | awk '{print "XOM,"$0}' | grep 0 >>ystk_stage.csv cat YUM.csv | awk '{print "YUM,"$0}' | grep 0 >>ystk_stage.csv # Load this into Oracle table: sqlldr trade/t bindsize=20971520 readsize=20971520 rows=123456 control=ystk_stage.ctl # They there? grep loaded ystk_stage.log sqt< @qry_batsig3 21:07:53 SQL> -- 21:07:53 SQL> -- qry_batsig3.sql 21:07:53 SQL> -- 21:07:53 SQL> 21:07:53 SQL> -- Helps me look at recent data for abnormally large MAS. 21:07:53 SQL> 21:07:53 SQL> 21:07:53 SQL> -- Get prices from past and future in same row as price1: 21:07:53 SQL> CREATE OR REPLACE VIEW batsigv30 AS 21:07:53 2 SELECT 21:07:53 3 tkr 21:07:53 4 ,ydate 21:07:53 5 ,LAG(clse,1,NULL)OVER(PARTITION BY tkr ORDER BY ydate) price0 21:07:53 6 ,clse price1 21:07:53 7 ,LEAD(clse,5,NULL)OVER(PARTITION BY tkr ORDER BY ydate) price2 21:07:53 8 FROM ystk_stage 21:07:53 9 -- Prevent divide by 0 later on: 21:07:53 10 WHERE clse>0 21:07:53 11 / View created. Elapsed: 00:00:00.28 21:07:53 SQL> 21:07:53 SQL> CREATE OR REPLACE VIEW batsigv32 AS 21:07:53 2 SELECT 21:07:53 3 tkr 21:07:53 4 ,ydate 21:07:53 5 ,price1 21:07:53 6 ,price2 21:07:53 7 ,(price2 - price1)/price1 gain1week 21:07:53 8 ,AVG(price1)OVER(PARTITION BY tkr ORDER BY ydate ROWS BETWEEN 4*5 PRECEDING AND CURRENT ROW) 21:07:53 9 -AVG(price0)OVER(PARTITION BY tkr ORDER BY ydate ROWS BETWEEN 4*5 PRECEDING AND CURRENT ROW)mas1 21:07:53 10 FROM batsigv30 21:07:53 11 / View created. Elapsed: 00:00:00.02 21:07:53 SQL> 21:07:53 SQL> 21:07:53 SQL> CREATE OR REPLACE VIEW batsigv34 AS 21:07:53 2 SELECT 21:07:53 3 tkr 21:07:53 4 ,ydate 21:07:53 5 ,price1 21:07:53 6 ,price2 21:07:53 7 ,gain1week 21:07:53 8 ,mas1/price1 mvg_avg_slope 21:07:53 9 ,STDDEV(mas1/price1)OVER(PARTITION BY tkr)stddev4tkr 21:07:53 10 FROM batsigv32 21:07:53 11 / View created. Elapsed: 00:00:00.02 21:07:53 SQL> 21:07:53 SQL> PURGE RECYCLEBIN; Recyclebin purged. Elapsed: 00:00:00.02 21:07:53 SQL> DROP TABLE batsig3; Table dropped. Elapsed: 00:00:00.34 21:07:54 SQL> CREATE TABLE batsig3 COMPRESS AS 21:07:54 2 SELECT 21:07:54 3 tkr 21:07:54 4 ,ydate 21:07:54 5 ,price1 21:07:54 6 ,price2 21:07:54 7 ,gain1week 21:07:54 8 ,mvg_avg_slope 21:07:54 9 ,stddev4tkr 21:07:54 10 FROM batsigv34 21:07:54 11 / Table created. Elapsed: 00:00:03.02 21:07:57 SQL> 21:07:57 SQL> -- Look for correlation between abnormally large MAS and gain1week: 21:07:57 SQL> 21:07:57 SQL> SELECT 21:07:57 2 tkr 21:07:57 3 ,CASE WHEN SIGN(mvg_avg_slope)>0 THEN'slope_up'ELSE'slope_down'END up_down 21:07:57 4 ,CORR(mvg_avg_slope,gain1week)corr_mas_g1w 21:07:57 5 ,COUNT(mvg_avg_slope) occurrences 21:07:57 6 FROM batsig3 21:07:57 7 WHERE ABS(mvg_avg_slope)> 2*stddev4tkr 21:07:57 8 GROUP BY SIGN(mvg_avg_slope),tkr 21:07:57 9 HAVING COUNT(mvg_avg_slope)>10 21:07:57 10 AND ABS(CORR(mvg_avg_slope,gain1week)) > 0.3 21:07:57 11 ORDER BY ABS(CORR(mvg_avg_slope,gain1week))DESC 21:07:57 12 / TKR UP_DOWN CORR_MAS_G1W OCCURRENCES ----------- ---------- ------------ ----------- SINA slope_up -.85996624 12 LVS slope_down -.77302025 59 IAG slope_down -.57803052 37 DNDN slope_up -.56742989 38 MVG slope_down -.54092657 47 GOOG slope_down -.532438 51 AXU slope_down -.52112439 46 GDX slope_up .510863724 14 XOM slope_down -.50531408 244 MT slope_up -.50367825 31 GDX slope_down -.4990244 28 FXI slope_up -.49806802 31 C slope_down -.49036707 196 IOC slope_down -.46826535 46 XLU slope_down -.46357214 105 C slope_up .449857594 48 WYNN slope_down -.44758509 64 UA slope_up -.44302496 11 EFA slope_up .440087015 21 V slope_down -.43390916 35 SWC slope_down -.43028735 183 CMG slope_up -.42715384 22 PM slope_down -.42073331 46 MET slope_down -.41822412 57 QQQ slope_up -.41043319 34 GLD slope_up -.4045642 50 STT slope_down -.40289853 188 VLO slope_up .396798389 75 GFI slope_down -.38945665 156 DTV slope_down -.38713246 47 EBAY slope_up .386525673 86 FSLR slope_down -.37918789 40 DTV slope_up .377711183 30 GS slope_down -.37739107 90 CEO slope_down -.37717764 49 SLW slope_down -.37039582 35 PRU slope_down -.36754152 58 TM slope_down -.36573099 118 RIG slope_down -.36047209 175 AUY slope_down -.34633857 57 PALL slope_down -.34124265 14 ARG slope_up -.34053109 94 QCOM slope_down -.34009164 160 MET slope_up .338609738 26 ABX slope_down -.33541241 139 MDT slope_down -.33339431 227 VMW slope_down .330759136 58 HMY slope_up -.32127981 62 FXI slope_down -.32077324 47 AU slope_down -.31795495 119 BBT slope_up -.31156179 106 VECO slope_down -.30859293 182 MOS slope_down -.30788479 31 53 rows selected. Elapsed: 00:00:00.12 21:07:57 SQL> 21:07:57 SQL> 21:07:57 SQL> -- Look for correlation between abnormally large MAS and gain1week, 21:07:57 SQL> -- Where the data is recent: 21:07:57 SQL> 21:07:57 SQL> SELECT 21:07:57 2 tkr 21:07:57 3 ,CASE WHEN SIGN(mvg_avg_slope)>0 THEN'slope_up'ELSE'slope_down'END up_down 21:07:57 4 ,CORR(mvg_avg_slope,gain1week)corr_mas_g1w 21:07:57 5 ,COUNT(mvg_avg_slope) occurrences 21:07:57 6 FROM batsig3 21:07:57 7 WHERE ABS(mvg_avg_slope)> 2*stddev4tkr 21:07:57 8 -- recent 21:07:57 9 AND ydate > sysdate - 30 21:07:57 10 -- recent 21:07:57 11 GROUP BY SIGN(mvg_avg_slope),tkr 21:07:57 12 HAVING COUNT(mvg_avg_slope)>0 21:07:57 13 AND ABS(CORR(mvg_avg_slope,gain1week)) > 0.3 21:07:57 14 ORDER BY ABS(CORR(mvg_avg_slope,gain1week))DESC 21:07:57 15 / TKR UP_DOWN CORR_MAS_G1W OCCURRENCES ----------- ---------- ------------ ----------- GOOG slope_down -1 2 CEO slope_down -1 2 FSLR slope_down -1 2 QQQ slope_down -1 2 IYR slope_down -1 2 HAL slope_down -.89499956 9 C slope_down -.88926171 5 GS slope_down -.87425889 3 XOM slope_down -.8622449 10 TM slope_down -.85638658 7 BBT slope_down -.83528705 12 MT slope_down -.77847983 9 DTV slope_down -.75842375 12 DNDN slope_down -.73985103 14 TKR slope_down -.73967956 9 GLD slope_up -.71217275 7 EFA slope_down -.66917772 7 STT slope_down -.6523693 9 MET slope_down -.63420563 6 UA slope_down -.5841679 4 CAT slope_down -.54075622 9 VLO slope_down -.45520622 5 APC slope_down -.38903216 5 MDT slope_down -.38579264 3 24 rows selected. Elapsed: 00:00:00.08 21:07:57 SQL> 21:07:57 SQL> -- Look for recent abnormally large MAS: 21:07:57 SQL> 21:07:57 SQL> SELECT 21:07:57 2 ydate 21:07:57 3 ,tkr 21:07:57 4 ,CASE WHEN SIGN(mvg_avg_slope)>0 THEN'slope_up'ELSE'slope_down'END up_down 21:07:57 5 ,mvg_avg_slope 21:07:57 6 FROM batsig3 21:07:57 7 WHERE ABS(mvg_avg_slope)> 2*stddev4tkr 21:07:57 8 -- recent 21:07:57 9 AND ydate > sysdate - 20 21:07:57 10 ORDER BY ydate,tkr 21:07:57 11 / YDATE TKR UP_DOWN MVG_AVG_SLOPE ---------- ----------- ---------- ------------- 2011-08-25 APC slope_down -.01051293 2011-08-25 BBT slope_down -.01247834 2011-08-25 CAT slope_down -.01034749 2011-08-25 DNDN slope_down -.10307363 2011-08-25 DTV slope_down -.0108561 2011-08-25 EFA slope_down -.00730801 2011-08-25 HAL slope_down -.01805456 2011-08-25 MT slope_down -.02707469 2011-08-25 STT slope_down -.0113696 2011-08-25 TKR slope_down -.0146807 2011-08-25 TM slope_down -.00728283 2011-08-25 XOM slope_down -.00729181 2011-08-26 BBT slope_down -.01154888 2011-08-26 DNDN slope_down -.10093473 2011-08-26 DTV slope_down -.00982402 2011-08-26 EFA slope_down -.00632894 2011-08-26 HAL slope_down -.01540108 2011-08-26 MET slope_down -.01263888 2011-08-26 MT slope_down -.02526147 2011-08-26 STT slope_down -.01079962 2011-08-26 XOM slope_down -.0054345 2011-08-29 BBT slope_down -.00863013 2011-08-29 DNDN slope_down -.09582119 2011-08-29 DTV slope_down -.00777075 2011-08-29 HAL slope_down -.01325352 2011-08-29 MT slope_down -.02196068 2011-08-30 BBT slope_down -.00855432 2011-08-30 DNDN slope_down -.09543487 2011-08-30 DTV slope_down -.00725489 2011-08-30 GLD slope_up .005684507 2011-08-30 HAL slope_down -.0119404 2011-08-30 TM slope_down -.00726972 2011-08-31 DNDN slope_down -.08802544 2011-09-01 DNDN slope_down -.09591894 2011-09-02 GLD slope_up .00587312 2011-09-06 GLD slope_up .005506522 36 rows selected. Elapsed: 00:00:00.08 21:07:57 SQL> 21:07:57 SQL> exit Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options oracle@h2:/pt/s/rluck/svmd/bp$ Next I discuss the question, what besides mvg_avg_4wk_slope could we mix with gain1week in a scatter plot? Ans: - mvg_avg_Nwk_slope? - over-sold-under-sold indicators? - vol x slope? - slope1 x slope2? - slope1 - slope2? - slope1 op slope2 op slope3 ... ??