指数20日均值策略

超过20日均值占比

沪深300

select t1.riqi as "日期",t1.count as "20日" , t2.count as "总",t1.count / t2.count as "占比" from (select riqi,count(1) as count from gu_history where jj_code = '000300' and close > ma20 GROUP BY riqi order by riqi desc) t1
left join (select riqi,count(1) as count from gu_history where jj_code = '000300' GROUP BY riqi) t2 on t2.riqi = t1.riqi;

中证300

select t1.riqi as "日期",t1.count as "20日" , t2.count as "总",t1.count / t2.count as "占比" from (select riqi,count(1) as count from gu_history where jj_code = '399905' and close > ma20 GROUP BY riqi order by riqi desc) t1
left join (select riqi,count(1) as count from gu_history where jj_code = '399905' GROUP BY riqi) t2 on t2.riqi = t1.riqi;

20日均值占比策略

数据来源：指数成分股股价大于20日均值的占比

策略

从20%以下，上涨到20%以上，开始买入
底背离，加速买入（底：股价在相对在低位。底背离：股价下跌，但是均值上涨）
从80%以上，下跌到80%以下，开始卖出
顶背离，加速卖出（顶：股价相对在高位。顶背离：股价上涨，但是均值下跌）

**PS：**策略2/4待验证。改策略配合宏观经济，可实现一定的超额收益

DB脚本

CREATE TABLE `gu_history` (
  `id` varchar(36) NOT NULL COMMENT '主键',
  `gu_code` varchar(36) DEFAULT NULL COMMENT '代码',
  `gu_name` varchar(36) DEFAULT NULL COMMENT '名称',
  `riqi` varchar(64) DEFAULT NULL COMMENT '日期',
  `close` decimal(32,9) DEFAULT NULL COMMENT '收盘价',
  `ma20` decimal(32,9) DEFAULT NULL COMMENT 'ma20',
  `rate` decimal(32,9) DEFAULT NULL COMMENT '占比',
  `jj_code` varchar(36) DEFAULT NULL COMMENT '基金代码',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=COMPACT;

操作

指数数据DB.py			获取沪深300/中证500的历史数据
saveDB300.py			获取沪深300成分股的历史数据
saveDB500.py			获取中证500成分股的历史数据
300回测.py				
500回测.py

以沪深300为例

数据获取：执行指数数据DB.py和saveDB300.py
图标展示：执行300回测.py

每月涨幅分析


with temp as (
select t.yy,t.mm,t.yymm,g2.`close` as start,g3.`close` ent,(g3.`close` - g2.`close`)/g2.`close` rate
from (
	SELECT
	DATE_FORMAT(g1.riqi,'%Y') as yy,
	DATE_FORMAT(g1.riqi,'%m') as mm,
	DATE_FORMAT(g1.riqi,'%Y-%m') as yymm,
	min(g1.riqi) as start,
	max(g1.riqi) as end
FROM
	gu_history g1 
WHERE
	g1.gu_code = 'sh.000905'
	GROUP BY DATE_FORMAT(g1.riqi,'%Y-%m')
	) t 
left join gu_history g2 on g2.gu_code = 'sh.000905' and g2.riqi = t.start
left join gu_history g3 on g3.gu_code = 'sh.000905' and g3.riqi = t.end
ORDER BY t.yymm
) 
select 
tt.mm as "月份",
tt1.rate as "2010",
tt2.rate as "2011",
tt3.rate as "2012",
tt4.rate as "2013",
tt5.rate as "2014",
tt6.rate as "2015",
tt7.rate as "2016",
tt8.rate as "2017",
tt9.rate as "2018",
tt10.rate as "2019",
tt11.rate as "2020"
from (
select tt1.mm from temp tt1 
GROUP BY tt1.mm
) tt
left join temp tt1 on tt1.mm = tt.mm and tt1.yy = '2010'
left join temp tt2 on tt2.mm = tt.mm and tt2.yy = '2011'
left join temp tt3 on tt3.mm = tt.mm and tt3.yy = '2012'
left join temp tt4 on tt4.mm = tt.mm and tt4.yy = '2013'
left join temp tt5 on tt5.mm = tt.mm and tt5.yy = '2014'
left join temp tt6 on tt6.mm = tt.mm and tt6.yy = '2015'
left join temp tt7 on tt7.mm = tt.mm and tt7.yy = '2016'
left join temp tt8 on tt8.mm = tt.mm and tt8.yy = '2017'
left join temp tt9 on tt9.mm = tt.mm and tt9.yy = '2018'
left join temp tt10 on tt10.mm = tt.mm and tt10.yy = '2019'
left join temp tt11 on tt11.mm = tt.mm and tt11.yy = '2020'

每月涨跌概率

2010-2020年，11年间各行业每月的涨跌概率和平局涨幅


with temp as (
select t.yy,t.mm,t.yymm,g2.`close` as start,g3.`close` ent,(g3.`close` - g2.`close`)/g2.`close` rate
from (
	SELECT
	DATE_FORMAT(g1.riqi,'%Y') as yy,
	DATE_FORMAT(g1.riqi,'%m') as mm,
	DATE_FORMAT(g1.riqi,'%Y-%m') as yymm,
	min(g1.riqi) as start,
	max(g1.riqi) as end
FROM
	gu_history g1 
WHERE
	g1.gu_code = 'sz.399363'
	GROUP BY DATE_FORMAT(g1.riqi,'%Y-%m')
	) t 
left join gu_history g2 on g2.gu_code = 'sz.399363' and g2.riqi = t.start
left join gu_history g3 on g3.gu_code = 'sz.399363' and g3.riqi = t.end
ORDER BY t.yymm
) 
select tt1.mm as "月份",
avg(tt1.rate) as "平局涨幅",
sum(if(tt1.rate > 0,1,0)) / count(1) as "涨跌概率"
from temp tt1 GROUP BY tt1.mm;