《股票量化回测》- Power Excel与VBA结合 (Power BI企业级分析与应用节选)

本节我们向大家节选第三部书的综合示例部分——量化回测股票。

作品：《量化回测股票》

作品特色：作品通过对历史数据进行量化回测，洞察数据背后的变化规律，帮助决策者得出最好的交易策略。

技术特点：平均值计算、VBA

分析目的：

1.依据指定的N只股票和N个交易策略，回测所有股票与所有策略相交的回测结果。

2.创建度量”日均价格变化%、AbsAveDailyChange%（价格绝对值日均变化%）MaxDailyChange%（最大价格变动）、MinDailyChange%（最小价格变动）。

首先是作品的思路，如下：

1. 明确分析目的

2. 从网上收集原始数据

3. 追加入模型、创建SMA、信号、创建筛选

4. 定义交易策略

5. 模拟交易过程

6. 展示交易量化结果

7. 依据指定度量，通过RANKX进行排名

解释：Power Excel是指Excel中使用了Power Query与Power Pivot功能后的增强性Excel应用。

下图为量化分析目的示意图，通过股票与策略的矩阵得出对应的年化率。这个作品的分析应用为Excel，是因为要实现事务性处理模拟，而Power BI仅为分析性处理应用，因此不适用。相反，在Excel中使用VBA变成可实现模拟过程。

本示例是通过一个Excel Add-ins进行股票数据收集。除了Adds-in，也可以通过PowerQuery中的获取数据实现。

勾选图 4.4.4中相应的Power Pivot选项即可完成加载。

通过追加功能，将事实表合并到一张事实表中，见下图。

事实表中创建新计算列SMA4、SMA9，见下图。

公式参考：

SMA4=AVERAGEX (
    FILTER (
        EVERYTHING,
        EARLIER ( EVERYTHING[Symbol] ) = EVERYTHING[Symbol]
            && EARLIER ( EVERYTHING[Index] ) - 3 <= EVERYTHING[Index]
            && EVERYTHING[Index] <= EARLIER ( EVERYTHING[Index] )
    ),
    EVERYTHING[close]
)

以下的公式为信号值，SMA4 – SMA 9 =1 表示4日均线大于9日均线，相反，表示负信号出现。

4-9=if(EVERYTHING[SMA 4]-EVERYTHING[SMA 9]>0,1,0)

将均线和信号放入Excel表格中，可见每日的平均价格与信号的变动，见下图。

接下来为分析报表添加筛选器，并且添加功能按钮，触发回测，见下图。

其实逻辑是通过股票仓位、现金、股票价值等等几个变量之间的关系得出的。例如，策略出现买入信号时，回测应用会模拟全仓买进，股票仓位数量为 (现金-佣金)/收盘价位，当信号消失时，回测应用则全部卖出股票，股票仓位为0，现金为股票卖出数量*收盘价格-佣金+现金（之前不足买入一股的现金）。如此这般在选定的时间区间内持续地按信号执行回测操作，直至得出最终的累积金额，最后再转化为年化回报率 。下图为模拟交易的过程。

从价格波动的角度，我们希望对股票进行排名，示例中排名方式有四种。

首先计算价格变化%，公式为：平均值（今日收盘价-上个交易日收盘价）/今日收盘价。

为此，添加计算列：

上个交易日收盘价=AVERAGEX(FILTER(EVERYTHING,EARLIER(EVERYTHING[Symbol])=EVERYTHING[Symbol]&&EVERYTHING[Index]=EARLIER(EVERYTHING[Index])-1),EVERYTHING[close])

价格变化%=DIVIDE(EVERYTHING[close]-EVERYTHING[PreviousDayClosed],EVERYTHING[PreviousDayClosed],BLANK())

绝对价格变化%=ABS([DailyChange%])

度量值：

日均价格变化%:=AVERAGE([日价格变化%])

最大变化%:=MAX([日价格变化%])

最小变化%:=MIN([日价格变化%])

绝对值日均变化%:=AVERAGE([绝地值价格变化])

下一步是创建动态排名，用户可选择以上四种度量之一作为排序依据。首先创建两张内表作为参数表，见下图，将其导入模型中。

然后使用Switch语句创建“方式”，将排序度量与用户所选排序ID一一对应。

所选排序ID:=MIN('排序依据'[ID])

方式:=SWITCH([所选排序ID],1,[日均价格变化%],2,[绝对值日均变化%],3,[最大变化%],4,[最小变化%])

接下来添加一组度量，可以实现升序与降序功能。

升序排名:=IF(HASONEVALUE(EVERYTHING[Symbol]),RANKX(SUMMARIZE(ALLSELECTED('EVERYTHING') ,EVERYTHING[Symbol]),[方式], ,0, SKIP))

降序排名:=IF(HASONEVALUE(EVERYTHING[Symbol]),RANKX(SUMMARIZE(ALLSELECTED('EVERYTHING') ,EVERYTHING[Symbol]),[方式], ,1, SKIP))

排名:=if( HASONEVALUE(OrderBy[排序]) ,if(values(OrderBy[排序])="升序",'排序依据'[升序排名],'排序依据'[降序排名]),'排序依据'[降序排名])

创建新Excel表单，参照下图插入透视表与筛选器。

最后，为分析结果添加一个排名限制。创建以下一组度量，将所选排名数量与真实排名做判断，返回1或0。将“是否显示排名”添加至透视表中、“所选排名行数”添加至筛选器中，见下图。

所选排名行数:=MIN([显示排名])

是否显示排名:=if([排名]<=[所选排名行数],1,0)

提示：现代BI已经在事务型处理能力上进行了强化，事务处理与分析处理也不是完全泾渭分明。以上示例中，VBA负责事务型功能处理、PowerPivot负责分析型功能处理。Excel是作为二者共同的载体进行了集成。

特别声明：以上操作，纯属演示，不构成任何交易购买建议。