Excel自动实现Markowitz均值-方差优化的VBA代码故障求助

核心需求回顾 #

• 基于滚动24个月窗口执行Mean-Variance优化，最大化夏普比率
• 自动计算每月最优权重、组合收益、标准差并存储
• 替代手动4000次Solver操作，避免人为错误

修正后的VBA代码 #

Sub AutoMarkowitzOptimization()
    Dim ws As Worksheet
    Dim lastRow As Long, startRow As Long, currentRow As Long
    Dim targetCell As Range, changingCells As Range
    Dim retRange As Range
    
    ' 指定目标工作表，替换为你的实际表名
    Set ws = ThisWorkbook.Worksheets("Data")
    ' 获取数据最后一行（假设日期标识在A列）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 提前重置Solver，避免残留参数干扰
    On Error Resume Next
    SolverReset
    On Error GoTo 0
    
    ' 关闭屏幕刷新提升运行速度
    Application.ScreenUpdating = False
    
    ' 循环处理每个滚动窗口（从第24行开始，覆盖所有完整窗口）
    For currentRow = 24 To lastRow
        startRow = currentRow - 23 ' 计算当前窗口的起始行
        
        ' 定义当前窗口的收益数据范围（示例：B-D列是资产收益，根据实际调整）
        Set retRange = ws.Range(ws.Cells(startRow, "B"), ws.Cells(currentRow, "D"))
        ' 目标单元格：当前行的夏普比率计算单元格（示例：F列）
        Set targetCell = ws.Cells(currentRow, "F")
        ' 可变单元格：当前行的资产权重单元格（示例：G-I列）
        Set changingCells = ws.Range(ws.Cells(currentRow, "G"), ws.Cells(currentRow, "I"))
        
        ' 重置Solver参数
        SolverReset
        
        ' 设置Solver核心参数：最大化夏普比率
        SolverOk SetCell:=targetCell.Address, _
            MaxMinVal:=1, _
            ValueOf:=0, _
            ByChange:=changingCells.Address, _
            Engine:=1, _
            EngineDesc:="GRG Nonlinear"
        
        ' 添加约束：权重总和为1
        SolverAdd CellRef:=changingCells.Address, _
            Relation:=2, _
            FormulaText:="1"
        
        ' 添加约束：权重非负（若允许做空可删除此条）
        SolverAdd CellRef:=changingCells.Address, _
            Relation:=3, _
            FormulaText:="0"
        
        ' 执行Solver并自动保存结果
        SolverSolve UserFinish:=True
        
        ' 计算并存储组合收益（直接用当前权重乘最新一期收益求和）
        ws.Cells(currentRow, "H").Value = WorksheetFunction.SumProduct(changingCells, retRange.Rows(retRange.Rows.Count))
        ' 计算并存储组合标准差（需确保协方差矩阵已基于当前窗口计算完成）
        ws.Cells(currentRow, "I").Value = ws.Cells(currentRow, "E").Value ' 替换为你的标准差公式单元格
    Next currentRow
    
    ' 恢复屏幕刷新
    Application.ScreenUpdating = True
    MsgBox "滚动窗口优化全部完成！"
End Sub

关键调试与适配要点 #

• Offset引用修正：避免在Solver参数中直接嵌套Offset，提前用变量定义动态范围（如retRange、changingCells），确保引用逻辑清晰无歧义
• Solver引用启用：必须在VBA编辑器的「工具→引用」中勾选Solver，否则代码会触发运行时错误
• 表格结构适配：代码中列号（B/D/F/G等）、窗口起始逻辑需完全匹配你的Excel数据布局，比如资产收益列、权重存储列的位置
• 约束调整：根据策略需求修改约束条件，比如允许做空则删除权重非负约束，或添加单资产仓位上限
• 性能优化：循环前关闭ScreenUpdating可大幅提升运行速度，处理181行数据的耗时会显著降低

内容的提问来源于stack exchange，提问作者Nillie