我们还是举上证指数、沪深300、中证500三个指数均线定投和均线轮动定投的例子:
1、从通达信等系统导出每天的收盘,按下“3”再按“4”,数据导出格式选excel。
2、导出的日期还不能马上用,因为是字符格式而不是数字格式,而且通达信导出的日期前面还多了一个空格,我们用trim(日期)+0做个转换,trim是去空格函数,而+0就讲字符型的函数最简单的变成日期型了,然后把格式变成日期型就可以了。
3、同样的方法把三个指数的日期和每天的收盘做在一张表上了
4、我们把三个指数按照日期排成3列,分别是在B\C\D上,而A列是日期,这个sheet我们起名为“指数”,我们再建立一个“参数”的sheet,在B2填写的是均线日期,在B3填写的是我们每天定投的金额,初始我们暂定B2=1218(5年交易天数),B3=100元。
5、然后我们再建立一个名字叫BIAS的sheet,格式也是和“指数”一样,在A列是日期,B\C\D列分别是50、300、500的bias值,为了计算动态的bias,我们先来计算动态的均值,在excel里均值函数是average,但如果简单的用average,天数就是一个固定值,如何随着“参数”中的B2天数的变化而变化呢?我们这里还要引进一个不太常用的函数offset,offset函数是一个引用区域函数,这个区域的位置由以下参加决定,offset(r,y0,x0,y,x)中的五个函数,r表示这个区域的起始位置单元,y0表示位置下移多少单元格,x0表示位置右移多少单元格,y表示向上包含多少高度的单元格,x表示向右包含多少宽度的单元格。那么我们在“bias”这个sheet中的B1219这个单元格(就是上证50在11年12月30日)输入:=AVERAGE(OFFSET(指数!B1219,0,0,-参数!$B$2,1))就表示从指数的B1219(就是50在11年12月30日的收盘)这个单元格开始,保持起始点不变,区域宽度是1,高度是往上的参数!$B$2(也就是5年均线天数1218个交易日)这个区域的平均值,这样就把上证50在11年12月30日的5年平均值计算出来了,而且是动态的,随着“参数”B2的变化也会一起变化,注意这里之所以要用$B$2这样的绝对引用,完全是为了复制公式的方便,把这个单元格的公式复制到整个表里,我们可以得到动态的50、300、500从2011年年底到最新的2018年8月14日的每天的均线值了。
6、直到现在,我们还只计算了每天的均线,bias=收盘/均线-1,我们很方便的继续在bias这个sheet的B1219单元里写出动态的bias:= AVERAGE(OFFSET(指数!B1219,0,0,-参数!$B$2,1))/指数!B1219-1,然后把公式复制到所有指数的每一天上。
7、建立一个“份额”的sheet,也是A列是日期,BCD列分别是50、300、500的在当天的定投份额,以B1220的50在2012年1月4日为例,=IF(BIAS!B1220<0,参数!$b$3>
8、再建立一个“收益率”的sheet,也是A列是日期,BCD列分别是50、300、500的定投收益率,还是以B1220的上涨50为例子,=IFERROR(SUM(份额!B$1220:B1220)*指数!B1219/(COUNTIF(份额!B$1220:B1220,'>0')*参数!$B$3)-1,0),这里的iferror函数主要是用来遇到分母为0的异常情况的,而分子上的sum函数就是累计从2012年1月4日开始买入的份额,所以开始的1200需要绝对引用$,而结束的1200不能用绝对引用。而分母的countif是统计从2012年1月4日开始定投的次数,乘上定投金额B3就是定投的累计成本,这样就分别把50、300、500三个指数每天的均线定投收益率计算出来了。
9、你试试修改一下“参数”中的B2天数,每天的定投收益率也一起变化了,怎么样,方便吧?
10、然后我们再计算一个轮动策略,策略很简单,就是BIAS当天在均线线下而且是三个指数中最小值,就定投这个品种,为此我们再建立一个“轮动份额”的sheet,同样A列是日期,BCD列分别是50、300、500在轮动策略下的份额,和前面那个份额比,不同的是前面那个份额的sheet里,有可能同一天50、300、500三个指数同时满足均线下的条件而定投,而轮动定投策略中永远只选bias最小的那个指数品种来定投。在B1220单元格的公式为=IF(AND(BIAS!B1220=MIN(BIAS!B1220:D1220),BIAS!B1220<>
11、在“收益率”的第E列建立每天的轮动收益率公式,在2012年1月4日这个E1220的单元格里写上=IFERROR((SUM(轮动份额!B$1220:B1220)*指数!B1220+SUM(轮动份额!C$1220:C1220)*指数!C1220+SUM(轮动份额!D$1220:D1220)*指数!D1220)/(COUNTIF(轮动份额!B$1220:D1220,'>0')*参数!$B$3)-1,0),iferroe是用来处理分母为0的情况的,分子之和就是三个指数在轮动定投策略下的定投累计到当天的现值,而分母就是累计定投的成本。
12、最后把50、300、500和轮动策略每天的定投做个图出来,因为2013年2月1日前没有满足条件的,所以从这个时间开始,而500从2013年5月开始满足条件定投,300一直到2015年年初才开始。从结果来看,如果选一个还是500相对比较好,但事先你怎么知道呢?所以轮动策略还是一个比较好的策略。
我这么说印象肯定不深刻,给大家一个福利,到我的网盘上去下载这个定投模板的明细,相信你看了这个模板再对照上面的文章后会更加深刻的理解定投、量化回测和excel的应用的:https://pan.baidu.com/s/1hQUxe-z2C7KLop7b9iE5eQ
联系客服