Following up from my previous post, we are building a simple trading model in excel for Bitcoin based on simple moving averages. I picked this type of model as moving averages are used so much in technical analysis, which seems to dominate the crypto trading space. My hope is that this tool might be useful to help calibrate expectations.
In this model, I'm using daily data and when the price of BTC is trading above its 50 day simple moving average, you're invested. When it is trading below, you sit in cash earning 0. On the model I show you how to build below, you can easily toggle the length of the moving average to see how that affects the results. Another simple idea is to convert it to an exponential moving averages to make it more sensitive to recent data. As you play around with it though, beware of over fitting though and please read all my disclaimers at the bottom. PAST PERFORMANCE IS NOT INDICATIVE OF FUTURE RESULTS.
This was done completely out of sample as I've never built a crypto trading model. With that said, the results are intuitive as I've built a lot of traditional asset class models. If you're wondering how I picked the date to start the model, it simply started when the data set I downloaded started, adjusting for time to build up at least a 200 day moving average. You can see where I download the free data in the prior post.
These types of trading strategies tend to do well in trending markets and really look great in the middle of a draw down (aka sell-off) as they may help you miss some of the downside. For markets that tend to gap down hard, aka a "flash crash", they may not be fast enough to take you out though. They also tend to perform poorly when you get into a sideways market as transaction costs can start to add up quickly. There is no perfect model and they all have their strengths and weaknesses. Models of all types are widely used though as they can help reduce emotional impulses/behavioral biases that often hurt investment results.
With all that said, you can see from the graph below that this simple model would have outperformed the buy and hold over the same period by a significant margin (over 2,000% on a compounded basis). However, as mentioned in the prior post, we are ignoring transaction costs and taxes in this simulation. Below the chart is some high level observations and the screenshots on how to build the model.
If you've found this to be informative, please support and follow!
Thank you so much.
A few high level observations.
- On the screenshot below, you can see the descriptive statistics we built in the last post applied to both time series for the same amount of time. On the sharpe ratio, I ignore the risk free rate as I don't know what that is in the various countries where readers live.
- The total compounded return for the buy and hold was 17,670%.
- The total compounded return for the 50 day SMA strategy was 20,017%.
- The out performance in large part is due to the trading model helping you avoid a decent portion of the large draw downs. That becomes apparent when you compare the minimum daily returns and the skew. Would that still be the case when you account for taxes and trading costs? Depends on how much you pay in taxes and trading costs.
- Even though the average annualized return is lower on the trading strategy, the volatility is lower as well. This example really demonstrates well the power of compounding when you're able to reduce the downside but still keep a lot of the upside.
Screenshots of the excel formulas
Please note, I rearranged the sheet to clean it up a bit. This is likely to make a lot more sense if you read the part I first though.
1. Below, we calculate the simple moving average. I use the offset function to define the length of the window.
2. Below, we write the formula to tell excel that we want to be invested today if yesterday's close was above its 50 day moving average. If it was not, we tell it we want to earn 0% (sit in cash).
A note on this post vs. the last post
I applied the same set of metrics that we built on the last post to this strategy so we can see just how the two measures up. However, I adjusted the time period on the original buy and hold vs. this one in order to give enough data to build a moving average. That means the descriptive metrics on the buy and hold of this post will look a little different than the last post.
Some important must-read disclaimers:
1) Use these tools at your own risk as I assume no responsibility for the results. I would also highly encourage readers to get a second opinion before making any financial decisions based on this.
2) This is a very simple moving average model, built completely out of sample. That means I have no idea whether or not it is going to be effective. Why this type of model? Given how popular moving averages tend to be in technical trading, this simple tool could give you the ability to measure how well the the particular configuration you tend to look at on technical charts actually works.
3) I am completely ignoring the impact of transaction costs and taxes as I have no idea what your situation is. Results you get in your quant model should be appropriately discounted to reflect this as well as the fact that "trading on the close" is often not possible for most people, if possible at all in crypto. Discounting the results is especially necessary when comparing it to a buy and hold strategy that has no recurring transaction costs.
4) Always take back tests with a grain of salt and beware of the tendency to overfit.
5) I’m sure there may be better ways to do this in excel than what I am doing. I know later versions of excel have fined tuned functions for limited data sets but this is intended to be an educational piece only. Please feel free to share tricks/shortcuts!
6) Please use common sense. I am some random guy on the internet so please double check and test everything I share! :)