投资组合跟踪就是你的投资记账本——知道钱从哪来到哪去。收益归因分析告诉你到底是选股能力强还是择时运气好。没有记录就没有发生从错误中学习比从成功中学习更重要。一、投资组合跟踪表的核心设计1.1 为什么需要跟踪表常见问题“我买了什么股票来着”“这只股票赚了还是亏了”“哪个板块贡献最多收益”跟踪表的价值清晰掌握持仓状况实时监控盈亏分析投资行为生成定期报告1.2 核心字段设计持仓表字段字段说明数据来源资产代码唯一标识手动输入资产名称显示名称自动填充资产类型股票/基金/债券手动选择持有数量当前持仓手动输入成本单价买入均价自动计算当前价格最新市价自动获取市值数量×当前价自动计算成本数量×成本价自动计算盈亏市值-成本自动计算收益率盈亏/成本自动计算权重市值/总市值自动计算1.3 Excel公式实现// 市值H列 E2*G2 // 数量 × 当前价格 // 成本I列 E2*F2 // 数量 × 成本单价 // 盈亏J列 H2-I2 // 收益率K列 J2/I2 // 权重L列 H2/SUM($H$2:$H$100)二、实时市值更新的自动化方案2.1 方案1Power Query自动刷新步骤用Power Query从网页获取实时价格与持仓表关联设置自动刷新每X分钟M语言示例let // 从API获取价格 价格数据 Json.Document(Web.Contents(https://api.example.com/prices)), 转换为表 Table.FromRecords(价格数据), 合并到持仓 Table.NestedJoin(持仓表, {资产代码}, 转换为表, {code}, 价格, JoinKind.LeftOuter), 展开价格 Table.ExpandTableColumn(合并到持仓, 价格, {price}, {当前价格}) in 展开价格2.2 方案2VBA定时更新Sub 更新价格() Dim ws As Worksheet Dim 最后一行 As Long Dim i As Long Set ws ThisWorkbook.Sheets(持仓) 最后一行 ws.Cells(ws.Rows.Count, A).End(xlUp).Row For i 2 To 最后一行 调用API或从其他sheet获取价格 ws.Cells(i, G).Value 获取价格(ws.Cells(i, A).Value) Next i 刷新计算 Application.Calculate MsgBox 价格更新完成 End Sub Sub 设置定时更新() 每5分钟更新一次 Application.OnTime Now TimeValue(00:05:00), 更新价格 End Sub2.3 方案3手动更新按钮最简单的方案创建一个刷新价格按钮点击后手动更新价格适合不频繁查看的用户三、收益归因分析3.1 什么是收益归因收益归因Return Attribution分析收益的来源资产配置贡献个股选择贡献择时贡献行业配置贡献3.2 Brinson模型简化版Brinson模型将超额收益分解为资产配置效应因超配/低配某类资产带来的收益个股选择效应因选择具体标的带来的收益交互效应两者的交互作用Excel实现假设资产类型组合权重组合收益基准权重基准收益股票60%15%50%12%债券30%5%40%4%现金10%2%10%2%计算// 资产配置效应 (组合权重-基准权重)*基准收益 // 个股选择效应 基准权重*(组合收益-基准收益) // 交互效应 (组合权重-基准权重)*(组合收益-基准收益)3.3 个股贡献分析计算每只股票的贡献// 个股贡献 权重 × 收益率 L2*K2按维度汇总按行业汇总按资产类型汇总按时间维度汇总四、定期报告生成4.1 月度报告模板报告内容投资组合月度报告 报告期间2024年1月1日 - 2024年1月31日 一、组合概览 - 期初市值XXX万元 - 期末市值XXX万元 - 本月收益XXX万元X.X% - 累计收益XXX万元X.X% 二、持仓分析 - 股票占比XX% - 基金占比XX% - 债券占比XX% 三、收益归因 - 资产配置贡献X.X% - 个股选择贡献X.X% - 择时贡献X.X% 四、主要贡献/拖累 - 最大贡献XXXX.X% - 最大拖累XXX-X.X% 五、下月计划 - 调仓计划 - 关注标的4.2 VBA自动生成报告Sub 生成月度报告() Dim 报告Ws As Worksheet Dim 持仓Ws As Worksheet Set 持仓Ws ThisWorkbook.Sheets(持仓) Set 报告Ws ThisWorkbook.Sheets.Add 报告Ws.Name 月度报告_ Format(Date, yyyymm) 写入报告标题 报告Ws.Range(A1).Value 投资组合月度报告 报告Ws.Range(A2).Value 报告期间 Format(DateAdd(m, -1, Date), yyyy年m月) 写入组合概览 报告Ws.Range(A4).Value 一、组合概览 报告Ws.Range(A5).Value 期末市值 Format(SumRange(持仓Ws.Range(H:H)), #,##0.00) 万元 ... 更多内容 格式化 报告Ws.Range(A1).Font.Size 16 报告Ws.Range(A1).Font.Bold True MsgBox 月度报告生成完成 End Sub4.3 导出PDFSub 导出PDF() ActiveSheet.ExportAsFixedFormat _ Type:xlTypePDF, _ Filename:C:/投资报告/月度报告_ Format(Date, yyyymm) .pdf MsgBox PDF导出完成 End Sub五、数据透视表在交易分析中的应用5.1 按维度分析创建数据透视表选中持仓数据【插入】→【数据透视表】选择分析维度常用分析维度行标签值分析目的资产类型市值、盈亏大类资产配置行业市值、盈亏行业分布买入日期按月盈亏择时能力5.2 切片器动态筛选添加切片器按资产类型筛选按盈亏状态筛选盈利/亏损按时间范围筛选5.3 计算字段添加自定义计算// 盈亏占比 盈亏 / 总盈亏 // 目标权重偏差 当前权重 - 目标权重六、实战案例完整的投资组合跟踪系统6.1 系统架构┌─────────────────────────────────────────┐ │ 数据输入层 │ │ 交易记录表、持仓表、价格数据 │ ├─────────────────────────────────────────┤ │ 计算处理层 │ │ 成本计算、盈亏计算、归因分析 │ ├─────────────────────────────────────────┤ │ 展示分析层 │ │ 数据透视表、图表、仪表盘 │ ├─────────────────────────────────────────┤ │ 报告输出层 │ │ 日报、周报、月报 │ └─────────────────────────────────────────┘6.2 交易记录表日期代码名称方向价格数量金额手续费备注2024/1/5600519茅台买入1700100170000170自动更新持仓用SUMIFS计算当前持仓// 当前数量 SUMIFS(交易记录[数量], 交易记录[代码], A2, 交易记录[方向], 买入) -SUMIFS(交易记录[数量], 交易记录[代码], A2, 交易记录[方向], 卖出) // 成本单价加权平均 SUMIFS(交易记录[金额], 交易记录[代码], A2, 交易记录[方向], 买入) / SUMIFS(交易记录[数量], 交易记录[代码], A2, 交易记录[方向], 买入)6.3 持仓总览表自动汇总显示总资产总盈亏收益率各资产类型占比七、总结与行动清单7.1 核心要点模块功能关键指标持仓跟踪实时监控市值、盈亏、收益率收益归因分析来源配置贡献、选择贡献定期报告总结回顾月度/季度表现交易分析行为复盘胜率、盈亏比7.2 下一步行动今天就做创建交易记录表记录最近5笔交易本周完成搭建持仓跟踪表自动计算盈亏本月目标生成第一份月度投资报告7.3 一个提醒跟踪是为了改进不是为了自责。分析亏损原因避免重复犯错总结盈利经验提炼可复制的模式保持客观不要让情绪影响记录建议每次交易后当天记录每周回顾一次持仓每月生成正式报告投资是一场马拉松详细的记录是你进步的阶梯。标签投资组合 | 持仓跟踪 | 收益归因 | Brinson模型 | 投资报告 | 盈亏统计 | 数据透视表字数约3000字推荐阅读上一篇《技术指标大全——RSI、KDJ、布林带的Excel实现》下一篇《风险评估与管理——VaR与压力测试Excel实现》
