Historical Volatility Calculation with Excel

[Tuxster3]

Your accent is WAY too thick.

I can't understand a WORD you're saying!

[goofer19]

hmm, U diddnt do anything with the just calcluated logaritme of the price changes in column H. shouldnt u calculate the standard deviations of the price changes in natural logarithm? instead of over the column C2

[lordbinder]

thats correct it was an error that someone pointed out a while ago I didnt remake the video for lack of time

[lordbinder]

forgot, you have just to call data on column H instead of C

[bigd8849]

what is the formula of the first step, its too blurry to see.

[lordbinder]

I think you mean the natural log, should be

=LN(last day close/close of the day before)in the example of the excel is

=LN(B2/B3)

[dumkamurg]

god bless you man.. you have made it so much easier for me... thank you sooo muchhhhhhhhhhhhhhhhhhhhhhhh..

[lordbinder]

thx, im glad you found it useful

[dumkamurg]

what was the formula please for standard deviation. the screen was a bit too difficult to see.

[lordbinder]

=(STDEV(C2:C41)*SQRT(260))

C2:C41 represents last 40 days close data, if you want to plot 6 days volatility you have to adjust to C2:C8

[dumkamurg]

Thank you very much...I actually have a dissertation on impact of political change on stock market volatility. so i was looking for a model which measured stock market volatility in the time frame when political change happened. i was looking at arch/garch models. but since i dont have a mathematical background those models are too difficult..

could you suggest a simpler model which looked at historical volatility..

thank you ever so much...

[lordbinder]

garch model is the easier I think, but thats for estimating volaility, if you already have the historical volaility, you dont need to estimate. of course that would depend in the analysis you are making

[dumkamurg]

also would be very grateful if you could just show me how to put all the data on a graph, as the starting part of the video shows.

Thank you very much

[lordbinder]

assuming you are using excel 2007, select the data you want to plot, then go to "insert" and slect line charts, select the type of lyne you want, and click it thats it

[dumkamurg]

THANK YOU SO MUCH.. MOST OBLIGED..

[dumkamurg]

I sincerely think you should have been my project supervisor because the one i have is of no help..

in your earlier post you said that i dont need to use the garch model as it estimates future volatility and one which matters to me is historical volatility.. my project seeks to research the impact of political events on market volatility.. so if i can prove that everytime a political change has happened, the market had become very volatile.

any help with theory wud be greatly appreciated.

[lordbinder]

look for 2 data series, in first world the stock market indexes and the references rates such as 5 or 10 years yields. The most important for emerging economies would be the EMBI+, take for example argentina, the political crisis of the farmers allow a 300% jump on the EMBI+. Your hypotesis is correct, you could construct a data series of those indexes and compare thorugh time the historical volatility to prove that increases on such events, and many times on advance of the event itself

[lordbinder]

no surprise on the project supervisor, when i study at the states my thesis tutor knowledge was far behind mine, extremely discouraging

[dumkamurg]

thank you so much for your help..

[ghostofgar]

Very cool lordbinder, thanks for putting this up