1. Comparing Securities
Correlation is a statistical concept, used to explain the relationship between
two variables. It quantifies how changes in one variable, like the returns
of a financial asset correspond to changes in another variable such as a
second financial asset. A correlation coefficient is calculated, which can
range from 1 to +1. A correlation of =1 indicates a perfect negative
correlation, meaning that as one variable increases the other decreases
at a perfectly consistent rate. Conversely, a correlation of +1 indicate
a perfect positive correlation where both variables increase or decrease
together at a consistent rate. Correlation coefficient of zero suggests
no linear relationship between the two variables. The letter R is often
used to denote this correlation coefficient. Excel has a correlation function
to make calculating the correlation coefficient fairly straightforward.
So let's do that now, by going back to our Excel workbook.
To demonstrate how the correlation function in Excel works, I need some
data, and I've got some data on the comparing securities worksheet,
which I can get to by using the shortcut control page down.
So what I've got here is over 2022, the weekly closing prices for
the 40 100, which is the largest index in the UK plus the
closing weekly prices for two stocks in the 40 100 BP,
which is an oil and gas company and Unilever a consumer goods company.
So the first thing I need to do is just work out the
weekly returns, and again, I'm going to use continuously compounded returns,
so that's the log function, and for the index, it's the closing price
divided by the opening price a week before.
And I'm going to do the same for BP and Unilever.
And so remember, it's the natural log of the closing price divided by
the opening price, and for Unilever, it's the log of the opening price
divided by the closing price, the price a week before.
So there are my weekly returns in week one, and then I can
just hold out the three columns H, I and J and then just
scroll down and I've color coded things just to make things a little
bit easier, and I'm going to hit Control D. And so there are
my weekly returns for the index and two stocks in the index.
So let's go back to the demo sheet by holding down Control and pressing
page up to get back to comparing securities. I want to work out
on the correlation between the 40 100 in BP and the 40 100 in Unilever
to see which stock had the high or lower correlation with the index.
So I start by typing coral, which is short for correlation obviously,
and then once the brackets is open, I can use the Control page
down to go to the comparing security sheet again. So I need to
erase the first array will be the index, so I'm going to start
in H8, Control Shift arrow down to select the entire range,
hit comma, and then I can then select the second array,
which I'm going to set the weekly returns for BP. Control,
Shift arrow down close bracket and hit Enter to take me back.
And so the correlation between the 40 100 and BP looks like it's
0.5887. So let's do it for Unilever and see which stock had the higher
correlation. So again, we start typing correlation and when the coral comes
up, tab for the bracket, control page down, and then let's go up
to the top and remember the first array is the index.
And so I can Control Shift arrow down to slick that whole array, hit
the comma, and then go back up to the top of the data
for Unilever and again, Control Shift arrow down to slick the array.
Close bracket before hitting Enter to take me back to the demo sheet,
and so it looks like Unilever has a lower correlation for the data
than B payment comparing the weekly returns with the weekly returns of the
40 100 index.
2. Let's practice!