calculate exponentially weighted moving average (EWMA) in one cell without separate column

[kmaloney01]

i already calculuated the EWMA (exponentially weighted moving average) in excel calculate EWMA (Exponentially weighted moving average).xlsx (G2523 in the excel file, the EWMA is red marked) for the last day.

i need to calculate the EWMA for all historical dates... (G2522, G2521 etc.) that i don't need to generate 100 new columns (for column J, K & L), i'm looking for a way to generate G2523, G2522 etc. with a single formula in one cell?

for those who don't know EWMA here's a little explanation: https://www.youtube.com/watch?v=P_tr9_Ue220

thank you very much for your help.

Excel File EWMA:
calculate EWMA (Exponentially weighted moving average).xlsx

[Glenn Kennedy]

I'm not a financial analyst, or ANYTHING like it, so I didn't watch the clip the whole way through. Four stoopid questions, though:

Lambda featured in the clip, but you don't seem to be using it. Why?
Why choose the square root of 250 (columns G & N), as opposed to any other number?
Why does your date range for the EWMA cover 133 days (column L)? Why not 137 (random number) or 365 (a rolling year)?
Why not just copy the formulae back up the columns? Doesn't that give you your result?

Sorry if these Qs are dopey, I just don't understand EWMA yet!!

[kmaloney01]

thank you for looking at the file. here the answers

Lambda featured in the clip, but you don't seem to be using it. Why?
Answer: Lambda is used, look at column J "weights"

Why choose the square root of 250 (columns G & N), as opposed to any other number?
Answer: M is the daily volatility, to calculate the year volatility the market has around 250 open days a year and not 365

Why does your date range for the EWMA cover 133 days (column L)? Why not 137 (random number) or 365 (a rolling year)?
Answer: because it doesn't matter, column L is only the weight average & it has to be 100 %.

Why not just copy the formulae back up the columns? Doesn't that give you your result?
Answer: well it's not that easy, i need a new colums M, N & O to calculate one new EWMA

here's a new file i calculated the next EWMA with new columns, it may helps to understand. i don't wanna create 130 new columns to get the result...

calculate EWMA (Exponentially weighted moving average) 1.01.xlsx

I'm not a financial analyst, or ANYTHING like it, so I didn't watch the clip the whole way through. Four stoopid questions, though:

Lambda featured in the clip, but you don't seem to be using it. Why?
Why choose the square root of 250 (columns G & N), as opposed to any other number?
Why does your date range for the EWMA cover 133 days (column L)? Why not 137 (random number) or 365 (a rolling year)?
Why not just copy the formulae back up the columns? Doesn't that give you your result?

Sorry if these Qs are dopey, I just don't understand EWMA yet!!

[Glenn Kennedy]

OK. I see the problem now. Dunno if I can help, but we'll see.

[kmaloney01]

i think with formula =SUMPRODUCT((ROW(...)) ... its possible to solve it, but i'm not an expert in this, anyone an idea?

[kmaloney01]

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

[ewma]

Could anyone assist me urgently please? I tried pasting the above formula into the spreadsheet posted above, but it doesn't seem to work.

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.

Any help appreciated.

[FDibbins]

ewma welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.