Backtesting Trading Strategies in Power BI

backtesting trading strategies in power bi

There are a couple of requests about backtesting of trading strategies after the latest post on catching overbought stocks. I thought that would be an interesting experiment and I have decided to try it on Power BI instead of the usual python. It is easier in my opinion also.

The most challenging part of this task is not so much about the codes. It is coming up with the programming logic behind. Here is mine.

Bob earns 4k a month and he is interested to invest 10% of his income into the stock market. Bob wonders if it would be better to just DCA every month or time the market by waiting for a dip. Since I have written about the % from 50MA deviation, this would be a perfect backtesting case study.

1. DCA Strategy vs %<50MA Strategy

In the DCA strategy, he would invest 10% or $400 at the end of every month to buy whatever quantity of stocks based on the closing price on that day.

On the other hand, timing the market means Bob has to wait for a dip or correction. And because he is timing the market, Bob would want to go in more aggressively at dips.

My trading signal would be if the price falls 5% below the 50MA, buy whatever quantity at $400. But if the price falls 10% below the 50MA, he would double down and buy whatever quantity at $800. Finally, if the price is at or above the 50MA, don’t buy it because you want to time the market and buy low.

This is how it would look like visually.

Backtesting Trading Strategies

As long as the % change from 50MA falls below 5%, Bob would be buying every single day with $400 until that condition no longer holds true. And if % change from 50MA falls below 10%, Bob would be buying every single day using $800 until that condition no longer holds true. Any time the line chart falls below either of the red lines, it is a buy.

Those days when % change from 50MA is above > -5%, Bob does nothing.

That is the overview logic for both backtesting trading strategies. The next part would be translating these ideas into codes.

2. Backtesting Trading Strategies in DAX

2.1 DCA Trading Strategy Code

First, we need to create a new column called salary. It would credit in 4000 at every month-end. The logic for this in Power BI DAX is simply:

salary = IF(df[Date] = ENDOFMONTH(df[Date]), 4000, 0)

Second, we need to determine how much quantity of stocks to buy at every end of the month in our DCA strategy. The quantity is based on the price on that day. That’s the advantage of DCA if prices are high you buy little, if prices are low you buy more. Let’s name the quantity buys for our DCA strategy “Qty1”.

Qty1 = IF(df[Date] = ENDOFMONTH(df[Date]), ROUNDUP(400/df[Adj Close],0),0)

Third, we would also need to calculate the total capital spent on buying the stocks. Let’s call this “Capital_Invested1” which simply takes how much quantity you buy * the closing price on that day.

Capital_Invested1 = df[Qty1] * df[Adj Close]

Fourth, we need to also find the cumulative capital invested. Knowing the cumulative capital invested can tell us our daily profits and how much cash balance do we have left.

Total_Capital_Invested1 = CALCULATE(SUM(df[Capital_Invested1]), FILTER(ALL(df), df[Date]<=EARLIER(df[Date])))

Fifth, we would also add in a column called “Cash Savings”. This is simply how much cash balance we have left in the bank. The formula for this is simply the cumulative sum of your salary minus the cumulative capital invested.

Cash Savings1 = 
VAR earnings = 
    df[Date] <= EARLIER(df[Date])
earnings - df[Total_Capital_Invested1]

Sixth, to calculate the daily profits, we must first need to know our portfolio value. Our portfolio value is the cumulative quantity of stocks you have bought * closing price on that day.

Total_Portfolio_value1 = 
VAR Qty = 
    FILTER(ALL(df), df[Date]<=EARLIER(df[Date])))
Qty * (df[Adj Close])

Finally, the daily profits is just the total portfolio value minus cumulative capital invested.

Open Position1 = 
df[Total_Portfolio_value1] - df[Total_Capital_Invested1]

That’s it for our DCA strategy. We just need these few columns and that is sufficient to plot some insights in Power BI.

2.2 % From 50 MA Trading Strategy Code

As for our %50MA strategy, we just need to duplicate the formulas and create new columns with a different name (2 instead of 1 at the end of each title)

The primary difference is our quantity column. This is because the trigger signals are different. DCA buys only when it is the end of the month. %50MA strategy buys based on the % Change column in our dataset.

Based on the earlier strategy we described, this is how it would look like in DAX.

Qty2 test = 
df[% Change] <-10, ROUNDUP(800/df[Adj Close],0), 
df[% Change] <-5, ROUNDUP(400/df[Adj Close],0), 

Here comes the problem.

For DCA strategy, we only invest 10% of our salary at the end of each month. So Bob will always have sufficient cash balance on hand to invest.

But for our %50MA strategy, what happens if the stock price remains < 10% below 50MA for a couple of weeks. The code would ask Bob to invest $800 every single day until that condition becomes false. And what if Bob doesn’t have any cash left? Does he borrow and go into leverage?

If we follow the above steps, then yes. It is possible for “Cash Savings2”, the %50MA strategy, to go into a deficit position. This is a highly risky move and Bob will definitely not follow it.

2.3 The Circular Dependency Problem

Hence, we must tweak the number of stocks bought in such a way that cash savings MUST be >0. It can’t be blindly suggesting qty2 to be $800/Adj close price every single day even when Bob already has no money.

Yet it is not possible to simply write if cash savings <0, qty2 = 0, else $800/Adj Close. This is because cash savings is based on qty2 and qty2 is based on cash savings.

Backtesting Trading Strategies

This circular dependency is a known problem in software engineering. Here is how our situation look like in the above diagram.

In order to break out of the circle, I came up with a workaround which is to create entirely new columns. I copied all the columns and distinguish them between test and actual. Now we can write this logic in the new qty column.

Qty2 actual = IF(df[Cash Savings2 test]<0, 0, df[Qty2 test])

This is how the workaround looks like visually.

Backtesting Trading Strategies

The flaw of this is Bob might invest less than what he should have based on the %50MA strategy. This is because our new quantity is based on the old cash savings and we have to wait till it turns positive, even if he already has sufficient money in cash savings2.

This solution is not perfect and elegant, but at least it does keep Bob out of the market when he has no money. When cash savings turn positive from subsequent paydays, the %50MA strategy trigger signals for qty buys will resume once again.

2.4 Revised Qty Buys for % From 50 MA Strategy

Here is an example of how the actual and test compares.

Backtesting Trading Strategies

This is the stock price of Google from 2010. On 31 Mar 2010, $4000 of Bob’s salary came in. But no signals were triggered yet cause the price is still above 50MA. On 30 Apr 2010, the price dip -5% below 50MA hence, Bob will invest $400/price on that day which is about 2 qty.

Prices continue staying <-5% of 50MA and he continues investing every day from 4 May 2010 to 19 May 2010. Cash savings continue to decline as all his money goes into GOOG stocks.

But on 20 May 2010, GOOG price corrects even further to -10% below 50MA. Our strategy is to double down and invest $800/price on that day. But realise that cash savings have turned negative and he has already invested >100% of his income into the stock market.

To avoid this issue, we created those new columns and notice that “Qty2 Actual” got reset to 0 when cash savings1 turned negative. In this way, Bob stays out of the market despite the dip and he starts investing again only when cash savings1 turned positive.

3. Backtesting Trading Strategies in Power BI

Now that we have gone through the trading strategies, the concepts and the programming codes. It is time to put it all together now in Power BI visuals.

I will use the Vanguard S&P 500 Index Fund ETF (Ticker Symbol: VOO) for our first test and the timeframe for this backtesting is 10 years. Here are the results for VOO using our DCA strategy vs % from 50 MA strategy.

Backtesting Trading Strategies

In the summary stats table on the top-left corner, it breaks down the metrics for each strategy.

Qty buy is just how much units you have bought
Tx counts is the number of transactions you have made (to calculate commission cost)
Avg cost is your average cost price of that stock
Profits is how much money you have made
Total invested is how much money you have spent in total
Warchest is how much money you have left in your bank

If you have started out as a fresh graduate in 2010, you would have made $62.5k by investing 10% of your income every month into the markets. But if you buy aggressively at dips using the % 50MA strategy, your profits would be $80.2k.

Let’s try the Nasdaq index (QQQ).



Backtesting Trading Strategies

And FB.


And Apple.


And Google.

Backtesting Trading Strategies

And Tesla. ($12m profits)


And Alibaba.

Backtesting Trading Strategies

And Disney.


And Berkshire Hathaway.

Backtesting Trading Strategies

And Visa.


And JP Morgan.

Backtesting Trading Strategies

The list goes on and the patterns are pretty much consistent.

4. Backtesting Trading Strategies Conclusion

After backtesting both trading strategies on different stocks, % From 50 MA seems to always outbuy DCA in quantities. Total capital invested for %50MA strategy is also higher than DCA. And because we always buy low at dips, profits easily outperform that of DCA, if you buy good quality companies.

Interestingly, if I tried changing the %50MA signal to be based on corrections (<-15% – <-30%) rather than dips, Bob would have underinvest severely and DCA outperforms %50MA. Unless he more than quardruple his capital investments and invest extrmely aggressively.

A fine delicate balance must be achieved between buying at dips and buying at corrections. If you are too greedy, you don’t get to buy anything. If you buy high at retail, your profits are low.

Buying at 50MA is already a sound strategy by itself, simple yet powerful. What more if you are buying at -5% or -10% below the 50MA, your average cost price would definitely be attractive and your portfolio position will likely be better than that of a DCA as seen above.

  • 45

Be the first to comment

Leave a Reply

Your email address will not be published.