calculate new volatility in excel

i try to get the new volatility formula into excel, has anyone an idea?

it's a new and better way to calculate the volatility on shares.

n = observation period (250 days)
Thz = trading hours in minutes (510 minutes)
daily highest and lowest price is in the matrix .xls below

new volatility Formula.jpg

new volatility DAX en.xlsx

The strategy I use for entering formulas like this looks something like this:

1) At least on the first attempt, do not try to put it all into a single cell. A single cell formula for this will have so many terms and parentheses and places where a single character can get out of place, and it will often take forever to find the misplaced character. In essence, I like a "divide and conquer" approach to these kind of formulas.
2) In this divide and conquer approach, identify which parts of the formula will be useful to separate out. For example, if I follow your formula, the final square root part is not dependent on j or i or n, so it will be a constant. I would be tempted to pull that part out and put it in a cell by itself [=sqrt(365*24*60/510)]. The other formulas can refer to this cell (using an absolute reference), or I can even make use of the distributive property and pull this constant out of the summation. Sum(A*i)=A*sum(i) for A is a constant.
3) In a spreadsheet, I usually prefer to perform these kind of summations where I compute each term in the summation in a row. Then the final computation for the summation is a simple sum function. Similar to what you did in your spreadsheet for the sum(j=1 to 2n)(j) part of the calculation.
4) Look for algebraic or other simplifications. For example, the aforementioned sum(j=1 to 2n)(j) is a well known sum, allegedly first described by Gibbs as a child: http://www.wikihow.com/Sum-the-Integers-from-1-to-N

Hopefully that will give you some ideas for how to approach programming this in a spreadsheet.

If you want to put this kind of summation into a more symbolic language (like VBA), it should be easy enough using a For...Next loop:http://msdn.microsoft.com/en-us/library/5z06z1kb.aspx something like:

Please Login or Register  to view this content.

Some people find this programming notation is easier for these kind of summations than building the summation using spreadsheet formulas.

Originally Posted by kmaloney01

i try to get the new volatility formula into excel, has anyone an idea? it's a new and better way to calculate the volatility on shares.
n = observation period (250 days)
Thz = trading hours in minutes (510 minutes)
daily highest and lowest price is in the matrix .xls below
Attachment 337995
Attachment 337994

It is a different measure of volatility, just as the VIX formula is. It is not necessarily "better". That must be determined by a peer review.

To answer your question, put the following formula into J2522 formatted as Percentage, and copy it up through J501:

Please Login or Register  to view this content.

where we also have the following formulas:
K2522: =SUMPRODUCT(ROW($D$2023:$D$2522)-ROW($D$2022))
L2522: =SQRT(2)
M2522: =SQRT(60*24*365/510)

You can put them anywhere, changing the SUMPRODUCT formula appropriately.

Obviously, I simplified the original formula algebraically.

I also made an important change so the result is comparable to the standard definition of volatility; that is, a percentage like the values in your column I.

In contrast, the original formula scales (multiplies) the percentage by 100, perhaps to be comparable to the VIX index. For example, 12.34% becomes 12.34.

I can explain the derivation of the Excel formula in detail, if you wish.

Originally Posted by kmaloney01

it was in a diploma thesis from the university of wien, a work from heinz lorenz habermuth
[....]
Attachment 338148

Habermuth attributes the formula to Reinhold Fend and Christian Luible, “Historische Volatilität: Annualisierte Standardabweichung versus neue Berechnungsmethode”, Vereinigung Technischer Analysten Deutschlands e. V., January 2009.

But without seeing the Fend and Luible paper, preferably in English , I can only guess about a key detail that is not explained. To wit....

The formula uses the subscript t-i+1. What is "t"? Presumably "t" is the same as (and should be) "T". That is, it is the time index for which we are calculating volatility. And presumably t>=2*n is true; otherwise, t-i+1 might be a negative subscript. I believe t=2*n. That is how I used it in the Excel formula above.

In summary, the Fend-Luible formula seems to calculate the annualized trailing weighted moving average (WMA; a.k.a "sum of the digits"[*] weighted average) of the percentage difference between the daily low price and daily average price over 2*n days.

Well, the WMA divided by SQRT(2). I have no explanation for that. (Well, a guess; but I don't like it.)

[*] "Sum of the digits" is a misnomer, IMHO. It should be "sum of the numbers".

Errata....

Originally Posted by joeu2004

In summary, the Fend-Luible formula seems to calculate the annualized trailing weighted moving average (WMA; a.k.a "sum of the digits"[*] weighted average) of the percentage difference between the daily low price and daily average price over 2*n days. Well, the WMA divided by SQRT(2). I have no explanation for that. (Well, a guess; but I don't like it.)

More correctly: the absolute percentage difference in the daily low and the daily high relative to the daily average.

-----

Well, I know what the algebra is doing. I find it difficult to explain it in words.

The demoninator is (H[i]+L[i])/2. That is the daily average.

The numerator is (H[i]-L[i])/2. That is the difference between the daily average and daily low and between the daily high and daily average. Note that L[i] + (H[i]-L[i])/2 = H[i] - (H[i]-L[i])/2 = (H[i]+L[i])/2.

So the ratio ((H[i]-L[i])/2) / (H[i]+L[i])/2) is the percentage difference relative to the daily average. That simplifies to (H[i]-L[i]) / (H[i]+L[i]).

Note that ( (H[i]-L[i])/(2*SQRT(2)) ) / ( (H[i]+L[i])/(2*100) )
= ( ((H[i]-L[i])/2) / ((H[i]+L[i])/2) ) * 100 * (1/SQRT(2))
= ( (H[i]-L[i]) / (H[i]+L[i]) ) * 100 / SQRT(2).

Thus, the percentage difference is scaled by 100 (changing 12.34% to 12.34, for example; just a presentation change), then divided by SQRT(2).

Since 100/SQRT(2) is constant, it can be factored out of the Sigma-i.

And I choose not to scale the percentage difference by 100, omitting the multiplication by 100.

first of all, your formula is brilliant and it works, THANK YOU VERY MUCH.

formula in one cell J2522:

Please Login or Register  to view this content.

yes t means the Time.

i calculated it in a more complicated way and the result is exactly the same ;-) i love part of your formula "SUMPRODUCT((ROW(D2023:D2522)-ROW(D2022))" didn't know that function before in that context, it's a very smart way not to use an extra-column.

well, the work from Reinhold Fend and Christian Luible is only in german available :-(

new volatility - VTAD Award 2009 1.Preis.pdf

but there are no more information about the formula...

and why the WMA has to be divided by "SQRT(2)"? i don't know, has anyone else an idea?

with your summary, you hit the nail right on the head, thank you very much, that formula is brilliant!

cheers

Originally Posted by joeu2004

It is a different measure of volatility, just as the VIX formula is. It is not necessarily "better". That must be determined by a peer review.

To answer your question, put the following formula into J2522 formatted as Percentage, and copy it up through J501:

Please Login or Register  to view this content.

where we also have the following formulas:
K2522: =SUMPRODUCT(ROW($D$2023:$D$2522)-ROW($D$2022))
L2522: =SQRT(2)
M2522: =SQRT(60*24*365/510)

You can put them anywhere, changing the SUMPRODUCT formula appropriately.

Obviously, I simplified the original formula algebraically.

I also made an important change so the result is comparable to the standard definition of volatility; that is, a percentage like the values in your column I.

In contrast, the original formula scales (multiplies) the percentage by 100, perhaps to be comparable to the VIX index. For example, 12.34% becomes 12.34.

I can explain the derivation of the Excel formula in detail, if you wish.



Habermuth attributes the formula to Reinhold Fend and Christian Luible, “Historische Volatilität: Annualisierte Standardabweichung versus neue Berechnungsmethode”, Vereinigung Technischer Analysten Deutschlands e. V., January 2009.

But without seeing the Fend and Luible paper, preferably in English , I can only guess about a key detail that is not explained. To wit....

The formula uses the subscript t-i+1. What is "t"? Presumably "t" is the same as (and should be) "T". That is, it is the time index for which we are calculating volatility. And presumably t>=2*n is true; otherwise, t-i+1 might be a negative subscript. I believe t=2*n. That is how I used it in the Excel formula above.

In summary, the Fend-Luible formula seems to calculate the annualized trailing weighted moving average (WMA; a.k.a "sum of the digits"[*] weighted average) of the percentage difference between the daily low price and daily average price over 2*n days.

Well, the WMA divided by SQRT(2). I have no explanation for that. (Well, a guess; but I don't like it.)

[*] "Sum of the digits" is a misnomer, IMHO. It should be "sum of the numbers".