投资组合数字的特征计算.pdf

《投资组合数字的特征计算.pdf》由会员分享，可在线阅读，更多相关《投资组合数字的特征计算.pdf（28页珍藏版）》请在三一文库上搜索。

1、实用标准文案 精彩文档 实验一：投资组合数字特征的计算 一、实验目的 通过上机实验，使学生充分理解Excel 软件系统管理和基本原 理, 掌握投资组合数字特征的Excel 计算。 二、预备知识 （一）相关的计算机知识：Windows 操作系统的常用操作；数据库的基础 知识； Excel 软件的基本操作。 （二）投资组合数字特征的理论预备知识 假设投资者投资 N个风险资产，对该组合作以下定义。 用 i w表示投资在资产i 上的比例，则投资组合W的矩阵形式为： 12 (,) T N Ww ww投资组合W 的期望收益和方差的矩阵形式为： 1 ()( )( ) () N T wii i T w E r

2、wE rW E r Var rW SW 式中 E(r) 表示各资产期望收益率组成的列矩阵 （列向量），S是 N个风险资产的方差 -协方差矩阵。任意两个投资组合之间的协 方差的矩阵形式为： 1212 (,) T Cov W WW SW 三、实验内容 利用 Excel 计算投资组合的期望、标准差和协方差，并绘制投资组合的标 准差- 期望收益曲线。 四、实验步骤 本实验通过一个具体的实例展开。已知：3 家上市公司 2001 年 12 月至 2003 年 12 月共 25 期的股票月末收盘价格。试运用EXCEL 计算下列问题： （1）计算各只股票的月 / 年收益率、方差、标准差。 （2）计算三只股票的

3、方差 - 协方差矩阵、相关系数。 （3）构造两个投资组合，其中：组合1 =（0.2 0.4 0.4 ）, 组合 2 =（0.5 0.3 0.2）, 计算各自的方差，两组合之间的协方差、相关 系数。 （4）绘制三家公司股票的标准差- 期望收益曲线。 首先新建一个 EXCEL 工作表，在 B3：D27区域中输入 3 个公司股票的价格。 实用标准文案 精彩文档 A B C D 1 股票价格 2 日期公司 A 公司 B 公司 C 3 2001 年 12 月15.7954 25.8483 23.3923 4 2002 年 1 月18.1096 27.1296 24.0336 5 2002 年 2 月17

4、.2228 26.2177 23.5039 6 2002 年 3 月16.3931 24.3938 22.4581 7 2002 年 4 月15.5634 26.0828 21.3913 8 2002 年 5 月16.2599 25.8535 22.3458 9 2002 年 6 月16.8354 24.4777 21.1025 10 2002 年 7 月18.0153 25.7313 22.7998 11 2002 年 8 月19.4385 28.0968 22.5169 12 2002 年 9 月19.6121 27.5198 21.9434 13 2002 年 10 月19.8209 2

5、9.1826 22.2854 14 2002 年 11 月18.9781 29.0086 20.9745 15 2002 年 12 月20.1406 28.7765 23.4447 16 2003 年 1 月18.3827 30.3982 23.6745 17 2003 年 2 月18.2366 31.04 25.1686 18 2003 年 3 月19.4056 30.9233 25.0111 19 2003 年 4 月20.9838 32.7841 25.9374 20 2003 年 5 月21.8532 33.7225 26.8638 21 2003 年 6 月23.5568 33.72

6、25 26.3037 22 2003 年 7 月26.7878 32.502 27.5285 23 2003 年 8 月26.9548 32.7379 27.4702 24 2003 年 9 月24.9494 36.3362 29.9358 25 2003 年 10 月24.0647 38.4158 29.701 26 2003 年 11 月27.726 41.0243 31.5206 27 2003 年 12 月25.06 39.3644 34.0269 3 家上市公司25 期的股票收盘价格 1、 计算各只股票的月 / 年收益率、方差、标准差。 具体步骤如下： （1）公司 A股票的的每月收益

7、率： 选定单元格 B30，在编辑栏输入 =LN （B4/B3） ， 回车后在 B30中出现结果。应用自动填充单元格命令求出各自月收益率所对应单 元格区域 B30：B53的值。同理可以求出公司B、C的月收益率，分别对应单元格 C30 ：C53 、D30 ：D53中的值。 （2）公司 A的股票月收益率：选择单元格B54 ，在编辑栏输入 =AVERAGE（B30： B53） 。应用自动填充单元格命令可求出公司B、C的月期望收益率，分别对应单 元格 C54 、D54中的值。 （3）公司 A的股票年收益率：选择B55单元格，在编辑栏输入 =12*B54。应用自 动填充单元格命令可求出公司B、C的股票年期

8、望收益率，分别对应单元格C55 、 D55中的值。 （4）公司 A的股票月收益率方差： 选择 B56 单元格，在编辑栏输入 =VARP（B30： B53） 。应用自动填充单元格命令可求出公司B、C的股票月收益率方差，分别对 实用标准文案 精彩文档 应单元格 C56 、D56中的值。 （5）公司 A 的股票年收益率方差：选择B57 单元格，在编辑栏输入 =B56*12。 应用自动填充单元格命令可求出公司B、C 的股票年收益率方差，分别对应单元 格 C57 、D57中的值。 （6）公司 A的股票月标准差：选择B58 单元格，在编辑栏输入 =STDEVP（B30： B53） 。应用自动填充单元格命令

9、可求出公司B、C的股票月标准差，分别对应单 元格 C58 、D58中的值。 （7）公司 A 的股票年标准差：选择B59 单元格，在编辑栏输入 =SQRT （B57） 。 应用自动填充单元格可求出公司B、C 的股票年标准差，分别对应单元格C59 、 D59中的值。计算结果如下 : A B C D E F 28 月收益率 29 日期公司 A 公司 B 公司 C 30 2002 年 1 月0.1367234 0.0483805 0.0270459 0.117784 0.211664 = 0.13 45 46 47 48 总回报率方差0.0151 49 50 总回报率方差0.1228 第二步： 计算各

10、投资项目的单项回报率期望值、单项回报率方差，及各投资项目之间的 相关系数。 根据前面介绍的Excel 的公式，可计算出各投资项目的各个统计量。其计算方法与例1 同，这里不再重复。计算公式见表4-6 。 表 4-6 投资组合优化模型中统计量的计算公式 25 统计量计算 26 期望值 =AVERAGE(B4:B23) =AVERAGE(C4:C23) =AVERAGE(D4:D23) 27 方差 =VAR(B4:B23) =VAR(C4:C23) =VAR(D4:D23) 28 标准方差=STDEV(B4:B23) =STDEV(C4:C23) =STDEV(D4:D23 29 30 相关系数 3

11、1 股票 1 股票 2 债券 32 股票 1 1 =CORREL(B4:B23,C4:C23) =CORREL(B4:B23,D4:D23) 33 股票 2 =C32 1 =CORREL(C4:C23,D4:D23) 34 债券=D32 =D33 1 第三步：建立非线性规划模型 本问题的决策变量是各投资项目的投资比例。用单元格B40:D40 分别表示股票1、股票 2 和债券的投资比例。如表4-5 所示。 本问题的目标函数是投资组合的风险最小，即投资组合总回报率的方差最小。由（4-2 ） 式可知，投资组合总回报率R的方差的计算公式如下： R的方差 = ji jiijjimm xxxxx 222

12、2 2 2 2 1 2 1 . 本题中有三个投资项目，所以上式变为： R的方差 = 233223322332133113311331122112211221 2 3 2 3 2 2 2 2 2 1 2 1xxxxxxxxxxxxxxx = 322332311331211221 2 3 2 3 2 2 2 2 2 1 2 1 222xxxxxxxxx（4-4 ） 实用标准文案 精彩文档 用单元格C48 表示投资组合总回报率R 的方差（即目标函数） 。根据（ 4-3 ）式，在单 元格 C48中输入： =SUMPRODUCT(B41:D41,B27:D27)+2*B40*C40*C32*B28*C2

13、8+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 与债券之间的相关系数。 如果我们建立了如下的协方差矩阵，则上述表达式可大大简化。 A B C D 30 协方差矩阵 31 股票 1 股票 2 债券 32 股票 1 =B27 =COVAR(B4:B23,C4:

14、C23) =COVAR(B4:B23,D4:D23) 33 股票 2 =C32 =C27 =COVAR(C4:C23,D4:D23) 34 债券=D32 =D33 =D27 这时，投资组合总回报率R的方差（即目标函数） ，可以用如下更简单的方法表示： =SUMPRODUCT(MMULT(B40:D40,B32:D34),B40:D40) 本问题有三个约束条件。第一个约束条件是投资组合总回报率的期望值约束。由（4-1 ） 式可知，投资组合总回报率R的期望值的计算公式如下： R的期望值 = mm xxx. 2211 本题中有三个投资项目，所以上式变为： R的期望值 = 332211 xxx（4-5

15、 ） 用单元格B45 表示实际投资组合总回报率R的期望值。根据上式，在单元格B45 输入： =sumproduct (B26:D26,B40:D40) 上式中，单元格B26：D26中的三个值分别为三个项目的单项投资回报率期望值，单元 格 B40： D40中的值分别是三个项目的投资比例。（ 4-5 ）式计算得到的投资组合总回报率的 期望值必须不小于要求达到的值，本题中要求达到的期望值为0.13 ，用单元格D45表示。 第二个约束条件是投资比例之和应等于1。用单元格E40 表示投资比例之和，可用下 式计算： =sum(B40:D40) 它应等于1。 第三个约束是条件非负约束。 第四步：利用“规划求

16、解”功能求出非线性规划的解。 在规划求解参数框中输入目标单元格（目标函数地址）、可变单元格（可变变量地址） 和约束条件。其规划求解参数框如图4-3 所示。 实用标准文案 精彩文档 图 4-3 规划求解参数对话框 然后在规划求解参数框中选择“假定非负”（注意：本问题是非线性规划问题，所以不选择 “采用线性模型” ） ，最后在规划求解参数对话框中单击“求解”得到本问题的解。 第五步：判断用“规划求解”功能求出是否是本问题的最优解。 本问题的模型如公式（4-3 ）所示： o.b, min R的方差 = ji jiijjimm xxxxx 222 2 2 2 2 1 2 1 .（4-4 ） s.t.

17、R的期望值 = mm xxx. 2211 P 1. 21m xxx m xxx,., 21 0 本问题为最小化问题，其目标函数是总风险（总方差）最小。由公式（4-4 ）可知，总 风险是决策变量的幂函数。 由凸函数的定义可知，幂函数Y=cx a，a1，c0， x0 是凸函数。 可见公式4-4 的目标函数是若干个凸函数之和，因此，本问题的目标函数是一个凸函 数。此外，本问题的约束条件均为线性，所以本问题满足运用“规划求解”功能正确求解非 线性规划问题的条件，所求得的解是整体最优解。 模型运行结果见表4-5 。由该表可得本问题的最优解如下：股票1、股票 2、债券的投 资比例为0.5063:0.324

18、3:0.1693。这时，投资组合的总回报率期望值达到所要求的0.13 ， 而投资组合的总回报率的方差最小，为0.0151 。 第六步：置信区间分析 当投资组合总回报率服从正态分布时，根据正态分析的统计理论，总回报率的置信区 间如下。 （1）总回报率的值落在区间 总回报率期望值- 总回报率标准方差，总回报率期望值+ 总回报率标准方差的概率是68% ； （2）总回报率的值落在区间 总回报率期望值-2总回报率标准方差，总回报率期望值 +2总回报率标准方差 的概率是95% ； （3）总回报率的值落在区间 总回报率期望值-3总回报率标准方差，总回报率期望值 +3总回报率标准方差 的概率是99.7%。 实

19、用标准文案 精彩文档 本题中，总回报率期望值=0.13 ，总回报率的标准方差=总方差=0151.0=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） 。 上面的计算是对单元格的操作来实现的，这是很麻烦的，稍不小心，就

20、很容易出错， 为避免出错，我们编制了一个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“)

21、Range(“d27“) = Application.Var(Range(“d4:d23“) Range(“b28“) = Application.StDev(Range(“b4:b23“) 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“) = Applica

22、tion.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) = “=B402“ Cells(41, 3) = “

23、=c402“ Cells(41, 4) = “=d402“ 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

24、:$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 Basic编辑器界面 下，单击 工具 菜单中的 引用 命令，如图4-4 所示，打开 引用 -V

25、BAProject对话框，如 图 4-5 所示，然后选中 可使用的引用 列表框中的“SOLVER ”复选框。 图 4-4 执行引用命令 图 4-5 引用 -VBAProject对话框 如果“ SOLVER ”未出现在 可使用的引用 列表框中，则需要单击 浏览 按钮，并打开 “OfficeLibrary SOLVER”子文件夹中的“Solver.xla” 。 4.5 通用投资组合优化决策模型及其信息化实现 4.5.1最优投资组合的确定 考虑一个投资组合X，它由 n 个证券组成，每个证券的预期收益率为)( i rE，方差记为 2 i ，证券之间的协方差记为)(ji ij ，i 、j=1 ，2，n。

26、于是证券投资组合的收益率 X r 实用标准文案 精彩文档 和风险)( 2 X r可以表示成 VXXr T X )( 2 在给定预期收益率)( X rE之下， 如何选择证券组合的权重 n xx ,., 1 ，使证券组合X具有最小 方差呢？ 记 T n rErErEe)(),.,(),( 21 ，为确定最小方差集合，我们考虑如下优化模型，即一 般的马柯维茨模型 ) 2 1 min(VXX T ， )( 1 . .1 X T n i i rEeX x ts 这是一个等式约束的极值问题， 在有些情形下， 投资者把不进行卖空作为一种投资策略，因此， 讨论在不允许卖空的约 束下如何确定最小（或最优）方差集

27、合是必要的。这时在约束条件中需要加入xi大于0， i=1, ,n 。模型为 ) 2 1 min(VXX T ， 0 )( 1 1 X rEeX x X T n i i 这是二次规划模型。利用 Kuhn-Tucker 条件，可得类似结论。 对于上面的二次规划模型， 我们可以通过如下的VBA程序来进行求解。 4.5.2通用投资组合风险的最优化模型的VBA实现 （1）首先建立一个名字为“给定最低预期收益率的最优投资组合规划求解模型.xls” 的工作簿，在当前的sheet1 上设计模型的结构，如图4-6 所示。 图 4-6 给定最低预期收益率的最优投资组合规划求解模型 是否允许卖空的下拉列表框控件插入

28、在单元格B5的位置，此下拉列表框控件的单元格 链接为“ $B$5” ，数据区域为“$C$5:$C$6” ，并在单元格C5和 C6中分别输入“允许卖空” 实用标准文案 精彩文档 和“不允许卖空” ，且此两个单元格的字体颜色设置为白色，以便使模型界面美观。 （2）模型由以下三部分组成：已知数据区域、命令按钮区域（包括一个 准备数据 按钮、一个 开始计算 按钮、一个 清除表格 按钮）和计算结果区域（在单击 开始计算 按钮后计算结果的输出区域） （3）对 准备数据 按钮指定一个名字为“Sub 准备数据 () ”的宏，并编写如下的程序 代码： Sub 准备数据 () Dim n, i, j As Int

29、eger 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 .Weight = xlMedium End With For i = 1 To n Cells(11, i + 1) = “证券 “ & i Cells(11,

30、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) = “输入各个证券间的协方差矩阵“ C

31、ells(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

32、 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

33、= 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 Next j Next i Range

34、(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

35、= 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) = “合计 “ C

36、ells(19 + n, 1) = “比重（ % ）“ Cells(20 + n, 1) = “预期收益率 “ Cells(21 + n, 1) = “标准差 “ 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(“ & my

37、range1 & “,“ & 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).NumberFo

38、rmat = “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,

39、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:

40、某 4 个证券的投资组合，各个证券的预期收益率、标准差和证券之间的协方差矩 阵如表 4-7 和 4-8 所示，要求的最低期望收益率为15% ，试计算不允许卖空情况下的最优投 资组合。 表 4-7 各证券的预期收益率 证券 1证券 2证券 3证券 4 预期收益率8%12%6%18% 表 4-8 各证券间的协方差矩阵 证券 1证券 2证券 3证券 4 证券 10.10240.03280.0655-0.0022 证券 20.03280.0676-0.00580.0184 证券 30.0655-0.00580.20250.0823 证券 4-0.00220.01840.08230.1296 计算步骤如

41、下： （1）在单元格B3 中输入证券数量“4” 。 （2）单击 准备数据 按钮，对模型自动对工作表进行格式化，格式化后的工作表如图 4-7 所示。 图 4-7 格式化后的工作表 （3）按照格式化后的工作表输入表4-7 和 4-8 所示的有关数据。由于协方差矩阵的对 成性，只需要在被模型为浅黄色的单元格中输入协方差数据即可。 （4） 单击是否允许卖空下拉列表框的下拉三角按钮，选择不允许卖空， 单击 开始计算 实用标准文案 精彩文档 按钮，则模型自动计算最优投资比例结果，分别如图4-8 所示。 图 4-8 最优投资比例计算结果 4.6 通用投资组合优化决策信息系统及其VBA实现 下面以最优投资组合

42、模型为例，来说明投资组合优化决策信息系统的实现方法。 4.6.1设计自定义菜单 为了方便用户使用应用信息系统程序，可以在Excel系统菜单上增加自定义菜单，此 自定义菜单有3 个命令，分别为“准备数据”、 “优化计算”和“删除数据”，并把增加自定 义菜单的宏设计为自动宏。 建立一个名为“最优投资组合决策信息系统.xls ”的工作簿，在Visual Basic编辑器 窗口中，单击 插入 菜单中的 模块 命令，插入一个“模块1” ，在此模块中编写如下的自 动宏代码： Sub auto_open() MenuBars(xlWorksheet).Menus.Add “最优投资组合决策信息系统“ Men

43、uBars(xlWorksheet).Menus(“最优投资组合决策信息系统“).MenuItems.Add “准备 数据 “, “准备 “ MenuBars(xlWorksheet).Menus(“最优投资组合决策信息系统“).MenuItems.Add “优化 计算 “, “计算 “ MenuBars(xlWorksheet).Menus(“最优投资组合决策信息系统“).MenuItems.Add “删除 数据 “, “删除 “ End Sub Sub auto_close() MenuBars(xlWorksheet).Reset MenuBars(xlWorksheet).Activa

44、te End Sub 这样，在每次打开工作簿“最优投资组合决策信息系统.xls ”时，就自动在Excel系 统菜单上加载 最优投资组合 自定义菜单， 如图 4-9 所示。 而关闭此工作簿时，该自定义菜 单自动卸载。 实用标准文案 精彩文档 图 4-9 最优投资组合决策信息系统的自定义菜单 4.6.2设计基本数据输入窗体 由于证券最优投资组合的类型一般分为三大类型，即： （1）直接求最低风险下的最优投资组合，此时的目标函数是风险（标准差或方差）最 小； （2）给定最低预期收益下的最低风险的最优投资组合，此时的目标函数是风险（标准 差或方差）最小；但有一个最低预期收益率的约束条件； （3）给定最高

45、风险下的最大预期收益率的最优投资组合，此时的目标函数是预期收益 率最大，但有一个最高风险的约束条件。 因此，需要设计一个输入已知数据（包括投资组合的证券数量、最低预期收益率或最 高风险、是否允许卖空）的窗体。 在 Visual Basic编辑器窗口下，单击 插入 菜单中的 用户窗体 命令，插入一个用户 窗体 UserForm1， 将其 Caption属性设置为 “最优投资组合决策 （输入基本数据） ” ， 如图 4-10 所示。 图 4-10 插入用户窗体 窗体的设计步骤如下： （1）在此窗体上插入3 个框架 Frame1Frame3，各框架说明如下： 框架 Frame1 的 Caption属

46、性设置为“投资组合的证券数量”，在此内插入1 个标签 Label1 ，其 Caption属性设置为“证券数量” ，插入 1 个文本框 TextBox1 ，其 Value 属性设 置为空值。 框架Frame2 的 Caption属性设置为“是否允许卖空”，在此内插入2 个单选按钮 OptionButton1和 OptionButton2，其 Caption 属性分别设置为“允许卖空”和“不允许卖 空” ，其单选按钮OptionButton1的 Value 属性设置为True。 框架Frame3 的 Caption属性设置为“优化模型选择”，在此内插入3 个单选按钮 OptionButton3、

47、OptionButton4和 OptionButton5， 其 Caption 属性分别设置为 “最低风险” 、 实用标准文案 精彩文档 “给定最低收益率”和“给定最高风险（标准差）” ，其中单选按钮OptionButton3的 Value 属性设置为True 。在此框架内插入两个文本框TextBox2 和 TextBox3 ，其 Value 属性设置为 空值。 框架 Frame3 的 5 个控件 OptionButton3、OptionButton4、OptionButton5、TextBox2 和 TextBox3 的 TabIndex 属性分别设置为0、1、2、3、4 和 5，以便控制光

48、标的依次移动。 （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)

49、r = Val(TextBox2.Value) c = Val(TextBox3.Value) 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