Manual and Help for Excel addin Metrixus - Metrics for Financial Market

-> Content

Previous: 2.2.12. Multiple Linear Regression

Next: 2.2.14. ARCH - GARCH - IGARCH - EGARCH - GJR (TGARCH)

EWV - Exponentially Weighted Volatility - its a technique for treatment of historic data (temporal series) which searches value the occurencies more recents to calculate the standard deviation (or volatileness for financial assets). This technique also is know as EWMA – Exponentially Weighted Moving Average.

When determinates teh standard deviation of a database equally weighed, all the deviations and errors (squared) from observations relating to mean has the same weight. Using exponentially weighting, the last (more recent) errors has a bigger weight which decreases as go in direction to old data, being the weight or coefficient determined by the following rule:

• Coefficient D₀ = (1 - )
• Coefficient D_0-i = () * Coefficient D_0-i+1, for any i >0

where D₀ its the last (most recent) observation and its the decay factor or exponential balance choosen.

The decay factor is, therefore, what determinates teh relevance degree from the last (most recent) sample data. A big factor gives a excessive weight to the observation (square error) end, softing the past movements. a factor so small it will have contrary effect, making the lasts (most recent) errors be less relevants.

The following table exemplifies the coefficients to applied to the errors observations using the decay factor of 0.94 (commom for financial assets):

Coefficient	Observation	Error2 Observed	New Error2
1 - = 0.060	Last (D0)	A	0.060 *A
* 0.060 = 0.056	D-1	B	0.056 * B
* 0.056 = 0.053	D-2	C	0.053 * C
0.050	D-3	D	0.050 * D
0.047	D-4	E	0.047 * E
0.044	D-5	F	0.044 * F
0.041	D-6	G	0.041 * G
0.039	D-7	H	0.039 * H
0.037	D-8	I	0.037* I
0.034	D-9	J	0.034 * J
0.032	D-10	K	0.032 * K
0.030	D-11	L	0.030 * L
0.029	D-12	M	0.029 * M
0.027	D-13	N	0.027 * N

The decline factor choosen also is responsible for the tolerance or precision level in results. By the decreasing data participation (square errors) most old, after a certain number of observations the applied coefficient will be so small that the errors value can be disconsidered.

Considering a declien factor , a tolerance T and a database with mean 0 have:

where k represents the position in time from the values of square error are undervalued.

In other words, the sum of undervalued coefficients its equivalent to the tolerance in the results. The table below exemplifies the relation between the amount of historic data used in calculating EWV, according to tolerance and the decay factor choosen:

In example, using equal to 0.94 and a tolerance of 1%, from the observation 74 the errors values can be undervalued for calculating EWV. Using a tolerance of 0.1%, the errors values from observation 112 can be undervalued.
On this way, note that choosing the decay factor its critical in the process of exponential weighting.

For the determination of best decay fact, can be used the shrinking forecast error variance for the next step, that is,the factor is choosen for the forecast variance for next step (based in given data) bee shirinked. Here is good to remenber that must choose a tolerance for calculations of best factor according to the size of historic database, as well the size of database mus be enough big to allow forecast and analysis of thsi variance according to the tolerance.

The EWV its so used in finances for VaR (Value at Risk) calculus and volatileness of financial assets returns, being usual apply a logarithmic operator to the returns before the calculations. Yet in finances, its commom use the returns mean being equal 0, reducing some possible bias in the database.

Access:

• Menu - Insert | Function | Metrixus
• - Toolbar Default | Metrixus

Description: Returns the sample standard deviation (or volatileness) and the mean of data or from the prices returns obtained by the exponetial weighting and, yet, the tolerance, according to the informed parameters. Allows to apply a logarithmic operator to the returns, useful in the analysis of financial assets.

The function return its obtained by the matrical format, being necessary, to obtain all output data, tree cells vertically selection and using of CTRL + SHIFT + RETURN after formula typing.

Call: MX.EWV (Data, Decay, Order, Returns, Null_Avg)

Note 1: Results must be extracted from excel through selecting area for results, inserting formula with output area selected and pressing CTRL+SHIFT+RETURN.

Note 2: The Microsoft Excel has limitations to the size of data informed for the external functions and its sheets. We advise to not use external functions calls with big data size from Excel sheets. In generic form, Microsoft Excel does not support a data number greater than 32.767 fields. More details, consult on-line Help or Microsoft Excel Support.

The results for a data set n (or n–1 for the EWV prices return), a decay factor and where the most old data has index n is:

• EWV Standard Deviation or Volatileness:can consider the data directly or the prices returns and apply a logarithmic operator to returns. Uses the informed order to weight the samples. If have application of logarithmic operator, this is reverted before returning results. The standard deviation is returned in the first cell.
  (Dados= Data; Retornos= Returns; Média= Mean; Média nula= Null Mean;)
  
  
Where:

• Mean: mean in data or returns, applied or not a logarithmic operator and using the informed order to ponder the samples. If exists application of logarithmic operator, its reverted before the results return. The mean is returned in the second cell.
  
  
  
Where:

• Tolerance: calculated for the amount of data n (or n-1 for prices return) and for the decay factor informed. The tolerance its returned in the third cell.
  

Example using prices return:

• Data interval: A1:E30 (145 days)
• Decay factor 0.94
• Older order,with most old days first. The value 15.73% its the oldest data.
• Analyze data return, using logarithmic operator
• Use calculated mean
• Data about interests rates from january to july 2001. The datas are next to the rate practised, but must be understood as hypothetical here. The last cells from interval are empty and are not considered in calculations (in fact, are ignored).

= {MX.EWV( A1:E30, 0,94, 0, 1, 0)}

Results:

The functions return its matrical and can be returned 1, 2 or 3 cell, being the first correspondent to the volatileness of returns (used the logarithmic operator and reverted teh final operation), the second correspondent to the return means (usied the logarithmic operator for the calculus and reverted for the final operation) and the third its correspondent to the tolerance.

• 1ª cell => Returns volatileness
• 2ª cell => Returns mean
• 3ª cell => Data amount tolerance

The following table illustrate the effected intermediate calculations.

Table of logarithmic returns with 144 data.

Table containing the contribution of each return (logarithmic return minus mean, raised to square and multiplied by the respective decay coefficient ). Observe that the first days (oldest) has a participation very low for variance calculation. The sum of this table its correspondent to the variance by exponential weighting. The square root of the sum its equivalent to the standard deviation or volatileness EWV.

Comparing the standard deviation result of 0.80% as the standard deviation equally weighed for the logarithmic return of 0.733% (not showed here), perceive the exponetial weighting effect.

Access:

• Menu - Metrixus | Best EWV Decay
• - Toolbar Metrixus

Description: Determinates the best decay factor to be used in the exponetial weighting of informed data. Uses a simulation process by exhaustion which objective to shrink teh forecats variance errors (or variance of variance) to the next instance, idicating the best decay factor in historic data.
The option Minimum tolerance determinates the amount of data used for the first forecast in each simulation. Given the minimum tolerance, if the size of the database does not be enough big for a determined decay factor, this factor will not be simulated as possible best factor. In example, for a database with 200 datas and a tolerance level set as 1%, only its possible to simulate best factors lesser than 0.97.

The option Analysis of returns from asset prices allow, as in the function EWV, effect the weighting of the returns direclty from prices.

The option its only able in case of returns of prices and allow the apply of logarithmic operators on the returns.

Also in the function EWV, its possible use data in alphabetical order direct or reverse. Use the option Reverse order (most recent data appears first) to indicate that most recent data are appears first and the most old data are the alst values informed.

-> Content

Previous: 2.2.12. Multiple Linear Regression

Next: 2.2.14. ARCH - GARCH - IGARCH - EGARCH - GJR (TGARCH)