How to Create a Crypto Correlation Matrix in Excel
In this article, we will learn how to create a crypto correlation matrix in Excel. We can apply these concepts to other asset classes.
We all know that diversification can help stabilize your crypto portfolio over the long term. This is because different investments go up over time, but they don’t all go up at the same time!
In this post, we will also show how correlation analysis can help with diversification.
What is correlation?
Investments are correlated if they move up or down in the same way. They are uncorrelated if they do not.
The level of correlation between two investments can range from +1 (100% correlated) to -1 (100% inversely correlated). These numbers (between -1 and +1) are are called correlation coefficients.
Correlation coefficients can also be zero if there is no relationship between two investments.
Why is correlation analysis useful?
Diversification doesn’t work if investments are perfectly correlated. If two different investments always move in the same way, what is the point of owning both of them?
But if we combine uncorrelated assets in an investment portfolio, we can improve the benefits of diversification.
As an analogy, this is like having a football team of players who each have different strengths. Here, the team becomes better with strikers, midfielders, defenders, and so on.
Each of these players contributes to the team’s overall performance at different points in the game.
What is a correlation matrix?
A correlation matrix is a table showing the correlation coefficients between two or more investments.
To use a simple example, let’s assume Bob has held two investments in his crypto portfolio for the last 5 years: Bitcoin and Ethereum.
Using Excel, we can build a correlation matrix between these two assets over the 5 year period:
Correlation Matrix: Bitcoin and Ethereum monthly returns (Sep 2016 to August 2021).
We will lay out the steps needed to produce the above matrix in this article. But first, notice that the monthly returns of Bitcoin and Ethereum have not been as correlated in the past as some might think!
As shown in the above table, the correlation coefficient between Bitcoin and Ethereum (of the monthly returns from September 2016 to August 2021) is 0.514.
In other words, Bitcoin and Ethereum are not highly correlated. This means that owning both of them would have been great for diversification. As a result, Bob’s overall portfolio risk went down.
Note: using the same time period, gold and the S&P 500 had a correlation coefficient of 0.042. This is expected as gold and the US stock market would be less correlated than bitcoin and Ethereum. This is why diversification between different asset classes offers relative safety over time!
How to build a crypto correlation matrix in Excel
Here are the steps you would need to follow in order to replicate Bob’s correlation matrix:
Step 1: Download monthly price data
First, download the monthly Bitcoin price data from Yahoo Finance:
How to download Bitcoin monthly price data from Yahoo Finance.
Next, download the monthly Etheruem price data:
How to download Ethereum monthly price data from Yahoo Finance.
You should now have two separate Excel files in your downloads section on your computer. Here is an example of what the Bitcoin price file looks like:
Bitcoin monthly price data Excel file (downloaded from Yahoo Finance).
Step 2: Paste the price data onto the same Excel spreadsheet
Now, copy and paste the downloaded data from each file into a single Excel spreadsheet.
Next, copy the closing prices of each investment (found in column E). You can then paste these next to each other on a new tab. This tab should also contain the dates in the first column and have appropriate headings in the top row:
Correlation matrix step 2: Get the monthly closing prices on a single Excel sheet.
Step 3: Create two more columns showing the monthly returns for each asset
The next step involves a bit of maths in Excel. We need this in order to calculate the monthly returns of each asset.
In column D, type the following formula:
=B3/B2 -1
This formula is asking for the:
(Current monthly price ÷ previous monthly price)-1
You can do this for each date so that you have monthly returns for Bitcoin and Ethereum from the beginning to the end of the period.
You can now format these cells to “%” to show the % monthly returns.
Note: the first row should not have the above formula because there is no previous monthly price. Therefore, you can just type “0.00” in these cells.
Crypto Correlation matrix step 3.
Step 4: Create the crypto correlation matrix in Excel:
Now that you have the monthly returns of Bitcoin and Ethereum, you can use the Data Analysis Tool in Excel to create a correlation matrix.
This can usually be found in the “data tab” at the top of your Excel window. If you don’t see this on your version of Excel, follow these steps from Microsoft.
When you click Data Analysis, a box will pop up in Excel showing various options. Select “correlation”:
After that, follow the 3 steps shown on the below image:
This will produce Bob’s correlation matrix on a new worksheet:
Correlation Matrix: Bitcoin and Ethereum monthly returns (Sep 2016 to August 2021).
Adding more assets to a crypto correlation matrix
Using the same steps as above, you can add as many investments as you like to your crypto correlation matrix.
Here is an example of a correlation matrix that shows the monthly returns of the top 5 digital assets by market cap over the last year:
Crypto Correlation Matrix: (Monthly returns September 2020 to August 2021).
In the above table, green is the best for diversification and red is the worst. Even in the recent bull market, it’s surprising that the monthly returns of the top 5 crypto assets have not been highly correlated!
This would have resulted in diversification benefits.
Conclusion:
There are many aspects to consider when choosing a long-term crypto portfolio. If you like numbers, you can use correlation analysis as part of your crypto strategy.
While future correlations don’t always agree with those of the past, this can help you choose the right mix of long-term investments for your portfolio.
Knowing which assets to own, you can then assign appropriate weightings to each asset and devise a strategy for rebalancing your portfolio.
The usual disclaimer: This is not investment advice. It is just for information and education.
Sign up for our newsletter if you would like to get these posts as they come out, or follow me on Twitter.
