在Excel中为内幕交易创建高效仪表板

一个好的电子表格可以使162,000份申报文件看起来很少，至少在有人将文本按日期排序之前是这样。

内幕数据素来以专业性著称。其实不然。大部分工作都是事务性的，这是个好消息，因为当您不再要求Excel充当数据库，而是让它作为电子表格时，它非常擅长处理事务性工作。

本文是一份实用指南，旨在将内幕交易申报的CSV导出文件转化为您可以实际使用的内容。没有宏，没有Power Query的复杂操作，也没有由已离职员工编写的VBA“神技”。只有干净的导入，一些数据透视表模板，以及可以降低您在投资组合经理面前出糗几率的示例公式。

为什么 Excel 仍然是首选

内幕交易的第一遍筛选通常不是建模问题。它是一个过滤问题。您想知道：

• 哪些发行人近期有买入行为，
• 交易活动是否集中在几位内部人士之间，
• 交易是公开市场购买还是信息量较低的其他类型，
• 申报日期是否滞后于交易日期，
• 以及交易金额是否足够大，值得关注。

这正是数据透视表的用武之地。

一份像样的 CSV 文件在 Excel 中回答这些问题比在大多数内部仪表板中更快，这主要是因为 Excel 允许分析师即兴发挥。您可以随意拖动字段，测试一个假设，并在 20 秒后放弃它，而无需提交任何工程工单。这是旧软件少数几个能发挥作用的场景之一。

最低限度的可用内部人士 CSV

如果您正在从内部平台或供应商下载数据，理想的导出文件应包含以下列：

如果您的导出文件缺少其中任何一项，请在您的流程备注中写入 n/a，而不是假装该字段无关紧要。缺失性是分析的一部分。

在使用数据透视表之前，请做三件事

1. 将范围转换为表格 选择 CSV 中的任意单元格，按 Ctrl + T，确认标题。给表格起一个规范的名称，例如 tblInsider。

2. 立即修复日期列 检查 transaction_date 和 filing_date 是否是真实的日期，而不是假装是日期的左对齐文本。如果 Excel 认为 2026-03-04 是文本，您的月度摘要将变成抽象艺术。

3. 标准化交易价值 如果CSV不包含transaction_value，请将其添加为计算列：

   =[@shares]*[@price]
   

   如果任一字段缺失，请使用：

   =IFERROR([@shares]*[@price],NA())
   

五分钟设置

最快速实用的工作簿包含三个工作表：

• RawData
• Pivots
• Lookups

这样的结构足以保持整洁，而无需假装您正在工作簿中构建数据仓库。

步骤1：干净地导入CSV

在Excel中打开CSV，或者如果您想更好地控制数据类型，请使用数据 > 从文本/CSV。加载后：

• 转换为表格，
• 冻结首行，
• 应用筛选器，
• 并将shares、price和transaction_value格式化为数字。

如果您的文件包含混合货币，请勿跨货币汇总价值字段，除非您有一个经过外汇标准化处理的列。这听起来很明显。但糟糕的图表也正是由此而生。

步骤2：添加辅助列

辅助列是Excel从“尚可”变为“实用”的关键。以下是值得添加的辅助列。

签署值

如果您的交易代码区分买入和卖出，请创建一个签署度量，使买入为正，卖出为负。

=IF(OR([@transaction_code]="P",[@transaction_code]="Buy"),[@transaction_value],
 IF(OR([@transaction_code]="S",[@transaction_code]="Sell"),-[@transaction_value],0))

调整代码以匹配您的数据源。美国 Form 4 数据通常使用 P 表示购买，S 表示出售。其他数据集可能使用纯英文标签。

年-月桶

对于月度透视表，文本标签通常比 Excel 的自动日期分组更容易使用。

=TEXT([@transaction_date],"yyyy-mm")

如果您更喜欢用于排序和图表的真实月末日期：

=EOMONTH([@transaction_date],0)

申报滞后天数

这在操作和分析上都很有用。

=[@filing_date]-[@transaction_date]

格式化为数字。如果结果是乱码，则您的日期是文本格式。

公开市场标志

一个简单的二进制标志有助于分离数据集中通常包含最多信号的部分。

=IF(OR([@transaction_code]="P",[@transaction_code]="S",[@transaction_code]="Buy",[@transaction_code]="Sell"),1,0)

如果您的数据源包含这些字段，更精细的版本将排除计划交易或衍生品事件。

内部人标签

这不是必需的，但它能让数据透视表更易读。

=[@insider_name]&" ("&[@insider_role]&")"

步骤3：创建您的第一个数据透视表

从 tblInsider 插入一个数据透视表到 Pivots 工作表。

第一个数据透视表应该回答这个直接的问题：谁在买什么？

行

• issuer_name

列

• transaction_code

值

• transaction_value 的总和
• filing_id 的计数

筛选器

• transaction_date
• insider_role
• security_type
• market

这能立即告诉您一家公司最近的活动是由购买、出售还是行政事务主导。

真正有用的数据透视表秘诀

大多数内部人数据工作簿之所以变得无法使用，是因为它们试图一次性显示所有内容。最好有三个职责明确的数据透视表。

秘诀 1, 发行人热力图

这是您的分类视图。

行

• issuer_name

列

• year_month

值

• signed_value 之和

筛选器

• open_market_flag = 1
• market
• insider_role

它显示了什么: 一家公司在一段时间内是否存在净买入或净卖出的集中现象。

为何重要: 孤立的交易可能是噪音。多位内部人士重复的净买入则更难忽视。并非总能预测，但至少值得关注。

提升可读性

• 按最新月份的 signed_value 对行进行排序。
• 应用红到绿的条件格式。
• 将日期范围保持在较短时间，例如 6 到 12 个月。

如果透视表在此之后看起来像一棵圣诞树，则您的分类需要改进。

秘诀 2, 内部人士排行榜

此图表回答了谁最活跃。

行

• insider_label

值

• transaction_value 的总和
• filing_id 的计数

筛选器

• transaction_code
• transaction_date
• issuer_name

图表显示内容：哪些内部人正在进行最大规模的交易，以及交易活动是集中在一个人身上还是分散在几个人身上。

重要性：一位董事进行小额购买是一回事。而首席执行官、首席财务官和两位董事在同一个月内进行购买则是另一回事。

添加前10名筛选器

右键单击行标签，按交易价值总和应用值筛选器 > 前10名。这是创建可用观察名单最快捷的方法。

配方3, 申报及时性监控器

这是与合规性相关的透视表。

行

• issuer_name

值

• filing_lag_days 的平均值
• filing_lag_days 的最大值
• filing_id 的计数

筛选器

• 市场
• 年月

显示内容：交易日期后文件提交的速度。

重要性：如果您监控多个司法管辖区或数据供应商，滞后结构至关重要。迟到的信号仍然是信号，但已不再新鲜。

内部人交易工作簿的示例公式

数据透视表负责聚合。公式负责整理。两者缺一不可。

XLOOKUP 用于代码映射

假设您的原始数据在 transaction_code 列中包含交易代码，并且您在 Lookups 工作表中维护一个包含 code 和 bucket 列的映射表。

映射示例：

使用：

=XLOOKUP([@transaction_code],Lookups!A:A,Lookups!B:B,"Unmapped")

这为您提供了用于数据透视表的清晰 transaction_bucket 字段。

如果您看到 Unmapped，那很好。这意味着工作簿正在告诉您真相。

标记多名内部人购买同一发行人股票的情况

一个有用的筛选条件是，在同一个月内，是否有不止一名内部人购买了同一发行人的股票。

如果您有 issuer_name（发行人名称）、year_month（年月）、insider_name（内部人姓名）和购买标志，一个简单的计数可以提供帮助。在辅助列中：

=COUNTIFS(tblInsider[issuer_name],[@issuer_name],tblInsider[year_month],[@year_month],tblInsider[transaction_code],"P")

如果您的数据源使用标签而非代码，请相应调整。

一个更精细的版本是计算不同的内部人数量，现代 Excel 可以通过动态数组处理，尽管并非在所有表格设置中都能优雅地实现。在单独的工作表中，您可以使用：

=COUNTA(UNIQUE(FILTER(tblInsider[insider_name],(tblInsider[issuer_name]=A2)*(tblInsider[year_month]=B2)*(tblInsider[transaction_code]="P"))))

这看起来很复杂，但它确实有效。

每家发行人的最新申报

如果您想快速检查最新情况：

=MAXIFS(tblInsider[filing_date],tblInsider[issuer_name],[@issuer_name])

这对于列出每家发行人及其最新申报日期的覆盖范围表非常有用。

按发行人划分的净值

对于数据透视表之外的汇总表：

=SUMIFS(tblInsider[signed_value],tblInsider[issuer_name],A2)

其中 A2 包含发行人名称。

仅限买入的平均购买价格

=SUMIFS(tblInsider[transaction_value],tblInsider[issuer_name],A2,tblInsider[transaction_code],"P")/SUMIFS(tblInsider[shares],tblInsider[issuer_name],A2,tblInsider[transaction_code],"P")

如有需要，请用 IFERROR 包裹：

=IFERROR(SUMIFS(tblInsider[transaction_value],tblInsider[issuer_name],A2,tblInsider[transaction_code],"P")/SUMIFS(tblInsider[shares],tblInsider[issuer_name],A2,tblInsider[transaction_code],"P"),NA())

内部人净值环比变化

如果您有一个月度汇总表，其中发行人作为行，月份作为列，那么简单的减法即可。如果您更喜欢公式驱动的汇总：

=SUMIFS(tblInsider[signed_value],tblInsider[issuer_name],A2,tblInsider[year_month],B$1)-SUMIFS(tblInsider[signed_value],tblInsider[issuer_name],A2,tblInsider[year_month],TEXT(EOMONTH(DATEVALUE(B$1&"-01"),-1),"yyyy-mm"))

这种公式让人怀念 SQL。不过，它确实能完成任务。

监控内部人活动的实用工作流程

工作簿的重点不在于欣赏其架构，而在于决定哪些值得进一步关注。

每日或每周例行工作

一个合理的工作流程如下：

1. 下载最新的 CSV 文件。
2. 粘贴或刷新到 RawData 中。
3. 检查行数和日期格式。
4. 刷新所有数据透视表。
5. 审查发行人热力图，查找新的净买入。
6. 检查活动是否集中在多位内部人之间。
7. 排除非自由裁量或低信息量的交易类型。
8. 导出一个简短的观察列表，进行更深入的研究。

如果工作簿稳定，这可以在几分钟内完成。

什么算是有趣

确切的阈值取决于您的投资范围，但常见的活跃迹象包括：

• 同一发行人多次公开市场购买，
• 涉及高级管理人员而非仅非执行董事，
• 相对于薪资或先前持股而言，交易金额具有重要意义，
• 在数周或数月内重复购买，
• 以及及时申报，这降低了您查看过时信息的可能性。

以下情况不被视为有趣，至少不单独视为有趣：

• 单笔小额购买，
• 期权行权并立即出售以弥补成本，
• 赠与，
• 例行税务处置，
• 以及未经调整的混合证券汇总价值。

如果其他人将使用工作簿，请添加切片器

如果工作簿是供团队使用的，切片器可以使数据透视表不那么令人望而生畏。

有用的切片器：

• 市场
• 交易类别 (transaction_bucket)
• 内部人角色 (insider_role)
• 年月 (year_month)

它们并非必需。然而，它们是一种礼节。

关于监管的简短说明，因为数据受规则影响

内部人数据集并非自然对象。它们是披露制度的产物。如果您使用美国 Form 4 数据、欧洲 MAR 披露或英国 PDMR 公告，字段和时间安排反映的是法律义务，而非分析师的偏好。

这在 Excel 中很重要，因为您会倾向于比较不同类型的数据。

美国基线

在美国，受《证券交易法》第16节约束的内部人通常在交易发生后的两个工作日内，通过 Form 4 报告多项交易。美国证券交易委员会（SEC）的EDGAR系统提供这些申报文件，且交易编码惯例已成熟。

欧洲基线

在欧盟，市场滥用条例（尤其是第19条）规范管理人员的交易披露。法国金融市场管理局（AMF）等国家主管机构在当地监督该制度。其大体思路相似，但发布机制和源文件格式的变化程度超出许多用户的预期。

这在电子表格中为何重要

如果您要比较不同市场的申报延迟，或汇总来自多个司法管辖区的交易类型，您需要一个标准化的分类体系。否则，您的数据透视表将对一个错误的问题给出看似精确的答案。Excel 在此是无辜的，它只是忠实执行指令。

常见错误，以及如何在午餐前避免它们

这些错误是重复性的，这对编辑来说很方便，但对分析师来说则不那么方便。

错误1：将不同证券的原始股数直接相加

一万股普通股和一万份期权不是一回事。尽可能汇总 transaction_value 或标准化衡量指标，而不是混合证券类型的原始单位。

错误2：忽略直接与间接所有权

信托、配偶账户或受控实体可能很重要，但这与直接所有权不同。请将 ownership_nature 作为可用筛选条件。

错误3：将所有卖出交易都视为看跌信号

卖出比买入更具噪音。它们可能反映多元化、税收、流动性需求或预先安排的计划。数据透视表无法推断动机，它只能计数。

错误 4：未检查就让 Excel 自动分组日期

Excel 的自动分组很方便，直到它出错。如果您的日期以文本形式导入，分组将静默失败或产生奇怪的类别。构建您自己的 year_month 字段，您会睡得更安稳。

错误 5：忘记重复项

如果您随着时间推移追加新的 CSV 文件，重复的申报很常见。保留一个 filing_id 并使用以下方法检查重复项：

=COUNTIF(tblInsider[filing_id],[@filing_id])

任何大于 1 的值都值得检查。

错误 6：在没有货币背景的情况下信任数值

500,000 欧元和 500,000 美元的购买在粗略筛选时足够接近，直到它们不再接近。如果您的文件混合了货币，要么转换它们，要么将分析保持在本地。

最快速实用的仪表板

如果您想要一个分析师可以维护而不会反抗的最终设置，请制作一个包含四个对象的仪表板工作表：

1. 按发行人划分的内部人净值，当前月份
2. 按交易价值排名的顶级内部人
3. 每月净买入趋势
4. 平均申报滞后时间

添加市场、交易区间和月份的切片器。保持配色方案克制。内部人数据本身已经足够引人注目了。

简单的仪表板标题公式

如果单元格 B2 包含选定的发行人，您可以使用以下公式获取其最新净值：

=SUMIFS(tblInsider[signed_value],tblInsider[issuer_name],B2,tblInsider[year_month],TEXT(EOMONTH(TODAY(),0),"yyyy-mm"))

并使用以下公式获取最新申报日期：

=MAXIFS(tblInsider[filing_date],tblInsider[issuer_name],B2)

这使得仪表板具有一定的动态性，而无需任何比工作表公式更复杂的功能。

如果您还有60分钟

在五分钟版本奏效后，下一个合理的升级是使用 Power Query 进行可重复的导入和代码映射。这并非因为其华丽，而是因为永远复制粘贴CSV文件是流程风险悄然进入简单任务的方式。

尽管如此，五分钟版本足以将原始申报文件转化为值得阅读的精选名单。这才是重点。

回报很简单：一个整洁的Excel工作簿可以将内幕交易申报文件从档案转变为监控工具。具体的下一步是使用您自己的导出数据构建一个工作簿，然后在其上测试一个问题，例如，高级管理人员在过去30天内集中进行的公开市场购买是否能发现您当前流程遗漏的名称。如果能，您就拥有了一个工具。如果不能，开放的问题不是Excel失败了，而是您的交易分类法失败了。

---

编辑说明：本文在没有实时网络研究的情况下编写（Grok在此生成批次中不可用）。上述引用的是常青来源；数值声明取自我们截至2026-05-17的数据库快照。

最后审阅 · 2026-05-18 · 作者 Simon Azoulay · 来源：SEC EDGAR, AMF BDIF, 以及其他28家监管机构。