Portfolio Design

Advanced concepts in stock investment portfolio design.  Fundamentals, technical analysis and many other related topics are discussed.

EXCEL Spreadsheet Rev 2

I would like to introduce the reader to my Ranking System Optimizer Rev 2 spreadsheet. But a word of caution first. You may be disappointed with the results you get with the new version. I know I am but I think it is how I am using it, not that it isn't useful.

The spreadsheet is available here:


Before Starting

Please make sure you have read the previous tutorials that are located here:


New Features

When you open up the new spreadsheet you will see that I have added a second sheet called Feedback. This second sheet will allow you to paste results from the ranking system optimizer back into the spreadsheet. The feedback will be used to calculate the best iteration to be used on the next optimization cycle.


Also I have increased the range of sensitivity, allowing the user to generate random weightings down to 1% variation from the reference weight.  In theory, this should help with improving the monotonic nature of the final ranking system buckets.  


Test Period Permutations

You are not required to use the feedback feature, you can use the spreadsheet the same way as described before.  But if you decide to use the feedback then you must be aware that it is set up for four time periods.  So you must set up the Ranking System Optimizer permutations for four test periods of your choice as shown below.


In this example, I have chosen to optimize over the last ten years, but divided into two periods, the first starting at 11/20/03 and the second starting at 11/11/10.  Each period will receive the same attention, so the last three years will be optimized to the same extent as the first seven years.  Now since I am optimizing for three month rebalance, I decided to also optimize with an offset of approximately six weeks from the original start date.  Thus the second set of seven / three periods started at 01/01/04.  In all I have defined four periods.  Do not specify any fewer or any more than four periods.  Instead of my example, you may wish to skip certain periods of time such as the bear markets in 2000 and 2008.  This would allow you to target selective time periods for your optimization.

Copying the Optimization Results

Once you have finished generating permutations and running the ranking system optimizer, you must then download the results into EXCEL as shown below.


Then block select and copy the last five columns of the optimizer results spreadhseet.


Now go back to the Ranking System Optimizer Rev 2 spreadsheet and select the Feedback sheet.  Block select and paste the results into the first five columns.


Select the Best Weights For the Next Optimization Cycle

Now here it gets a little tricky.  Remember in previous tutorials that you were instructed to click on the Calculate Now button? Well, don't do that.  Instead go to the Formulas menu tab and click on Calculate Sheet.  It is very important that you don't disturb the numbers on the first sheet of the spreadsheet.


One of two things will happen, well three actually.  The spreadsheet will select the best iteration to try next, or it won't.  The third possibility is rare but does happen... two iterations will be selected.  In that case you decide which one to go with.

New iteration selected

New iteration selected

No iteration selected

No iteration selected

Reduce Sensitivity 

If no iteration was selected then go back to the first sheet of the spreadsheet and reduce the size of the randomness as shown below.  If you are already at the lowest setting then stop.  Your optimization is complete.


Prepare for Next Optimization Cycle

If an iteration was selected then you proceed as you would with the original version of the spreadsheet.  Go to the first sheet of the spreadsheet and copy the appropriate iteration into the reference weights then click on the Calculate button, starting the cycle over.


My Findings

Don't let me discourage you from trying this new process but I would like to say that my results were a little disappointing.  It took a long time to optimize and prune factors.  I could see the ranking performance of each test period being optimized but the overall result didn't appear at first glance to be any better than the original except that I had pruned a few nodes.  I think the reason for this is that I tried to cover too much ground in the choice of my test periods.  It might be better to target specific time periods that have similar market characteristics.

Anyways, if you try this for yourself then let me know how it goes.

by Steve Auger