• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Choosing least correlated portfolio

Archibald112

New Member
Hi everyone,

I was trying to find some neat functions on excel to point out least correlated portfolio for investment.

I am attaching small excel file. Instruments are correlated... from investment point of view one wants least correlated markets in a portfolio. I can pick one by one and use the trial/error approach...

But there has to be a way using Excel to find scientific way to do it. Let's say I want to pick 3 least correlated instruments. How to do that in a scientific way on excel?

Any help is appreciated!

BR,
Archibald112
 

Attachments

  • Correlations Excel - small.xlsx
    41.8 KB · Views: 7
Hi everyone,

I was trying to find some neat functions on excel to point out least correlated portfolio for investment.

I am attaching small excel file. Instruments are correlated... from investment point of view one wants least correlated markets in a portfolio. I can pick one by one and use the trial/error approach...

But there has to be a way using Excel to find scientific way to do it. Let's say I want to pick 3 least correlated instruments. How to do that in a scientific way on excel?

Any help is appreciated!

BR,
Archibald112
Hi,
Can you explain the result(s) you expect to get from this data?
 
Archibald

If the Numbers are correlation co-efficients as obviously Instrument 1 has a perfect correlation with Instrument 1, then the smaller the number the lower the correlation.
Negative numbers imply an inverse correlation and so larger negatives are less correlated inversely to the original
 
Hi Archibald ,

I am not sure what you are looking for ; see if the attached file helps.

If you type in :

correlation Excel

in Google , there are many promising results.

Narayan
 

Attachments

  • Correlations Excel - small.xlsx
    8.9 KB · Views: 4
Hello Mike, Hui and Narayank,

Thank you guys for warm welcoming. I'm new to forum space so should be more clear what I am looking for.

Table is correlation coefficients. I.e. I want to build a portfolio of 3 instruments with least correlation between the instruments. So basically, how to find the 3 least correlated instruments using excel?

If correlation coefficient is 0 there is no correlation between the 2 instruments. So one would like to choose 3 instruments (with correlations between all 3 as close to 0 as possible).

This is a small table is serving as an example. Real table is like 50 instruments so it is virtually impossible to find the right mix (i.e. 20 instruments) that have least overall correlation (every instrument has relatively low correlation to every other) by hand :)

There are some different hypothesis that can be implemented. i.e.:

If correlation is above 0,5 (and below minus 0,5) 2 instruments are correlated
OR f.x. if correlation is above 0,66 (and below minus -0,66) instruments are correlated.


I hope it helps. have a good weekend everyone.
Archibald
 
Hi:

Please find the attached. I just made a matrix table using formulas, maybe not very elegant, but it serves the purpose, I guess ....
 

Attachments

  • Correlations Excel - small.xlsx
    10.5 KB · Views: 3
Hi Nebu,

This is moving to a very good direction. All sets are very low correlated.

But I marked by hand correlations and compared Set Z and set Y. set Y has overall lower correlation than set Z. I hope you can see what I mean in the attached excel sheet.

So that is the goal finding the least correlated 3 instruments.
 

Attachments

  • Correlations Excel - small-2 - edited.xlsx
    10.7 KB · Views: 3
Hi:

I understood how you arrived at -0.32 & -0.13 for set Z but could not make out how you arrived at 0.14, similar with Set Y I got how you arrived at -0.08 & -0.07 but not -0.29, In my logic we have to first decide on an instrument on the basis of that starting point, the formula will give the next 2 options with lowest correlation tom pair with, hope I made sense, else you will have to build a logic to decide what should be the starting point ....

Thanks
Nebu
 
Hi Nebu,

Set Y:

I3 vs I7 correlation is -0,08
I3 vs I9 correlation is -0,07
I7 vs I9 correlation is -0,29

Same for Z.

Every variable is correlated to every other variable.

Well that is the issue, starting point cannot be picked randomly... even though one picks lowest correlation it does not mean that correllations to other instruments will be low as well.

Wag,

I have used CORREL function to derive the the correlation table :) Now I need to pick i.e. 3 least correlated instrument. Hmmm... quite a challenge!

BR,
Archibald112
 
Well there is a solution for this, but you will have to approach the problem little differently. There is a built in function in excel called solver, you can use that to identify optimal portfolio, this tool uses linear programming technique. For using this tool you have to define your objective function and identify your constraints. But if you are looking for a more complex solution you will have to buy the solver software. (This basic solver add-in in excel is supplied by a company called frontline systems, as per my understanding they do have complex tools but will have to pay for it)....
http://www.solver.com/welcome-mac-users-solver-now-included-excel-2011
 
Hi Nebu,

thanks for tips and your time. Will play around with solver.

If there is anyone else who has ideas, would be lovely to hear them :)
 
Hi Archibald ,

I am not able to understand your actual requirement ; let me try and put it in my own words :

1. You have posted a correlation matrix , where 9 instruments have been correlated with one another as a 2-way correlation ; thus instrument 1 has been correlated with instruments 2 through 9 ; instrument 2 has been correlated with instruments 3 through 9 ,..., instrument 8 has been correlated with instrument 9.

Using this 2-way correlation , you now wish to arrive at a 3-way correlation ; thus , if we wish to correlate instruments 1 , 5 and 6 , this will be derived using the following correlations :

instrument 1 with instrument 5 = 0.08

instrument 5 with instrument 6 = 0.41

instrument 1 with instrument 6 = 0.41

Is this correct ?

If so , what will be the measure of this 3-way correlation ? Only if we can arrive at a consolidated correlation measure can we then decide how to minimize this , and thereafter , identify the minimal value.

Can you explain this step ?

Narayan
 
HI Narayan,

The correlations you pointed out are correct, yes.

As far as I see it there is no need for 3 way correlation. Goal is to find i.e. 3 instruments with lowest correlations to each other. (with some additional constraints.. f.x. any 2 way correlation has to be under 0,51 and above -0,51).

The measure of this step would be:
a) every 2 way correlation has to be as close to 0 as possible
b) one might derive deviation i.e. 0.3. then multiply by 3(3 two way correlations). And say summed up correlation has to be under 3x0,3=0,9
c) so that 0,9 has to be minimised taking into account point A (every 2 way correlation has to be as close to 0 as possible)

I hope it makes sense (?).


Archibald
 
Hi Archibald ,

I am sorry , but I am not able to make much headway.

1. What is the meaning of any 2-way correlation has to be under 0.51 and above -0.51 ?

Do we first segregate all those 2-way correlations which fall in the range :

-0.51 <= x <= 0.51

2. If , for your purposes , a 2-way correlation of -0.27 is the same as +0.27 , then the ideal way to minimize 3-way correlation is to minimize the RMS values i.e. minimize the square root of the average of the squares.

Thus , for the same set of 1 , 5 and 6 , we would arrive at a consolidated measure of 0.338.

If we can derive similar measures for all the segregated 2-way correlation values , we can see which of them is the minimum.

Narayan
 
Hi again,

1. 0.51 is an arbitrary number. And yes -0,51 and 0,51 is the same for this project. Meaning is that if correlation is above 0,66 (or 0,51) then 2 instruments are correlated. One wants to avoid correlation when building a portfolio.

Do we first segregate all those 2-way correlations which fall in the range :

-0.51 <= x <= 0.51

Yes we do!

2. Yes -0,27 is same as 0,27. RMS approach makes sense.

But how will you do it on the correlation table. This is a small table that serves as example... main is like 50 instruments. Do you see a way to automate it?

BR,
Archibald


 
Hi Archibald ,

I think a macro can do what you want ; once we agree on the algorithm that we need to follow , there is not much complexity even though lots of data will be involved.

See the attached file , and decide whether the trio of instruments 1 , 5 and 9 form the least correlated set.

I have not yet written the macro ; forming the triplets , and then forming the doublets from a triplet is the complicated procedure , but first we need to know whether the result is correct.

Narayan
 

Attachments

  • Correlations Excel - small-2 - edited.xlsm
    20.5 KB · Views: 8
Hi Narayan,

1,5 and 9 are least correlated. Did it by hand and can't find more efficient trio.

I really appreciate your help here Narayan.

BR,
Archibald
 
Back
Top