Mike Allen's Blog

Doing Smarter Things With IT

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”

     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 "")
     #"Add EMA"

Here’s a picture of the final output, any feedback is greatly appreciated, thank you for reading:


Comments are closed