如何用Excel统计历史波动率

How Do You Calculate Volatility In Excel?by AdamHGrimes







I received a question from a reader who asked, “Can you
calculate volatility in Excel?” The answer is, yes you can, but there
are a few things you need to know. Without going into too much detail
here, there are many ways to calculate volatility. Two of the most
common measures are implied and historical (also called realized or statistical) volatility.
It is fairly simple to calculate historical volatility in excel, and I
will show you how in this post. Calculating implied is quite a bit more
complicated. You technically can do it in excel, but you have to impute it from an option price. In addition, there’s actually a volatility surface,
or different values of implieds for different strike prices and
maturities. That’s a topic for another day; today let’s just look at how
to calculate a simple historical volatility in Excel.
有位读者问我“能用Excel计算历史波动率吗？”答案是可以，当然没问题，但有几件事需要了解一下。计算波动率的方法有很多，在此不一一赘述。波动率中最主要的是隐含波动率和历史波动率，历史波动率也被称为实际波动率或统计波动率。今天我们要做的就是探究如何用Excel计算历史波动率。用Excel计算历史波动率很简单，本文将展示如何来做。隐含波动率的计算过程有点复杂。理论上也可以在Excel上算出来，但必须通过期权的价格进行推算。此外，实际上还有波动率曲面的存在，也就是按照不同的期权行权价和不同的期限展示出来的隐含波动率水平。但那是另一个话题，今天我们要探讨的就是如何用Excel计算比较简单的历史波动率。

1.Collect your raw data, in the form of a closing price for each
time period. Many people do not know, but Yahoo Finance is a good source
of daily data that can be downloaded into a spreadsheet.Your data will likely include other data points such as high,
low, volume, etc, but just ignore everything except the close.
准备工作：搜集每一个统计时点的收盘价原始数据。很多人不知道雅虎财经网站是个非常好的电子表格数据下载来源。数据中可能还包括最高价，最低价，成交量等其他数据，但只留下收盘价，其他的均可放弃。

2. The first step is to convert the prices into a return series.
Again, let’s not dig too deeply into the theory in this post, but
prices are somewhat arbitrary. Is a $50 price a change a lot? Well, that
depends on the price of the asset and how much prices usually change.
Converting to returns is nothing more than changing the price series
into a series of percentage changes. This is the first step in nearly
all quantitative or mathematical market analysis. In Excel, start at the
second price from the top in your series (assuming closing prices are
in a column with the newest price at the bottom). In the cell to the
right of prices, divide the second price by the first and subtract one,
as in the pic. Copy this formula down the entire column.
数据加工第一步：
根据收盘价计算出一系列回报率数据。本文不会过多的探讨理论知识，但成交价多少有些随机性，偶然性。50美元的涨跌幅度算是很大的变化吗？这得看资产价格的绝对值大小以及通常的价格变动幅度。将收盘价转换成回报率只不过是将一系列的成交价转换成一系列的百分比波幅。这是几乎所有量化或以数理统计为基础的市场分析的第一步。假设在Excel表中成交价数据是按从上到下的顺序排列的，先从第二个成交价开始，在第二个成交价右边的数列中用第二个成交价除以第一个成交价，再减去1，然后把公式下拉复制一直到最后一个成交价所在的位置为止。见下图，

3.
Next, find the standard deviation of the returns. The formula for
standard deviation in Excel is =STDEV(…), and takes a range of prices as
an input. In the graphic, I have calculated a 10 day standard deviation
of prices, but that is for the illustration only. Set your window to
whatever time period you want to evaluate, and, again, copy the formula
down. Twenty days is a good starting point if you haven’t done this
analysis before.
数据加工第二步：
计算一系列回报率的标准差。Excel中标准差计算公式为=STDEV(…)，需要先确定一个计算周期，我在下图中选择以10天作为标准差计算周期，但仅限于演示说明。在第10个回报率数字的右侧栏中用标准差公式算出这10天的标准差，同理下拉复制到最后一个成交价所在位置。如果以前没做过此类分析，将20天作为计算周期是个不错的选择。

4.
So far, the procedure has been straightforward: calculate a return
series, and then calculate the standard deviation of that series. There
is one more step, which is perhaps the only part of this that is
conceptually a little bit complicated. You have calculated the standard
deviation of the returns for whatever the time interval of your data
is. If you have daily data, you have calculated a daily standard
deviation, and so on for hourly, weekly or any period. Historical
volatility is the annualized standard deviation of returns. We must multiple the standard deviation by an annualization factor,
which is the square root of how ever many of your periods are in a
year. This example is daily data; there are 262 trading days in a year,
so we multiply the standard deviation by SQRT(262). If you are using
weekly data, the annualization factor is SQRT(52), etc.
但目前为止，计算过程清晰又直白：先计算回报率，再计算标准差。下面还有一个步骤，可能只是在这步会遇到比较复杂的概念。截至目前为止，已经得出回报率的标准差，不管选择的周期天数是多少。如果用的是每日成交价数据，算出来的就是日均标准差，同理，用的是什么周期的数据，得出的就是什么周期的标准差，以此类推。历史波动率是回报率的年化标准差，因此我们必须用计算出来的标准差乘上年化因子，也就是所选择的数据周期在一年内有多少个周期这个数据的平方根。以日均数据为例，假设一年有262个交易日，那么我们就会用=SQRT(262)计算出年化因子，然后乘以日均标准差。如果数据周期是一周，那么年化因子等于SQRT(52)，以此类推。
This is one example, but a slightly more complex example, with graphs, can be found step by step on the tabs in this spreadsheet.
We will consider exactly what this measure of volatility is, what it
does, what we can do with it, and, even more importantly, what’s wrong
with it in a future post.
这个例子难度有点大，每一步骤后面都附有图表。以后我们会探讨波动率究竟是什么，能起什么作用，我们能用来做点啥，以及最重要的，它有哪些缺陷。

Edit: This sheet and article use an annualization factor of 262.
There is some debate over what number to use here, but you’ll see it
doesn’t really make much of a difference. 252 is probably the most
accurate number (and is what I use in my calculations now), but, as long
as you’re consistent in your calculations, the actual number doesn’t
really matter.
附加说明：本文中年化因子是用262交易日算出来的，有些人对此有异议，但差别真的不大。更准确的数字应该是一年有252交易日，但不管是多少，只要在计算过程中一直用下去，实际结果不会有大的区别。

译者注：按照操作步骤，以追踪标准普尔500指数走势的交易所交易基金SPY为例，计算一下SPY的历史波动率。

第一步，原始数据。SPY收盘价的下载网址为https://finance.yahoo.com/quote/SPY/history?period1=1505360042&period2=1536896042&interval=1d&filter=history&frequency=1d，最左边的红线标注出处为数据的期限，打开下拉式菜单可选择起始日期；中间偏右的红线标注出处为数据的频率，下拉式菜单的选项包括每日，周度和月度；最右的红线标注出处为下载功能键，点击后即可下载成Excel格式。

第二步，原始数据筛选。
下载后，原始数据格式如下

去掉开盘价，最高价，最低价，成交量等没用的数据，只留下经调整后的收盘价

第三步，计算回报率。
数据筛选完成，A列为从旧到新的日期排列，B列为经调整后的收盘价，C列为回报率。例如，在C3栏计算8月14日收盘价比前一交易日的同比波幅

结果为283.899994/282.100006-1=0.64%

光标按住C3，并下拉该公式至最新日期2018-9-13成交价处，得出最近一个月SPY的回报率

第四步，计算回报率的标准差。
标准差公式为=stdev()，我在这里选择计算每5天的标准差，因此（）内的数据范围为C3至C7，标准差数据所在栏为D7

结果显示2018-8-14至2018-8-20这5个交易日SPY的回报率为0.61%

光标按住D7，并下拉该公式至最新日期2018-9-13的成交价处，得出最近一个月SPY的标准差

第四步，标准差的年化处理。
假设一年有252个交易日，年化因子的公式为=SQRT(252)，结果所在栏为E7

最终结果：2018-8-13至2018-9-13期间SPY的5日日均历史波动率