How to build your own strategy comparison spreadsheet (Patreon)

Hello my dear Patrons and welcome to this post where I will explain in detail  how you can build your own spreadsheet to compare your trading strategies.

I thought about making a video about this subject first but I decided to make a post of it so that I can show you more detail and also give you the formulas and hints at the correct moments. I hope you don't mind.

As far as I know, you can create this sheet in all the popular spreadsheet programs like Excel, Google sheets and LibreOffice Calc and others. As long as it contains the '=IF' function.

Creation of this sheet is quite simple but it can help you enormously in determining which strategy is the correct one for you. And I deliberately say 'strategy' and not 'algorithmic bot trading strategy', because you can use this for normal trading strategies as well. As long as you use the correct parameters, ratios and score factors.

A little background to create some context

First let me tell you a little bit about the background and origins of this sheet and then we’ll dive right into the creation of it. So bear with me .

First of all I want to tell you something about the reasons why I use this method. 

Way back, during the previous crypto bear market I again became interested in Bitcoin and crypto in general. I heard about bitcoin around 2014 when it was 25 cents or so, and never thought about it again. But in that last bear cycle I recognised the potential of crypto and decided to follow it again. I also began trading Bitcoin, Ethereum and other coins to attain more of that magic internet money and had no Idea what I was doing.

After getting rekt a few times (mind you, it weren't huge amounts of money, but still you could have a nice vacation with it...) I became aware that I had to attain more knowledge about how to trade professionally to make some real money. 

By that time I also read about the psychological factors in trading. And after a while I was happy to get break even and recognised that I simply did not have the time, nor the psychological treats to be an active trader. After that conclusion I searched for a solution for both these problems - my mind and my time. 

I then found out that using an automatic trading bot was the answer to both my time and psychological problem. I then used commercial solutions (3commas, Cryptohopper etc) and open source bots like the Gekko trading bot. But after a while I found out that Freqtrade was the best fit for me and until this day I am still using this great program. Freqtrade has great flexibility and a lot of good options. However it is quite technical and the learning curve is steep. But you'll probably know that from my video's.

By using an automated bot a new problem arose: "Which algorithmic strategy is the best to run on the bot which gives me the best positive results with the least chance of losing my money?"

I had to trust the system instead of myself and that is only possible if I have absolute faith in not only technical aspects like up-time, latency and responsiveness but of course also the trading strategy itself.

Searching for the best algo trading bot strategy

And this is where I am now. Finding the best algorithmic trading strategy to let it run on my bot.

Now I have tested quite a lot of algo strategies up until now. More than I already posted on my YouTube channel. A lot of them were during the time when I was using the Gekko bot which was before I even started my YouTube channel. And making a video of these tests takes a big chunk of my time so therefore I did not recreate these as a video (maybe later though)...

While I was testing these strategies I was also lacking a methodical way to find out which strategies were the best for my use and also how to compare these.

In the beginning I was only focussed on the profits of these strategies and was not paying attention to other factors like winrate, drawdown and ratios like the Sharpe and Sortino ratio as they are a relative new addition to the Freqtrade bot. But before these additions I had to use other metrics.

After a while I realised that these were as important, or even more important, then only gained profits. It’s nice to know that your strategy has a lot of gains but when it is giving large amounts of profits back to the market, the fun quickly disappears during these periods. Other factors and metrics are used to determine if a strategy nog only has good profits, but also keep them as well.

The only new problem arose and that is that there are many MANY of these metrics and other calculations available. As you can see in the summary metrics above. Together with the situation before I had to use a ways to use these metrics to get an overall feeling and conclusion which strategy was the best, according to my personal risk appetite.

So this is where my earlier skills as a project portfolio manager came into play. In that job I was responsible for ranking all the projects a large organisation was planning for the coming year. Based on that ranking the decision could be made which programs to execute and which not. This system, albeit simplified in this specific situation, is also applied to the algorithmic strategy ranking list. Also known as the Strategy league.

Creating the spreadsheet for comparison

So, now we come to the point where I will explain to you how you can build a similar ranking spreadsheet. Add your preferred scoring and points system and then compare your own strategies with the help of that list.

Let’s begin by creating a new spreadsheet. I will use Excel, but as said other spreadsheet programs will work as well. The only issues there might be are that functions may be called different. Also be aware that other country settings might use commas instead of points for decimals or vice versa.
I will not go into the look & feel or make-up of the spreadsheet. That I leave up to your own creativity.

For this demonstration I will use the Freqtrade summary metrics you see on your terminal screen every time a backtest has been done. You can see how this looks in the picture of the previous section of this post.

Freqtrade provides a lot of other output as well in its results overview, but for this demonstration the summary metrics will suffice.

So now the first and most important question you should ask yourself is:

“What information do I need to determine if this algo strategy performs well for me?”

Some people only need a couple of info points like Absolute profit, win rate and draw down. Other people need more information to decide if a strategy performs well and to their expectations.

This is completely subjective and you should decide that for yourself!

For demonstration purposes I will use the following metrics to determine if a strategy is good enough for me to use (or at least do the next step in the overarching strategy testing cycle: a forward test or dry run):

• Total profit percentage
• CAGR
• Sortino
• Sharpe
• Calmar
• Drawdown

As said, you can go completely nuts with these metrics and even create your own by combining these results. Like for example a ratio between market change percentage and total profit percentage of this strategy (which is an interesting idea come think of it...).

But my advice is to keep it relatively simple, especially in the beginning. Otherwise you’ll end up in a state which is also known as “analysis paralysis”. And cannot decide what is good or not.

What I also almost forgot to mention here is that you’ll have to test your strategies against the same bot setup and subset of data. Otherwise you cannot compare these results with each other. But I hope you already knew that by now.

Lets first add some information like the strategy name and the time-frames I use to compare these. And now I will add these 6 metrics into the spreadsheet as columns and also add the column ‘score’ at the end.

Now your spreadsheet will look something like this:

Now I will add another 6 columns that will be the headers of the scores we will calculate a little bit later on.

Let’s call these:

• Profit score
• CAGR score
• Sortino score
• Sharpe score
• Calmar score
• Drawdown score

And your spreadsheet will look like this now:

Let’s also add some initial values of the backtest in the spreadsheet. This will help to see if the calculations are correct or not. These are from the summary metrics picture earlier.

Adding a separate scoring sheet

Now add a new sheet to the workbook and let’s call this sheet ‘Score_factors’. 

This sheet will contain the scoring tables. The reason why I do this on a separate sheet is that, if I want to adjust a certain score, I can do this from a central sheet. 

If I change the scoring here, all the other calculations will be adjusted to my new scoring and I do not have to worry about recalculating all the earlier tested strategies and their respective scoring.

By the way, let’s also rename Sheet1 to ‘Strategies’.

Now let's add the following column pairs to the score_factors sheet.

• Profit metric: Profit score
• CAGR metric: CAGR score
• Sortino metric: Sortino score
• Sharpe metric: Sharpe score
• Calmar metric: Calmar score
• Drawdown metric: Drawdown score

Now comes another part where your own subjective preferences come to play. Here you’ll have to determine for yourself how you want to score each metric and how many points you want to give to each score.

To give you an example:

Every time a strategy scores below 0. That is: with a loss. I deduct 10 points of the total score it has.

So here I add 0 to the metric and -10 to the score it gets.

Then if a strategy scores between 0 and 50 percent, I give it a score of 10. If it has a profit between 50 and 100 percent I’ll score it 20 points etcetera.

This scoring can be linear, parabolic, something in between. Whatever you want. As long as it balances out your wish for profits and appetite for risk. You’ll have to find out at which scoring you are comfortable with.

I’ll add some other values and leave a score for everything that has a profit of higher than 1000 percent. All these strategies will get 125 points. And again, if a certain scoring does not feel right, then adjust it. There is no right or wrong here and everybody has his or her own preference.

Then I’ll do the CAGR metric and I’ll add just a scoring between 0 and 100 percent here divided in parts of 25 percent. Everything that is below 0 percent will get 10 points deducted and every strategy that has a CAGR higher than 100% gets 25 points. Again, it is completely up to you how you score these and which level of detail you want to use.

Now the Sortino, Sharpe and Calmar metrics are a little bit more special. Now remember that I said that you should score according to your preference. Well, that is still the case here but with these ratios there is something else going on because these metrics use a certain range to measure a strategies performance. According to Investopiedia’s page on the usage of the Sharpe ratio, this ratio should be considered good if it scores higher than 1, scores very good if it is higher than 2 and excellent if it scores higher than 3. If it is 1 or lower, then it is considered suboptimal.

This scoring type is also the case with the Sortino and Calmar metrics. So here you should use at least scores of 0, 1, 2 and 3 and above.

Now comes the part where you individually have to decide how to score these different metrics. Do you score them all similarly? Do you prefer the Sharpe ratio more, and therefore score it higher or just the opposite. It’s up to you to decide.

For now I am using fictional scores here to give an example. Please add your own preferences here because mine are not optimised.

And finally there is the Drawdown metric. Since drawdown has a reverse order, namely 0 percent drawdown is the best and 100 percent drawdown the worst. The scoring here should be reversed. So the higher the percentage, the lower the scoring. Or even negative.

And since higher than 100 percent drawdown is not possible, I’ll use quarters of 25 percent here and the rest has the lowest score.

The score_factors sheet should now look something like this:

Creating the formulas for determining the score

Now we come to the part where we have to create the formulas that eventually add up to the final score of a strategy.

Part of this can be done in the excel cell, but I find it also convenient to do some coding in a text editor like studio code or just a notepad.

The formula used is the ‘=IF’ formula. And goes like this for the profit metric:

IF the profit metric of a strategy is lower than 0, THEN use profit score -10 ELSE IF the profit metric of a strategy is lower than 50, THEN use score 10 ELSE IF...

and so on and so on… until all other metrics are above 1000, they will receive a score of 125.

Let’s see this in action:

First I am going to create the initial formula for the profit

score:=IF(B4<Score_factors!A2,Score_factors!B2,Score_factors!B8)

Now I can use this initial formula and edit it in my text editor. I’ll have to be aware that the references to the cells and values in the other ‘score_factors’ sheet should be locked.For that I’ll have to use the $ sign for the row and column reference like this:

=IF(B4<Score_factors!$A$2,Score_factors!$B$2,Score_factors!$B$8)

Now let’s add the next profit metric and respective score the Backtest values should be compared with. But this time I’ll do it in the text editor:

Copy the IF statement after the = (equal sign) and replace the final ‘Score_factors!$B$8’ with it.

=IF(B4<Score_factors!$A$2,Score_factors!$B$2,IF(B4<Score_factors!$A$2,Score_factors!$B$2,Score_factors!$B$8))

Then adjust the pasted values $A$2 and $B$2 to refer to the next row in that scoring sheet:

=IF(B4<Score_factors!$A$2,Score_factors!$B$2,IF(B4<Score_factors!$A$3,Score_factors!$B$3,Score_factors!$B$8))

Up until now the profit score field stayed at 125. That is because the strategy score of 84.66 was not below cell A2 (0) or A3 (50). So the formula gave it the ELSE value of 125.

But this will be different next time.

Now again paste the formula 

‘IF(B4<Score_factors!$A$2,Score_factors!$B$2,Score_factors!$B$8’

Over the last ‘Score_factors!$B$8’ value. Adjust the $A$2 and $B$2 to refer to the next row (4) in that scoring sheet. The formula should look like this now:

=IF(B4<Score_factors!$A$2,Score_factors!$B$2,IF(B4<Score_factors!$A$3,Score_factors!$B$3,IF(B4<Score_factors!$A$4,Score_factors!$B$4,Score_factors!$B$8)))

Paste this in the Profit score sell of the strategies sheet and see what happens…

If everything went well, the Profit score changed into 20, which is the score you gave to profits between 50 and 100 percent.

Now repeat the replacement of the ‘Score_factors!$B$8’ part in your formula until you end up with row 7 of your score factors:

=IF(B4<Score_factors!$A$2,Score_factors!$B$2,IF(B4<Score_factors!$A$3,Score_factors!$B$3,IF(B4<Score_factors!$A$4,Score_factors!$B$4,IF(B4<Score_factors!$A$5,Score_factors!$B$5,IF(B4<Score_factors!$A$6,Score_factors!$B$6,IF(B4<Score_factors!$A$7,Score_factors!$B$7,Score_factors!$B$8))))))

And replace the Profit score formula with the final version.

Now you can test if this formula calculates the correct scores by changing the score of the  ‘Total profit %’ field of the strategy. Also test if it gives the negative score when your strategy has a loss.

This exact same principle can be applied to all the other score factors as well.

Create the initial IF, THEN, ELSE formula and adjust it until all metrics are used to score them.

I have prepared all the formulas for the other metrics as well for you. So I’ll add them to the sheet to save you some time. Stay aware though that your sheet can be a little bit different and some calculations might not work or give unexpected results.
If that is the case, then this is the 'assignment' moment where I tell you to do some investigation where the errors are...

CAGR

=IF(C4<Score_factors!$C$2,Score_factors!$D$2,IF(C4<Score_factors!$C$3,Score_factors!$D$3,IF(C4<Score_factors!$C$4,Score_factors!$D$4,IF(C4<Score_factors!$C$5,Score_factors!$D$5,IF(C4<Score_factors!$C$6,Score_factors!$D$6,Score_factors!$D$7)))))

Sortino

=IF(D4<Score_factors!$E$2,Score_factors!$F$2,IF(D4<Score_factors!$E$3,Score_factors!$F$3,IF(D4<Score_factors!$E$4,Score_factors!$F$4,Score_factors!$F$5)))

Sharpe

=IF(E4<Score_factors!$G$2,Score_factors!$H$2,IF(E4<Score_factors!$G$3,Score_factors!$H$3,IF(E4<Score_factors!$G$4,Score_factors!$H$4,Score_factors!$H$5)))

Calmar

=IF(F4<Score_factors!$I$2,Score_factors!$J$2,IF(F4<Score_factors!$I$3,Score_factors!$J$3,IF(F4<Score_factors!$I$4,Score_factors!$J$4,Score_factors!$J$5)))

Drawdown

=IF(Strategies!G4<Score_factors!$K$2,Score_factors!$L$2,IF(Strategies!G4<Score_factors!$K$3,Score_factors!$L$3,IF(Strategies!G4<Score_factors!$K$4,Score_factors!$L$4,IF(Strategies!G4<Score_factors!$K$5,Score_factors!$L$5,Score_factors!$L$6))))

Now that I have added and tested all these formulas, Its time to calculate the score of the strategy on this specific timeframe.

Summing up the scores

In this example with fictitious test data I only have to sum up all the different scores I got for all the different metrics. For this I use the ‘=SUM’ function in Excel.

And these numbers add up to a score of 40 for this timeframe.

To do all these calculations for the other timeframes, I only have to select and drag these calculations to the last row.

The advantage of using an absolute reference ($A$1) to the scoring table is that I do not have to redo all these formula creations. The only referred cell that is changed is the cell of the next row in this strategy sheet.

Adding fictitious data and fine tuning

I”ll add some other fictitious data to this sheet to give it a final look.

Here you can see the final sheet with fictitious data.

The 1 hour and 30 minute time frame both have a score of 120. You can handle this situation in 2 ways. Accept the score and look to the other metrics to determine your preferred time frame. For example: you let the Sharpe ratio decide to take the 30 minute time frame to do your trading on.

A second way to force your decision is to adjust your scoring system so that it gives more resolution to the final score. Here also determined by your preferred metric.

So In this case I could adjust the drawdown metric and scoring to determine the final outcome for this, and other tested strategies. Which is a more sustainable way. But expect to fine tune this until  you are satisfied with your final scoring system.

End

So, there you have it. My secret system for scoring the strategies that I test. My strategy league is also based on this. I simply use the best scoring time frames of the strategy and rank them to the final score they have.

I did not show you my personal scoring system and rankings in this post. That is my 'personal trading secret' and it is purely based on my personal preferences (but I try to be as objective as possible in the mean time though).
However with the information I provided in this post you will come very close to it.

In my videos you can also see on which metrics I score the strategies. You can replicate that of course, but remember that using your own preferred metrics is a much better way to determine if an algorithmic strategy is specifically good for you.

In any case, I hope you learned a lot from it and am curious about your feedback.

Good luck building your own strategy and until the next time.