In: Finance
^AORD | RIO | ANZ | WES | TLS | |
Date | Adj Close | Adj Close | Adj Close | Adj Close | Adj Close |
31/1/13 | 5120.4 | 66.36 | 19.84806 | 19.57102 | 3.212754 |
28/2/13 | 4979.9 | 67.05 | 19.71675 | 19.69255 | 3.255187 |
31/3/13 | 5168.6 | 57.2 | 22.00425 | 21.24546 | 3.594419 |
30/4/13 | 4914 | 55.8 | 19.03257 | 19.22232 | 3.421195 |
31/5/13 | 4775.4 | 55.18 | 20.2205 | 19.39867 | 3.442847 |
30/6/13 | 5035.7 | 52.37 | 21.05536 | 19.86891 | 3.601637 |
31/7/13 | 5125.3 | 57.51 | 21.00583 | 19.91794 | 3.536677 |
31/8/13 | 5217.7 | 58.3 | 21.77701 | 20.86289 | 3.688453 |
30/9/13 | 5420.3 | 61.74 | 23.94198 | 21.79624 | 3.844303 |
31/10/13 | 5314.3 | 63.99 | 22.56942 | 21.50087 | 3.755246 |
30/11/13 | 5353.1 | 66.06 | 23.43381 | 22.06193 | 3.896254 |
31/12/13 | 5205.1 | 68.18 | 21.90694 | 21.04004 | 3.814618 |
31/1/14 | 5415.4 | 65.64 | 23.36837 | 21.51595 | 3.747824 |
28/2/14 | 5403 | 66.84 | 24.03729 | 21.20243 | 3.877173 |
31/3/14 | 5470.8 | 63.55 | 25.06247 | 21.98485 | 3.984024 |
30/4/14 | 5473.8 | 61.7 | 24.34993 | 22.31941 | 4.075611 |
31/5/14 | 5382 | 59.3 | 24.85111 | 21.53699 | 3.976392 |
30/6/14 | 5623.1 | 59.31 | 25.32071 | 22.65917 | 4.190094 |
31/7/14 | 5624.6 | 66.38 | 24.9182 | 22.29369 | 4.24352 |
31/8/14 | 5296.8 | 62.63 | 23.04729 | 22.42818 | 4.153627 |
30/9/14 | 5505 | 59.58 | 24.97038 | 23.48155 | 4.412249 |
31/10/14 | 5298.1 | 60.41 | 23.79267 | 21.65335 | 4.459271 |
30/11/14 | 5388.6 | 59.1 | 24.61535 | 21.98998 | 4.678707 |
31/12/14 | 5551.6 | 58 | 25.31339 | 22.9756 | 5.09407 |
31/1/15 | 5898.5 | 57.56 | 27.10834 | 23.1127 | 4.992188 |
28/2/15 | 5861.9 | 64.41 | 28.10554 | 23.80803 | 5.060349 |
31/3/15 | 5773.7 | 57.23 | 26.07279 | 23.67265 | 4.996192 |
30/4/15 | 5774.9 | 57.15 | 25.45914 | 23.64556 | 4.988173 |
31/5/15 | 5451.2 | 58.2 | 25.36044 | 21.13805 | 4.924016 |
30/6/15 | 5681.7 | 53.75 | 25.73849 | 22.99026 | 5.204701 |
31/7/15 | 5222.1 | 52.86 | 21.99743 | 22.02082 | 4.627292 |
31/8/15 | 5058.6 | 50.29 | 21.32798 | 22.10167 | 4.620785 |
30/9/15 | 5288.6 | 48.6 | 21.43036 | 22.20873 | 4.447815 |
31/10/15 | 5218.2 | 50.65 | 21.3831 | 21.46487 | 4.414868 |
30/11/15 | 5344.6 | 45.91 | 22.81056 | 23.44851 | 4.620785 |
31/12/15 | 5056.6 | 44.71 | 19.74792 | 23.76406 | 4.637259 |
31/1/16 | 4947.9 | 39.13 | 18.29418 | 22.02842 | 4.324265 |
29/2/16 | 5151.8 | 40.28 | 19.15989 | 24.12315 | 4.390159 |
31/3/16 | 5316 | 42.69 | 19.82142 | 24.89136 | 4.549177 |
30/4/16 | 5447.8 | 51.55 | 20.80963 | 23.64598 | 4.744384 |
31/5/16 | 5310.4 | 44.69 | 20.34638 | 23.33752 | 4.718923 |
30/6/16 | 5644 | 45.5 | 21.79728 | 24.9845 | 4.897155 |
31/7/16 | 5529.4 | 49.56 | 22.69144 | 24.69935 | 4.464305 |
31/8/16 | 5525.2 | 47.6 | 23.30722 | 26.4623 | 4.524136 |
30/9/16 | 5402.4 | 51.61 | 23.49281 | 24.60811 | 4.34946 |
31/10/16 | 5502.4 | 54.18 | 23.96519 | 25.07016 | 4.410597 |
30/11/16 | 5719.1 | 57.75 | 26.40722 | 25.28618 | 4.454266 |
31/12/16 | 5675 | 59.9 | 25.42628 | 24.15813 | 4.366928 |
31/1/17 | 5761 | 66.68 | 26.8239 | 25.63424 | 4.209718 |
28/2/17 | 5903.8 | 61.99 | 27.62254 | 27.98426 | 4.069976 |
31/3/17 | 5947.6 | 60.46 | 28.43854 | 26.69895 | 3.808148 |
30/4/17 | 5761.3 | 60.44 | 24.31513 | 26.51268 | 3.970581 |
31/5/17 | 5764 | 62.81 | 25.59965 | 24.91076 | 3.880341 |
30/6/17 | 5773.9 | 63.27 | 26.41078 | 25.28949 | 3.699859 |
31/7/17 | 5776.3 | 65.79 | 26.20577 | 26.46303 | 3.311826 |
31/8/17 | 5744.9 | 67.84 | 26.38404 | 26.71963 | 3.281864 |
30/9/17 | 5976.4 | 66.53 | 26.66927 | 27.0235 | 3.328882 |
31/10/17 | 6057.2 | 69.45 | 25.3679 | 28.39412 | 3.225442 |
30/11/17 | 6167.3 | 70.95 | 26.31148 | 28.71733 | 3.413514 |
31/12/17 | 6146.5 | 75.81 | 26.165 | 28.32299 | 3.451129 |
31/1/18 | 6117.3 | 76.85 | 26.59529 | 26.71963 | 3.150213 |
28/2/18 | 5868.9 | 81.16 | 24.59034 | 27.81488 | 3.017585 |
31/3/18 | 6071.6 | 72.7 | 24.57203 | 29.29397 | 3.056025 |
30/4/18 | 6123.5 | 79.86 | 24.91077 | 30.49197 | 2.69084 |
31/5/18 | 6289.7 | 82.97 | 26.60997 | 33.03524 | 2.517857 |
30/6/18 | 6366.2 | 83.44 | 27.60878 | 33.1021 | 2.72928 |
31/7/18 | 6427.8 | 81.2 | 27.79724 | 34.43402 | 2.979144 |
31/8/18 | 6325.5 | 72.74 | 26.55343 | 34.4696 | 3.139685 |
30/9/18 | 5913.3 | 78.76 | 24.4333 | 32.23617 | 3.03142 |
31/10/18 | 5749.3 | 76.4 | 25.25308 | 30.69307 | 2.883785 |
30/11/18 | 5709.4 | 73.28 | 23.74844 | 31.30518 | 2.805047 |
31/12/18 | 5937.3 | 78.47 | 24.30186 | 31.29546 | 3.060946 |
31/1/19 | 6252.7 | 87.05 | 27.18546 | 32.23792 | 3.080631 |
28/2/19 | 6261.7 | 96.16 | 25.27276 | 34.65 | 3.32 |
31/3/19 | 6418.4 | 97.91 | 26.40873 | 36.01 | 3.38 |
30/4/19 | 6491.8 | 95.37 | 27.06895 | 37.05 | 3.65 |
31/5/19 | 6699.2 | 100.3 | 28.21 | 36.16 | 3.85 |
Using historical prices of the Australian All Ordinaries Index (AORD) and the following four companies traded on the Australian Stock Exchange: Rio Tinto Limited (RIO.AX), ANZ Ltd (ANZ.AX), Wesfarmers Ltd (WES.AX) and Telstra Corp. (TLS.AX) provided above, answer the following questions:
1. Compute the monthly return correlation coefficients between each pair of two stocks and between each stock and the market index. which pair of stock has the highest and lowest correlation? Which stock (s) has the highest and lowest correlations with the market?
Hint: use Excel function CORREL() to compute the correlation coefficient between two data series.
1.1. Can you suggest some economic reasons to explain the high and low correlation between stocks in (1)? If you were to form a portfolio of two stocks, which ones would you choose to maximise the benefits of diversification? Give reasons for your answer.
BETWEEN STOCKS |
Monthly return correlation coefficients |
RIO & ANZ | 0.62 |
RIO & WES | 0.76 |
RIO & TLS | -0.69 |
AORD & RIO | 0.78 |
ANZ & WES | 0.60 |
ANZ & TLS | -0.19 |
ANZ AORD | 0.84 |
AORD & WES | 0.89 |
TLS & WES | -0.52 |
AORD & TLS | -0.40 |
HIGHEST AND LOWEST CORRELATION BETWEEN STOCKS :
BETWEEN STOCKS |
Monthly return correlation coefficients |
RIO & ANZ | 0.62 |
RIO & WES | 0.76 |
RIO & TLS | -0.69 |
ANZ & WES | 0.60 |
ANZ & TLS | -0.19 |
TLS & WES | -0.52 |
HIGHEST CORRELATION IS BETWEEN RIO & WES stocks i.e 0.76 & lowest correlation is between RIO & TLS stocks i.e -0.69.
BETWEEN INDEX & STOCK |
correlation coefficients |
AORD & RIO | 0.78 |
ANZ & AORD | 0.84 |
AORD & WES | 0.89 |
AORD & TLS | -0.40 |
HIGHEST CORRELATION IS BETWEEN Index AORD is with WES stock i.e 0.89 & lowest correlation is between AORD & TTLS stock i.e -0.40. |