# Calculating Volatility & Standard Deviation - MS code



## RichKid (16 September 2005)

Just been reading a chat involving Chris Tate and Louise Bedford (http://www.investoriq.com.au/downloads/DownloadFiles/Chat Transcript.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 (16 September 2005)

*Re: Metastock FAQ*

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 (16 September 2005)

*Re: Metastock FAQ*



			
				wayneL said:
			
		

> here 'tis RK
> 
> (StDev(log(C/Ref(C,-1)),*20*) * sqrt(252))*100
> 
> ...




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


----------



## tech/a (17 September 2005)

*Re: Metastock FAQ*

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 (17 September 2005)

*Re: Metastock FAQ*

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 (17 September 2005)

*Re: Metastock FAQ*

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 (17 September 2005)

*Re: Metastock FAQ*

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 (18 September 2005)

*Re: Metastock FAQ*

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


----------



## RichKid (18 September 2005)

*Re: Metastock FAQ*



			
				wayneL said:
			
		

> 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 (18 September 2005)

*Re: Calculating Volatility & Standard Deviation- MS code*

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


----------



## MultiFinanceIT (27 September 2005)

*Re: Calculating Volatility & Standard Deviation- MS code*

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 (27 September 2005)

*Re: Calculating Volatility & Standard Deviation- MS code*

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


----------



## MultiFinanceIT (4 October 2005)

*Re: Calculating Volatility & Standard Deviation- MS code*

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 (11 October 2005)

*Re: Metastock FAQ*



			
				tech/a said:
			
		

> 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 (12 October 2005)

*Re: Metastock FAQ*



			
				denl said:
			
		

> 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 (18 October 2005)

*Re: Calculating Volatility & Standard Deviation- MS code*

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


----------



## tech/a (18 October 2005)

*Re: Metastock FAQ*



			
				wayneL said:
			
		

> here 'tis RK
> 
> (StDev(log(C/Ref(C,-1)),*20*) * sqrt(252))*100
> 
> ...




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.


----------

