投资组合最优决策模型应用

投资组合最优决策模型应用 7一般地最优投资组合模型计算 1多种风险资产的最优投资组合 之间的关 以上分析 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 明，在投资组合的期望收益率Rp与投资组合标准差 系曲线上，存在一个最低风险(标准差)的投资组合(即最优投资组合)，该投资组合的各项资产投资比重矩阵的计算公式为: 而在此投资组合下的期望收益率和标准差分别为: 其中 表示n种风险资产的期望收益率，I=(1，1，，1)。 2无风险资产与多种风险资产的最优投资组合 当无风险资产与多种风险资产的构成投资组合时，首先可以计算出多种风险资产的最优投资组合，即资本配置线与风险资产的有效边界相切的那一点。切点所代表的最有投资组合的期望收益率Rp和标准差的计算公式分别为: 式中RF为无风险资产的收益率。参数A、B、C、D的计算同上。 在多种风险资产的最有投资组合中各种风险资产的投资比重为: **无风险资产与多种风险资产构成的最优投资组合,其风险和收益落在资本配置线(CAL)上, 计算公式为 为该直线的斜率. 3不允许卖空的最优投资组合的电子表格计算 在有些情形下，投资者把不进行卖空作为一种投资策略，因此，讨论在不允许卖空的约束下如何确定最小方差集合是必要的。这时在约束条件中需要加入xi ，i=1,…,n。相应的模型为 大于0 1 min(1 2XVX)， 这一模型不能被简化为一种线性方程式的求解问题。由于该模型的求解目标为二次的而限制条件为线性的(一次的)等式与不等式，因此，它称为二次规划，解决这类问题需要专门的计算机程序，对于中等规模的模型可以应用表格加以解决。在金融领域有许多专门设计的程序来解决由数百乃至千计所组成的模型。 两个模型的区别在于当允许卖空时，大部分(如果不是全部)最优的xi有非零值(或 正或负)，因此大体上所有资产都被使用。而当不允许卖空时，许多最优的xi值为零。 例:考虑前面的三项资产，但本例不允许卖空。在本例中模型不能被简化为一组方程式的形式，但考虑不同资产的两两组合，我们能得到有效边界。一般的解法如下所示。 表 组合收益与风险 投资组合优化，就是确定一组投资项目的最优投资比例。这里所说的“最优”，可以是指在一定期望投资回报水平下使得风险最小，或者是指在一定风险水平下使得投资回报最大，本章只讨论前者，后者放在第10章再讨论。在20世纪50年代，Harry Markowitz研究了一定期望投资回报水平下使得方差最小的最优投资比例问题，Harry Markowitz在该问题上取得的研究成果以及关于投资的其他研究成果，使他荣获1990年诺贝尔经济奖。下面通过例子说明投资组合优化问题的建模与VBA求解方法。 大部分投资者的目标是获得大的投资回报和承担小的投资风险。投资组合优化模型就是确定一组投资项目的最优投资比例(或者各项目的最优投资额)，在该投资组合的总回报率的方差不超过某个可接受的值的约束下(即在可接受的风险水平下)，使得总回报率的期望值最大(即投资回报最大);或者在投资组合的总回报率的期望值不低于某个所要求的值的约束下(即在所要求的投资回报水平下)，使得总回报率的方差最小(即投资风险最小)。由于总回报率的方差通常总 是投资比例的非线性函数，所以该规划是一个非线性规划。 例如，对于目标函数为风险最小的投资组合优化模型，由(4-2)式可得到投资总回报率R的方差估计量，又由(4-1)式可以得到投资总回报率R的期望值。该模型的形式如下: 2 2222 o.b, min R的方差 i j (4-3) s.t. R的期望值 (4-3)式中，R为投资组合的总回报率; ; x1,x2,...,xm第1至第m个项目的投资比例(决策变量) 2 2 2 m 第1至第m个项目的单项回报率的方差; 第1至第m个项目的单项回报率的标准方差; 为第i个投资项目与第j个投资项目的相关系数; 为第1至第m个项目的单项期望回报率; P为投资者所要求的回报率水平。 下面通过例2说明投资组合优化问题的建模与求解方法。 例2 投资组合优化问题 计算例1中对三个投资项目的最优投资比例，要求在总投资回报率不低于0.13的前提下，使得投资的风险最小。 解:这是以投资总风险最小为目标，以总回报率不低于要求值为约束条件的优化问题，该问题可以用(4-3)式建立非线性规划模型来求解。该问题的Spreadsheet如表4-5所示。其步骤如下: 第一步:输入已知数据 首先在Spreadsheet上输入已知数据。在A4:D23输入三个投资项目在各历史年份的回报率，以及所要求的总回报率期望值。 表4-5 已知数据表 3 4 第二步:计算各投资项目的单项回报率期望值、单项回报率方差，及各投资项目之间的相关系数。 根据前面介绍的Excel的公式，可计算出各投资项目的各个统计量。其计算方法与例1同，这里不再重复。计算公式见表4-6。 表4-6 投资组合优化模型中统计量的计算公式 第三步:建立非线性规划模型 本问题的决策变量是各投资项目的投资比例。用单元格B40:D40分别表示股票1、股票2和债券的投资比例。如表4-5所示。 本问题的目标函数是投资组合的风险最小，即投资组合总回报率的方差最小。由(4-2)式可知，投资组合总回报率R的方差的计算公式如下: 2222 R的方差 i j 本题中有三个投资项目，所以上式变为: R的方差= 2 2 2 2 2 2 222222 (4-4) 用单元格C48表示投资组合总回报率R的方差(即目标函数)。根据(4-3)式，在单元格C48中输入: =SUMPRODUCT(B41:D41,B27:D27)+2*B40*C40*C32*B28*C28+2*B40*D40* D32*B28*D28+2*C40*D40*D33*C28*D28 上式中，单元格B41:D41中的三个值分别为三个项目投资比例的平方;单元格B27:D27中的三个值分别为三个项目的单项回报率的方差;单元格B28:D28中的三个值分别为三个项目的单项回报率的标准方差;单元格C32、D32、D33中的值分别为股票1与股票2、股票1与债券、股票2与债券之间的相关系数。 如果我们建立了如下的协方差矩阵，则上述表达式可大大简化。 5 这时，投资组合总回报率R的方差(即目标函数)，可以用如下更简单的方法表示: =SUMPRODUCT(MMULT(B40:D40,B32:D34),B40:D40) 本问题有三个约束条件。第一个约束条件是投资组合总回报率的期望值约束。 -1)式可知，投资组合总回报率R的期望值的计算公式如下: 由(4 R的期望值 本题中有三个投资项目，所以上式变为: R的期望值(4-5) 用单元格B45表示实际投资组合总回报率R的期望值。根据上式，在单元格B45输入: =sumproduct (B26:D26,B40:D40) 上式中，单元格B26:D26中的三个值分别为三个项目的单项投资回报率期望值，单元格B40:D40中的值分别是三个项目的投资比例。(4-5)式计算得到的投资组合总回报率的期望值必须不小于要求达到的值，本题中要求达到的期望值为0.13，用单元格D45表示。 第二个约束条件是投资比例之和应等于1。用单元格 E40表示投资比例之和，可用下式计算: =sum(B40:D40) 它应等于1。 第三个约束是条件非负约束。 第四步:利用“规划求解”功能求出非线性规划的解。 在规划求解参数框中输入目标单元格(目标函数地址)、可变单元格(可变变 量地址)和约束条件。其规划求解参数框如图4-3所示。 图4-3 规划求解参数对话框 然后在规划求解参数框中选择“假定非负”(注意:本问题是非线性规划问题，所以不选择“采用线性模型”)，最后在规划求解参数对话框中单击“求解”得到本问题的解。 第五步:判断用“规划求解”功能求出是否是本问题的最优解。 本问题的模型如公式(4-3)所示: 6 2222o.b, min R的方差 (4-4) s.t. R的期望值 x1,x2,...,xm?0 本问题为最小化问题，其目标函数是总风险(总方差)最小。由公式(4-4)可知，总风险是决策变量的幂函数。 由凸函数的定义可知，幂函数Y=cx，a?1，c?0，x?0是凸函数。 可见公式4-4的目标函数是若干个凸函数之和，因此，本问题的目标函数是一个凸函数。此外，本问题的约束条件均为线性，所以本问题满足运用“规划求解”功能正确求解非线性规划问题的条件，所求得的解是整体最优解。 模型运行结果见表4-5。由该表可得本问题的最优解如下:股票1、股票2、债券的投资比例为0.5063:0.3243:0.1693。这时，投资组合的总回报率期望值达到所要求的0.13，而投资组合的总回报率的方差最小，为0.0151。 第六步:置信区间分析 当投资组合总回报率服从正态分布时，根据正态分析的统计理论，总回报率的置信区间如下。 (1)总回报率的值落在区间[总回报率期望值-总回报率标准方差，总回报率期望值+总回报率标准方差]的概率是68%; (2)总回报率的值落在区间[总回报率期望值-总回报率标准方差，总回报率期望值总回报率标准方差]的概率是95%; (3)总回报率的值落在区间[总回报率期望值-总回报率标准方差，总回报率期望值总回报率标准方差]的概率是99.7%。 本题中，总回报率期望值=0.13，总回报率的标准方差==0.0151=0.1228，所以当总回报率服从正态分布时，有:总回报率以68%的概率落在区间[0.0072，0.2528](即 [0.13-0.1228，0.13+0.1228]);以95%的概率落在区间[-0.1156，0.3756](即[0.13-2*0.1228，0.13+2*0.1228]);以99.7%的概率落在区间[-0.2384，0.4984](即 [0.13-3*0.1228，0.13+3*0.1228])。 上面的计算是对单元格的操作来实现的，这是很麻烦的，稍不小心，就很容易 出错，为避免出错，我们编制了一个VBA程序，来简化上述的操作。VBA程序 如下: Sub js() Range("b26") = Application.Average(Range("b4:b23")) Range("c26") = Application.Average(Range("c4:c23")) Range("d26") = Application.Average(Range("d4:d23")) Range("b27") = Application.Var(Range("b4:b23")) Range("c27") = Application.Var(Range("c4:c23")) Range("d27") = Application.Var(Range("d4:d23")) Range("b28") = Application.StDev(Range("b4:b23")) 7 a Range("c28") = Application.StDev(Range("c4:c23")) Range("d28") = Application.StDev(Range("d4:d23")) Range("b32") = 1 Range("c32") = Application.Correl(Range("b4:b23"), Range("c4:c23")) Range("d32") = Application.Correl(Range("b4:b23"), Range("d4:d23")) Range("b33") = Range("c32") Range("c33") = 1 Range("d33") = Application.Correl(Range("c4:c23"), Range("d4:d23")) Range("b34") = Range("d32") Range("c34") = Range("d33") Range("d34") = 1 Cells(40, 5) = "=SUM(B40:D40)" Cells(41, 2) = "=B40 " Cells(41, 3) = "=c40 " Cells(41, 4) = "=d40 " Cells(45, 2) = "=SUMPRODUCT(B26:D26,B40:D40)" Cells(48, 3) = "=SUMPRODUCT(B41:D41,B27:D27)+2*B40*C40*C32*B28*C28+2*B40*D40*D32*B28*D28+2*C40*D40*D33*C28*D28" Cells(50, 3) = "=SQRT(C48)" SolverReset SolverOk setcell:="$c$50", MaxminVal:=2, ValueOf:="0", byChange:="$b$40:$d$40" SolverAdd CellRef:="$e$40", Relation:=2, FormulaText:="100%" SolverAdd CellRef:="$b$45", Relation:=3, FormulaText:="13%" SolverSolve (True) End Sub 需要注意的是:在使用规划求解函数如SolverOk()、SolverAdd()等编制程序进行求解之前，首先必须建立对规划求解加载宏的引用，方法是:在Visual ]菜单中的[引用]命令，如图4-4所示，打开[引用Basic编辑器界面下，单击[工具 -VBAProject]对话框，如图4-5所示，然后选中[可使用的引用]列表框中的“SOLVER ”复选框。 -4 执行引用命令 图4 8 图4-5 [引用-VBAProject]对话框 如果“SOLVER ”未出现在[可使用的引用]列表框中，则需要单击[浏览]按钮，并打开“\Office\Library\ SOLVER”子文件夹中的“Solver.xla”。 4.5通用投资组合优化决策模型及其信息化实现 4.5.1最优投资组合的确定 考虑一个投资组合X，它由n个证券组成，每个证券的预期收益率为E(ri)，方差记为 ，证券之间的协方差记为，i、j=1，2，…，n。于是证券投资组合的收益率rX和风险可以表示成 在给定预期收益率E(rX)之下，如何选择证券组合的权重x1,...,xn，使证券组合X具有最 小方差呢, T记，为确定最小方差集合，我们考虑如下优化模型，即一 般的马柯维茨模型 ， 这是一个等式约束的极值问题， 9 在有些情形下，投资者把不进行卖空作为一种投资策略，因此，讨论在不允许卖空的约束下如何确定最小(或最优)方差集合是必要的。这时在约束条件中需要加入xi大于0，i=1,…,n。模型为 min(1 2XVX)， 这是二次规划模型。利用Kuhn-Tucker条件，可得类似结论。对于上面的二次规划模型，我们可以通过如下的VBA程序来进行求解。 4.5.2通用投资组合风险的最优化模型的VBA实现 (1)首先建立一个名字为“给定最低预期收益率的最优投资组合规划求解模型.xls”的工作簿，在当前的sheet1上设计模型的结构，如图4-6所示。 图4-6 给定最低预期收益率的最优投资组合规划求解模型 是否允许卖空的下拉列表框控件插入在单元格B5的位置，此下拉列表框控件的单元格链接为“$B$5”，数据区域为“$C$5:$C$6”，并在单元格C5和C6中分别输入“允许卖空”和“不允许卖空”，且此两个单元格的字体颜色设置为白色，以便使模型界面美观。 (2)模型由以下三部分组成: 已知数据区域、命令按钮区域(包括一个[准备数据]按钮、一个[开始计算]按钮、一个[清除表格]按钮)和计算结果区域(在单击[开始计算]按钮后计算结果的输出区域) (3)对[准备数据]按钮指定一个名字为“Sub 准备数据()”的宏，并编写如下的程序代码: Sub 准备数据() Dim n, i, j As Integer n = Cells(3, 2) Cells(10, 1) = "输入各个证券的预期收益率" Cells(10, 1).HorizontalAlignment = xlCenter Range(Cells(10, 1), Cells(10, 1 + n)).Select Selection.Merge With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous 10 .Weight = xlMedium End With For i = 1 To n Cells(11, i + 1) = "证券" & i Cells(11, i + 1).HorizontalAlignment = xlCenter Next i Cells(12, 1) = "预期收益率" Range(Cells(12, 1), Cells(13, 1)).HorizontalAlignment = xlCenter Range(Cells(12, 1), Cells(12, 1 + n)).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With Cells(14, 1) = "输入各个证券间的协方差矩阵" Cells(14, 1).HorizontalAlignment = xlCenter Range(Cells(14, 1), Cells(14, 1 + n)).Select Selection.Merge With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With For i = 1 To n Cells(15, i + 1) = "证券" & i Cells(15, i + 1).HorizontalAlignment = xlCenter For j = 1 To n Cells(15 + j, 1) = "证券" & j Cells(15 + j, 1).HorizontalAlignment = xlCenter Next j Next i For i = 1 To n For j = i To n Cells(15 + i, 1 + j).Select With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin End With 11 With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin End With Next j Next i Range(Cells(15 + n, 1), Cells(15 + n, 1 + n)).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium End With End Sub (4)对[开始计算]按钮指定一个名字为“Sub 开始计算()”的宏，并编写如下的程序代码: Sub 开始计算() Dim n, i, j As Integer Dim myrange1, myrange2, myrange3 As String Dim x1, x2, x3 As String n = Cells(3, 2) For i = 1 To n For j = 1 To n Cells(15 + j, 1 + i) = Cells(15 + i, 1 + j) Next j Next i If Cells(5, 2) = 1 Then Cells(17 + n, 1) = "优化计算结果——允许卖空" Else Cells(17 + n, 1) = "优化计算结果——不允许卖空" End If For i = 1 To n Cells(18 + n, i + 1) = "证券" & i Next i Cells(18 + n, n + 2) = "合计" Cells(19 + n, 1) = "比重(%)" Cells(20 + n, 1) = "预期收益率" Cells(21 + n, 1) = "标准差" 12 myrange1 = "b" & 12 & ":" & Chr(65 + n) & 12 „各个证券收益率数据区域 myrange2 = "b16" & ":" & Chr(65 + n) & 15 + n „协方差矩阵数据区域 myrange3 = "b" & 19 + n & ":" & Chr(65 + n) & 19 + n „投资比例结算结果数据区域 Cells(20 + n, 2) = "=sumproduct(" & myrange1 & "," & myrange3 & ")" Cells(21 + n, 2) = "=sqrt(sumproduct(" & myrange3 & ",mmult(" & myrange3 & "," & myrange2 & ")))" Cells(19 + n, n + 2) = "=sum(" & myrange3 & ")" x1 = Chr(66 + n) & 19 + n „投资组合比重合计率数据区域 x2 = "b" & 21 + n „投资组合标准差数据区域 x3 = "b" & 20 + n „投资组合预期收益率数据区域 Range(myrange3).NumberFormat = "0.00%" Range(x1).NumberFormat = "0.00%" Range(x2).NumberFormat = "0.00%" Range(x3).NumberFormat = "0.00%" „开始利用规划求解工具计算 SolverReset SolverOk setcell:=x2, MaxminVal:=2, ValueOf:="0", byChange:=myrange3 SolverAdd CellRef:=x1, Relation:=2, FormulaText:="100%" SolverAdd CellRef:=x3, Relation:=3, FormulaText:="$b$7" If Cells(5, 2) = 2 Then SolverAdd CellRef:=myrange3, Relation:=3, FormulaText:="0" End If SolverSolve (True) End Sub (4)对[清除表格]按钮指定一个名字为“Sub 清除表格()”的宏，并编写如下的 程序代码: Sub 清除表格() Cells(3, 2) = "" Rows("10:10000").Delete Shift:=xlUp End Sub 4.5.3通用投资组合风险的最优化模型的应用举例 例3:某4个证券的投资组合，各个证券的预期收益率、标准差和证券之间的协 -7和4-8所示，要求的最低期望收益率为15%，试计算不允许卖方差矩阵如表4 空情况下的最优投资组合。 13 表4-8 各证券间的协方差矩阵 (1)在单元格B3中输入证券数量“4”。 (2)单击[准备数据]按钮，对模型自动对工作表进行 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 化，格式化后的工作表如图4-7所示。 图4-7 格式化后的工作表 (3)按照格式化后的工作表输入表4-7和4-8所示的有关数据。由于协方差矩阵的对成性，只需要在被模型为浅黄色的单元格中输入协方差数据即可。 (4)单击是否允许卖空下拉列表框的下拉三角按钮，选择不允许卖空，单击[开始计算]按钮，则模型自动计算最优投资比例结果，分别如图4-8所示。 图4-8 最优投资比例计算结果 14 4.6通用投资组合优化决策信息系统及其VBA实现 下面以最优投资组合模型为例，来说明投资组合优化决策信息系统的实现方法。 4.6.1设计自定义菜单 为了方便用户使用应用信息系统程序，可以在Excel系统菜单上增加自定义菜单，此自定义菜单有3个命令，分别为“准备数据”、“优化计算”和“删除数据”，并把增加自定义菜单的宏设计为自动宏。 建立一个名为“最优投资组合决策信息系统.xls”的工作簿，在Visual Basic编辑器窗口中，单击[插入]菜单中的[模块]命令，插入一个“模块1”，在此模块中编写如下的自动宏代码: Sub auto_open() MenuBars(xlWorksheet).Menus.Add "最优投资组合决策信息系统" MenuBars(xlWorksheet).Menus("最优投资组合决策信息系统").MenuItems.Add "准备数据", "准备" MenuBars(xlWorksheet).Menus("最优投资组合决策信息系统").MenuItems.Add "优化计算", "计算" MenuBars(xlWorksheet).Menus("最优投资组合决策信息系统").MenuItems.Add "删除数据", "删除" End Sub Sub auto_close() MenuBars(xlWorksheet).Reset MenuBars(xlWorksheet).Activate End Sub 这样，在每次打开工作簿“最优投资组合决策信息系统.xls”时，就自动在Excel系统菜单上加载[最优投资组合]自定义菜单，如图4-9所示。而关闭此工作簿时，该自定义菜单自动卸载。 图4-9 最优投资组合决策信息系统的自定义菜单 4.6.2设计基本数据输入窗体 由于证券最优投资组合的类型一般分为三大类型，即: (1)直接求最低风险下的最优投资组合，此时的目标函数是风险(标准差或方差)最小; (2)给定最低预期收益下的最低风险的最优投资组合，此时的目标函数是风险(标准差或方差)最小;但有一个最低预期收益率的约束条件; (3)给定最高风险下的最大预期收益率的最优投资组合，此时的目标函数是预期收益率最大，但有一个最高风险的约束条件。 因此，需要设计一个输入已知数据(包括投资组合的证券数量、最低预期收益率或最高风险、是否允许卖空)的窗体。 15 在Visual Basic编辑器窗口下，单击[插入]菜单中的[用户窗体]命令，插入一个用户窗体UserForm1，将其Caption属性设置为“最优投资组合决策(输入基本数据)”，如图4-10所示。 图4-10 插入用户窗体 窗体的设计步骤如下: (1)在此窗体上插入3个框架Frame1~Frame3，各框架说明如下: ?框架Frame1的 Caption属性设置为“投资组合的证券数量”，在此内插入1个标签Label1，其Caption属性设置为“证券数量”，插入1个文本框TextBox1，其Value属性设置为空值。 ?框架Frame2的Caption属性设置为“是否允许卖空”，在此内插入2个单选按钮OptionButton1和OptionButton2，其Caption属性分别设置为“允许卖空”和“不允许卖空”，其单选按钮OptionButton1的Value属性设置为True。 ?框架Frame3的Caption属性设置为“优化模型选择”，在此内插入3个单选按钮OptionButton3、OptionButton4和OptionButton5，其Caption属性分别设置为“最低风险”、“给定最低收益率”和“给定最高风险(标准差)”，其中单选按钮OptionButton3的Value属性设置为True。在此框架内插入两个文本框TextBox2和TextBox3，其Value属性设置为空值。框架Frame3的5个控件OptionButton3、OptionButton4、OptionButton5、TextBox2和TextBox3的TabIndex属性分别设置为0、1、2、3、4和5，以便控制光标的依次移动。 (2)在用户窗体上插入2个命令按钮CommandButton1和CommandButton2，它们的Caption属性分别设置为“确定”、“取消”，其中命令按钮CommandButton2的Cancel属性设置为True。 4.6.3基本数据输入窗体的程序代码设计 (1)对窗体上的“确定”、 “取消”按钮分别编写如下的程序代码: Private Sub CommandButton1_Click() Dim n, i, j, r, c As Double Dim mystr1, mystr2 As String n = Val(TextBox1.Value) r = Val(TextBox2.Value) c = Val(TextBox3.Value) 16 If OptionButton1.Value = True Then mystr1 = OptionButton1.Caption ElseIf OptionButton2.Value = True Then mystr1 = OptionButton2.Caption End If If OptionButton3.Value = True Then mystr2 = OptionButton3.Caption ElseIf OptionButton4.Value = True Then mystr2 = OptionButton4.Caption ElseIf OptionButton5.Value = True Then mystr2 = OptionButton5.Caption End If Cells(2, 1) = "最优投资组合——" & mystr2 Cells(4, 1) = "证券数量" Cells(4, 2) = n Cells(5, 1) = mystr2 If OptionButton3.Value = True Then Cells(5, 2) = "" ElseIf OptionButton4.Value = True Then Cells(5, 2) = r ElseIf OptionButton5.Value = True Then Cells(5, 2) = c End If Cells(5, 2).NumberFormat = "0.00%" Cells(6, 1) = "是否允许卖空" Cells(6, 2) = mystr1 Cells(10, 1) = "输入各个证券的预期收益率" Range(Cells(10, 1), Cells(10, 1 + n)).Select Selection.Merge For i = 1 To n Cells(11, i + 1) = "证券" & i Next i Cells(12, 1) = "预期收益率" Cells(14, 1) = "输入各个证券之间的协方差矩阵" Range(Cells(14, 1), Cells(14, 1 + n)).Select Selection.Merge For i = 1 To n Cells(15, i + 1) = "证券" & i Cells(15, 1 + i).HorizontalAlignment = xlCenter For j = 1 To n Cells(15 + j, 1) = "证券" & j Cells(15, 1 + i).HorizontalAlignment = xlCenter Next j Next i 17 For i = 1 To n Cells(15, i + 1) = "证券" & i Cells(15, 1 + i).HorizontalAlignment = xlCenter For j = i To n Cells(15 + i, 1 + j).Select With Selection.Interior .ColorIndex = 36 .Pattern = xlSolid End With With Selection.Borders(xlEdgeLeft) .LineStyle = xlContinuous .Weight = xlThin End With With Selection.Borders(xlEdgeTop) .LineStyle = xlContinuous .Weight = xlThin End With With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin End With With Selection.Borders(xlEdgeRight) .LineStyle = xlContinuous .Weight = xlThin End With Cells(15, 1 + i).HorizontalAlignment = xlCenter Next j Next i Range(Cells(15 + n, 1), Cells(15 + n, 1 + n)).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin End With End End Sub Private Sub CommandButton2_Click() End End Sub (2)为5个单选按钮编写如下的程序代码: Private Sub OptionButton1_Enter() OptionButton1.Value = True OptionButton2.Value = False End Sub 18 Private Sub OptionButton2_Enter() OptionButton2.Value = True OptionButton1.Value = False End Sub Private Sub OptionButton3_Enter() OptionButton3.Value = True OptionButton4.Value = False OptionButton5.Value = False TextBox2.Enabled = False TextBox3.Enabled = False End Sub Private Sub OptionButton4_Enter() OptionButton4.Value = True OptionButton3.Value = False OptionButton5.Value = False TextBox2.Enabled = True TextBox3.Enabled = False End Sub Private Sub OptionButton5_Enter() OptionButton5.Value = True OptionButton3.Value = False OptionButton4.Value = False TextBox2.Enabled = False TextBox3.Enabled = True End Sub 4.6.4为[最优投资组合]自定义菜单指定宏 为自定义菜单中的[准备数据]命令指定一个名字为“准备”的宏，为[优化计算]命令指定一个名字为“计算”的宏，为[删除数据]命令指定一个名字为“删除”的宏，它们均保存在“模块2”中，宏代码分别如下所示: Public Sub 准备() UserForm1.Show End Sub Public Sub 计算() Dim n, i, j As Integer Dim myrange1, myrange2, myrange3 As String Dim x1, x2, x3, x4 As String n = Cells(4, 2) For i = 1 To n For j = 1 To n Cells(15 + j, 1 + i) = Cells(15 + i, 1 + j) Next j Next i 19 Cells(17 + n, 1) = "优化计算结果" Range(Cells(17 + n, 1), Cells(17 + n, 2 + n)).Select Selection.Merge Selection.HorizontalAlignment = xlCenter With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With For i = 1 To n Cells(18 + n, i + 1) = "证券" & i Cells(18 + n, i + 1).HorizontalAlignment = xlCenter Next i Cells(18 + n, n + 2) = "合计" Cells(18 + n, i + 1).HorizontalAlignment = xlCenter Cells(19 + n, 1) = "比重(%)" Range(Cells(19 + n, 1), Cells(19 + n, 2 + n)).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlThin .ColorIndex = xlAutomatic End With Cells(20 + n, 1) = "预期收益率(%)" Cells(21 + n, 1) = "标准差(%)" Range(Cells(19 + n, 1), Cells(21 + n, 1)).Select Selection.HorizontalAlignment = xlCenter Range(Cells(21 + n, 1), Cells(21 + n, 2 + n)).Select With Selection.Borders(xlEdgeBottom) .LineStyle = xlContinuous .Weight = xlMedium .ColorIndex = xlAutomatic End With myrange1 = "B" & 12 & ":" & Chr(65 + n) & 12 „各个证券收益率数据区域 myrange2 = "B16" & ":" & Chr(65 + n) & 15 + n „协方差矩阵数据区域 myrange3 = "B" & 19 + n & ":" & Chr(65 + n) & 19 + n „投资比例计算结果数据区域 Cells(20 + n, 2) = "=sumproduct(" & myrange1 & "," & myrange3 & ")" Cells(21 + n, 2) = "=sqrt(sumproduct(" & myrange3 & ",mmult(" & myrange3 & "," & myrange2 & ")))" Cells(19 + n, n + 2) = "=sum(" & myrange3 & ")" x1 = Chr(66 + n) & 19 + n „投资组合比重合计率数据区域 x2 = "B" & 21 + n „投资组合标准差数据区域 x3 = "B" & 20 + n „投资组合预期收益率数据区域 x4 = "B5" 20 Range(myrange3).NumberFormat = "0.00%" Range(x1).NumberFormat = "0.00%" Range(x2).NumberFormat = "0.00%" Range(x3).NumberFormat = "0.00%" Cells(20 + n, 2).NumberFormat = "0.00%" „开始利用规划求解工具计算 If Cells(5, 1) = UserForm1.OptionButton3.Caption Then SolverReset SolverOk Setcell:=x2, MaxMinVal:=2, ValueOf:="0", byChange:=myrange3 SolverAdd CellRef:=x1, Relation:=2, FormulaText:="100%" SolverOk Setcell:=x2, MaxMinVal:=2, ValueOf:="0", byChange:=myrange3 If Cells(6, 2) = UserForm1.OptionButton2.Caption Then SolverAdd CellRef:=myrange3, Relation:=3, FormulaText:="0" „不允 许卖空 End If ElseIf Cells(5, 1) = UserForm1.OptionButton4.Caption Then SolverReset SolverOk Setcell:=x2, MaxMinVal:=2, ValueOf:="0", byChange:=myrange3 SolverAdd CellRef:=x1, Relation:=2, FormulaText:="100%" SolverAdd CellRef:=x3, Relation:=3, FormulaText:=x4 If Cells(6, 2) = UserForm1.OptionButton2.Caption Then SolverAdd CellRef:=myrange3, Relation:=3, FormulaText:="0" End If SolverOk Setcell:=x2, MaxMinVal:=2, ValueOf:="0", byChange:=myrange3 ElseIf Cells(5, 1) = UserForm1.OptionButton5.Caption Then SolverReset SolverOk Setcell:=x3, MaxMinVal:=1, ValueOf:="0", byChange:=myrange3 SolverAdd CellRef:=x1, Relation:=2, FormulaText:="100%" SolverAdd CellRef:=x2, Relation:=1, FormulaText:=x4 If Cells(6, 2) = UserForm1.OptionButton2.Caption Then SolverAdd CellRef:=myrange3, Relation:=3, FormulaText:="0" End If SolverOk Setcell:=x3, MaxMinVal:=1, ValueOf:="0", byChange:=myrange3 End If SolverSolve (True) Range(myrange3).NumberFormat = "0.00%" End Sub Public Sub 删除() Rows("1:10000").Select Selection.Delete Shift:=xlUp End Sub 这样，最优投资组合决策信息系统就就建立起来了。用户只要打开工作簿“最优投资组合决策信息系统.xls”，单击“最优投资组合决策信息系统”菜单中的有关命令，即可进行证券最优投资组合的计算。 21 4.6.5最优投资组合决策信息系统应用举例 例4:某4个证券的投资组合，各个证券的预期收益率、标准差和证券之间的协方差矩阵如表4-9和4-10所示，要求的最低期望收益率为15%，试计算不允许卖空情况下的最优投资组合。 表4-9 各证券的预期收益率 表4-10 各证券间的协方差矩阵 (1)打开工作簿“最优投资组合决策信息系统.xls”。 (2)单击“最优投资组合决策信息系统”菜单中的[准备数据]命令，打开[最优投资组合决策(输入基本数据)]对话框，如图4-11所示。 图4-11 [最优投资组合决策(输入基本数据)]对话框 (3)在[最优投资组合决策(输入基本数据)]对话框中输入有关数据，然后单击[确定]按钮,则模型自动对工作表进行格式化，并返回工作表，等待用户输入各 个证券的预期收益率以及它们之间的协方差，如图4-12所示。 22 图4-12格式化后的工作表 (4)在格式化后的工作表中，输入各个证券的预期收益率以及它们之间的协方差数据，如图4-13所示。 图4-13 输入已知数据 (4)单击[最优投资组合决策信息系统]菜单中的[优化计算]菜单，则模型自动进行优化计算，并输出计算结果，如图4-14所示。 图4-14 优化计算结果 见excel文件 23