measuring historical volatility

[Guest]

i have a set of financial data that i would like to use to find its
historical volatility. I know the equation for determining volatility but i
was wondering if there is an excel addin that would speed up the process so i
dont have to do everything manually

 

[Guest]

i have a set of financial data that i would like to use to find
its historical volatility. I know the equation for determining
volatility but i was wondering if there is an excel addin that
would speed up the process so i dont have to do everything
manually

I would be very interested in learning your definition of historical
volatility and the equation that you use. I wonder if there is more
than one definition.

My definition is the standard deviation of the price changes. The
following is the "template" that I use to compute historical volatility.
Consider a history of 100 price points.

A1: price1
A2: price2
..... 'etc through A100
B2: A2/A1 - 1 'percentage change
C2: LN(A2/A1) 'log return
..... 'copy B2:C2 down through B100:C100
C101: EXP(AVERAGE(C2:C100)) - 1
C102: EXP(STDEV(C2:C100)) - 1
C103: EXP(STDEV(C2:C100)/SQRT(COUNT(C2:C100))) - 1

C101 is the average return; it can also be computed using
=(A100/A2)^(1/COUNT(A2:A100)) - 1. C102 is the standard
deviation, the definition of volatility that I use. C103 is the
standard error of the average, which is useful for determining
a confidence interval around the average.

Does that help?

 

[Bill Martin]

i have a set of financial data that i would like to use to find its
historical volatility. I know the equation for determining volatility but i
was wondering if there is an excel addin that would speed up the process so i
dont have to do everything manually

-----------------

There is a Yahoo group devoted to Excel's use in stock market issues such as
this. You can find it at:

http://groups.yahoo.com/group/xltraders/

Bill

 

[Guest]

Thanks for the help guys,

The equation i use is straight forward,

Take the natural log of the price change: LN(closing price/previous close)
Use as many price changes as you would like, i used 21 days.

Take the standard deviation of the natural log of all the price
changes...multiply this figure by the square root of 250 (this annualizes the
volatility).

I plan to use an advanced form of measuring volatility (GARCH method) but my
hope was someone would have an excel addin they could share.

Thank again for your guys help, yahoo excel financial trading site will be
useful too

 

[Bill Martin]

As I recall, you're supposed to use the sqrt of the number of days of data
you've used to annualize it -- not simply the sqrt(250). In the example you
cited it would be sqrt(21).

Also you're supposed to *divide* by the sqrt(time period), not *multiply* by it
which makes quite a difference. Using the volatility over a longer period of
time tends to reduce the calculated volatility. If you multiply it will
artificially increase it instead.

Here's a simple reference. It has the equations near the bottom.
http://en.wikipedia.org/wiki/Historical_volatility

And also here are two links from the other board I referenced. The software
does provide plug ins that do a bunch of technical analysis stuff including
historical volatility. One provides documentation, the other the actual
software. Note that I have not tried it myself.

docs: http://tadoc.org/
software: http://ta-lib.org/

Bill
-----------------------

 

[Guest]

As I recall, you're supposed to use the sqrt of the number
of days of data you've used to annualize it -- not simply the
sqrt(250). In the example you cited it would be sqrt(21).

I disagree. You scale based on the relationship between the
units of time, not the sample size.

If you have a daily volatility, you scale by 21 for to get monthly
volatility and by 252 to get annual volatility.

It has nothing to do with how many days -- the sample size
-- that you used to determine daily volatility. Of course, the
computation of the daily volatility statistic -- standard deviation
-- depends on the number of days (data points) in the sample.
But not how you subsequently annualize it.

Example references, none of which mention sample size in
determining the scale factor:

http://en.wikipedia.org/wiki/Historical_volatility
http://www.riskglossary.com/link/volatility.htm
http://www.riskmetrics.com/courses/measuring_risk/time.html

Also you're supposed to *divide* by the sqrt(time period), not
*multiply* by it which makes quite a difference.

I disagree. You divide by the sqrt of time as a __fraction__ of
a year to convert a shorter-period volatility to annual volatility.
Conversely, you multiply by the sqrt of time as a __fraction__
of a year to convert annual volatility to a shorter-period volatility.

But operative word is "fraction". Those rules are the same as
multiplying by the sqrt of time in units per year and dividing by
the sqrt of time in units per year, respectively.

This is confirmed by the Wikipedia article you cite, as well as
the additional articles I cite above. Using the Wikipedia examples ....

To convert daily volatility (vd) to annual volatility (va): va =
vd / sqrt(1/252). That is the same as va = vd*sqrt(252).
Proof:

va^2 = vd^2 / (1/252) = 252 * vd^2
va = vd * sqrt(252)

Conversely, to convert annual volatility (va) to monthly volatility
(vm): vm = va * sqrt(1/12). That is the same as vm =
va / sqrt(12). Proof:

vm^2 = va^2 * (1/12) = va^2 / 12
vm = va / sqrt(12)

 

[Guest]

(This is not the place for the following discussion. But I cannot
resist the temptation. Forgive me!)

Take the natural log of the price change: LN(closing price/previous close)
Use as many price changes as you would like, i used 21 days.

Hmm, at first I wondered why you use so few data points.
Then it hit me: a one-month moving average, perhaps for
Bollinger bands? Oy!

Take the standard deviation of the natural log of all the
price changes...multiply this figure by the square root of
250 (this annualizes the volatility).

On second thought, there would be no need to annualize daily
volatility for Bollinger bands.

In any case, my point is: IMHO, 21 data points is far too few
for any serious understanding of daily price volatility -- unless
you have very small volatility to begin with.

Suppose you want to know the mean +/- 100% with 95%
confidence. With only 21 data points, that would mean that
volatility (sd) must be only 2.3 times the average [1].

I have not seen such low daily volatility, at least not in the
stock market. In fact, in one article available on the web [2],
during a 10-year period ending Jan 30 2004, the S&P500 had
a daily average change of 0.04% and a daily volatility of 1.14%.
Thus, the daily volatility was 28.5 times the average!

If that were based on just 21 data points, you would only know
the mean +/- 1225% (0.04 +/- 0.49) with 95% confidence. Even
with only 68% confidence, you would only know the mean +/- 625
(0.04 +/- 0.25)%. In contrast, the article relied on 2520 data
points to know the mean +/- 111% with 95% confidence
(0.04 +/- 0.0445).

I plan to use an advanced form of measuring volatility
(GARCH method)

GIGO, IMHO.


-----
Footnotes

[1] 100%*mean = 1.96*sd / sqrt(21) = 1.96*mean*x / sqrt(21)
x = mean*sqrt(21) / (1.96*mean) = sqrt(21) / 1.96

[2] http://www.investopedia.com/printable.asp?a=/articles/04/021804.asp
states that the annual average return was 10.6% and the
annualized volatility was 18.1%. So the daily average return is:

(1 + 10.6%)^(1/252) = 0.04%

and the daily volatility is:

18.1% / sqrt(252) = 1.14%

That computed daily volatility matches the text of the article.

 

[Guest]

Clarification ....

If that were based on just 21 data points, you would only know
the mean +/- 1225% (0.04 +/- 0.49) with 95% confidence.

Since the mean value is itself a percentage, it is confusing to
write "mean +/- 1225%". I meant 1225% of the mean, i.e.
mean +/- 12.25*mean.

Also, where I write "0.04 +/- 0.49", I should have written
"0.04% +/- 0.49 points", i.e. -0.45 <= mean <= 0.53.

 

[thepcer]

Probably a little too late given this is a six year old thread...heres a spreadsheet that

1. Downloads financial data from Yahoo Finance (although you could modify it for any data provider). You give it a ticker symbol,two dates and click a button.
2. Calculate and plots the historical volatility.You give it a time window

Everything is automated through VB. It's available here
Historical volatility excel