Calculating EWMA

Could anyone assist me urgently please?

With reference to this thread:

https://www.excelforum.com/excel-for...te-column.html

I tried posting the formula into the original posters thread, as seen in the following quote:

meanwhile i got the solution. put the follow formula into G2522

=(SQRT(SUMPRODUCT(SMALL((1-R2)*R2^(ROW(2389:2522)-ROW(A2389));ROW(2389:2522)-ROW(A2389)+1);I2389:I2522)))*SQRT(250)

cheers

Is the formula correct? I am looking to modify the formula for my own needs but I need to make sure the above formula works first, which it doesn't seem to. The software refuses to accept it.

I have posted my excel spreadsheet for anyone who is able to help. It is the sheet named "United States". For my needs I do not need to Square root what I get and then multiply by the square root of 250, so I assume the start and end points are not needed. I have been able to identify that I2389:I2522 in the above formula is simply Returns² so that part is ok, it's this part which has me completely stumped as to what it's doing.

SMALL((1-R2)*R2^(ROW(2389:2522)-ROW(A2389));ROW(2389:2522)-ROW(A2389)+1)

Any help asap appreciated, as this has to do with my dissertation.

Welcome to the forum (again )

If you posted your file here, it didnt come through
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Thanks.

What exactly are you trying to do here?
(keep in mind I am no statistician - can barely even spell that lol)

Originally Posted by FDibbins

Thanks.

What exactly are you trying to do here?
(keep in mind I am no statistician - can barely even spell that lol)

Note: Lambda = 0.94. I need EWMA in one column (column K), without having to do the following steps repeated in 5,000 columns, 5,000 times.

If you look at the spreadsheet I posted essentially what I'm trying to do stepwise is as follows.

1) Calculate Weights 1 (column E).....This is essentially:
in cell E5220... (1-lambda)
in cell E5219....E5220*lambda........i.e. the following day's Weight*lambda
in cell E5218....E5219*lambda........i.e. the following day's Weight*lambda
etc

2) Column F (R^2*Weights) = Returns squared (column D) * Weights 1 (column E)
in cell F5220.....D5220*E5220
in cell F5219.....D5219*E5219
etc

3) In cell G5220 = Sum of the whole of Column F (R^2*Weights)
This can also be done using

=SUMPRODUCT(D$3:D5220,E$3:E5220)

which basically skips the need for step 2. It's a workaround not sure if it can be incorporated into the final formula.

4) Repeat steps 1-3, but do not touch the Returns squared column, we do not need to modify this. What we need are new weights, i.e. Weights 2, however we go one cell up. So:
Calculate Weights 2 (column H).....This is essentially:
in cell H5219... (1-lambda)
in cell H5218....H5219*lambda........i.e. the following day's Weight*lambda
in cell H5217....H5218*lambda........i.e. the following day's Weight*lambda
etc

You could calculate Column I (R^2*Weights 2) or you could skip this and use the following in cell J5219:

=SUMPRODUCT(D$3:D5219,H$3:H5219)

_________

These 2 values highlighted in yellow (cells G5220 and J5219) are my EWMA, but I need them in a single column, like you see with the highlighted red cell in K5220, such that I don't repeat these steps again and again with new columns.

You will notice that G5220 and K5220 are identical, this is because the cell K5220 which is the EWMA can also be calculated as follows:

=lambda*J5219+(1-lambda)*D5219

i.e. lambda*the previous day's variance +(1-lambda)*the previous day's Returns squared(R^2)


Hope it's clear now.