下载文档原格式
学会使用Excel进行股票和投资组合分析第一章：Excel基础知识在进行股票和投资组合分析之前，了解Excel的基础知识是必不可少的。
Excel是一款功能强大的电子表格软件，可以用于数据的收集、整理和分析等多种用途。
在这一章节中，将介绍Excel的基本操作，如单元格、公式和函数的使用，以及数据的导入和导出等技巧。
1.1 单元格和工作表Excel的最基本单位是单元格，我们可以在单元格中输入文本、数字和公式等。
单元格还可以通过合并、拆分和格式化等操作进行美化和整理。
另外，Excel的工作簿可以包含多个工作表，通过使用不同的工作表可以更好地组织和分析数据。
1.2 公式和函数Excel的公式是用于进行计算的表达式，可以使用各种数学函数、逻辑函数和文本函数等。
通过灵活地应用公式和函数，我们可以快速进行数据的运算和分析。
在股票和投资组合分析中，常用的函数包括SUM、AVERAGE、MAX、MIN、IF等。
1.3 数据的导入和导出在进行股票和投资组合分析时，我们通常需要从外部数据源导入数据，或者将分析结果导出到其他软件或文件中。
Excel提供了多种导入和导出数据的方法，如从文本文件、数据库和Web导入数据，以及将数据导出为文本文件、图像和PDF等。
第二章：股票分析股票分析是投资者判断个股投资价值的关键步骤。
在这一章节中，将介绍如何使用Excel进行股票基本面分析和技术面分析，并通过实例演示具体的分析方法和技巧。
2.1 基本面分析基本面分析是通过研究公司的财务状况、经营情况和行业发展等因素，来评估股票的投资价值。
在Excel中，我们可以通过导入财务报表数据和其他相关数据，计算关键指标如市盈率、市净率和ROE等，并进行比较和分析。
2.2 技术面分析技术面分析是根据股票的历史价格和交易量等信息，来判断股票的走势和买卖时机。
在Excel中，我们可以使用图表和函数等工具，绘制股票价格和交易量的趋势图，并计算技术指标如移动平均线、相对强弱指标和MACD等，以辅助投资决策。
使用Excel进行投资组合分析教程第一章：投资组合分析简介在金融领域，投资组合是指将不同的资产按照一定的比例组合在一起，以达到分散风险和实现预期收益的目标。
而投资组合分析则是评估和优化投资组合的方法。
第二章：Excel基础在进行投资组合分析之前，我们需要掌握一些基本的Excel操作技巧。
这包括如何输入数据、如何进行数据排序和筛选、如何进行公式的计算等等。
第三章：构建投资组合首先，我们需要确定投资组合中包含的资产。
然后，我们可以在Excel中创建一个表格，将每个资产的名称、代码、投资金额等信息输入其中。
接着，我们可以添加一些公式来计算每个资产的权重和总投资金额。
第四章：计算投资组合的收益与风险在投资组合分析中，我们常常关心的是收益和风险。
在Excel 中，我们可以利用一些内置的函数来计算投资组合的收益和风险。
如计算收益使用“=SUMPRODUCT(权重范围, 资产收益范围)”函数，计算风险可以使用“=SQRT(SUMPRODUCT(权重范围^2, 资产风险范围^2))”函数。
第五章：优化投资组合优化投资组合是指在给定的约束条件下，寻找最佳的投资组合。
在Excel中，我们可以使用“Solver”工具来进行投资组合的优化。
首先，我们设定一些约束条件，如投资金额的总额、各个资产的权重之和等等。
然后，我们通过调整权重的数值，使得投资组合的风险最小或者收益最大。
第六章：风险与收益的均衡在实际投资中，投资者往往会追求收益最大化但同时也要控制风险。
Excel提供了一些分析工具，可以帮助我们找到风险与收益之间的均衡点。
例如，我们可以使用“曲线拟合”功能来拟合资产收益和风险之间的关系，并根据这个关系来选择最合适的投资组合。
第七章：实例分析在本章中，我们将通过一个实例来演示如何使用Excel进行投资组合分析。
我们将选择几个常见的资产，包括股票、债券和黄金，然后利用Excel的各种功能来构建、分析和优化投资组合。
第八章：总结与展望在本章中，我们对整个教程进行总结，并展望未来可能的发展方向。
如何使用Excel进行股票投资分析股票投资分析是投资者在购买和交易股票时的重要工具之一。
通过对企业的财务数据和市场信息进行分析，投资者可以评估股票的投资价值，并做出更明智的投资决策。
Excel是一种功能强大的电子表格软件，可以帮助投资者进行股票投资分析。
本文将介绍如何使用Excel进行股票投资分析，以帮助投资者更好地理解和运用Excel工具。
一、数据导入与整理在进行股票投资分析之前，首先需要将相关的财务数据和市场信息导入Excel中，并进行合理的整理。
投资者可以通过在线财经平台或财经网站下载股票的历史价格数据和财务报表数据，然后将这些数据导入Excel表格。
在Excel中，可以使用“导入外部数据”功能将下载的数据导入表格，或者直接将数据从复制粘贴到Excel中。
确保导入的数据包括股票的日期、开盘价、收盘价、最高价、最低价等信息，以及涉及股票财务分析的各种指标，如营业收入、净利润、资产负债表等。
导入数据后，接下来需要进行数据整理和格式化工作。
可以使用Excel的筛选功能，按照需要筛选出特定时间段的数据，或者按照股票名称筛选出特定股票的数据。
此外，还可以使用Excel的排序功能对数据进行排序，以便更好地观察和分析数据。
二、技术指标分析技术指标是评估股票价格走势和市场趋势的重要工具。
使用Excel可以对股票的历史价格数据进行技术指标分析，以辅助投资者的决策。
常用的技术指标包括移动平均线、相对强弱指数（RSI）、布林带等。
通过在Excel中创建相应的公式和图表，可以计算和展示这些技术指标。
例如，可以通过计算收盘价的移动平均线来判断股票的趋势。
使用Excel的“AVERAGE”函数可以计算出一定时间内的移动平均价格，然后通过将计算结果绘制成折线图，可以清晰地看到股票价格的均线走势。
此外，还可以使用Excel的筛选功能和条件格式化功能，根据技术指标的数值范围将股票的价格进行分类，以便投资者更好地观察和分析数据。
Excel金融计算专业教程简介在金融行业中，Excel是一个非常重要的工具。
它可以用于各种金融计算，包括财务分析、投资组合管理、风险评估等。
本教程将介绍一些常用的Excel金融计算功能，以帮助金融分析师更高效地运用Excel进行工作。
目录1.Excel基础知识2.Excel函数3.数据导入和清洗4.财务分析5.投资组合管理6.风险评估1. Excel基础知识在开始学习Excel金融计算之前，我们需要了解一些基础知识。
这些知识包括Excel的界面、单元格、工作表等基本概念，以及一些常用的快捷键和操作技巧。
1.1 Excel界面Excel的界面由菜单栏、工具栏、工作区和状态栏组成。
菜单栏包含了各种功能和命令，工具栏可以快速访问常用功能，工作区是我们进行数据输入和计算的地方，状态栏显示有关工作簿和工作表的信息。
1.2 单元格Excel的主要工作单元是单元格。
每个单元格由一个列字母和一个行号组成，例如A1、B2等。
单元格可以包含各种类型的数据，如数字、文本、日期等。
1.3 工作表Excel工作簿由多个工作表组成。
每个工作表包含若干行和列的单元格组合。
我们可以在工作簿中创建多个工作表，以便更好地组织和管理数据。
1.4 快捷键和操作技巧熟练掌握Excel的快捷键和操作技巧可以提高工作效率。
一些常用的快捷键包括复制、粘贴、剪切、撤销等。
此外，使用数据筛选、排序、填充等功能也可以帮助我们更好地处理和展示数据。
2. Excel函数Excel提供了丰富的函数库，用于各种金融计算。
这些函数可以用于税务计算、利率计算、财务分析等。
以下是一些常用的Excel函数：•SUM：计算指定单元格范围内的和。
•AVERAGE：计算指定单元格范围内的平均值。
•MAX：计算指定单元格范围内的最大值。
•MIN：计算指定单元格范围内的最小值。
•PMT：计算贷款的付款额。
使用这些函数可以简化金融计算的过程，并提高计算的准确性和效率。
3. 数据导入和清洗在金融分析中，数据的质量对结果的准确性至关重要。
用Excel进行金融行业投资组合分析和风险管理的六种方法投资组合分析和风险管理是金融行业中非常重要的任务。
为了提高投资决策的准确性和效率，许多金融从业者选择使用Excel这一强大的工具进行分析和管理。
本文将介绍用Excel进行金融行业投资组合分析和风险管理的六种方法。
一、历史回报率计算在进行投资组合分析之前，我们需要先计算每支投资工具的历史回报率。
通过Excel的内置函数和数据导入功能，可以轻松地获取各种金融工具的历史价格数据并进行计算。
将历史价格数据输入Excel中的工作表，然后使用平均函数等函数计算每支工具的历史回报率。
二、风险评估和分散投资在投资组合分析中，风险评估是一个不可或缺的环节。
通过Excel的统计函数，我们可以计算出每支投资工具的风险指标，例如标准差、方差和协方差。
然后，根据投资期望目标和风险承受能力，选择适当的投资组合，实现分散投资。
三、收益-风险分析通过将每支投资工具的收益和风险指标输入Excel中的散点图或折线图，我们可以直观地分析不同投资组合的收益-风险特征。
在图表中，我们可以清楚地看到不同投资组合的位置和变化规律，从而作出更明智的投资决策。
四、马科维茨均值-方差模型马科维茨均值-方差模型是投资组合分析中的经典方法。
通过Excel的线性规划插件或自定义函数，我们可以实现该模型的计算和优化。
通过输入各支投资工具的预期收益率、协方差矩阵和目标收益率，Excel可以自动计算出最优的投资组合权重，并给出有效前沿曲线。
五、价值暴露和敞口控制在风险管理中，价值暴露和敞口控制是非常重要的步骤。
通过Excel的逻辑函数和筛选功能，我们可以对投资组合进行敞口的检查和管控。
通过设定敞口限制和预警条件，Excel可以及时发出警报，并提供相应的调整建议，从而帮助我们更好地管理风险。
六、场景分析和压力测试场景分析和压力测试是评估投资组合鲁棒性和抗风险性的重要手段。
通过Excel的数据表和数据透视表功能，我们可以方便地建立各种场景和压力测试模型。
excel证券使用技巧Excel证券使用技巧Excel 是一种广泛使用的电子表格软件，它不仅可以进行简单的数据输入和计算，还可以应用于复杂的证券分析和投资决策过程。
以下是一些 Excel 证券使用技巧，可以帮助投资者更好地利用 Excel 进行证券分析和决策。
1. 数据导入：Excel 可以导入从证券交易所或金融网站获取的证券数据。
使用数据->获取外部数据功能，可以直接从网站上下载证券数据，并自动更新数据。
2. 数据清晰：一旦导入数据，可以使用 Excel 的筛选和排序功能来整理和清晰化数据。
使用筛选功能可以根据特定的条件过滤数据，使用排序功能可以根据某列的数值大小对数据进行排序。
3. 数据分析：Excel 提供了广泛的数据分析功能，可以帮助投资者进行证券分析和决策。
例如，可以使用 Excel 的图表功能来绘制股票价格和成交量的趋势图，以及其他技术指标的图表。
4. 公式运算：Excel 的公式功能可以应用于证券分析和决策。
例如，可以使用 Excel 的平均函数来计算股票的平均价格，使用标准差函数来计算股票价格的波动性等。
5. 数据可视化：使用 Excel 的条件格式功能可以根据特定的条件对证券数据进行格式化，以便更好地可视化数据。
例如，可以将盈利数据以不同的颜色显示，以便更好地观察股票的盈利能力。
6. 应用宏：Excel 的宏功能可以用于自动化一系列的操作。
例如，可以编写一个宏来自动下载证券数据并进行数据分析，从而节省时间和精力。
7. 透视表：Excel 的透视表功能可以用于对证券数据进行汇总和分析，以便更好地了解证券的情况。
透视表可以对数据进行分类、汇总和计算，并根据需要进行筛选和排序。
8. 数据连接：Excel 可以连接到外部数据源，并与其他软件或数据库进行交互。
例如，可以将 Excel 的证券数据与交易软件或金融数据库进行连接，从而实现数据的自动更新和交互。
总而言之，Excel 是一个强大的工具，可以帮助投资者进行证券分析和决策。
基于excel的金融学原理Excel是一款功能强大的电子表格软件，可以用于进行各种金融学计算和分析。
下面将介绍一些基于Excel的金融学原理，涉及到投资组合理论、资本资产定价模型和衍生品定价等内容。
1.投资组合理论投资组合理论是金融学中的一个基本原理，用于确定在给定风险下的最佳投资组合。
这个理论的核心概念是通过资产之间的相关性来实现风险的分散，从而使投资组合风险最小化。
Excel可以用来计算投资组合的风险和预期收益，并通过求解最小方差组合、有效边界等方法来确定最佳投资组合。
2.资本资产定价模型资本资产定价模型（CAPM）是用于计算资产预期回报的一个模型。
这个模型表明资产的回报是由市场回报和个别风险之间的正相关关系决定的。
Excel可以用来计算资产的预期回报，并通过β系数来度量个别风险对总风险的贡献。
通过CAPM模型，可以评估一个投资项目的风险和回报，并决定是否值得进行投资。
3.衍生品定价衍生品是一种基于基础资产的金融工具，其价值是从基础资产中派生出来的。
Excel可以用来计算各种衍生品的定价，包括期权、期货和利率互换等。
在衍生品定价中，Black-Scholes模型和Binomial模型是最常用的定价模型。
Excel提供了一系列的函数和工具，可以帮助进行衍生品的定价计算。
4.统计分析在金融学中，统计分析是一个重要的工具。
Excel提供了各种统计函数和工具，可以用于计算和分析金融数据的统计指标，如均值、标准差、相关系数等。
通过统计分析，可以帮助了解金融市场的特征和行为。
5.金融建模金融建模是基于数学和统计的金融学方法，用于对金融市场和金融产品进行建模和分析。
Excel可以用来构建各种金融模型，如股票价格模型、利率模型和风险模型等，并进行模拟和分析。
通过金融建模，可以帮助预测金融市场的走势和评估金融产品的风险。
总结起来，Excel作为一种功能强大的工具，可以用于实施各种金融学原理，包括投资组合理论、资本资产定价模型和衍生品定价等。
在Excel中进行金融建模和分析的方法第一章：Excel的基本功能1.1. 单元格和公式在Excel中，单元格是最基本的组成单位，用于存储数据。
我们可以通过在单元格中输入公式实现各种数学运算、逻辑判断和文本处理等功能。
公式可以使用基本的四则运算符、函数和引用其他单元格的内容。
1.2. 数据筛选和排序Excel提供了强大的筛选和排序功能，可以帮助我们快速定位和筛选需要的数据。
通过设置筛选条件，可以快速找到符合要求的记录。
同时，我们还可以对数据进行升序或降序排序，以便更好地进行分析和比较。
第二章：金融模型的建立2.1. 基本的财务模型财务模型是金融分析的基础，主要用于预测和分析企业的财务状况和经营能力。
在Excel中，我们可以通过建立各种财务模型，如财务报表模型、财务比率模型等，来对企业进行全面的财务分析。
2.2. 资产定价模型资产定价模型是金融建模的重要内容，用于估计资产的内在价值。
在Excel中，我们可以使用CAPM（Capital Asset Pricing Model）等模型，计算资产的预期收益率，并进行风险评估和投资组合优化。
2.3. 期权定价模型期权是金融衍生品中常见的一种，其价值受到多种因素的影响。
通过使用Black-Scholes期权定价模型，我们可以在Excel中计算期权的理论价格，为投资者和交易员提供相应的参考和决策依据。
第三章：金融数据分析3.1. 数据导入和清洗金融数据通常来自各种来源，如证券交易所、财经新闻网站等。
在Excel中，我们可以通过导入外部数据功能，将这些数据导入到工作表中进行分析。
同时，为了确保数据的准确性和一致性，我们还需要进行数据清洗、去重和格式化处理等。
3.2. 数据可视化数据可视化是金融数据分析的重要手段之一，能够直观地展示数据的变化趋势和关联关系。
在Excel中，我们可以使用各种图表类型，如折线图、柱状图和散点图等，对金融数据进行可视化展示，帮助我们更好地理解和解释数据。
capm模型检验（Excel版,含推导,分析,数据,结论）CAPM模型其实质是讨论风险与收益的关系,其基本的验证思路是考察是否只有股票β系数表)与其收益有关,而且这两者为线性正相关。
它是对股票收益率的事前预测，把其变成类似计量经济学归的表达式也就是CAPM模型的事后形式，本次通过EVIEWS进行回归分析验证CAPM模型在此股票上是否有。
见下式：E(Rj)-Rf=(E(Rm)-Rf)βj (1)//这是CAPM的原本模型股票名称：股票代码：Variable Coefficient Std. Error1.三一重工600031X-2.4479543.243226C-0.1942910.15955R-squared0.015579 Mean dependent varAdjusted R-squared-0.011766 S.D. dependent varS.E. of regression0.163904 Akaike infSum squared resid0.967127 Schwarz criterionLog likelihood15.82955 F-statisticDurbin-Watson stat 1.012855 Prob(F-statistic)Variable Coefficient Std. Error2.航天机电600152X-2.2681723.287982C-0.1761940.161752R-squared0.013046 Mean dependent varAdjusted R-squared-0.014369 S.D. dependent varS.E. of regression0.166166 Akaike infSum squared resid0.994004 Schwarz criterionLog likelihood15.30874 F-statisticDurbin-Watson stat 1.060726 Prob(F-statistic)3.四川路桥600039Variable Coefficient Std. ErrorX-2.139265 3.276311C-0.1773710.161178R-squared0.011704 Mean dependent varAdjusted R-squared-0.015748 S.D. dependent varS.E. of regression0.165576 Akaike infSum squared resid0.98696 Schwarz criterionLog likelihood15.44387 F-statisticDurbin-Watson stat 1.044981 Prob(F-statistic)4.凤凰光学600071Variable Coefficient Std. ErrorX-2.135465 3.263153C-0.1792890.16053R-squared0.011756 Mean dependent varS.E. of regression0.164911 Akaike infSum squared resid0.979048 Schwarz criterionLog likelihood15.59678 F-statisticDurbin-Watson stat 1.050367 Prob(F-statistic) 5.中金黄金600489Variable Coefficient Std. ErrorX-2.892332 3.228677C-0.2173140.158834R-squared0.021806 Mean dependent varAdjusted R-squared-0.005366 S.D. dependent varS.E. of regression0.163169 Akaike infSum squared resid0.95847 Schwarz criterionLog likelihood16.0004 F-statisticDurbin-Watson stat 1.029506 Prob(F-statistic) 6.方兴科技600552Variable Coefficient Std. ErrorX-2.436679 3.288756C-0.191880.16179R-squared0.01502 Mean dependent varAdjusted R-squared-0.012341 S.D. dependent varS.E. of regression0.166205 Akaike infSum squared resid0.994472 Schwarz criterionLog likelihood15.2998 F-statisticDurbin-Watson stat 1.115256 Prob(F-statistic) 7.江苏舜天600827Variable Coefficient Std. ErrorX-2.552558 3.254945C-0.1947030.160127R-squared0.016796 Mean dependent varAdjusted R-squared-0.010515 S.D. dependent varS.E. of regression0.164497 Akaike infSum squared resid0.974129 Schwarz criterionLog likelihood15.69249 F-statisticDurbin-Watson stat 1.018081 Prob(F-statistic) 8.凯乐科技600260Variable Coefficient Std. ErrorX-3.110213 3.242644C-0.2230970.159521R-squared0.024918 Mean dependent varS.E. of regression0.163875 Akaike infSum squared resid0.966781 Schwarz criterionLog likelihood15.83636 F-statisticDurbin-Watson stat 1.045083 Prob(F-statistic) 9.古越龙山600059Variable Coefficient Std. ErrorX-2.535157 3.252968C-0.1968760.160029R-squared0.016591 Mean dependent varAdjusted R-squared-0.010726 S.D. dependent varS.E. of regression0.164397 Akaike infSum squared resid0.972946 Schwarz criterionLog likelihood15.71558 F-statisticDurbin-Watson stat 1.012261 Prob(F-statistic) 10.鄂尔多斯600295Variable Coefficient Std. ErrorX-2.574677 3.241956C-0.1976850.159488R-squared0.017218 Mean dependent varAdjusted R-squared-0.010081 S.D. dependent varS.E. of regression0.16384 Akaike infSum squared resid0.96637 Schwarz criterionLog likelihood15.84443 F-statisticDurbin-Watson stat 1.035606 Prob(F-statistic)t-Statistic Prob.-0.754790.4553-1.2177450.2312 Mean dependent var-0.075549 S.D. dependent var0.162949e info criterion-0.727871 Schwarz criterion-0.641682 F-statistic0.569708 Prob(F-statistic)0.455285t-Statistic Prob.-0.6898370.4947-1.0892850.2833 Mean dependent var-0.066172 S.D. dependent var0.164985e info criterion-0.70046 Schwarz criterion-0.614271 F-statistic0.475875 Prob(F-statistic)0.49472t-Statistic Prob.-0.6529490.5179-1.1004710.2784 Mean dependent var-0.073602 S.D. dependent var0.164288e info criterion-0.707572 Schwarz criterion-0.621383 F-statistic0.426343 Prob(F-statistic)0.517937t-Statistic Prob.-0.6544170.517-1.1168560.2715 Mean dependent var-0.075704有股票的系统风险(用β系数代事前预测，把其变成类似计量经济学回分析验证CAPM模型在此股票上是否有效e info criterion-0.71562 Schwarz criterion-0.629431 F-statistic0.428262 Prob(F-statistic)0.517002t-Statistic Prob.-0.8958260.3763-1.3681830.1797 Mean dependent var-0.077016 S.D. dependent var0.162733e info criterion-0.736863 Schwarz criterion-0.650674 F-statistic0.802504 Prob(F-statistic)0.376297t-Statistic Prob.-0.7409120.4636-1.1859820.2434 Mean dependent var-0.073684 S.D. dependent var0.165189e info criterion-0.699989 Schwarz criterion-0.613801 F-statistic0.548951 Prob(F-statistic)0.463552t-Statistic Prob.-0.7842090.438-1.2159330.2319 Mean dependent var-0.070886 S.D. dependent var0.163639e info criterion-0.720657 Schwarz criterion-0.634469 F-statistic0.614984 Prob(F-statistic)0.438047t-Statistic Prob.-0.959160.3439-1.3985380.1705 Mean dependent var-0.07223e info criterion-0.72823 Schwarz criterion-0.642041 F-statistic0.919987 Prob(F-statistic)0.343876t-Statistic Prob.-0.7793360.4409-1.2302490.2266 Mean dependent var-0.073903 S.D.dependent var0.163522e info criterion-0.721873 Schwarz criterion-0.635684 F-statistic0.607365 Prob(F-statistic)0.440875t-Statistic Prob.-0.7941740.4323-1.2394980.2232 Mean dependent var-0.072795 S.D. dependent var0.163021e info criterion-0.728654 Schwarz criterion-0.642465 F-statistic0.630712 Prob(F-statistic)0.432299。
实验报告李锦梅201130980113学院名称理学院专业班级统计学1班提交日期评阅人____________评阅分数____________证券实验四收益与风险与EXCEL金融计算【实验目的】理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序；【实验条件】1、个人计算机一台，；2、Excel软件。
【知识准备】理论知识：课本第三章收益与风险实验参考资料：《金融建模—使用EXCEL和VBA》电子书第三章，3.1,3.2【实验项目内容】运用Excel进行股票投资以及投资组合收益率与风险的计算；【实验项目原理】总结证券的收益与风险的计量：简单投资组合的投资收益风险计算：• 投资组合的收益率等于成分资产收益率按其比例加权平•两项资产组成的投资组合，其方差的计算公式是：• 三项资产的投资组合的方差计算公式是： 【实验项目步骤与结果】1.单只股票收益率与风险的计算A ．打开数据文件“实验四组合的回报与风险.xls”选择“1单个股票回报与风险计算实例”子数据表格；B ．期望收益的计算（完成表格中黄色标记的单元格的计算）；(1)计算股票的每月收益率,在C3单元格定义=(B3-B4)/B4如下图（2）计算股票的月期望收益率，在F5单元格定义=AVERAGE($C$3:$C$62)（3）计算股票的年收益率，在G5单元格定义=F5*12C 、方差与标准差的计算在EXCELL 中方差，样本方差，标准差，样本标准差分别p σ=用VAR、STEDV。
可以通过EXCELL中的工具栏[fx]/[统计]。
(1)计算月度股票收益率的方差,在F6单元格定义=VAR($C$3:$C$62)（2）计算月度股票收益率的标准差，在F7单元格定义=SQRT(F6)（3）计算年度股票的年收益率方差与标准差，在G6单元格定义=F6*12在G7单元格定义=sqrt（F7）D．通过比较中国股市数据和美国股市数据总结收益与风险的关系答：通过比较发现中国股市数据的月度预期收益（1.73%）比美国股市数据（0.65%）大，与此同时月度的方差（1.147%）和标准差也比美国(0.38%)的大，说明中国的总体收益和风险都比美国大。
实验报告证券投资分析学院名称专业班级提交日期2013年12月5评阅人____________评阅分数____________证券实验二 证券投资组合，CAPM 检验与EXCEL 金融计算 【实验目的】1、理解资产组合收益率和风险的计算方法，熟练掌握收益率与风险的计算程序；2、进一步理解最优投资组合模型，并据此构建多项资产的最优投资组合；3、进一步理解资本资产定价模型（CAPM ）的基本思想以及在股票分析中的应用。
【实验条件】1、个人计算机一台，预装Windows 操作系统和浏览器；2、计算机通过局域网形式接入互联网；3、统计软件或者Excel 软件。
第一部分实验内容：运用Excel 进行股票投资以及投资组合收益率与风险的计算；【知识准备】理论知识：课本第三章 收益与风险，第四章 投资组合模型，第五章 CAPM 实验参考资料：《金融建模—使用EXCEL 和VBA 》电子书 第三章，第四章，第五章【实验项目内容】1.单只股票收益率与风险的计算2.两资产股票收益率与风险的计算3.组合收益率样本均值与样本方差的计算4.组合投资分散风险的作用是不是十分明显？第二部分实验内容：运用Excel 进行最优投资组合的求解； 【知识准备】理论知识：课本第三章 收益与风险，第四章 投资组合模型，第五章 CAPM 实验参考资料：《金融建模—使用EXCEL 和VBA 》电子书 第三章，第四章，第五章 【实验项目内容】1．.打开2组合优化模型。
Xls 文档，翻到“用规划求解计算最优组合”子数据表；2．调用规划求解功能进行求解。
点击“工具”在下拉菜单点击“规划求解”，如没有此选项说明需要加载规划求解后才能使用，如何加载见实验补充文档“EXCEL 规划求解功能的安装”。
【实验项目原理】投资组合是指将资金配置在一种以上的证券上，获取在风险可控下最大化回报的投资方式。
1、两种证券组合的收益和风险假设有两种证券A 、B ，投资者将资金按照A X 、B X 的比例构建证券组合，则该证券组合的收益率可以表示为：P A A B BR X R X R =+，其中A X +B X =12、两种证券组合的预期收益率：()()()P A A B B E R X E R X E R =+两种证券组合的预期收益率是两种证券预期收益率的加权平均，权数是投资在该证券上的资金比例。
在没有卖空的情况下，证券组合的期望收益率总是依据投资于两种证券的资金比例在两种证券期望收益率之间变动，即既不会超过其中期望收益率的大者，也不会小于其中期望收益率的小者。
因此，在没有卖空的情况下，证券组合并不会提高投资收益率水平。
但一旦发生卖空行为，证券组合收益率的波动范围将极大增加，因为投资者可以通过卖空期望收益率较低的证券品种来扩大证券组合的期望收益率。
3、两种证券组合的风险：222222(,)p A A B B A B A B X X X X Cov R R σσσ=++证券A 、B 收益率之间的协方差：1(,)[()][()]nA B i A A B B i Cov R R P R E R R E R ==--∑协方差主要是衡量两只证券收益率变动的相关性：如果协方差为正，说明证券A 、B 收益率变动正相关；如果协方差为负，说明证券A 、B 收益率变动负相关；如果协方差为零，说明证券A 、B 收益率变动不相关。
相关系数是协方差经标准化之后衡量两种证券收益率变动相关性及相关程度的指标，其计算公式如下：(,)/AB A B A B Cov R R ρσσ= ， 11AB ρ-≤≤①相关系数越大，越接近1，说明两只证券收益率变动的正相关性越强；②相关系数等于1时，称两种证券收益率变动完全正相关；③相关系数越小，越接近-1，说明两只证券收益率变动的负相关性越强；④相关系数等于-1时，称两种证券收益率变动完全负相关；⑤相关系数等于0时，称两种证券收益率变动完全不相关。
当相关系数越小时，投资组合的风险越低。
而且，只要相关系数不为1，则两个证券的组合就可以或多或少降低风险，而不等比例地降低收益，即可以达到分散风险而不等比例低降低收益的好处。
这就是分散化投资理念成立的理论基础。
4、两种证券组合的可行集可行集又称机会集合，指由两种证券所构建的全部证券组合的集合。
由于任何一个确定的组合都可以求出其预期收益率和标准差，可以在以标准差为横坐标、期望收益率为纵坐标的坐标系中用一个点来表示，因而两种证券组合的可行集，就可以用所有组合的期望收益率和标准差构成的集合来表示。
两个证券构成的组合的可行集是一条曲线，随着相关系数的不断变小，这条曲线的弯曲程度越来越高； 当相关系数达到最小值-1时，这条曲线弯曲程度达到极限，是一条折线，从函数角度来看，是一个有交点的分段函数； 当相关系数逐渐变大时，这条曲线的弯曲程度越来越小，直至当相关系数等于+1时，曲线没有任何弯曲，即已经变成为直线。
5、两种风险证券组合的有效集证券组合的可行集表示了所有可能的证券组合，其为投资者提供了一切可行的投资机会，投资者现在需要做的是，在可行集中选择满意的证券组合即有效率的证券组合进行投资，这就是证券组合的有效集问题。
证券组合的有效集，又称为有效边界，是指在期望收益率一定时其风险（即标准差）最低的证券组合，或者指在风险一定时其期望收益率最高的证券组合。
有效集是可行集的一个子集。
从两种证券组合的可行集中，可以很容易找到证券组合的有效集：当两种证券收益率相关系数在-1到+1之间时，两种证券组合的有效集是其可行集的上半部分，即从最小方差沿着曲线向上方延伸。
当两种证券收益率变动完全正相关时，有效集就是可行集。
当两种证券收益率变动完全负相关时，有效集是一条射线。
6、多种资产组合的可行集和有效集从多种资产组合的可行集即图5-6中的ABCD区域内，可以发现，多种证券组合的有效集是区域左边界的一段即BCD（又称上边界）曲线。
这里B点是一个特殊的位置，它是上边界和下边界的交汇点，这一点所代表的组合在所有可行组合中方差最小，因而被称为最小方差组合。
7、无风险资产和多种风险资产组合的可行集和有效集资本分配线是表明风险资产和无风险资产之间的各种可行的风险-收益组合的图形。
资本分配线在投资市场的现实意义是，投资者通过调节持有的现金或国债等无风险资产的比例，可以达到控制风险的目的。
在所有的资本分配线中，由风险证券M和无风险证券F进行再组合所形成的可行集(也是有效集)射线CAL（M）的斜率最大。
这一条可行集射线被称为最优资本分配线，相应的投资组合M被称为切点组合。
切点M是通过无风险证券所对应的点F作n种风险证券有效集的切线而得到的。
内容提要⊙证券组合的期望收益率是各证券期望收益率的加权平均，其权数就是投资于各证券的资金比例。
证券组合的方差不但包含了原先个别证券的风险（有权数进行调整），还隐含个别证券之间相互影响所带来的风险。
⊙协方差测度两只证券回报率之间的互动性，实际上反映了系统风险对证券收益率的影响。
即两种证券回报率之间一起变动的方向和程度。
⊙两种证券组合的可行集可以是直线，或者折线，抑或双曲线，具体形状取决于两只证券的相关系数。
三种以上证券组合的可行集是一个二维实体区域。
从可行集中，可以找到有效集，理性投资者的投资组合都应该在有效集上。
⊙随着组合中证券数量的增加，组合的风险呈现明显降低的趋势。
但在组合证券数量达到15只以上时，增加证券数量，降低风险的效果越来越不明显。
一个充分分散的组合可以消除所有的非系统风险，其风险大小最后完全由系统风险所决定。
【实验项目步骤与结果】第一部分实验内容：实验步骤见实验内容1.单只股票收益率与风险的计算A．打开数据文件“实验二之一组合的回报与风险.xls”选择“1单个股票回报与风险计算实例”子数据表格；B．期望收益的计算（完成表格中黄色标记的单元格的计算）；(1)计算股票的每月收益率,在C3单元格定义=(B3-B4)/B4如下图(2）计算股票的月期望收益率，在F5单元格定义=AVERAGE($C$3:$C$62)(3）计算股票的年收益率，在G5单元格定义=F5*12C、方差与标准差的计算(1)计算月度股票收益率的方差,在F6单元格定义=VAR($C$3:$C$62)(2）计算月度股票收益率的标准差，在F7单元格定义=SQRT(F6)(3)计算年度股票的年收益率方差与标准差，在G6单元格定义=F6*12*12在G7单元格定义=F7*12D．通过比较中国股市数据和美国股市数据总结收益与风险的关系结果如下图：比较美国股市数据总结收益与风险的关系可见中国的回报率比较高，无论是月度的收益率以及年度的收益率，但是相对而言中国的标准差以及方差都较美国而言都比较大，说明风险较大，所以中国的股市比美国股市而言，高收益而且高风险2.两资产股票收益率与风险的计算A．打开数据文件“实验二之一组合的回报与风险.xls”选择“2两资产组合回报与风险计算实例”子数据表格；B.组合协方差的计算，在D77单元格定义=COVAR(E5:E64,F5:F64)*12C.组合相关系数的计算，在D78单元格定义=CORREL(E5:E64,F5:F64)D.组合收益率样本均值与样本方差的计算在D78单元格定义=(D73+E73)/2在D79单元格定义=D74*D71+E74*E71在D80单元格定义=D74^2*D72+2*D74*E74*D77+E74^2*E72在D81单元格定义=D81^(1/2)E.注意比较两组样本的相关系数F.试总结组合相关系数与投资组合分担风险的关系结果如下图：（1）组合一无论从预期回报以及组合方差以及标准差上都比组合二要高，说明了组合一的高风险高回报（2）两个组合的协方差都大于0，说明证券A，B收益变动正相关（3）组合一的相关系数接近于1并且大于组合二，可以看出组合一证券收益率变动的正相强于二，组合二的投资风险较低，组合一的收益较高，这个与我们在第一点得出的结论相符合第二部分实验内容：运用Excel进行最优投资组合的求解；（1）调用工具中的规划求解得到结果（2）在规划求解选项卡里面选择“选项”，再选择“非负”再运行一次，比较两次返回的投资比例值的正负。
（3）投资比例是负值说明风险已太大【实验心得】1.了解了单只股票收益率与风险的计算2.学会了两资产股票收益率与风险的计算3.知道了组合收益率样本均值与样本方差的计算4.更深层次的理解了组合投资分散风险的作用5. 对EXCEL规划求解功能有了进一步的认识和掌握6.经过各项操作更加感觉EXCEL的功能之强大!【教师评语与评分】。
