Calculating Exponential Moving Average in one cell (Scan Function?)

[aryan1997]

Hi all,

I am trying to figure out how to calculate the Exponential moving average of a stock. The sheet shows the steps involved. I tried using the scan function but I couldn't figure it out. I hope someone smarter than me can help with the formula or point me in the right direction. I know I can do it by adding helper columns but this will be much more efficient and help me learn something new. Thanks.

Book1
	A	B	C	D	E	F	G	H
1	APPLE INC. (XNAS:AAPL)
2		Date	Closing Price	EMA Calculation		Multiplier	0.3
3		08-22-2022	$ 167.57
4		08-23-2022	$ 167.23
5		08-24-2022	$ 167.53
6		08-25-2022	$ 170.03
7		08-26-2022	$ 163.62
8		08-29-2022	$ 161.38
9		08-30-2022	$ 158.91
10		08-31-2022	$ 157.22
11		09-01-2022	$ 157.96
12		09-02-2022	$ 155.81
13		09-06-2022	$ 154.53
14		09-07-2022	$ 155.96
15		09-08-2022	$ 154.46
16		09-09-2022	$ 157.37
17		09-12-2022	$ 163.43	160.68	#Starting no. is the last 14 day average
18		09-13-2022	$ 153.84	159.69	# After that its (Close - Previous Day EMA)*Multiplier + Previous Day EMA
19		09-14-2022	$ 155.31	160.81
20		09-15-2022	$ 152.37	158.72
21		09-16-2022	$ 150.70	157.70
22		09-19-2022	$ 154.48	156.10
23		09-20-2022	$ 156.90	154.48
24		09-21-2022	$ 153.72	154.48
25		09-22-2022	$ 152.74	155.21
26		09-23-2022	$ 150.43	154.76
27		09-26-2022	$ 150.77	154.15
28		09-27-2022	$ 151.76	153.04
29		09-28-2022	$ 149.84	152.36
30		09-29-2022	$ 142.48	152.18	# Value I am looking for
Sheet1

Cell Formulas
Range	Formula
B3:C30	B3	=STOCKHISTORY(A1,WORKDAY(TODAY(),-29),TODAY(),0,0,0,1)
D17	D17	=AVERAGE(C3:C16)
D18:D30	D18	=((C16-D17)*$G$2)+D17
Dynamic array formulas.

 

[offthelip]

I have written my own EMA function that I use for exactly this purpose and I keep it in an addin so I can use it anywhere, you could put itin your personal.xlb file:
put 14 id D1 this is the time period, then put this in D18:

=ema(D$1,D17,C18)

and copy down:
this is the code for hte ema fucntion;

Function EMA(Period, Lasttime, Drive)
If Not (IsNumeric(Period)) Then
  EMA = "This functions calculates the Expontial Moving average, it has three parameters: Period (days), The last time Value of the EMA, the latest value of variable"
Else
    TC = 2 / (Period + 1)
    EMA = Lasttime + TC * (Drive - Lasttime)
End If
End Function

 

[aryan1997]

I have written my own EMA function that I use for exactly this purpose and I keep it in an addin so I can use it anywhere, you could put itin your personal.xlb file:
put 14 id D1 this is the time period, then put this in D18:

=ema(D$1,D17,C18)

and copy down:
this is the code for hte ema fucntion;

Function EMA(Period, Lasttime, Drive)
If Not (IsNumeric(Period)) Then
  EMA = "This functions calculates the Expontial Moving average, it has three parameters: Period (days), The last time Value of the EMA, the latest value of variable"
Else
    TC = 2 / (Period + 1)
    EMA = Lasttime + TC * (Drive - Lasttime)
End If
End Function

Thanks for the reply but this doesn't give me the solution to the problem. This would still require me to have separate columns for closing price and multiple cells to calculate separately. I am looking to create a formula that would output the final value (152.18) in the same cell as the formula.

On a side note, with lambda you can create the custom function you mentioned without vba.

 

[aryan1997]

Thanks for the reply but this doesn't give me the solution to the problem. This would still require me to have separate columns for closing price and multiple cells to calculate separately. I am looking to create a formula that would output the final value (152.18) in the same cell as the formula.

On a side note, with lambda you can create the custom function you mentioned without vba.

Just realized that you are a well-known member, so you probably know about lambda already

 

[offthelip]

You can use my ema function and wrap in another function like this:

Sub test()
Dim first As Range
Dim second As Range
Set first = Range("C3:c16")
Set second = Range("c17:c30")
result = totema(first, second, 14)
MsgBox result
End Sub
Function totema(Avrange As Range, emarange As Range, Period)
startav = WorksheetFunction.Average(Avrange)
emaarray = emarange
For i = 1 To UBound(emaarray, 1)
 startav = EMA(Period, startav, emaarray(i, 1))
Next i
totema = startav
End Function

Function EMA(Period, Lasttime, Drive)
If Not (IsNumeric(Period)) Then
  EMA = "This functions calculates the Expontial Moving average, it has three parameters: Period (days), The last time Value of the EMA, the latest value of variable"
Else
    TC = 2 / (Period + 1)
    EMA = Lasttime + TC * (Drive - Lasttime)
End If
End Function

You can then put this in a singel cell:

=totema(C3:C16,C17:C30,14)

 

[aryan1997]

You can use my ema function and wrap in another function like this:

Sub test()
Dim first As Range
Dim second As Range
Set first = Range("C3:c16")
Set second = Range("c17:c30")
result = totema(first, second, 14)
MsgBox result
End Sub
Function totema(Avrange As Range, emarange As Range, Period)
startav = WorksheetFunction.Average(Avrange)
emaarray = emarange
For i = 1 To UBound(emaarray, 1)
 startav = EMA(Period, startav, emaarray(i, 1))
Next i
totema = startav
End Function

Function EMA(Period, Lasttime, Drive)
If Not (IsNumeric(Period)) Then
  EMA = "This functions calculates the Expontial Moving average, it has three parameters: Period (days), The last time Value of the EMA, the latest value of variable"
Else
    TC = 2 / (Period + 1)
    EMA = Lasttime + TC * (Drive - Lasttime)
End If
End Function

You can then put this in a singel cell:

=totema(C3:C16,C17:C30,14)

Ah got it, this is a good solution as well. I was eventually able to figure it out by using the scan function.

 

[aryan1997]

My solution: Here is the custom function I was able to create. I can already see some improvements I could make but for now this gets the job done. One example is that instead of using SCAN we can use REDUCE to get the final value instead of me using Index and Count to get it.

LAMBDA(stock, periodindays,
LET(
stocksymbol, stock,
period, periodindays,
smoothingfactor, 2 / (period + 1),
startingaverage, AVERAGE(
STOCKHISTORY(
stocksymbol,
WORKDAY(TODAY(), -period * 2),
WORKDAY(TODAY(), -period),
0,
0,
1
)
),
emadata, STOCKHISTORY(
stocksymbol,
WORKDAY(TODAY(), -(period - 1)),
TODAY(),
0,
0,
1
),
currentemadata, SCAN(
startingaverage,
emadata,
LAMBDA(a, b, ((b - a) * smoothingfactor) + a)
),
currentema, INDEX(currentemadata, COUNTA(currentemadata)),
currentema
)
)