qry_spyfrimay.txt This file shows 2 trading ideas. First, I model buying SPY near market close and then selling it the next session near market close. It looks like both Friday and Monday are the best days to do this. On Friday I see 938 times since 1993 when I could have done this. The average gain is 0.062% which would get chopped down to about 0.05% after trading costs. The Sharpe ratio is only 0.044 which is poor. I would like to see it at least above 1.0. Next, I model the buying SPY every day of the year. Then, I sell it exactly 20 trading days later. Next, I tally up the gains and group them by Month. October is the clear winner with an avg-gain of 1.998%. This is a decent return for a 1 month holding period and it covers trading costs well. This opportunity came up 397 times in October. Again, the Sharpe ratio is lower than what I want. The media signal of "Sell in May" is supported by the data. It looks wise to sell in May and return on October 1. 19:09:53 SQL> -- 19:09:53 SQL> -- qry_spyfrimay.sql 19:09:53 SQL> -- 19:09:53 SQL> 19:09:53 SQL> -- I start by getting the 1 day gain and 20-trading-day-gain for each tkrdate. 19:09:53 SQL> CREATE OR REPLACE VIEW spyfrimay AS 19:09:53 2 SELECT 19:09:53 3 tkrdate 19:09:53 4 ,tkr 19:09:53 5 ,ydate 19:09:53 6 ,clse 19:09:53 7 ,(LEAD(clse,1,NULL)OVER(PARTITION BY tkr ORDER BY ydate)-clse)/clse g1 19:09:53 8 ,(LEAD(clse,20,NULL)OVER(PARTITION BY tkr ORDER BY ydate)-clse)/clse g20 19:09:53 9 FROM ystk 19:09:53 10 WHERE ydate > sysdate - 123456 19:09:53 11 AND tkr = 'SPY' 19:09:53 12 ORDER BY tkr,ydate 19:09:53 13 / View created. Elapsed: 00:00:00.10 19:09:53 SQL> 19:09:53 SQL> -- Look at each day of week: 19:09:53 SQL> SELECT 19:09:53 2 TO_CHAR(ydate,'Dy') 19:09:53 3 ,AVG(g1) 19:09:53 4 ,SUM(g1) 19:09:53 5 ,AVG(g1)/STDDEV(g1) sharpe_r 19:09:53 6 ,MIN(ydate) 19:09:53 7 ,MAX(ydate) 19:09:53 8 ,COUNT(g1) 19:09:53 9 FROM spyfrimay 19:09:53 10 GROUP BY TO_CHAR(ydate,'Dy') 19:09:53 11 ORDER BY SUM(g1) DESC 19:09:53 12 / TO_ AVG(G1) SUM(G1) SHARPE_R MIN(YDATE) MAX(YDATE) COUNT(G1) --- ---------- ---------- ---------- ---------- ---------- ---------- Fri .000617974 .579659394 .043797763 1993-01-29 2011-09-09 938 Mon .000651029 .575509855 .051177428 1993-02-01 2011-09-12 884 Tue .000497801 .479382328 .042192019 1993-02-02 2011-09-13 963 Wed .000088958 .085755253 .007210224 1993-02-03 2011-09-14 964 Thu -.00035707 -.33707527 -.03127363 1993-02-04 2011-09-15 944 Elapsed: 00:00:00.25 19:09:53 SQL> 19:09:53 SQL> -- Look at each month: 19:09:53 SQL> SELECT 19:09:53 2 TO_CHAR(ydate,'MM') 19:09:53 3 ,AVG(g20) 19:09:53 4 ,SUM(g20) 19:09:53 5 ,AVG(g20)/STDDEV(g20) sharpe_r 19:09:53 6 ,MIN(ydate) 19:09:53 7 ,MAX(ydate) 19:09:53 8 ,COUNT(g20) 19:09:53 9 FROM spyfrimay 19:09:53 10 GROUP BY TO_CHAR(ydate,'MM') 19:09:53 11 ORDER BY SUM(g20) DESC 19:09:53 12 / TO AVG(G20) SUM(G20) SHARPE_R MIN(YDATE) MAX(YDATE) COUNT(G20) -- ---------- ---------- ---------- ---------- ---------- ---------- 10 .019980792 7.93237453 .395122162 1993-10-01 2010-10-29 397 03 .014243792 5.96814868 .307375192 1993-03-01 2011-03-31 419 04 .014542164 5.68598631 .336628373 1993-04-01 2011-04-29 391 11 .013191064 4.85431138 .420773382 1993-11-01 2010-11-30 368 12 .005347335 2.04268201 .153480637 1993-12-01 2010-12-31 382 05 .00213619 .854475963 .056065303 1993-05-03 2011-05-31 400 01 .001626562 .600201206 .036359983 1993-01-29 2011-01-31 369 02 .00148185 .539393226 .029881704 1993-02-01 2011-02-28 364 08 -.00082616 -.3395525 -.01754612 1993-08-02 2011-08-31 411 07 -.00122307 -.48922708 -.02398245 1993-07-01 2011-07-29 400 06 -.00243881 -.99503473 -.05437663 1993-06-01 2011-06-30 408 09 -.00498233 -1.8185514 -.08011665 1993-09-01 2011-09-15 365 12 rows selected. Elapsed: 00:00:00.25 19:09:53 SQL> 19:09: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@hp2:/pt/s/rluck/svmd$