运用ExcelSolver构建最优投资组合王世臻

如何⽤Excel的规划求解功能实现⼀个投资组合在均值-⽅差法下的最优化？⽂中的计算⽅法参考了Samir Khan的“Mean-Variance Optimization with Transaction Costs”。

⼀般来讲，⼀个投资组合中各项资产的价格变动特征是不⼀样的，⽐如有的资产价格波动率很⾼，但有可能带来更⾼的回报；⽽其他的资产会在⼤盘下跌之时反⽽上涨。

在构建投资组合时通过精⼼挑选具有不同价格波动特点的资产，就可以在确保收益最⼤化的同时实现投资组合的风险最⼩化，⽽均值-⽅差法就可以实现这⼀点。

均值-⽅差法是⼀种⽐较传统的优化投资组合的做法，来源于美国经济学家、1990年诺贝尔经济学奖获得者Harry Markowitz于1950年代创⽴的基于均值-⽅差模型现代组合投资理论。

均值-⽅差模型的理论是解决投资者如何从所有可能的证券组合中选择⼀个最优组合的问题。

投资者的决策⽬标通常有两个：尽可能⾼的收益率和尽可能低的不确定性风险。

即先确⽴⼀个⽬标收益率，然后确定各项资产在投资组合中的权重，使整个投资组合的风险值即整个组合的价格波动的⽅差值最低，最终使这两个相互制约的⽬标达到最佳平衡。

本⽂的主题就是探讨如何⽤Excel的规划求解功能实现⼀个由四只股票构成的投资组合在均值-⽅差法下的最优化，即价格波动风险最低，回报率最⾼。

由于在对现有投资组合中各项资产的⽐率进⾏调整时交易成本会成为⼀个很⼤的影响组合回报率的因素，因此为贴近实际操作，⽂中的案例考虑到了交易成本，并将资产权重每变动1%的交易成本设定为0.1%；四只股票的初始权重均为25%，投资组合的⽉度预期回报率为1%。

1、按以下格式设置Excel表格2、通过雅虎财经⽹站下载美孚⽯油公司XOM、卡特彼勒公司CAT、可⼝可乐公司KO和波⾳公司BA在2018年2⽉1⽇⾄2019年2⽉1⽇这1年间的⽉度收盘价。

3、⽤LN()函数计算4只股票的⽉度回报率，（）内为⽉度收盘价所在的单元格4、⽤AVERAGE()函数计算这四只股票⽉度回报率的均值5、形成协⽅差矩阵。

使用Excel进行股票投资组合分析第一章：股票投资组合分析的概念和重要性股票投资组合分析是一种对不同股票进行定量和定性的评估方法，在投资决策中起到重要的作用。

通过股票投资组合分析，投资者可以评估不同股票的风险和收益，以便做出更明智的投资决策。

在这一章节中，我们将探讨股票投资组合分析的概念和重要性，并介绍Excel在分析中的应用。

第二章：Excel的基本功能和常用公式在进行股票投资组合分析之前，我们需要熟悉Excel的基本功能和常用公式。

Excel是一款功能强大的电子表格软件，可以帮助我们进行各种计算和数据分析。

在这一章节中，我们将介绍Excel 的基本功能，如单元格操作、数据输入和格式化等，并列举一些常用的公式，如计算均值、标准差和相关系数等。

第三章：股票数据的获取和整理在进行股票投资组合分析之前，我们需要获取和整理相关的股票数据。

在这一章节中，我们将介绍如何使用Excel从网络上获取股票数据，并演示如何将数据整理成适合分析的格式。

同时，我们也会介绍一些常用的数据处理技巧，如数据筛选、排序和透视表等。

第四章：股票的风险和收益评估股票的风险和收益是股票投资组合分析的核心内容。

在这一章节中，我们将介绍如何使用Excel对股票的风险和收益进行评估。

我们将介绍常用的风险衡量指标，如标准差、贝塔系数和夏普比率，并演示如何使用Excel进行计算和分析。

第五章：股票组合构建和优化在进行股票投资组合分析时，我们通常会选择多个股票构建一个投资组合。

在这一章节中，我们将介绍如何使用Excel进行股票组合的构建和优化。

我们将介绍常用的组合优化方法，如马科维茨模型和均值-方差模型，并演示如何使用Excel进行计算和优化。

第六章：风险控制和资产配置在进行股票投资组合分析时，风险控制和资产配置是非常重要的。

在这一章节中，我们将介绍如何使用Excel进行风险控制和资产配置。

我们将介绍常用的风险控制方法，如保险策略和动态平衡策略，并演示如何使用Excel进行计算和分析。

多项目投资组合优化决策的EXCEL模型设计作者：吴强来源：《环球市场》2019年第22期摘要：投资组合理论是公司理财的重要理论，在确定资金限额，项目之间的关联、互斥、预备及资金成本等多方面的因素下，投资人员很难有效的进行分析择优。

本文通过多个方案组合筛选缩小方案范围，约束条件过滤最优解组合，精确求出最优方案组合集，实现投资组合理论与实践应用模型的结合。

关键词：多项目投资组合;资金成本;约束条件;优化模式设计;Excel模型一、引言投资是企业（投资者）为适应今后生产经营过程中的长远需要而投入于固定资产增加、扩建、更新以及资源的开发与利用等方面的资金。

投资方式很多，将投资的品种、投资的预期收益、投资的时间等投资因素进行系统化和表格化的整理，可以形成一系列的备选投资方案。

投资决策是指对一个投资项目的各种方案的投资支出和投资后的收入进行对比分析，以选择投资效益最佳的方案。

在项目投资关系、资金限额及资金成本条件下，投资者经常会遇到投资项目最优投资组合及投资安排问题，投资者不仅要考虑各项目投资额大小，还要考虑项目投资顺序和投资最大收益的判断。

本文通过Excel多项目投资组合优化决策的模型设计，能够在此基础上进行扩展，合理选择企业的最优组合集，避免规划求解的某些弊端，计算过滤出一个最优投资组合集。

再通过企业的特定偏好，选择最适合自己的投资组合。

二、模型搭建（一）相关资料假设某集团公司有7个项目可以进行投资，资金限额为1500万元，资本成本为小于等于500万元利率为8%，大于500万元并小于等于1000万元的资金成本是g%，大于1000万并小于等于1500万元的资金成本是10%;7个项目分别是A、B、C、D、E、F、G，项目A、项目B和项目C为三选一项目，即这三个项目中必须选一个，且只能选一个;项目D是项目E的预备项目，即只有选的D以后才能选择E项目;项目F和项目G为互斥项目，即项目F和项目G 只能选一个项目。

如何使用Excel进行投资组合管理和风险分析第一章简介投资组合管理和风险分析是金融领域中非常重要的一部分。

它们涉及到对投资组合的构建、监控和调整，以及对投资风险的评估和控制。

在这一章节中，我们将介绍使用Excel进行投资组合管理和风险分析的基本方法和技巧。

第二章数据准备在进行投资组合管理和风险分析之前，首先需要准备好所需的数据。

这包括历史股票和债券价格、收益率以及市场指数的数据。

Excel提供了强大的数据处理和分析功能，可以帮助我们方便地获取和整理这些数据。

第三章构建投资组合在构建投资组合时，我们需要考虑多个因素，包括风险偏好、期望收益、资产类别和权重分配等等。

Excel提供了诸多函数和工具，如求解器、约束条件和目标函数等，可以帮助我们优化投资组合的效果，并找到最佳的资产配置方案。

第四章投资组合监控与调整投资组合管理并不是一次性的活动，而是一个持续的过程。

我们需要定期监控投资组合的表现，并根据市场情况进行调整。

Excel可以帮助我们实时跟踪和分析投资组合的收益率、波动性以及其它指标，以便及时做出决策。

第五章风险评估与控制风险分析是投资组合管理过程中的重要一环。

Excel提供了多种风险评估模型和工具，如VaR（风险价值）模型、条件风险模型和蒙特卡洛模拟等，可以帮助我们对投资组合的风险水平进行评估和控制，并制定相应的风险管理策略。

第六章数据可视化数据可视化是理解和解释投资组合管理和风险分析结果的重要手段。

Excel提供了丰富的图表和图形功能，可以帮助我们将数据直观地呈现出来，并观察其变化趋势和关联关系。

通过数据可视化，我们可以更加清晰地了解投资组合的表现和风险状况。

第七章实例分析在这一章节中，我们将通过一个实例来演示如何使用Excel进行投资组合管理和风险分析。

我们将选取一些具有代表性的资产，并根据历史数据进行投资组合的构建和分析。

通过实例的分析，我们可以更好地理解和应用Excel在投资组合管理和风险分析中的作用。

学会使用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中，并进行合理的整理。

投资者可以通过在线财经平台或财经网站下载股票的历史价格数据和财务报表数据，然后将这些数据导入Excel表格。

在Excel中，可以使用“导入外部数据”功能将下载的数据导入表格，或者直接将数据从复制粘贴到Excel中。

确保导入的数据包括股票的日期、开盘价、收盘价、最高价、最低价等信息，以及涉及股票财务分析的各种指标，如营业收入、净利润、资产负债表等。

导入数据后，接下来需要进行数据整理和格式化工作。

可以使用Excel的筛选功能，按照需要筛选出特定时间段的数据，或者按照股票名称筛选出特定股票的数据。

此外，还可以使用Excel的排序功能对数据进行排序，以便更好地观察和分析数据。

二、技术指标分析技术指标是评估股票价格走势和市场趋势的重要工具。

使用Excel可以对股票的历史价格数据进行技术指标分析，以辅助投资者的决策。

常用的技术指标包括移动平均线、相对强弱指数（RSI）、布林带等。

通过在Excel中创建相应的公式和图表，可以计算和展示这些技术指标。

例如，可以通过计算收盘价的移动平均线来判断股票的趋势。

使用Excel的“AVERAGE”函数可以计算出一定时间内的移动平均价格，然后通过将计算结果绘制成折线图，可以清晰地看到股票价格的均线走势。

此外，还可以使用Excel的筛选功能和条件格式化功能，根据技术指标的数值范围将股票的价格进行分类，以便投资者更好地观察和分析数据。

运用Excel Solver构建最优投资组合王世臻（20121563）黄燕宁（20121941）王爽（20125204）汪雅娴（20121336）杨瑞(20121799)潘晓玉（20123384）本文运用马科维茨投资组合优化程序来说明股票市场的分散化投资，借助Excel Solver构建最优投资组合。

我们从Resset金融研究数据库中从电子信息行业选取启明星辰等40只股票2010年至2013年的月收益率以及对应的无风险收益率等数据。

来源于Resset金融研究数据库二、模型设定我们可以设第i 只股票的期望风险溢价为i (r )E ，第i 只股票的权重为i w ，整体的期望风险溢价为p (r )E ，标准差为p σ，夏普比率为p S ，因此我们可以得到组合的期望风险溢价为：11224040()()()()()p i i E r w E r w E r w E r w E r =+++++(1)整体的标准差为：124040[(,)]11i j i j p w w Cov r r i j σ=∑∑==(2) 夏普比率为： p (r )p pE S σ= (3)三、构建组合我们分卖空和未卖空两种情况分别进行讨论： （一）允许进行卖空在这种情况下，为了找出最小的方差组合，我们以(2)式为目标函数，以4011i i w ==∑为约束条件运用Excel solver 求解可以得到最小的标准差为0.04127，此时的风险溢价为0.03901 ，夏普比率为0.94525，同时可以得到此时的风险组合如表。

为了画出风险组合的有效边界，我们以(2)式为目标函数，通过改变(1)式的值利用Excel solver 画出下图1：图1 有效边界与资本配置线图选取边界上夏普比率最高的组合，即有效边界上的最优的风险组合。

我们标准差风险溢价以(3)式为目标函数，以4011i i w ==∑为约束条件运用Excel solver 求解可以得到最优风险组合的标准差为0.0446，此时的风险溢价为0.0477 ，夏普比率为1.069507，得到图1。

用EXCEL实现多个资产的投资组合优化作者：祝媛博来源：《时代经贸》2012年第17期【摘要】我们可以用EXCEL来构建多个资产的投资组合，实现收益最大化或者风险最小化，并计算达到目标收益的概率。

【关键词】投资组合；最优一、风险资产数据假设我们要构建含五个风险资产的投资组合。

根据统计以往10年的五个资产的历史数据，我们得到以下数据相关系数（Correlation）风险资产1 风险资产2 风险资产3 风险资产4 风险资产5风险资产1 1 0.51 0.49 0.27 0.47风险资产2 0.51 1 0.98 0.5 0.94风险资产3 0.49 0.98 1 0.48 0.9风险资产4 0.27 0.5 0.48 1 0.46风险资产5 0.47 0.94 0.9 0.46 1预期收益（E（r）） 0.085 0.13 0.135 0.13 0.11收益标准差（） 0.091 0.206 0.212 0.19 0.12占组合最大百分比（%） 100 40 80 30 10占组合最小百分比（%） 0 10 0 0 0二、假设为了简化计算过程，我们做了一下假设：1.根据中心极限理论，我们假设五个资产的收益分布为正态分布。

2.我们假设资产的相关系数，预期收益，收益的标准差在短期内保持不变。

后面我们会通过压力测试来检验构建的投资组合对这些条件变动的敏感程度。

三、数学模型首先，我们计算投资组合的期望收益，是每个资产的期望收益，是将要构建的投资组合中每个资产的比重。

然后计算投资组合的收益的标准差，是两个资产间的协方差。

如果用矩阵的方式来计算，会有以下等式是五个资产的收益期望值的矩阵：是单位矩阵：只要确定了五个资产的比重，我们就可以计算出投资组合的收益期望值，标准差和达到目标收益的可能性（因为收益为正态分布，可以通过NORM.DIS公式，输入目标收益、投资组合期望、方差，得到概率值）。

相反地，我们也可以用EXCEL的规划求解功能，通过设定目标收益期望，标准差或者达到目标收益的概率，算出各资产的比例。

实验四：无风险资产与多种风险型资产最优投资组合的模型分析 一、实验目的通过上机实验，使学生充分理解Excel 软件系统管理和基本原理,掌握多资产投资组合优化的Excel 应用。

二、预备知识（一）相关的计算机知识： Windows 操作系统的常用操作；数据库的基础知识；Excel 软件的基本操作。

（二）实验理论预备知识现代资产组合理论发端于Markowitz(1952)提出的关于投资组合的理论。

该理论假设投资者只关心金融资产（组合）收益的均值（期望收益）和方差，在一定方差下追求尽可能高的期望收益，或者在一定的期望水平上尽可能降低投资收益的方差。

投资者的效用是关于投资组合的期望回报率和方差的函数，理性的投资者通过选择有效地投资组合以实现期望效用最大。

该理论第一次将统计学中期望与方差的概念引入投资组合的研究，提出用资产收益率的期望来衡量预期收益，用资产预期收益的标准差来度量风险的思想。

1、理论假设（Ⅰ）市场上存在n ≥2种风险资产，资产的收益率服从多元正态分布，允许卖空行为的存在。

{}12(,,,)T n ωωωωω=,代表投资到这n 种资产上的财富（投资资金）相对份额，它是n 维列向量，有11=∑=ni i ω，允许0<i ω，即卖空不受限制。

(Ⅱ) 用e 表示所有由n 种风险资产的期望收益率组成的列向量。

12(,,,)T n e R R R R == （1）p r 表示资产组合的收益率，)(p r E 和)(p r σ分别为资产组合p 的期望收益率和收益率标准差。

∑=⋅=⋅=ni ii Tp e r E 1)(μωω (2)(Ⅲ)假设n 种资产的收益是非共线性的(其经济意义为：没有任何一种资产的期望收益率可以通过其他资产的线性组合来得到，它们的期望收益是线性独立的。

)。

这样它们的方差-协方差矩阵可以表示为:⎪⎪⎭⎪⎪⎬⎫⎪⎪⎩⎪⎪⎨⎧=nn n n n n Q σσσσσσσσσ212222111211 （3）由于总是假定非负的总体方差,它还必须是一个正定矩阵,即对于任何非0的n 维列向量a ,都有0T a Qa >。