## Comment on Greek Interactions commented on 04 Aug 2016, 11:01 PM

I got the explanation from an excel expert(excel forum), about excel formulas used for Vitality cone calculation , please find the information below.

For example, is it the use of MATCH and OFFSET? Is it the multplication of SQRT(252)?

The formula calculates the annualized standard deviation of daily (natural) log returns, which are the values in column Sheet1!E. It is a measure of volatility.

The MATCH expression returns the row index (relative to row 2) of the closest date in column Sheet1!A before or equal to the date in Sheet1!N3.

The notation \$N3 means that we use N3, N4, N5 etc as we move down each column of formulas.

In contrast, the notation \$A\$2:\$A\$504 means that we always use A2:A504 in each formula.

The OFFSET expression returns the cell range of values (log returns) from column Sheet1!E corresponding to the last B5 (number of) dates ending with the relative row index returned by MATCH.

The notation B\$5 means that we use B5, C5, D5 etc as we move across each row of formulas.

In contrast, the notation \$E\$2 means that we always use E2 in each formula.

The expression SUM(…)/B\$5 calculates the (population or exact) variance of the daily log returns referenced by OFFSET.

The expression SQRT(SUM(…)/B\$5) calculates the standard deviation of the daily log returns.

The daily standard deviation is annualized by multiplying by SQRT(252) according to the “square root of time” rule, assuming 252 trade days per year (on average).

—–

The (population or exact) variance of a set of data is Sigma((x[i]-xhat)^2) / n, where x[i] is each of n data points, and xhat is the mean of the data. (The operation ^2 is the square of the calculation. Sigma is the sum of the calculations.)

Ostensibly, the values (x[i]-xhat)^2 are calculated in column Sheet1!E, based on the calculation of x[i]-xhat in column Sheet1!D.

However, the calculation in column Sheet1!D seems to be incorrect.

At a minimum, the reference to J4 in Sheet1!D3 should probably be written \$J\$4, so that J4 is referenced in each formula down the column.

Also, Sheet1!J4 should probably contain the formula =AVERAGE(C3:C510). [Errata]