measuring historical volatility

[JELLO]

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

[[email protected]]

"JELLO" wrote:
> 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]

JELLO wrote:
> 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

[JELLO]

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" wrote:

> JELLO wrote:
> > 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
>

[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
-----------------------

JELLO wrote:
> 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" wrote:
>
>
>>JELLO wrote:
>>
>>>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
>>

[[email protected]]

"Bill Martin" wrote:
> 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/m...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)

[[email protected]]

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

"JELLO" wrote:
> 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/printabl.../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.

[[email protected]]

Clarification ....

I wrote:
> 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.

[spiderxl]

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

Are you referring to sample volatility? or a model-based volatility? For mean-reverting model based (e.g. GARCH, E-GARCH or GARCH-in-the mean), I believe you are looking for the long-run volatility estimate of a fitted model.

There are few statistical and econometric excel add-ins (e.g. XLStat and NumXL) that can help you with your task, or alternatively, if you are already have a stand-alone statistical program (e.g. SAS, S-Plus, R, MatLab), I believe they offer excel add-in option and separate loolbox for econometrics.

Good luck.

[shg]

This is a 6-year old thread. They may not be stopping back regularly to check for answers.

[janschepens]

Since we seem to be resurrecting a five year old thread...I've put together an Excel spreadsheet.

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. Some VB does all the heavy lifting
2. calculate and plots the historical volatility. You give it a time window

It's available at http://investexcel.net/1979/calculat...atility-excel/

[Acem13]

I'm working on a project right now that involves historical stock volatility. I've found it much simpler just to take the standard deviation, or the forumula =stdev( There is no official standard way to measure stock volatility. Different people measure it in different ways, how you enter the formula into excel depends on the specific formula you want to use.

[arlu1201]

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.