## EMA Calculations with Power Query

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: