How to Perform Correlation Analysis on Cryptocurrencies in Google Sheets

John Young

Published in

Spreadstreet

6 min readNov 14, 2017

Are you a visual learner?

Does your brain turn to mush when you see gigantic data tables, or huge blocks of text?

I am a visual learner, and this is one of the reasons why I love correlation matrices. We can take a simple concept and display it in an easy to digest, visually stimulating graphic.

Cryptocurrency Correlation Tool Spreadsheet

WHAT IS CRYPTOCURRENCY CORRELATION?

Have you ever noticed that when the price of Bitcoin goes up or down, that a large majority of the market seems to follow?

Or, when the price of one cryptocurrency rises, another falls almost in lockstep?

This is highly apparent in the cryptocurrency world, as many of the currencies on the market are completely coupled with the movement of Bitcoin. Certain coins will move incredibly close to Bitcoin, or act as a hedge against falling prices.

In the financial world, correlation is a statistical measure of how two securities move in relation to each other.

Currency correlation, then, tells us whether two currency pairs move in the same, opposite, or comnpletely random direction, over a designated period of time.

Unless you plan on trading just one pair at a time, it’s crucial that you understand how different currency pairs move in relation to each other.

CORRELATION COEFFICIENT

Correlation is computed into what is known as the correlation coefficient, which ranges between -1 and +1.

Perfect positive correlation (a correlation coefficient of +1) implies that the two currency pairs will move in the same direction 100% of the time.
Perfect negative correlation (a correlation coefficient of -1) means that the two currency pairs will move in the opposite direction 100% of the time.

Cryptocurrency Correlation Graphic — Picture courtesy of our friends at babypips.com

If the correlation is 0, the movements between two currency pairs are said to have ZERO or NO correlation, they are completely independent and random from each other. This basically means that we have no real idea how one cryptocurrency will react to the movement of another.

PLOTTING THE CORRELATION COEFFICIENTS IN GOOGLE SHEETS

Take a look at the following table:

Cryptocurrency Correlation Coefficient Table in Google Sheets

I have plotted the 1-Day correlation coefficients of 12 different cryptocurrencies. Immediately we can notice a few things:

Diagonally from top-to-bottom, left-to-right you can see a perfect 1 correlation comparing a pair to itself. You will always see this in every table
The darker green a number, the closer that number is to 1. This specifies a positive correlation (currencies move close to one another)
The darker red a number, the closer that number is to -1. This specifies a negative correlation (currencies move opposite to one another)

The Romeo and Juliet’s of Cryptocurrency

Monero (XMR) and Dash (DSH): 0.92
Monero (XMR) and Litecoin (LTC): 0.83
Monero (XMR) and OmiseGO (OMG): 0.81

Monero is highly correlated with a few different cryptocurrencies

The Hatfield’s and McCoy’s of Cryptocurrency

Basically EOS and everybody

Note: This is merely for data shown on 11/13, and can change rapidly at any given time. You can also see different correlations by time period, where two currencies can be positively correlated in one-minute intervals, but negatively correlated in one-day intervals

HOW CAN WE USE THE DATA?

Now that we have taken the time to learn about correlations, let’s explore how to use them to our advantage.

1. Helping to avoid entering two positions that cancel each other out

Let’s say, hypothetically that BTC/USD and EOS/USD had a perfect -1.0 correlation (ie, they move in opposite directions 100% of the time).

If you were to invest in both, this would be the same as having virtually no position. When BTC/USD rallies, EOS/USD would undergo a selloff.

Showing EOS and OMG negative correlation in a table

On the other hand, going long in BTC/USD and shorting EOS/USD would be effectively doubling your position, since the correlations are so strong.

2. Diversification

Bitcoin Cash (BCH) and Dash (DSH) in the image above have a correlation of 0.59.

Instead of a user buying 100% worth of BCH, they could diversify their risk somewhat while still maintaining a core directional view.

The imperfect correlation between the two allows for more diversification and marginally lower risk.

3. Hedging

A trader can use price differentials to extract an advantage in the market.

Let’s use our perfectly negative correlation example from above with BTC/USD and EOS/USD.

On average (this is completely hypothetical, as I have not done the analysis for price movement on these currencies) let’s say Bitcoin moves $10, and EOS moves $1.

Since they are perfectly negatively correlated, a price movement upwards would be equal to $9 (Bitcoin up $10, EOS down $1). This acts as a nice hedge for the trader. The downside, of course, is smaller profits in the event of a strong Bitcoin move.

THE DOWNSIDE OF CORRELATION COEFFICIENTS

Financial analysts often disagree how correlations should be utilized. This specific correlation (Pearson) has several disadvantages:

1. Linear dependency

This approach is only valid for linear dependencies; straight-line relationships between two assets are not often observed

2. Capture bias

Correlation only captures the first two moments of the relationship.

A value of 0 does not necessarily mean a relationship is missing.

3. Correlation changes over time

Oh yes…big time. Let’s take a look at Ethereum Classic (ETC) and EOS (EOS):

Eth Classic and EOS changing correlation coefficients

You can see it ranged all the way from -0.40 to almost a perfect correlation at 0.95.

What does this mean?

It could mean a few things. First, events that impact a specific crypto but not another could wildly move the price against the comparison.

Second, we are still in a young industry, and movements have not come even close to settling. We are still in a period of great uncertainty surrounding cryptocurrencies.

TOOLS USED IN THE SHEET

CONCLUSION

Cryptocurrency correlation coefficients can be a very useful tool when assessing your next crypto investment.

The benefits of the analysis, such as helping to avoid canceling positions, diversification, and hedging are second-to-none.

I urge you to download the spreadsheet so you can do your own analysis. See how they change on an hourly, daily, weekly, and monthly basis. You are bound to uncover some tremendous insights that others in the market may not be seeing.

Cheers, and happy hunting!