How to build your own Bitcoin trading model in excel. Part I

How to build your own Bitcoin trading model in excel. Part I


Give a person a fish and they'll have food for a day.   Teach a person how to fish and they'll eat for a lifetime.


I understand, dear reader, that you are bombarded with reading options.  Thus,  I want to make sure you get the most value possible when you click on one of my posts.   With that in mind and in the spirit of decentralization, I’m excited to start a series of posts over the next few weeks to give people access to a suite of tools that may allow you to make more informed decisions on your own, rather than relying on someone else’s system.   If you do indeed find value in this series, please support the post by tipping, following, and sharing as that will let me know I am providing content that this community finds useful.   But first, none of this is to be construed as investment advice.  It is being provided as educational content only so please do your own research.  Please read the important disclaimers below.

Using some basic excel skills,  I am also going to show you how to build a simple trading model yourself.    If you don’t have excel, many of the functions may work in google sheets but I can’t guarantee it. Most of these techniques are commonly embedded in portfolio management software but there's something about building it yourself as it allows you to really understand what is going on and modify as desired.

By the end of this short series, you should be able to build your own Bitcoin trading model, evaluate its performance vs. a buy and hold approach, and evaluate its risk contribution to a larger portfolio of multiple strategies/coins.  I'm using Bitcoin as it has the longest data history and is freely available.

I’ll also introduce a concept called risk budgeting (based on a liability driven framework) that should help users assign purpose to risk and have a better understanding of how to hard wire against behavioral tendencies that tend to sabotage many.   This particular framework is a bit more intuitive than some others which can be quite technical. 

To kick things off in this first post though, we’ll start slow.  Below I’m pasting a handful of excel screen shots with the excel formulas visible in the formula bar.  Below each pasted screenshot, I’ll make a few comments for calibration.   I am downloading free data from the Bitfinex link at .  Specifically, I am using the closing price for the daily time series. 

In this first step,  I am simply creating a handful of metrics, not exhaustive by any means, that can be useful on a comparative basis when evaluating a time series of an asset or strategy’s performance.     Why is this the starting point?  Because we first need to understand the nature of risk/reward for a simple buy and hold so that we have some baseline to know whether or not our models add value.   I'd really encourage you to get your hands dirty with the data.

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)  I am going to build a very simple moving average model, 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’m not going to share all my secret sauce but hopefully enough to provide readers with an early foundation to build on.

4)  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.

5)  Always take back tests with a grain of salt and beware of the tendency to overfit.

6) 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!

7) Please use common sense.   As far as you know, I am some random guy on the internet so please test everything I share!

Here we go.....



  1. Calculating the daily return. This is straightforward and simply calculates the daily % change between two days.   Here, I am using the “close” set of prices.  Please note I first make sure that the data is sorted so that the most recent data is at the top.   Below, I have the close prices in column K and am doing the daily return calculations in column L.   Columns O & P are the additional metrics that will be built below.



2. Below I am calculating the average daily return using the average function in cell P3.    In my example, the daily returns run from L3 to L2319. 



3.  Below I am calculating the maximum daily return in this time series using the max function. 



4.  Below I am calculating the minimum daily return using the min function.  Of note, that is an insanely painful left tail at -38.85% in one day!  Buyer beware.



5.  Below I am converting the daily returns to the average annualized return.  Please note, this is different than a compounded return.  This is done by simply multiplying the average daily by the number of trading days on the calendar. 



6.   Below I am calculating the annualized volatility of the time series using the stdev function and multiplying it by the square root of the number of trading days on the calendar.  Annualized volatility is a commonly used metric to measure risk.   Having this will allow you to compare risk to other asset classes.   Sneak peak:  75.2% is off the charts high relative to traditional assets.   You also got paid quite well for assuming it though over the time period measured.




7.  Below I am doing a back of the napkin sharpe ratio.   I call it back of the napkin because I am ignoring the risk free rate as I don't know what country of origin each reader is in.   The idea here is to get a sense of how much return you earn for each unit of risk deployed.   Thus, the higher the better.   To do this properly though, you would need to find the periodic risk free rate for each time period and subtract that from the return of the asset.   For simplicity sake, we are ignoring the risk free rate though.  Anything higher than 1 is quite good and difficult to maintain for long periods of time.   Over extremely long periods of time, traditional asset classes tend to converge towards a sharpe ratio of about .3




8.  Below I am calculating the Hit ratio.  That measures the % of the observations that were positive.    Obviously strategies that make money more often than not are preferred but you also must consider the magnitude of the average gain and loss.



9.  Lastly, using the skew function to get an indication of the symmetry of the distribution of outcomes.  This isn't a stats class so please look up the implications but in general, a positive skew is preferred to a negative skew.  The presence of either would indicate that we aren't working with a normal curve which limits the accuracy of using probabilistic functions.



Happy building!  Please support this post if you've found value in it and follow me to be notified when future posts hit as each post will build on the prior one. 


P.S.  Please read the disclaimers again :)

How do you rate this article?




Pursuing interesting conversations that help lead to truth. Twitter: @NZFX6

From skeptic to hodlr to investor
From skeptic to hodlr to investor

In this article, I apply a quantitative tool used by institutional investors to to try to determine where fair value is for Bitcoin. My hope is that it will help people avoid the mistake of buying high and selling low.

Send a $0.01 microtip in crypto to the author, and earn yourself as you read!

20% to author / 80% to me.
We pay the tips from our rewards pool.