使用电子表格建立交易策略

介绍

表格是一项相当古老的发明，但这种类型的现代程序具有强大的功能，允许您直观地分析以表格形式呈现的数据。分析可以从不同的角度进行，而且进行得很快。它包括图形、摘要表、假设分析、条件单元格格式等等。

我建议测试一下这种能力来分析定制策略。

就我个人而言，我使用LibreOffice Calc是因为它是免费的，在我工作的任何地方都可以使用：-）然而，同样的方法也适用于其他电子表格：Microsoft Excel、Google Sheets 等。目前，它们都允许相互转换，并且具有构造公式的相同原理。

所以，我想假定，你有某种电子表格程序。您还有要分析的文本文件格式（*.txt或*.csv）的数据。本文简要介绍了如何导入这些文件。我将使用 MetaTrader 终端的历史记录，但是，任何其他数据都可以，比如Dukascopy或Finam。显然，您应该有一个配置信号的策略。这就是在交易中应用本文主张所需的全部内容。

我希望，这篇文章对不同类别的交易者有用，所以我会尽量把它写出来，这样即使是以前从未见过这种类型的程序的人也能理解。同时，它将涵盖一系列的问题，甚至一些有经验的交易员并不熟悉。

快速浏览表格 - 面向初学者

图1显示了一个典型的电子表格程序窗口。

图 1. 典型的电子表格程序窗口

任何表格都以一组“工作表”的形式呈现。您可以将它们视为不同任务的单独“选项卡”。

每张工作表都由“单元格”组成。每个单元本质上都是一个很小但功能非常强大的计算器。

为了让程序了解我们现在要处理哪个单元格，每个单元格都有坐标，比如在棋盘上或战舰棋盘游戏中。这些坐标一起定义了一个唯一的单元格“地址”。地址由一个列号或名称和一个行号组成（例如，图1显示了由表光标突出显示的“D19”单元格）。它可以在突出显示的坐标名称和名称行中看到。

除了坐标外，地址还可以包含工作表的名称，甚至表文件的名称。同一地址通常用作单元格的名称,但是，如果您愿意，您可以设置自己的名称，以明确此单元格或单元格范围存储的内容。您可以在名称行中查看（和更改）这些名称。

单元格可以包含简单数据（如报价或交易量）或用于计算其值的“公式”。

突出显示单元格的内容可以在“公式行”中看到（并更改）。

要编辑单元格值，请双击它或在公式行中进行更正。您也可以高亮显示单元格并按F2。如果需要创建新文本，可以高亮显示单元格并立即开始键入。但是，请记住，所有以前的数据都将从单元格中删除。

按 ESC 键（键盘左上角）可以取消编辑而不保存。按回车(Enter)键（光标下移）或制表(Tab)键（光标移到一边），确认编辑。

如果高亮显示多个单元格，Enter 和 Tab 键仅在高亮显示的片段中工作。这可以用来加速工作。

至于其他的按钮和菜单，我认为它们很容易掌握。

入门：导入报价

让我们准备数据来测试策略，如我所说，我将从终端获取数据。要执行此操作，请在任何图表窗口中按 Ctrl+S，或在终端菜单中选择 文件 -> 保存。终端提供通常的系统窗口来输入文件名和路径，

如果文件扩展名是*.csv，那么通常一切正常。如果它是 *.txt，那么在大多数情况下，您需要用鼠标右键单击它并选择“打开方式”->“选择另一个应用程序”，或者先打开电子表格应用程序并从中打开文件，因为默认情况下，系统倾向于使用记事本或其他文字处理器打开具有此扩展名的文件。

要转换数字，请在转换窗口中选择相应的列。然后指出整数部分和小数部分的分隔符，以及数字组（对于交易量）的分隔符（如果需要）。在 Excel 中，这是使用“更多…”按钮完成的。在 Calc 中，从列类型列表中选择 English USA。

还有另一个细节，导入成功后，最好在表中保留 5000-7000 行。事实上，数据越多，程序计算每个单元格的结果就越困难。同时，估计精度也没有显著提高。例如，当比较5000行和100000行数据的验证结果时，结果仅相差1%，而后者的计算时间显著增加。

一些使用表格的快捷键

快捷键	行为
Ctrl + 箭头	转到最近的连续数据行边界
Tab	确认输入并转到右边的单元格
Shift + Tab	确认输入并转到左边的单元格
Enter	确认输入并转到下方的单元格
Shift + Enter	确认输入并转到上方的单元格
Ctrl + D	从上往下填充高亮显示的列
Shift + Ctrl + 箭头	从当前的位置开始到连续范围的边界进行高亮显示

如何用相同的公式填充长列

对于小范围，可以使用图2所示的方法：将鼠标移到“选择标记”（表光标右下角的正方形）处。当鼠标光标变为细十字时，将此标记拖动到所需的行或列。

图 2. 通过拖曳填充

但是，对于大量数据，这将非常不方便。

因此，请使用以下任何方法。

方法 1. 限制范围

动作顺序如图3所示。

图 3. 通过限制范围填充

1. 在范围的顶部单元格中输入所需的公式并确认输入。
   
2. 使用“名称”字段移到区域的最底部单元格。
   
3. 按下 Ctrl + Shift + 向上箭头移动到区域中最上面的单元格并选择所有中间单元格。
4. 按下 Ctrl + D 来在单元格中填充数据。

该方法的一个小缺点是需要知道范围中最低行的数目。

方法 2. 使用相邻连续范围

操作顺序如图4所示。

图 4. 使用相邻范围填充

1. 用必要的公式选择单元格。
2. 按下 Shift + 向左箭头来选择邻近的单元格，
3. 按下Tab来把表格光标移动到左侧单元格，在这里，我们使用表格光标的功能来仅在应用的选择中移动。
4. Ctrl + Shift + 向下箭头 — 选择连续范围内最低行的两列。
5. Shift + 向右箭头 — 取消选择左侧列，右侧列保持选中状态。
6. Ctrl + D — 使用数据填充列。

注意图中公式的内容，将包含链接的公式复制到另一个单元格时，此链接将根据光标位置自动更改。因此，这种联系被称为“相对的”。

如果在复制过程中需要单元格的链接保持不变，请选择链接并按F4。$ 标记会出现在行号和列名之前，并且复制公式时该值不会更改。

有时，您可能只希望某一列或某一行保持完整，而不是整个链接。在这种情况下，只在不可更改部分保留$符号（您可以再按一次或两次F4）。

现在，在我们掌握了加快工作的基本方法之后，是时候转到策略本身了。

策略

让我们使用标准“Examples\Moving Average”EA中实现的策略。

在下列情况下打开一个仓位：

1. 当时没有仓位并且
2. 烛形与其主体穿过移动平均线（在МА的一侧打开，在另一侧关闭）

如果有以下情况关闭仓位:

1. 有未平仓的仓位并且
2. 烛形在与开口相反的方向穿过MA。

添加指标数据

使用电子表格计算的显著特点是，计算的小计通常需要单独保存。这使得更容易理解公式和检测错误，也简化了基于相邻单元数据建立公式的过程。此外，这种“碎片化”有时会产生新的想法。

但让我们回到我们的任务。

在导入和格式化之后，我的原始报价如下所示（图5）：

图 5. 原始报价

请注意整个表名与列名之间的空白行。这一行允许电子表格处理程序将这两个块视为单独的表，因此我可以组合顶部区域的单元格，但仍然对底部区域使用不同的过滤器，而且它们不会相互干扰。删除这一行可能会导致问题。

我已经修复了第一行和第一列，以隐藏目前不必要的信息，但所有数据仍然存在于表中（请参阅电子表格处理程序的帮助）。

时间和日期在A列，开盘价在B列，等等。表格的最后一行编号为6038。

制定战略的第一步是制定指标。为了使指示符可以自定义，让我们添加另一个表并在那里创建一个变量表。我们将使用名称行为每个变量指定一个适当的名称，以便在准备公式时清楚地知道要取什么和在哪里。

图 6. 变量页

现在让我们回到数据表。首先，在G列的列表中写下引号索引，以稍微简化最终的公式。等于行索引减3：

=ROW()-3

将公式写入G4单元格后，将其扩展到所有较低的单元格，使MA计算公式保持通用性。如果MA（偏移量+周数期）超过现有数据，则平均值计算变得毫无意义。

SMA计算公式本身写在主数据表的H4中，如下所示：

=IF( G4>(MovingPeriod+MovingShift), AVERAGE( INDIRECT( "E" & ( ROW()-MovingShift-MovingPeriod) & ":" & "E" & ( ROW()-MovingShift) ) ), "" )

当输入需要链接到其他单元格的公式时，可以用鼠标指定单元格。

当前公式从调用IF（）函数开始。正如您可能猜到的，这是条件检查函数。所有布尔表达式，如And，Or，Not，也将是函数，以备以后需要。

调用函数时，参数用括号指定，并用逗号（在本例中）或分号分隔。

IF函数接受三个参数：条件、IF 条件为 true 的值和 IF 条件为false的值。

在本例中，我使用它来检查是否有足够的数据来计算MA曲线的完整点。如果没有足够的数据，只需保存一个空字符串即可。否则，计算某个范围的平均值。

间接函数返回由文本字符串设置的范围中的值（或多个值）。这正是我需要的，因为计算平均值所需范围的地址应该基于输入值形成。

电子表格程序中的&符号表示两行的串联。因此，我将地址从几个部分中“组合”起来。第一部分是一个列名，收盘价（“Е”）位于其中，而第二部分是一个“远程”地址，计算方法是当前行号减去平均长度，再减去移位。这个表达式的第三部分是冒号，表示范围的连续性。它后面是考虑到移位的列名和行名。我决定不把它们太显眼。我希望，这些符号和插入符号能有助于解决这个问题。

公式应该扩展到下面的所有行。

结果，我们得到了下面的样子：

图 7. 添加МА计算后的表格

如我们所见，Н列中的数字开始只出现在22行（第19项）中。原因在图6中解释。

现在我们有了初始数据，还有指标数据。现在是实现策略的时候了。

实施策略

我们将以简单的信号形式实现这一策略。如果МА向下交叉，单元接收值为“-1”，否则为“1”。如果此时没有交点，则单元格包含空字符串值。

转到单元 I4，单元的基本公式如下：

=IF( AND( B4>H4,E4<H4 ),-1 , IF( AND( B4<H4,E4>H4 ), 1 , "") )

你可以在图表上查看，它是有效的。但这是一个简单的逆转公式。它不允许跟踪交易状态。您可以对它进行实验并获得有趣的结果，但我们现在的任务是实现本文开头描述的策略。因此，我们需要在每个柱（每行）记录交易状态。

J 这一列很适合。J4单元格中的公式如下：

=IF(AND(I4=-1,J3=""), -1 ,IF(AND(I4=1,J3=""), 1 ,IF(OR(AND(I4="",J3=1),AND(I4="",J3=-1),I4=J3), J3 ,"")))

如果发生了事件（交叉点），就检查上一笔交易的状态。如果交易是未平仓的，并且交叉点发生在相反的方向，则关闭交易。如果交易是关闭的，就开启交易。在所有其他情况下，只需保存状态。

让我们引入一个包含信号的列，以便清楚地看到，如果我们在数据对应的时期内实施了这一策略，我们将在何处买入和卖出，同时也便于分析该策略。

信号名称可以从辅助中获取，帮助可以在变量表中创建。

图 8. 添加交易名称辅助后的变量表

注意名称行：这里我将名称设置为整个选定范围，而不是单个单元格。

现在我们可以在主表的K4单元格中写入以下内容（带数据）：

=IF(AND(J3=1,J2=""),INDEX(DealTypes,1),IF(AND(J3=-1,J2=""),INDEX(DealTypes,2),IF(OR(AND(J3="",J2=1),AND(J3="",J2=-1)),INDEX(DealTypes,3),"")))

交易在下一个烛形打开时发出信号后开始。因此，要注意这个公式中索引的偏移。

如果没有交易（状态列中的前一个单元格为空）且信号已到达，请指定应进行的交易类型。如果交易已经开始，根据信号结束交易。

Index函数接受要在其中执行搜索的范围作为第一个参数。在我们的例子中，它是由名称设置的。第二个参数是范围内的行的索引。如果范围由多个列组成，要设置必要的列。如果指定了几个用分号分隔的范围，要设置从1开始的范围索引（分别是第三个和第四个参数）。

最后，在将此公式扩展到下面的所有单元格并应用条件格式（因为在分析过程中不需要格式，所以为了更直观的吸引力），我们将得到大约如下结果：

图 9. 交易信号

分析策略

为了分析策略的盈利能力，我需要计算交易期间价格所经过的距离。最简单的方法是分几个阶段。

首先，选择交易价格。如果交易已打开，请在信号旁边的列（L）中设置价格，并将其复制到随后的每个单元格中，直到交易关闭。如果没有交易，则向单元格中写入一个空行。L4单元格中的公式：

=IF(K4=INDEX(DealTypes;1);B4+Spread;IF(K4=INDEX(DealTypes;2); B4 ;IF(OR(K4=INDEX(DealTypes;3);N(L3)=0); "" ;L3)))

如果信号单元（K4）上有“Buy”字样，则交易开盘价等于烛形开盘价加点差。如果单词是“Sell”，只需写烛形开盘价，如果“Close”（或前一列单元格不包含数字）-空字符串，如果同一列的前一单元格是数字，而信号列不包含单词，只需复制前一单元格。

图 10. 交易起始价格

这样，我们将能够轻松地计算成交时的交易利润。

一定要扩展下面的公式。

我们可以立即计算相邻一列开盘价和收盘价之间的差额。但是，我们会做一些更特别的事情。我们将计算N列中的差异，以便仅能整理出唯一的数据，然后计算其频率。

在目前最简单的评估案例中，我不会使用任何资金管理，因为我的目标是评估策略的效率。因此，以点数计算点差就足够了。举例:

=IF(K4=INDEX(DealTypes;3);IF(I3=-1;ROUND((B4-L3)/Point);ROUND((L3-B4)/Point)); "" )

很明显，我们可以简单地乘（B3-L3）*I3，而不是检查平均条件，但对于初学者来说，这在视觉上不太清晰。

现在是时候玩这个把戏了。在М列中，对有关交易范围的所有唯一条目进行编号，而非唯一条目则没有编号。

=IF(N4<>"";IF(COUNTIF(N$3:N4;N4)=1;MAX(M3:M$4)+1;"");"")

外部条件很清楚：如果右边的单元格（N4）不是空的，检查它是否唯一，必要时给它编号，否则留下一个空字符串。

但是编号是如何工作的呢？

如果单元格值与第二个参数中指定的条件相对应，则函数 Countif 对指定范围内的数字量进行计数。假设公式是为M71单元计算的,N71单元格包含数字531（见图11）,这个数字以前从未见过。

如果在条件单元格中没有指示动作符号，则假定我们要检查两个值的相等性。这个数等于它本身（N71=N71），所以让我们来计算一下。计算总是从N$3单元格开始（注意数字3之前的美元符号），一直到当前单元格（公式中没有美元符号）。查看整个N$3:N71范围，并尝试计算此范围内531个数字的总数。因为以前没有这样的数字，所以总数是1（就是现在发现的数字）。这意味着满足条件：函数结果为1。因此，我们采用以下范围：公式所在的列从第一个有数字的单元格（M$4）开始，一直到当前单元格之前的单元格（M70）。如果之前有任何数字，取其中最大的一个加1。如果不是，最大的一个是0，相应地，第一个序列号已经准备好了！

图 11. 编号. 影响的单元格 (范围终点)

图 12. 编号 (范围起点)

在图11中，我尝试使用内置的分析工具来显示影响给定单元格的单元格。带箭头的点表示范围或“精确”单元格的开始，而矩形表示范围。为了清楚起见，我附上了图12，以表明箭头是连续的，并且正好从N$3开始，并且使进行比较的范围的开始可见。

此外，我将再添加两列值：result type 和 deal“module”。

对于结果类型，我使用数字：买入交易-1，卖出交易-2。在这种情况下，结果可以是正的，也可以是负的，这取决于我们是否收到了作为交易结果的利润或亏损。这将缩短最终分析公式。

下面是写入О4单元格的公式：

=IF(AND(N(N4)>0;I3=-1); 1 ;IF(AND(N(N4)<0;I3=-1); -1 ;IF(AND(N(N4)>0;I3=1); 2 ;IF(AND(N(N4)<0;I3=1); -2 ;""))))

“module”只是不考虑符号的损益金额。它描述了在交易结束信号到来之前，价格在一个方向上走了多远。这可以帮助您选择止损和获利（即使它们不是原始策略所需要的）。

“module”公式非常初级，设置在P4中：

=IF(N4<>"";ABS(N4);"")

为了构建频率（概率）图，最好将处理结果数据按升序排列。由于原始数据是按时间排序的，因此无法按其他方式排序，因此请将它们复制到另一个工作表中。

假设每个唯一利润结果都有自己的唯一编号（М列），那么至少有两种方法可以将未排序的数据复制到新表中。

其中之一是使用М列中的标准过滤器简单地选择“非空”单元格，然后从N列复制数据，并使用特殊的粘贴（仅值）将其粘贴到另一个页面上。

第二种方法是使用公式。它的优点是，当原始数据（相同的变量或其他一些变量，如果您决定使用其他测试范围）改变时，数据本身也会改变。缺点是可能仍然无法排序。您仍然需要使用“复制/粘贴”对其进行排序。

当排序和未排序的数据在同一张工作表上时，对我来说更方便，因为复制数据所需的操作更少。因此，我将显示一个选项，其中使用公式复制未排序的数据，然后再次手动复制以进行排序。

在新的利润数据表上，在А2中创建公式：

=VLOOKUP( ROW(1:1);'GBPUSDH1-Metaquotes-demo'.$M$3:$N$6038; 2 )

行（1:1）函数返回第一行的编号。向下填充单元格时，行号会发生变化，并相应地显示第二行、第三行等的编号。

Vlookup在范围的第一列（第二个参数）中查找某个值（第一个参数），然后返回位于相同检测行中的值，尽管在第三个参数中指定的列中（在我们的示例中，这是指定范围的第2列）。换句话说，从N列（从1开始）复制所有编号（唯一）的数值。

使用标准过滤器定义主工作表上的最后一个数字后，可以使用范围限制方法复制所有剩余数据。

图13动画中显示了以下操作。

图 13. 复制数据用于排序

现在我们需要描述有利可图和无利可图交易的频率，即建立一个概率序列。

在同一张表（利润数据）的D2单元格中，我们可以写出以下公式：

=COUNTIF('GBPUSDH1-Metaquotes-demo'.$N$4:$N$6038;C2)/COUNT('GBPUSDH1-Metaquotes-demo'.$N$4:'GBPUSDH1-Metaquotes-demo'.$N$6038)

它描述了每个利润值的频率（或概率）。

Count函数计算区间中的数值个数，Countif如果满足条件则执行相同的操作（在这种情况下，仅计算值等于C列单元格中的值的单元格）。

通常建议进行区间变化序列。理论上，我们可以说，交易的数量可能相当大。

建议使用以下公式计算间隔大小：

=(MAX($'Profit data'.C2:$'Profit data'.C214)-MIN($'Profit data'.C2:$'Profit data'.C214))/(1+3,222*LOG10(COUNT('GBPUSDH1-Metaquotes-demo'.$N$4:'GBPUSDH1-Metaquotes-demo'.$N$6038)))

我把这个公式放在“Variables”的E7单元格中，并把它命名为“Interval(区间)”。结果区间太大了。我不清楚概率是如何分布的，所以我把它除以4。最后的数字-344-被证明是更能适合我的目的。

在“Profit data(利润数据)”表中，我将排序列表中的第一个数字复制到了F2：

=C2

所有其他单元格都用以下公式填充：

=F2+Interval

单元格被填充，直到最后一个值超过最大交易值。

G2单元格包含以下公式：

=COUNTIFS('GBPUSDH1-Metaquotes-demo'.$N$4:$N$6038;">="&F2;'GBPUSDH1-Metaquotes-demo'.$N$4:$N$6038;"<"&F3)/COUNT('GBPUSDH1-Metaquotes-demo'.$N$4:$N$6038)

CountifS（与Countif不同）允许接受多个条件，并将它们与“AND”运算符组合。其余的都一样。

当这两个序列被构造出来时，我们马上就想看到它们的图形表示。幸运的是，任何电子表格处理器都允许实现这一点。

图 14. "即时" 概率分布图

图 15. 完成交易概率的区间分布图

图14展示了概率密度的负偏移。图15显示了从-942到2154的清晰可见的峰值和8944的峰值（一笔交易）。

我相信，分析表不会造成任何特别的困难（考虑到所分析的一切）。

图 16. 一些统计计算

这里唯一的新功能是使用Sumproduct函数，它接受两个区间作为参数，并返回这些区间成员的乘积之和（例如，第一行到第一行，第二行到第二行，等等）。我用这个函数来计算预期收益。我决定不应用任何更复杂的集成方法。

预期收益明显低于获得的利润，并且以百分比计算在0左右波动。

因此，该策略是可行的，但可能会遭受非常大的回撤。很可能，它在非常强劲的趋势中工作得很好（如果不是如此孤独的话，9000点的飙升似乎很有趣），然而，横盘期间很可能会付出代价。该策略需要认真修改，要么引入待定订单，例如获利回吐（约420-500点），要么引入一些趋势过滤器。改进需要额外的研究。

在测试器中运行策略

图 17. "Examples\Moving Average" EA 测试结果

老实说，EA的结果让我很惊讶。事实上，它在表中建议结束交易的情况下开始交易，反之亦然，这可能被认为是正常的，因为它的决策可能基于或多或少的数据（例如，在我的表中，2019年11月25日19:00开始，而我给EA的任务是从当天开始）。

我更惊讶的是，有些交易如下所示。。。

图 18. 我对算法的理解会错吗？还是测试器出了问题？

很可能，我只是搜索不够好，在算法中找不到这种行为的原因。

第二个奇怪的事实是，EA比我的表中建议的多做了20笔交易。但尽管如此，结果还是和我的很接近，尽管看起来很奇怪。

测试器中的EA	表格
预期收益 — +0.07 (几乎为 0)	预期收益 — -0.76 — +0.41 (在 0 附近浮动)
获利/亏损交易数 — 26.52%/73.48%	获利/亏损交易数 — 29.22%/70.78% (考虑到交易数量8%的差异，3%的差异在这里可以被认为是微不足道的)

测试器盈利能力图和使用该表得到的盈利能力图非常相似：波动在0左右，差异约为±500点，2月份出现峰值：

图 19. 表格盈利水平图

图 20. 测试器盈利水平图

准备表格和修改数字花了大约半个小时。为了开发EA，我决定使用现成的EA。大概花了10分钟来弄清楚大致的算法。但是，没有必要写一个新的 EA, 到头来发现我可能不会使用它。。。只有当我意识到策略是值得的时候，开发EA才是合理的。另外，我现在更喜欢手工交易：-）

结论

我相信，电子表格是测试和开发策略的一个非常好的工具，特别是对于那些没有编程技能的人，以及那些愿意快速创建原型并在之后将其转换为MQL的人。

当然，电子表格处理器公式有时类似于一个程序代码，格式在视觉上不太清晰。

然而，电子表格的清晰性、即时测试新想法的能力、突出显示特定单元、任何类型的图表等使电子表格成为不可或缺的工具。

如果您在表中保留交易日志或能够导入它，那么使用电子表格处理器可以轻松改进策略并检测交易中的错误。