Calculating Volatility & Standard Deviation - MS code

[RichKid]

Just been reading a chat involving Chris Tate and Louise Bedford (http://www.investoriq.com.au/downloads/DownloadFiles/Chat%20Transcript.pdf).

Tate suggests the Historical Volatility formula in MS is incorrect. He offers his own, I haven't emailed him for it yet.

Is anyone else aware of the issue? I plan to use it to estimate volatility as I don't have option software with volatility data. Bollinger Bands is the other option that I find useful. Basically to know when hv is low to buy calls. It's only standard deviation from what I gather. Calculating implied volatility is a different matter I think. I found Tate's and Bower's options books very good re volatility.

If anyone knows anything about this please post the correct formula, maybe MS has corrected it since the date of Tate's comments in 2004.

[wayneL]

here 'tis RK

(StDev(log(C/Ref(C,-1)),20) * sqrt(252))*100

The bold value is the statistical period and can be whatever value you choose.

I use 10,20 and 90

cheers

[RichKid]

here 'tis RK

(StDev(log(C/Ref(C,-1)),20) * sqrt(252))*100

The bold value is the statistical period and can be whatever value you choose.

I use 10,20 and 90

cheers

Thanks very much Wayne, that's great! Now time for me to fiddle with MS...

[tech/a]

To be able to automatically alter the formula where Wayne has suggested.

Prds:= Input("Number of periods",5,200,20);
(Stdev(Log(C/Ref(C,-1)),Prds) * Sqrt(252))*100

[tech/a]

I have a paper on Volatility and how its calculated plus its use in derivative trading in PDF format (25 pages) if you or anyone else wants to have it.
Just private mail me with an Email address and I'll send it off.

[rembrandt]

Hi folks ... am familiar with the problemo of H/V and Metastock and best I could do was 'Stdev(Log(C/Ref(C,-1)),20)*Sqrt(252)*100' which I see others are using also ... doesn't solve it completely and I don't know of any fix.

I did some work on H/V some years ago for derivatives traders and have a couple of webpages on my site for those who might like some info and a couple of methods to calculate H/V in Excel.

http://www.users.bigpond.com/equus2/std.dev.mht

http://www.users.bigpond.com/equus2/MyHistoricalVolatility.mht


Cheers ...

[RichKid]

Thanks folks, this is turning out to be interesting, just learning about volatility and how it's worked out is helping me in my general risk mgmt too. Will check out your site Rembrandt, thanks Tech, I'll be in touch.

PS Tech, if you could clear your old pm's (see inbox and sent folder) it'll be great as it's full atm and I can't send you any pm's. Thanks.

[wayneL]

I plot several lookback values concurrently...it looks more impressive to the onlooker. :)

[RichKid]

I plot several lookback values concurrently...it looks more impressive to the onlooker. :)

"Lookback value"?? that's just great, another thing about options that I have to learn! Thanks Wayne, it does look nice but very confusing for me, will do more reading, I thought you liked to keep your trading simple! ;)

[wayneL]

LOL RK

I was being flippant! :)

"lookback" period simply means the statistical period that you're looking back over. i.e 20 day volatility has a 20 day "lookback"...100 day volatility has a 100 day lookback etc.

I plot several lookback periods and then compare these to implied volatility to get a sense of the "expensiveness" of the option.

It is important to note that these values are "annualized" percentage values.... Only useful with regard to pricing of the option in a relative sense.

If writing options, I think a great idea is to optimize this calculation to the trading period in mind.

In other words if writing a naked put, we want to get an idea of the statistical probability of the underlying NOT reaching the strike price. The last thing we want is to have to defend a written position.

So if writing an option with 20 trading days til expiry, annualized volatility tells us nothing. We can "monthize" the calculation to get a better idea of "monthly" volatility:

(StDev(log(C/Ref(C,-1)),20) * sqrt(20))*100 (or any period we choose)

or ma((StDev(log(C/Ref(C,-1)),20) * sqrt(20))*100,100) [100 day {or whatever}average of the "monthized" claculation]

This will plot the percentage value of 1 standard deviation of the historical "monthly" movement.

From this, in addition to our t/a, we can make intelligent choices in the selection of our strike price based also on statistics.


I thought you liked to keep your trading simple!

Hmmmm yes. My t/a is embarrasingly simple! It's these options that keep my brain from withering due to inactivity HAHAHAA

[kbleivik]

The formulaes depend on the assumptions you make about the underlying process. The underlying process
1. Most probably are not (log)normal.
2. Most probably are skewed.
3. Most probably has heavier tails than the normal distribution.
4. May have fractal structure.

Therefore it may be very important to try to estimate the empirical distribution of the underlying process.

Bollinger Bands are symmetriccal around the average (midpoint). That may be misleading bacause of the points made above.

[wayneL]

Welcome to the forum kbleivik.

Your profile is most interesting...and all the way from Norway!

Looking forward to your continued input.

What is your view of the state of the world, and in particular, the US, economy?

Cheers

[kbleivik]

At present I am not tracking the world or US economy, since I use my time buliding sites.

But I have observed the following:
1. There were a worry about the US getting into deflation.
2. Zero interest rate in the USA as in Japan.
3. The red lights are off at present? What about Real Estate, private debt etc?
4. Along the left edge of the first page of MultiFinanceIT.com you may find a lot of good financial / economic sites if you have at last a 17 inches screen.
5. I have heard that the site shows best in Firefox, in other parts of the world, but I have no problems with IE here in Norway.
6. I am here to post if I find something interesting and looking for financial sites in Australia.

[denl]

To be able to automatically alter the formula where Wayne has suggested.

Prds:= Input("Number of periods",5,200,20);
(Stdev(Log(C/Ref(C,-1)),Prds) * Sqrt(252))*100
When I plot this formula and Waynel's formula on the same chart, with the same periods eg: 21 they are completly different. What is happening there?
Denis

[wayneL]

When I plot this formula and Waynel's formula on the same chart, with the same periods eg: 21 they are completly different. What is happening there?
Denis

There should be no difference Denis.

Double check that the code is the same....brackets etc

[denl]

Thanks, I did check again and both the H/V formulas are the same....

[tech/a]

here 'tis RK

(StDev(log(C/Ref(C,-1)),20) * sqrt(252))*100

The bold value is the statistical period and can be whatever value you choose.

I use 10,20 and 90

cheers

This is Waynes Formula.
His periods selected is 20 shown in BLACK.

Here is my formula

Prds:= Input("Number of periods",5,200,20);
(Stdev(Log(C/Ref(C,-1)),Prds) * Sqrt(252))*100

Where Waynes 20 is I have placed the Variable Prds this allows you to alter the periodicy.Note the 5,200,20 this means---the lowest value in the formula for periods is 5 and the highest is 200 the default is 20 which is Waynes.
For there to be 2 different plots the formulas would be different in some way.

Check carefully as a bracket missing will dramatically alter the logic of the formula---my bet is that a bracket is missing.