This is my first attempt attempt at EMA calculation in Power Query, this is a work in progress. My intention is to use this work as a basis for technical indicators used in the stock market such as Bollinger Bands.
I have taken my EMA Example from https://stockcharts.com/school/doku.php?id=chart_school:technical_indicators:moving_averages which I find very useful on the subject. I was also informed by Reza Rad at https://radacad.com/list-accumulate-hidden-gem-of-power-query-list-functions-in-power-bi which showed me how to use List.Accumulate, then I just needed to find an application for that knowledge and here it is.
The data that I have used is from the stockcharts.com EMA example, this helped me validate my solution, I started from their downloadable data. I’ve put some comments in the code, the magic is in the List.Accumulate, of course. The input data is in an Excel Workbook, in a table called “DataTable”, with two columns “Date” and “Close”
let
Source = Excel.Workbook(File.Contents("emadata.xlsx"), null, true),
DataTable_Table = Source{[Item="DataTable",Kind="Table"]}[Data],
#"Changed Type1" = Table.TransformColumnTypes(DataTable_Table,{{"Date", type date}, {"Close", type number}}),
EMA_Factor = .1818 ,
Average = 10 ,
#"Changed Type" = Table.TransformColumnTypes(#"Changed Type1",{{"Close", type number}}),
// Add an Index to out close price table
#"Add Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
// Create a list of the closing prices
ClosePriceList = #"Add Index"[Close],
// Calculate SMA , the Simple Moving Average
#"Add SMA" = Table.AddColumn(#"Add Index", "SMA", each if [Index] >= Average then List.Sum(List.Range(ClosePriceList,[Index]-Average,Average))/Average else ""),
// Get the first SMA value to be the first EMA
StartEMA = Table.Range(#"Add SMA",Average-1,1)[SMA]{0},
// Calculate the Expotential Moving Average
#"Add EMA" = Table.AddColumn(#"Add SMA", "EMA", each if [Index] >= Average then List.Accumulate(List.Range(ClosePriceList,Average,[Index]-Average),StartEMA,(state,current)=>((current - state) * EMA_Factor) + state) else "")
in
#"Add EMA"
Here’s a picture of the final output, any feedback is greatly appreciated, thank you for reading:
