Thread: Correlation Matrix:XOM,

The workbook contains 2 sheets. 1 the data sheet and the other the correlation Matrix. There are a 100 assets namely XOM, CVX, SLB etc. I need to a generate a correlation Matrix on a 1 on 1 basis of the asset. However the diagonal value must be 1. i.e when XOM vs XOM then 1, CVX vs CVX then 1 etc.I have filled in a couple of the cells of the correlation matrix myself.

Thanks

Hello,

Attached a way to compute the correlation matrix.
I change a little the original sheets.

First I add a sheet with the returns (and deleted the redundant date columns). While downloading with Bloomberg you can also with an option only download the price without the date column.

Then with the indirect function of excel you can compute the correlations just by coping the excel formula. For that you have to refer the relevant column, which are indexed with the "letters".

Cheers,
Charles