221
As readers of my earlier posts know, my crypto journey began as a skeptic, advanced to hodling, and is now heading towards investing. In my own case, given my personality and time constraints, I've chosen not to day trade but rather to adopt a longer term investment horizon that focuses on accumulating when I think decent value exists. Given how fast these markets can pump and the difficulty that comes with trying to assign valuations to a new asset class, this is trickier than it sounds.
As part of this journey, I've been building a variety of tools in excel. That got me to thinking about sharing what is possible using google sheets as that is something most people have access to for free. Below is a BTC dashboard in Google Sheets that automatically updates. This is just a start to where I'm ultimately heading but I thought I'd share in case others might have interest in doing something similar. If note, I hope you find the information useful.
Below I'm pasting today's daily update and below that I'll post a summary of how I think about each chart.
Going from left to right:
1. Bitcoin (50,200): This is simply the daily price of BTC with a 50 and 200 day moving average. Not much to add to this.
2. Stratval: I've posted on this a number of times. It is a proprietary tool I use to evaluate an asset's risk adjusted return relative to its own history. One could think of it as an indicator that your car is about to overheat. You don't know exactly when it will happen, but when it gets above 5, watch out.
3. Drawdowns: I like to be aware of the magnitude and duration of drawdowns. The recent 50% sell-off was painful, but you can see its actually been much worse. Google sheets only goes back to about 2015 so I need to splice some data in to get a more complete picture.
4. Log Regression Model: I posted on this a few days ago. I ran a log regression on BTC going back to its inception to get a sense of what returns might realistically look like for the next 4 months. This is 100% guaranteed to be wrong. I use it though to help anchor myself in realistic expectations.
5. I find Plan B's Stock to Flow model really interesting and I hope he's right! I built this cone chart to track progress towards his year end target. The dotted line is what it would take to hit it. The colored lines around it are +1/-1 and +2/-2 sigma (standard deviation) outcomes that are based on the historic volatility of Bitcoin. For a lower vol asset, the cones would narrow. If you squint, you can see I started tracking this on 9/1/21. I am not predicting this but it would take the mother of all rally's to hit the model's year end price target. If I understand it correctly though, it's really supposed to represent more of an average rather than a price target.
6. Lastly, I also like to look at rolling risk (annualized volatility). In traditional assets classes, spikes often coincide with extreme selling. Given crypto's tendency to pump though, this is not always the case. This is also a useful tool to help indicate when options might be decently priced.
In summary, how this information is interpreted and used depends on what someone is trying to accomplish. A deep value investor would look at it differently than a trend or a momentum investor. Since this isn't investment advice, I'm not giving any interpretations. I'm simply sharing this as educational material and as an idea of what is possible to achieve with Google Sheets and a free time series. I do hope you find the analysis interesting and I'm happy to post it regularly if the community finds value in it.
Lastly, if anyone has any ideas for additional types of analysis I would love to hear!
Cheers,
NZFX
