Correlation coefficient finding software or website?

[D1984]

OK, here is the hypothetical situation:

Say I have four asset classes and I want to find the exact blend of them that is closest in returns on a monthly basis (i.e. has the highest correlation to the fifth asset) to another asset class (or closest to inflation, or closest to the price of oil, etc....basically finding the blend that has the highest monthly coefficient vs the other asset or other return series). Let's call the fifth asset class "Asset Class X" for purposes of discussion. What I am looking for is something that will automatically look over the monthly returns for the four asset classes (or three, or five, or six asset classes) and calculate the exact blend of said asset classes that has the highest monthly correlation to the other asset class (Asset Class X) in question. Is there any website (whether free or paid) that can do this, or any macro (again, whether free or one that costs money) that can be downloaded for Excel or LibreOffice that can do this?

Note that I am fully aware that PortfolioVisualizer (and for that matter Excel or LibreOffice) can calculate correlation coefficients for a given set of return serieses (series? series's? I'm not sure exactly what the correct plural of series is). That is NOT what I am looking for. The problem with using Excel/LibreOffice/PV is that I would have to input the monthly returns for the asset classes, average them for each month (or each year if annually rebalanced), and then when I had calculated the average returns for the blend of these assets then Excel/LibreOffice/PV could tell me what the correlation of said blend was to Asset Class X. Then if I wanted to see if I could get the correlation coefficient any higher (i.e. any closer to a "1" which would be a perfect correlation) I would have to MANUALLY change the blend of the four assets in question; on top of that, there are--assuming I am working with asset class weightings as low as hundredths of a percent which is pretty standard--literally MILLIONS upon millions of potential blends and if I had to input and test them all manually one by one in the software it would take years if not decades.

In sum, what I am basically looking for is something like this:

Say I have four asset classes (let's call them A, B, C, and D) and I want to find out the exact blend that has the highest correlation to another asset class (call it Asset X). I want something that when I put in the returns four all four asset classes and then tell it "find the blend (either the monthly rebalanced blend or the annually rebalanced blend) of asset classes A, B, C, and D that has the highest correlation of its monthly returns to asset class X" it will take the numbers and data (i.e. the monthly return series data for all the asset classes), run the millions of potential blends of all four asset classes in its software brain, and spit out a result something like this:

The blend of the four asset classes you provided (A, B, C, and D) that has the highest monthly return correlation to asset class X is as follows:

Asset Class A: 19.57%
Asset Class B: 32.10%
Asset Class C: 43.64%
Asset Class D: 4.69%

Is there actually anything that can do this or am I asking the impossible?

[Mark Leavy]

This is a standard linear programming problem. If you already have all of the data in an excel spreadsheet, you can use the excel solver add in to find an answer.

If you've never used solver before, it is worth practicing with it on some smaller optimization problems before setting up your problem set. It is an industrial strength linear programming optimizer included for free in excel and an underutilized tool.

solver will not necessarily find the global maximum to your problem set, especially as posed, your equations are not continuous.
The answer it comes up with will be somewhat dependent on the starting solution you give it (i.e. the initial asset allocation percentages).

What I have done with similar optimization problems is to make the initial allocation random, and then run solver to find a local optimum.

Keep restarting the spreadsheet, using different initial random allocations and then keep the 'best' ending solution.

[dualstow]

Neat. I wonder if WolframAlpha can also help with that.

[Tyler]

Another way to approach the problem is to constrain it enough that it's realistic to model every possible solution.

For example, instead of looking at hundredths of a percent try looking at 10% intervals. A portfolio could then be broken into 10 sections, where each section could be filled with one of four assets. The total number of possible combinations is 4^10, or 1,048,576, which coincidentally happens to be the exact max number of rows in an Excel spreadsheet. It's like the problem is begging to be solved that way.

The hardest part is writing a few formulas to auto-fill every combination using the right counting pattern, but that's doable. You may also want to eliminate redundant portfolios (10% stocks has the same impact if it's in the first or tenth section) to increase efficiency. But once you have the full set of possible portfolios with 10% intervals, you can calculate the returns and correlations for each and use a simple search to find the closest portfolio to your target. It won't be exact, but it should get you in the ballpark.

FWIW, that method is similar to how I constructed the Portfolio Finder. It's primarily a combinatorics problem.

[Tyler]

You may also want to eliminate redundant portfolios (10% stocks has the same impact if it's in the first or tenth section) to increase efficiency.

Out of curiosity, I spent a bit of time working out the full set of solutions. With 4 asset options to fill 10% chunks of a portfolio, the number of possible non-repeating permutations is only 286. That's a far cry from the 1mm+ if you use the simplistic brute force method! And it's downright manageable for any spreadsheet.

That still may not be what you're looking for, so it's probably just an interesting mathematical curiosity. But if you want a table of combinations to play with, PM me and I'll hook you up. And thanks for the inspiration to flex my brain a bit.