Mike Allen Systems

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”

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:

image

Aging Debt using Power Query Lists

Power BI problems that require accessing previous rows of data can be difficult in Power BI, one way of resolving this is to translate columns into lists and then process the lists. This is an example that performs debtors aging, where we have one payments and five aging buckets that we wish to allocate those payments into. Please refer to my previous post http://mikeallen.com.au/post/Creating-an-Aging-Calendar-in-Power-Query as to how the aging buckets are created and assume that transactions have been grouped similar buckets numbered B1 thru B5.

Here is some sample data:

image

Here are the payments:

image

The method I’m going to use to age involves comparing the payment to the cumulative debt on the account going from B5, the oldest to B1 the newest,  so I need to get the data to look like this:

image

When I have this data I then apply this formula to each line to calculate the aging value:

if [Payment] >= [Cumulative] then 0
                     else if [Payment] < [Cumulative] and [Payment] > ([Cumulative] - [Actual])
                             then [Cumulative] - [Payment] else [Actual])

This handles the three cases of a fully paid, partially paid and unpaid buckets in one calculation, which I think is pretty neat!

Here is the Power Query that does all this, this is a Beta, I’m still refining it. The data is held in an Excel workbook in the format as shown above:

let
     Source = Excel.Workbook(File.Contents("BucketAging.xlsx"), null, true),
     Buckets_Sheet = Source{[Item="Buckets",Kind="Sheet"]}[Data],
     #"Promoted Headers" = Table.PromoteHeaders(Buckets_Sheet, [PromoteAllScalars=true]),
     #"Removed Other Columns" = Table.SelectColumns(#"Promoted Headers",{"Account", "Type", "Value"}),
     GroupRowsByAccount = Table.Group(#"Removed Other Columns", {"Account"}, {{"DataTable", each _, type table [Account=number, Type=text, Value=number]}}),
     MergePayments = Table.NestedJoin(GroupRowsByAccount, {"Account"}, Payments, {"Account"}, "Payments", JoinKind.LeftOuter),
     ExpandPaymentValue = Table.ExpandTableColumn(MergePayments, "Payments", {"Value"}, {"Value"}),
     RenamedPaymentColumn = Table.RenameColumns(ExpandPaymentValue,{{"Value", "Payment"}}),
     AddBucketNumbers = Table.AddColumn(RenamedPaymentColumn, "Bucket", each List.Numbers(1,5)),
     ExpandBuckets = Table.ExpandListColumn(AddBucketNumbers, "Bucket"),
     AddAgingList = Table.AddColumn(ExpandBuckets, "AgingList", each [DataTable][Value]),
     AddCumulativeValue = Table.AddColumn(AddAgingList, "Cumulative", each List.Sum(List.FirstN([AgingList],[Bucket]))),
     AddActualValue = Table.AddColumn(AddCumulativeValue, "Actual", each [AgingList]{[Bucket]-1}),
     AddAgingCalc = Table.AddColumn(AddActualValue, "Aging", each if [Payment] >= [Cumulative] then 0
                     else if [Payment] < [Cumulative] and [Payment] > ([Cumulative] - [Actual])
                             then [Cumulative] - [Payment] else [Actual]),
     AddBucketList = Table.AddColumn(AddAgingCalc, "AgingBuckets", each [DataTable][Type]),
     AddAgingBucket = Table.AddColumn(AddBucketList, "AgingBucket", each [AgingBuckets]{[Bucket]-1})
in
     AddAgingBucket

That’s all expanded out, so remove the columns that you don’t want.

I’ve tried to make the code self-documenting, it is conceptually fairly simple, you group the data down to a row for each account, expand it out for each bucket (in reverse order), then convert the values into a list and perform arithmetic on the list values while extracting the values back out again. Have a play, Lists in Power Query make it possible to do computation across rows of data.

This technique has been tested on several thousand accounts and performs well, any feedback is appreciated, this information is provided with no guarantee whatsoever Smile.







: