Calculate a 9 exponential moving average with an Excel formula

[grid]

Hello folks,

I have a question about how I can calculate a 9 exponential moving average with an Excel formula without using a script. It is important to me to know the formula for it. In this great forum I can only find a script for it that I don't need. I also searched the Internet and came across a website that explains the formula. I only see that the result is not correct or I am doing something wrong. To be more specific I have added an example that says exactly what I want

9ema formula.xlsx
	A	B	C	D	E
1
2	Meta close prices of 3-1-2023 until 28-1-2023
3
4	Date	Close
5	3-1-2023	124,74
6	4-1-2023	127,37
7	5-1-2023	126,94
8	6-1-2023	130,02
9	9-1-2023	129,47
10	10-1-2023	132,99
11	11-1-2023	132,89
12	12-1-2023	136,71
13	13-1-2023	136,98			In D17 is a 9ema of 17-1-2023 i tried to make by myself by replacing 12 in the formula with 9 but I'm not sure if that's correct.
14	16-1-2023	135,36			I need here the correct 9ema like any charting platform would display. In this case it would be 132,95. Could somebody help me with the correct Excel formula? I don't need a script because I need to have it working for example with Google Sheets too.
15	17-1-2023	135,36
16	18-1-2023	133,02	131,8208333	133,6444444
17	19-1-2023	136,15	132,486859	134,1455556	9ema on charting platform is 132,95
18	20-1-2023	139,37
19	23-1-2023	143,27
20	24-1-2023	139,37			In C17 is a 12ema that is calculated with the help of: https://investexcel.net/how-to-calculate-ema-in-excel/
21	25-1-2023	141,5
22	26-1-2023	147,3
23	27-1-2023	151,74
24	28-1-2023	147,06
Blad1

Cell Formulas
Range	Formula
C16	C16	=AVERAGE(B5:B16)
D16	D16	=AVERAGE(B8:B16)
C17	C17	=B17*2/(12+1)+C16*(1-2/(12+1))
D17	D17	=B17*2/(9+1)+D16*(1-2/(9+1))

Here is the link that has a script to calculate the 9 EMA: Exponential moving average using array in Excel VBA

Thank you very much!

grid

 

[offthelip]

Your equation for the ema is correct, however to understand why your answer is different to the trading platform answer you need to understand that the Exponential Moving Average is an infinite series so, what value you get depends totally on not just what the starting value is but also when you start the ema calculation. The platform value has probably be running for years so you would need to put many years of values in to get exactly the same answer. ( actually 90 days is probably sufficient for a 9 day ema) If you put the value from the platform into D16 instead of your average and then check your value against the value from the platform in D17 they should be the same.

 

[grid]

Hello offthelip,

So I need the last 90 close prices to get the accurate 9ema? I could get them. Anyways sorry for the late response and thanks for your answer.

Greets,

grid

 

[offthelip]

I have always used a factor of 10 as being adequate, however it really depends on how much the data you are calculating the moving average from varies. As an example if you put the value 10 in A2 to a45 and the value 50 in A46 to A150 and then put the 9 day moving average calculation in column B starting with zero in B2. You can see that on row 45 ( the last row with 10 in it) the EMA hass got to 9.999319435 which is with 0.007% of the target, after 90 days with target of 50 the values has got to 49.99825772, which is within 0.0003% of the target, both of these are very close. However if you now type the value 20 into A80 the value in B90 changes to 49.35401263 this is within 0.13% much further away. So how long you need to "stabilise" your ema is up to your judgement . I suggest you do a bit of testing. If it is convenient to download 90 days get all the data , then make comparisons between the values you get from starting with the full 90 days, then the last 60 days then the last 30 days and see what sort of difference you get and whether the difference is tolerable.