如何用EXCEL和STATA做股票价格模拟

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

用Excel和Stata进行股票价格模拟。

假设股票的日均对数收益率服从正态分布，均值为0，方差为0.05。期初股价为100元。模拟股票价格在一年内的波动。

（不用蒙特卡洛模拟）

谢谢！

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏4 回帖

关键词：用excel Stata EXCEL xcel tata

以阿里巴巴股票价格做一个简单的展示。我们首先打开雅虎财经的网站（https://finance.yahoo.com），阿里巴巴的股票代码为BABA，我们在搜索框中输入BABA，下面会提示出对应的公司，我们可以直接从提示中选择Alibaba公司，也可以直接点击右侧的Search进行搜索。

当我们点击搜索之后，页面直接跳转到与阿里巴巴公司股票相关的页面。我们会发现有阿里巴巴公司当前的股票价格，涨跌比例，以及其他的交易数据。当前页面显示的是Summary分页下的一些基本情况，我们需要的是阿里巴巴公司的历史交易数据，这个数据我们可以从Historical Data分页中找到，我们点击Historical Data分页。


当我们点开Historical Data分页之后，我们会立即得到最近一年的股票交易数据，我们从Time Period选项可以看出，默认选项是最近一年，由于当前日期为2017年9月20日，所以起始日期默认为2016年9月20日，这个我们可以自行设置。Show选项默认为Historical Prices，即历史价格数据，这个正好是我们需要的。Frequency选项默认为Daily，即每天的交易数据。需要注意的是，我们将这些选项设置完成之后，一定要点击Apply按钮，即应用按钮，否则我们设置的选项并没有作用，点击完Apply按钮之后，我们直接点击Download Data进行下载。

在这里，我们选择过去3年的交易时间，即将Time Period设置为Sep 20,2014-Sep 20,2017，然后点击Apply，最后点击Download Data进行下载。
我们发现下载的文件为BABA.csv，我们打开该文件之后发现共有7列数据，分别是Date日期，Open开盘价，High最高价，Low最低价，Close收盘价，Adj Close调整后的收盘价，Volume成交量。在这里，我们只需要使用Adj Close这一列，Adj Close与Close的不同之处在于，Adj Close考虑了股票拆股、发放股利等情况，将其调整为正常的收盘价，而Close只是显示当时实际的价格，而不考虑股票拆分和发放股利等情况。

删除不必要的数据之后，我们点击Date，然后对数据进行倒序排序，我们可以从数据选项卡下的排序找到，也可以将常用的排序工具添加到自定义功能区中直接使用。

我们在得到了倒序的数据之后，在C1单元格添加一个Return的列，通常情况下，我们计算的是对数收益率，而不是简单的算数收益率。我们在C2单元格中输入公式=LN(B2/B3)，然后我们得到了一个收益率的数据，我们双击该单元格的右下角来完成自动填充，从而获得整个收益率的分布。

需要注意的是，我们计算的Return数据中有一个缺失值，这是因为最后一个值无法计算，所以我们会得到一个#DIV/0!的结果。除此之外，我们还可以发现一共存在757个数据行，但是第一行为列标题，最后一行存在缺失值，所以我们得到真正有用的是755行数据。

我们得到了股票价格每天随机变化服从的分布，但是如何从这个分布中抽取一个随机收益率呢？这个时候我们需要为分布中的收益率设定一个标签，我们先在D1单元格输入列标题Label，然后在D2单元格中输入公式=RANDBETWEEN(1,755)，我们得到了一个随机标签，我们同样双击D1单元格右下角实现自动填充，从而获得所有的随机标签。

有了相应的标签之后，我们可以从Return的分布中随机提取每天的股票价格变化了，我们在E1单元格中输入列标题Random，然后在E2单元格中输入公式 =SMALL($C$2:$C$756,D2)，这个公式是从所有的Return分布中提取D2标签对应排序的数值。得出结果后，我们双击E2单元格右下角实现自动填充，从而得到所有的随机变化结果。

得到了上述的结果之后，我们就可以来预期未来的价格了。我们在F1单元格输入列标签Price，在F2单元格我们需要锁定起始价格，输入公式=$B$2，然后在F3单元格输入公式=F2*EXP(E2)，然后双击F2单元格右下角实现自动填充。由于之前我们求的是对数收益率，所以我们在这里需要使用EXP()函数进行相应的转化。就这样我们模拟了一条股价未来可能的走势。

在这里，我们使用了过多的辅助列，例如Label和Random列，我们完全可以将这两列中的公式，复制到F列，然后将D和E列进行删除，最后我们得到了更加简单的结果。

接下来，我们的任务就非常简单了，就是复制合并后D列的值，然后不断地进行粘贴，粘贴出多列来。
此时，我们将鼠标定位在D1单元格，然后按住Ctrl+Shift+→+↓键来实现快速精准选择单元格区域，然后从功能区中找到插入→图表→二维折线图，结果如下所示。

最后就是简单的图表修饰工作了，我们删除图例，删除网格线，然后添加坐标轴标题，横坐标轴为交易日，纵坐标轴为股票价格，图表标题为蒙特卡洛模拟股票价格未来走势，然后将横纵坐标轴边框设置为深色，适当地调整一下纵坐标轴的范围。最终呈现结果如下：
更加神奇的是，当你在Excel中按F9功能键来进行重新计算，你会发现，你的Price列中的数据都会发生相应的变化，与此同时，你的图表中的股价路径走势图也会发生相应的变化。当然，或许你觉得上述过程显得比较复杂，因为在Matlab、Python或者R中实现蒙特卡洛模拟同时进行绘图，可能更加简单些，不过前提是你要相应的编程语言或者统计语言。对于没有任何编程基础的读者朋友来说，Excel同样可以实现这种模拟结果，只要你懂得模拟的原理，用最基本的Excel操作同样可以实现。