85
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 each post by tipping, following, and sharing them as that will let me know I am providing content that this community finds useful and keep creating it! 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.
As I mentioned in my introductory post, I come to this forum with a multi-asset class investment background that spans both trading and institutional portfolio management. I worked on one of the largest fixed income trading desks in NY (bonds, FX, and commodities) then transitioned over to institutional investment management where I was the senior portfolio manager of a $6 billion+ multi-asset class macro strategy at a $100 billion+ pension plan for over 5 years.
What I am presenting below are elementary versions of concepts and tools I and other institutional PM's actually use in managing large portfolios. I believe they may compliment the technical trading analysis that I see dominates the crypto space. I am also going to show you how to actually build them yourself in excel. 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 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. It is based on a liability driven framework. The benefit to using it is that it may help bring clarity of thought, intuition, and a bit more control to the risk management process, which is otherwise 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 https://www.cryptodatadownload.com/data/ . 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.....
________________________________________________________________________
- 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 :)
