干货全拿走-用Excel构建股票量化交易模型

2023年1月程序更新，如下文：干货全拿走-用ExcelVBA做个股多模型交叉验证择时交易套利及回测系统 - 知乎 (zhihu.com)https://zhuanlan.zhihu.com/p/595084758

为啥优化呢，主要感觉之前用的体验不太好。具体来说就是模型虽然有，但是我如果想多个模型之前交叉验证，就需要同时打开好几个运行，太繁琐了，而且回测功能也有待优化。所以这次来了个彻彻底底的升级！之前的只能说是模块，现在的才是真真正正的系统！新系统演示可以看：

我用Excel做了一个股票多模型交叉验证择时交易套利及回测系统...._哔哩哔哩_bilibilihttps://link.zhihu.com/?target=https%3A//www.bilibili.com/video/BV1UY411U7Dt/

一、 前言

二、 实现思路

三、 核心代码

四、 注意事项

一、前言

大数据时代，数据分析的价值愈发凸显，数据对于金融市场亦如是。现在越来越多的金融机构和个人借助专业的软件去做数据分析和获取数据，但是显然对于普通人来说，一是学习门槛高，二是年费高。普通人可能做股票，根本不会分析也不会有数据支撑，极少数的人有自己的策略和理论，大多数人都成了韭菜。普通人接触最多的数据分析处理的工具就是EXCEL，而用excel去支撑普通人一般对于股票数据分析的需求，就是我做这个系列的初衷，也是将我原来自己用的一些东西分享出来，发挥更大的价值吧。自己有一部分是用excel做的网抓，另外一部分是算法和模型，这次分享的就是算法和模型的部分了。

说到算法和模型，其实每个人可能都有自己的原则和策略，但是大家可能觉得只有用专业的软件才能实现。我不否认这些软件在数据分析方面的专业性，只是相对简单的算法的话，其实用什么语言写差别并不大。Excel本身的函数公式和VBA扩展其实可以支持搭建一些量化分析模型的。

因为数据都是网抓获取，没有自己的数据，而且excel也没办法自动交易，所以能够做的量化模型的时间尺度就不是高频的。自己平时也不想因为交易占用很多时间，所以还是想做一个日尺度的量化模型，当日收盘后复盘，能指导反映次日的买卖点就可以了。

二、实现思路

（1）理论模型构建。想要把量化模型软件化，先要有经得起验证的量化理论。我构建了4个方向的理论，都是相对简单，比较容易用代码实现的，甚至不需要多么高级的建模工具，只需要Excel就可以。

一是基于统计学的概率分析模型，主要用于指导做T，使得做T成功率比较高，指导价格落在概率比较高的区间。这个可以看我的视频解说：https://www.bilibili.com/video/BV15Z4y1i7y2/

二是基于K线形态的分析模型，这个主要是基于经典的股票理论，结合历史相似性，判断某个K线形态后，预测次日发生另外一个K线形态的历史概率。这个可以看我的视频解说：https://www.bilibili.com/video/BV1yf4y1f7tB/

三是基于历史相似性匹配的分析模型，这个主要是自己根据量价指标定义了一个相似度系数，结合历史数据，计算次日最可能的某种量价形态是什么情况，看看历史有多少时候发生了类似的情况。这个可以看我的视频解说：https://www.bilibili.com/video/BV1vf4y1f79W/

四是基于多技术指标交叉验证的模型。这个底层比较复杂，需要单独计算各种技术指标，然后做下交叉验证，得出一个综合系数，来判断涨跌的可能性。这个可以看我的视频解说：https://www.bilibili.com/video/BV1N3411w7YY/

（2）数据获取。这个是最关键的一步，没有数据源一切都白扯。“巧妇难为无米之炊”。这里主要是用到了历史量价数据。相关数据都可以用之前的下载器下载到，方法类似，感兴趣的可以看我的这篇帖子：

用Excel实现自动获取期货、期权、股票行情及下载历史数据 - 知乎

（3）代码实现和界面设计。

之前的四个量化模型理论，实际上是做了4个程序来实现，界面主要是基于Excel设计的，用Excel就可以使用。基本上都有网抓、分析和数据存储三个模块，界面部分展示如下：

三、核心代码

先看下最后完成的效果，我上传了视频，可以直接观看：

一是基于统计学的概率分析模型， https://www.bilibili.com/video/BV15Z4y1i7y2/

二是基于K线形态的分析模型， https://www.bilibili.com/video/BV1yf4y1f7tB/

三是基于历史相似性匹配的分析模型， https://www.bilibili.com/video/BV1vf4y1f79W/

四是基于多技术指标交叉验证的模型， https://www.bilibili.com/video/BV1N3411w7YY/

如果要获取完整版的代码和Excel表格程序，可以查看上述视频的简介和评论置顶区。

代码方面，以技术指标模型为例，主程序如下，里面调用了很多单独写的计算指标的VBA工程：

Sub CmdStockIndex_Click()

ThisWorkbook.Worksheets("技术指标模型").Range("B2:AA" & (ThisWorkbook.Worksheets("技术指标模型").Range("B1048576").End(xlUp).Row + 1)).ClearContents

'这里先倒转数据分析

ReDim Preserve DateArray(ThisWorkbook.Worksheets("日历史").Range("A1048576").End(xlUp).Row - 2)

ReDim Preserve ClosePriceArray(ThisWorkbook.Worksheets("日历史").Range("A1048576").End(xlUp).Row - 2)

ReDim Preserve HighPriceArray(ThisWorkbook.Worksheets("日历史").Range("A1048576").End(xlUp).Row - 2)

ReDim Preserve LowPriceArray(ThisWorkbook.Worksheets("日历史").Range("A1048576").End(xlUp).Row - 2)

ReDim Preserve AmountArray(ThisWorkbook.Worksheets("日历史").Range("A1048576").End(xlUp).Row - 2)

ReDim Preserve ChangeRatioArray(ThisWorkbook.Worksheets("日历史").Range("A1048576").End(xlUp).Row - 2)

p = 0

For i = ThisWorkbook.Worksheets("日历史").Range("A1048576").End(xlUp).Row To 2 Step -1

DateArray(p) = ThisWorkbook.Worksheets("日历史").Range("A" & i).Value

ClosePriceArray(p) = ThisWorkbook.Worksheets("日历史").Range("B" & i).Value

HighPriceArray(p) = ThisWorkbook.Worksheets("日历史").Range("C" & i).Value

LowPriceArray(p) = ThisWorkbook.Worksheets("日历史").Range("D" & i).Value

AmountArray(p) = ThisWorkbook.Worksheets("日历史").Range("J" & i).Value / 1000000

ChangeRatioArray(p) = ThisWorkbook.Worksheets("日历史").Range("H" & i).Value

p = p + 1

Next

ThisWorkbook.Worksheets("技术指标模型").Range("B2:B" & (UBound(DateArray) + 2)) = Application.Transpose(DateArray)

ThisWorkbook.Worksheets("技术指标模型").Range("C2:C" & (UBound(ClosePriceArray) + 2)) = Application.Transpose(ClosePriceArray)

ThisWorkbook.Worksheets("技术指标模型").Range("D2:D" & (UBound(ClosePriceArray) + 2)) = Application.Transpose(HighPriceArray)

ThisWorkbook.Worksheets("技术指标模型").Range("E2:E" & (UBound(ClosePriceArray) + 2)) = Application.Transpose(LowPriceArray)

ThisWorkbook.Worksheets("技术指标模型").Range("F2:F" & (UBound(ClosePriceArray) + 2)) = Application.Transpose(AmountArray)

ThisWorkbook.Worksheets("技术指标模型").Range("G2:G" & (UBound(ClosePriceArray) + 2)) = Application.Transpose(ChangeRatioArray)

'——————————————————EMA指标

Dim Op5 As Integer, Op10 As Integer, Op20 As Integer, Op30 As Integer, Op60 As Integer

Op5 = 5

Op10 = 10

Op20 = 20

Op30 = 30

Op60 = 60

Call EMA(Op5, Op10, Op20, Op30, Op60)

'——————————————————MACD指标

Dim EMA12 As Integer, EMA26 As Integer, DIF9 As Integer

EMA12 = 12

EMA26 = 26

DIF9 = 9

Call MACD(EMA12, EMA26, DIF9)

'——————————————————KDJ指标

Dim EMA9 As Integer, K3 As Integer, D3 As Integer

EMA9 = 9

K3 = 3

D3 = 3

Call KDJ(EMA9, K3, D3)

'——————————————————BOLL指标

Dim MA20 As Integer, Sigma2 As Integer

MA20 = 20

Sigma2 = 2

Call BOLL(MA20, Sigma2)

'这一部分将最近10日的指标单独拿出来写入观察窗口————————

RowStart = UBound(DateArray) - 7

i = RowStart

For p = 30 To 39

ThisWorkbook.Worksheets("技术指标模型").Cells(2, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 3) '收盘

ThisWorkbook.Worksheets("技术指标模型").Cells(3, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 4) '盘高

ThisWorkbook.Worksheets("技术指标模型").Cells(4, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 5) '盘低

ThisWorkbook.Worksheets("技术指标模型").Cells(5, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 6) '交易量

ThisWorkbook.Worksheets("技术指标模型").Cells(6, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 7) '收盘涨跌幅%

ThisWorkbook.Worksheets("技术指标模型").Cells(7, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 8) 'EMA5

ThisWorkbook.Worksheets("技术指标模型").Cells(8, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 9) 'EMA10

ThisWorkbook.Worksheets("技术指标模型").Cells(9, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 10) 'EMA20

ThisWorkbook.Worksheets("技术指标模型").Cells(10, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 11) 'EMA30

ThisWorkbook.Worksheets("技术指标模型").Cells(11, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 12) 'EMA60

ThisWorkbook.Worksheets("技术指标模型").Cells(12, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 15) 'DIF

ThisWorkbook.Worksheets("技术指标模型").Cells(13, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 16) 'DEA

ThisWorkbook.Worksheets("技术指标模型").Cells(14, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 17) 'MACD

ThisWorkbook.Worksheets("技术指标模型").Cells(15, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 21) 'K

ThisWorkbook.Worksheets("技术指标模型").Cells(16, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 22) 'D

ThisWorkbook.Worksheets("技术指标模型").Cells(17, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 23) 'J

ThisWorkbook.Worksheets("技术指标模型").Cells(18, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 24) 'MA

ThisWorkbook.Worksheets("技术指标模型").Cells(19, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 25) 'UP

ThisWorkbook.Worksheets("技术指标模型").Cells(20, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 26) 'DOWN

ThisWorkbook.Worksheets("技术指标模型").Cells(21, p) = ThisWorkbook.Worksheets("技术指标模型").Cells(i, 27) '交易量变换率

i = i + 1

Next

End Sub

五、 注意事项

Excel作为一个办公软件，有它的方便性，但是也有局限。就是它的计算内核并不能与大数据分析或者数据库软件（可轻松处理大于1个GB的数据等）相比，处理海量数据的话真的会卡死。所以我做的这些模型，并不能把所有的股票数据导入进去，只能是选好了股票，然后针对个股去判断它的买卖时机，换句话说，这个并不是一个选股器，而是一个在用户选到了股票之后，针对这个个股的一个指导交易程序。如果用到excel选股器的话，可以参考我的另外一篇文章： 干货全拿走-用Excel制作小市值轮动价值投资选股器 - 知乎

如要获取源程序，可以访问视频演示（见二）中的视频简介区

或者软件地址：https://item.taobao.com/item.htm?ft=t&id=681629414983