投资学实验教程.doc

目 录第一章.模拟炒股- 2 -1.1.使用同花顺软件模拟炒股的基本步骤- 2 -1.2.股票的常用知识- 4 -1.3.K线图基础知识- 4 -1.4.分时走势图- 6 -第二章.电子表格的基本知识- 10 -2.1单元格- 10 -2.2公式- 10 -2.3绝对引用和相对引用- 12 -2.4模拟运算表- 13 -第三章.Excel电子表格金融模型- 20 -3.1两种风险资产的可行集- 20 -3.2两种资产和一种无风险资产组合最优化- 24 -3.3多资产模型（三种或三种以上）- 29 -3.4债券价格和市场利率、时间的关系- 34 -第一章. 模拟炒股1.1. 使用同花顺软件模拟炒股的基本步骤1. 双击打开同花顺软件，对于同花顺的首次使用者点击图中的“免费注册”按钮。图1-1然后按要求填写资料，注册帐户。注册完毕进入同花顺软件进行操作。2. 打开菜单“在线服务”里面的“模拟炒股”图1-2进去以后点击“免费开通”进入同花顺交易区：图1-3模拟炒股会给操作者提供20万的初始模拟资金，然后就是对所关注的股票进行买入和卖出交易。这些是模拟炒股的基本操作步骤。1.2. 股票的常用知识1. 深沪交易所的交易时间是从每周一至周五，上午9：30至11：30，下午1：00至3：00，法定公众假期或主管机关宣布的休市日除外。2. 我国上海证券交易所和深圳证券交易所对股票和基金交易实行"T+1"的交易方式，当日买进的股票，要到下一个交易日才能卖出。所以当日买入的股票不能当日卖出。所谓的T0和T1的T，是指股票成交的当天日期。凡在股票成交当天办理好股票和价款清算交割手续的交易制度，就称T0交易；凡在成交后的第二天才办理 好清算交割事宜的，就称为T1交易.以此类推，还可以有T3、T4等交易制度。3. 股票中的一手就是100股的股票,在股票交易中以"手"为单位,就是说至少要买卖100股.买股票最少要多少钱=每股价格乘于100股.1.3. K线图基础知识K线图（Candlestick Charts）又称蜡烛图、日本线、阴阳线、棒线等，常用说法是“K线”，起源于日本十八世纪德川幕府时代（16031867年）的米市交易，用来计算米价每天的涨跌。因其标画方法具有独到之处，人们把它引入股票市场价格走势的分析中，经过300多年的发展，已经广泛应用于股票、期货、外汇，期权等证券市场。 K线图基本用途就是为了寻找“卖买点”，虽然面对同样的K线图，但其中的领悟各有不同，必须长期认真观察，至少你的观察要经历一个完整的 “牛熊市”。市场上有一些书从技术分析角度介绍“如何做股票”，可以买来看一看，“波浪理论”是众人比较推崇的，也应该看，但是要“辩证”地看，暂时不懂也没关系，随着看盘时间的增加，会有所领悟。 根据每支股票当日的开盘价，收盘价，最高价，最低价四项数据，可以将股价走势图画成如下线图：1. 图1-4叫阳线，常用红色实体柱来表示。一般收盘价高于开盘价，最高价等于收盘价时，无上影；同样，最低价等于开盘价时，无下影。图1-42. 图1-5叫阴线，常用绿色实体柱来表示。一般收盘价低于开盘价，最高价等于开盘价时，无上影；同样，最低价等于收盘价时，无下影。图1-53. 图1-6叫十字线，收盘价等于开盘价，最高价等于开盘价时，无上影；最低价等于开盘价时，无下影。图1-61.4. 分时走势图分时走势图也叫即时走势图，它是把股票市场的交易信息实时地用曲线在坐标图上加以显示的技术图形。走势图坐标的横轴是开市的时间，纵轴的上半部分是股价或指数，下半部分显示的是成交量。分时走势图是股市现场交易的即时资料，其在实战研判中的地位极其重要，是即时把握多空力量转化即市场变化直接根本。分时走势图分为指数分时走势图和个股分时走势图，在这里主要是讲一下个股分时走势图。图1-7如上图所示，说明如下： 1） 白色曲线：表示该种股票即时实时成交的价格。 2） 黄色曲线：表示该种股票即时成交的平均价格，即当天成交总金额除以成交总股数。 3） 黄色柱线：在红白曲线图下方，用来表示每一分钟的成交量。 4） 成交明细：在盘面的右下方为成交明细显示，显示动态每笔成交的价格和手数。下面是分时走势图中经常出现的名词及含意：1. 内盘外盘。内盘常用S表示，外盘用B表示。内盘：在成交量中以主动性叫卖价格成交的数量，所谓主动性叫卖，即是在实盘买卖中，卖方主动以低于或等于当前买一的价格挂单卖出股票时成交的数量，显示空方的总体实力。 外盘：在成交量中以主动性叫买价格成交的数量，所谓主动性叫买，即是在实盘买卖中，买方主动以高于或等于当前卖一的价格挂单买入股票时成交的数量，显示多方的总体实力。2. 换手率指在一定时间内市场中股票转手买卖的频率，是反映股票流通性强弱的指标之一。换手率的计算公式为：换手率=某一段时期内的成交量/发行总股数×100% （在中国大陆：成交量/流通总股数×100%）3. 量比是衡量相对成交量的指标。它是指股市开市后平均每分钟的成交量与过去5个交易日平均每分钟成交量之比。其计算公式为：量比=现成交总手/（过去5个交易日平均每分钟成交量）×当日累计开市时间（分），简化为：量比=现成交总手/（过去5日平均每分钟成交量×当日累计开市时间（分）。4. 买一，买二，买三为三种委托买入价格，其中买一为最高申买价格。 5. 卖一，卖二，卖三为三种委托卖出价格，其中卖一为最低申卖价格。     6. 委买手数：是指买一，买二，买三所有委托买入手数相加的总和。7. 委卖手数：是指卖一，卖二，卖三所有委托卖出手数相加的总和。8. 委比：委买委卖手数之差与之和的比值。 委比旁边的数值为委买手数与委卖手数的差值。当委比为正值时，表示买方的力量比卖方强，股价上涨的机率大；当委比为负值的时候，表示卖方的力量比买方强，股价下跌的机率大。   9. 现手：已经成交的最新一笔买卖的手数。在盘面的右下方为即时的每笔成交明细，红色向上的箭头表示以卖出价成交的每笔手数，绿色箭头表示以买入价成交的每笔手数。 懂得如何看股票走势图，是炒股基本要求，而实战中的K线分析，必须与即时走势图分析相结合，才能真实可靠的读懂市场的语言，洞悉盘面股价变化的奥妙。K线形态分析中的形态颈线图形，以及波浪角度动量等分析的方法原则，也同样适合即时动态分时走势图分析。第二章. 电子表格的基本知识22.1 单元格单元格是表格中行与列的交叉部分，是组成表格的最小单位，单个数据的输入和修改都是在单元格中进行的。 单元格按所在的行列位置来命名，例如：地址“D7”指的是“D”列与第7行交叉位置上的单元格。如图所示：图2-12.2 公式公式是EXCEL工作表中进行数值计算的等式。公式输入是以“=”开始的。简单的公式有加、减、乘、除等计算。 例如=3×4-5 =B4*C4-C3如图所示：图2-2复杂一些的公式可能包含函数（函数：函数是预先编写的公式，可以对一个或多个值执行运算，并返回一个或多个值。函数可以简化和缩短工作表中的公式，尤其在用公式执行很长或复杂的计算时。）、引用、运算符（运算符：一个标记或符号，指定表达式内执行的计算的类型。有数学、比较、逻辑和引用运算符等。）和常量（常量：不进行计算的值，因此也不会发生变化。）2.3 绝对引用和相对引用相对引用Excel公式中的相对单元格引用是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将图2-2-1单元格 C5 中的相对引用复制到单元格 D6，将自动从=B4*C4-C3, 调整到=C5*D5-D4。图2-3绝对引用单元格中的绝对单元格引用总是在指定位置引用单元格。如果公式所在单元格的位置改变，绝对引用保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。例如，如果将图2-2-1单元格C5中的绝对引用复制到单元格 D6，则在两个单元格中一样，都是=$B$4*$C$4-$C$3。 如图所示：图2-4混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用 $C5、$D6 等形式。绝对引用行采用 C$5、D$6 等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。2.4 模拟运算表模拟运算表是一个单元格区域，它可显示一个或多个公式中替换不同值时的结果。有两种类型的模拟运算表：单变量模拟运算表和双变量模拟运算表。单变量模拟运算表中，用户可以对一个变量键入不同的值从而查看它对一个或多个公式的影响。双变量模拟运算表中，用户对两个变量输入不同值，而查看它对一个公式的影响。1、 单变量模拟运算表仍然以图2-2为基础，首先确定一块区域，然后引用要做模拟运算表的函数，这里引用C5单元格的函数如图所示图2-5由于C5单元格中包含B4、C4、C3三个变量，现在假设对C4做模拟运算表则点击菜单栏中的“数据-模拟运算表”得到如图所示：图2-6点击“输入引用列的单元格”所在的参考键，如何引用C4单元格可得如图所示：图2-7C10单元格的意义就是当B3=3、C3=5、变量C4=B10=3时，单元格C5所在的函数的值为4。2、 双变量模拟运算表仍然以图2-2为基础，先选定一块区域如图所示：图2-8假设以C4、C3为变量做模拟运算表，点击菜单栏中的“数据-模拟运算表”得到如图所示：图2-9分别对C3、C4进行引用如图所示图2-10点击“确定”后得到如图所示的结果图2-11单元格E9表示当变量C3= E7=8 、变量C4= C9=2、常量B4=3时C5的函数值为-2第三章. Excel电子表格金融模型33.1 两种风险资产的可行集问题：资产A的标准差和期望回报率分别为18%和30%；资产B的标准差和期望回报率分别为15%和10%。要求：当两种资产的相关系数发生变化时，画出不同相关系数所对应的可行集的动态图。如图所示：图3-1解决方案：对于两种资产组合，我们可以通过变动其中一种资产的比例来计算资产组合的标准差和期望收益率，并画出相应的可行集。然后通过微调项来实现相关系数的变动，以达到可行集的变动。第一步、构建电子数据模型。如图所示：图3-21. 相关系数：在E6输入=(F6-20)/20用来计算相关系数使得相关系数小于1大于-12. A资产比例：在E7输入=F7/10用来计算A资产比例使得该值处于0到1的相关范围。3. 标准差：计算标准的公式为：在单元格E8中输入=(E72*D42+2*E7*(1-E7)*E6*D4*D5+(1-E7)2*D52)0.54. 期望回报率：计算期望回报率的公式为，在单元格E9中输入=E7*E4+(1-E7)*E5。5. 可行集中风险最小的点：确定该点比例的公式为，在E11中输入=IF(E6<D5/D4,(D52-D4*D5*E6)/(D52+D42-2*D4*D5*E6),"")；在E12中输入=(E112*D42+2*E11*(1-E11)*E6*D4*D5+(1-E11)2*D52)0.5在E13中输入=E11*E4+(1-E11)*E56. 相关系数为-1时可行集中风险最小的点：在E15中输入=D5/(D4+D5)；在E16中输入=E15*E4+(1-E15)*E5第二步、构建模拟运算表如图所示：图3-31. 引用构建模拟运算表的函数：这里要引用的函数分别是标准差和期望回报率。在F24中输入=E8，在L24中输入=E9.2. 确定函数引用行列单元格的变量。标准差的变量为相关系数和A在资产组合中的比例，因此标准差需要用双变量的模拟运算表；期望回报率的变量为A在资产组合中的比例，因此期望回报率需要用单变量的模拟运算表。在G24中输入=(F6-20)/20，在F、K列和24行如图3-1-3所示直接输入其他数值。3. 把E6、E7作为变量分别做标准差和期望回报率的模拟运算表。第三步 画图1. 分别选定G25:I46和L25:L46这两块数据区域。2. 点击插入-图表，选择无数据点平划线散点图，作出静态图。3. 创建微调项。在主菜单上点击视图-工具栏-窗体，然后点击微调项按钮，当鼠标变成十字在图上画按钮，然后对该按钮右击，选择设置控件格式-控制，单元格链接中输入$F$6，最小值为0，最大值为40，步长为5；这样可以实现相关系数的范围为-1到1，数据每跳一下的值为0.25.4. 调整图形格式（可选）。习题假设两种资产的期望回报率分别为30%和10%，他们的标准差分别为20%和15%，每一种资产占资产组合的比例都为-0.3到1.3之间，要求：当两种资产的相关系数发生变化时，画出不同相关系数所对应的可行集的动态图。3.2 两种资产和一种无风险资产组合最优化问题：无风险利率为6%。风险资产A的标准差和期望回报率分别为17%和27%；风险资产B的标准差和期望回报率分别为13%和8%。两种风险资产的相关系数为-0.7.如图所示：图3-4其中代表风险资产组合占整个资产组合的比例；w代表A资产占风险资产组合的比例。要求：画出上图中的有效的风险收益曲线和风险资产的风险收益曲线，并画出动态图也即随着这两个比例的变化，P点和C点也会相应的发生变化。解决方案：对于两种资产的资产组合，我们可以通过变动第一种资产的比例w并计算所得到的资产组合的标准差和期望回报率来得到风险资产的风险收益曲线；通过切点和一个无风险资产的标准差和期望回报率来求有效的风险收益曲线。第一步、构建电子数据模型图3-51. 风险溢价：期望回报率与无风险资产回报率之差，所以在F5输入=E5-$E$3，在F6输入=E6-$E$3。2. A资产占风险资产的比例：在F8中输入=G8/10。3. 风险组合资产的标准差和期望回报率：（公式同前一个模型）在E10中输入=(F82*D52+2*F8*(1-F8)*D5*D6*F3+(1-F8)2*D62)0.5在F10中输入=F8*E5+(1-F8)*E64. 确定切点的A资产占风险资产的比例：公式为由于该公式比较复杂，分别确定分子、分母的值然后再来算该值。首先在E12中输入=D6*(F3*D5-D6)；在F12中输入=D52+D62-2*D5*D6*F3。确定分子在E13中输入=F6*E12-(E5-E6)*D62、确定分母在F13中输入=(E5-E6)*E12-F6*F12，然后确定该系数在F14中输入=E13/F13。5. 确定资本配置线：切点标准差在E16中输入=(F142*D52+2*F14*(1-F14)*D5*D6*F3+(1-F14)2*D62)0.5，切点期望值在F16中输入=F14*E5+(1-F14)*E6。斜率在F17中输入=(F16-E3)/E16。6. 确定风险资产占总资产的比例：在F21中输入=G21/10。7. 确定三个资产的风险和收益：由于总资产的标准差是风险资产与的乘积，在E23中输入=F21*E10；算期望收益率在F23中输入=F21*F10+(1-F21)*E3。第二步、构建模拟运算表和作图图3-61. 如上图所示分别在J25和K25中输入=E10和=F10，以W为变量对风险资产的标准差和期望回报率做模拟运算表。并以该表数据为基础做相应的风险资产的可行集。2. 分别用表中的数据画相应的切线和动态线。（提示：两点决定一条直线）3. 分别对、W做微调项。（参照前一个模型）4. 调整图形格式（可选）。习题无风险利率为5.1%。风险资产1的平均回报率为11.3%，标准差为23.2%；风险资产2的平均回报率为6.9%。标准差为18.7%；风险资产1和2之间的相关系数为24.5%。请建立“组合优化两种资产”的模型，使其包括有效的风险收益曲线和风险资产的风险收益曲线。3.3 多资产模型（三种或三种以上）问题：假设现在知道三公司六年的股票回报率，请作出三种股票的可行集。三种股票的股票回报率数据如下： 年份A股票B股票C股票三公司股票的六年回报率数据200136.0%35.0%53.0%2002-11.0%-8.0%-37.0%2003-18.0%-20.0%69.0%200470.0%28.0%50.0%200525.0%76.0%16.0%2006-72.0%-51.0%-61.0%预期要作出的结果图：多资产模型的公式：注释：最小方差函数所代表的图像就是可行集；· 代表可行集上最小标准差的期望收益率；· 代表以三种股票的平均收益率为元素的矩阵；· 代表三种股票的协方差矩阵；· 代表元素全为1的单位矩阵。设计表格过程中所需要的函数：标准差：STDEVP()期望回报率：AVERAGE()协方差：COVAR()转置矩阵：TRANSPOSE()矩阵乘积：MMULT()逆矩阵：MINVERSE()由于矩阵是一种二维以上的数的形式，所以我们在电子表格的设计过程中要用到一种新的技术，那就是数组。数组就是单元的集合或是一组处理的值集合。可以写一个数组公式，即输入一个单个的公式，它执行多个输入的操作并产生多个结果每个结果显示在一个单元中。数组公式可以看成是有多重数值的公式。与单值公式的不同之处在于它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元格。数组的元素可多达6500个。输入数组公式首先必须选择用来存放结果的单元格区域（可以是一个单元格），在编辑栏输入公式，然后按CtrlShiftEnter组合键锁定数组公式，Excel将在公式两边自动加上花括号“”。注意：不要自己键入花括号，否则，Excel认为输入的是一个正文标签。第一步、构建电子数据模型如图所示：1. 在E9中输入=STDEVP(E3:E8)，F9、G9中进行同样方式的输入；在E10中输入=AVERAGE(E3:E8)，F10、G10中进行同样方式的输入。2. 把E10:G10中的数据转置复制到D12:D14中；E12:E14中全部输入1；在G12中输入=COVAR($E$3:$E$8,F3:F8)，按同样的方式在F12:H14中输入公式。3. 选定D16:D18,然后在编辑栏中输入=MMULT(MINVERSE(F12:H14),D12:D14)，然后按CtrlShiftEnter组合键锁定数组公式；选定E16：E18，在编辑栏中输入=MMULT(MINVERSE(F12:H14),E12:E14)，然后按CtrlShiftEnter组合键锁定数组公式。4. 在E20中输入=MMULT(TRANSPOSE(D12:D14),D16:D18)，然后按CtrlShiftEnter组合键锁定数组公式；以数组的方式同样在F20中输入=MMULT(TRANSPOSE(D12:D14),E16:E18)、在G20中输入=MMULT(TRANSPOSE(E12:E14),E16:E18)。5. 在单元格H20中输入=E20*G20-F202、F21中输入=1/G200.5、G21中输入=H200.5/G20、H21中输入=F20/G20。6. 在这个模型中期望回报率变成自变量，因此在单元格G23中输入=(F21/G21)*(H23-H21)2+G212)0.5，单元格H23中输入=I23/100，而I23是通过后续的微调项自由斌值。7. 在G24中输入=(F21/G21)*(H24-H21)2+G212)0.5，H24直接引用H21的数据。第二步、做模拟运算表和画图如图所示在单元格G29中引用G23标准差的函数，以H23的期望回报率作为变量作单变量模拟运算表。选定F30：G40作散点图。然后选定I23作为动态链接单元格做微调项。习题无风险利率为4.7%。有5种风险资产，它们的期望回报率分别为11.3%，7.8%，9.2%，13.6%，10.5%，标准差分别为16.6%，24.3%，19.7%，22.9%和18.3%；风险资产之间的相关系数都为0。请建立“组合优化多种资产”的模型，使其包括有效的风险收益曲线、风险资产的风险收益曲线。3.4 债券价格和市场利率、时间的关系某公司发行的一种债券的面值为1000元，到期年限为10年，每年付息2次，息票利率为8%，假定在上市后不久市场利率有可能发生显著的变化，试制作一个可调图形来显示其在不同的市场利率下债券价值随时间的变化。债券的价格用PV函数来确定，它是返回某项投资的一系列将来偿还额的当前总值，其中第一个参数表示每一期的市场利率，第二个参数表示总期数，第三个表示各期所获金额，第四个表示终值的金额。由于后两个参数的金额的方向和投资付出方向是相反的，所以输入的金额应该是负数。第一步、如图所示先输入最左边的数据区市场利率的数据可以通过微调项进行动态调整，年息票额=D4*D7，付息总次数=D6*D5,债券发行价格=PV(D3/D6,D9,-D8/D6,-D4)第二步、描述在市场利率一定的情况下不同时间点的债券价值，如图上图所示中间的数据区其中K5输入=D10,J6输入=$D$3/$D$6*K5，K6输入=K5+J6-I6，后面的空格可以用格式柄进行拖动来复制公式。然后对一系列在不同时点下的债券价值做模拟运算表如上图右边的数据区域所示。2%到16%为市场利率，N列的数据为不同时间点的债券价值，以D3作为行的引用单元格。第三步、以模拟运算表的数据区为基础画图如图所示横坐标为债券的数据点，纵坐标为债券的价值，图中的红线表示在市场利率为4.5%的时候各个时间点的债券价值。习题一以上面的例子为基础请画出债券发行价格随市场利率的变化的图，图的最后形式如下图所示纵坐标为债券价值，横坐标为市场利率。习题二假设债券的面值为1000元，每年付息次数为2次，请分别画出在不同息票利率下和不同到期年限下价格收益率曲线图形。如下面2个图形。以上2个图分别是以息票利率和到期年限作为自变量通过微调项进行动态变化，两个图形都表示的是债券价格和到期收益率之间的关系。