Hi,
I want to calculate the correlation of the Bitcoin price with some other asset classes such as gold or oil. Therefore, I use the daily returns in percent of each asset and simply apply the correlation formula in excel. To be sure I got me two different datasources for oil and gold (Krugerrand and H&H gold, Texas and London Oil). Obviously the historic prices of Krugerrand and H&H are almost the same, the same applies for the two different kinds of oil.
My question/problem is: The correlation of the absolute values from Gold 1 - Gold 2 and Oil 1 - Oil 2 is close to one which makes sense. The correlation of the returns, however, is close to zero, i.e. non existent. How can that be? shouldn't the returns be at least very positively correlated as well?
Furthermore, the correlation of each time series with Bitcoin differs, even though I am thinking that Oil 1 and Oil 2 / Gold 1 / Gold 2 should have the same relation to Bitcoin.
I am trying to figure this out since days now and I am running out of time for this project. I would really appreciate if anyone has an idea what I am not seeing here.
I attached the excel, the most relevant sheets are highlighted in red.
The calculation of the correlation is done based on the time invariant data pairs (xi,yi). For the calculation of the returns the derivative between 2-time steps is considered, right. This is in principle something different and here we see a nice example that this can lead to interesting results.
The correlation of the day by day return is zero. Conclusion: there is no intraday correlation between these two values.
But there might by a correlation between day i and day i+n. So try to correlate (xi,yi+1), (xi, yi+2),… and you will observe an interesting result 😉. Hope that helped – and good luck.
Matthias Schubert Szymon Stereńczak
thank you for the fast responses!! That will definitely help :)
- Badges
- Science topic