New Ranking System Optimizer Spreadsheet

I would like to let everybody know that there is a new version of the Ranking System optimizer that can be found here .

This spreadsheet was produced by Ralph Nohe (ValueSeeker). He has indicated that he may produce a YouTube video regarding its use. Everyone is invited to provide comments and suggestions for future upgrades on this thread.

The optimizer has an expanded number of nodes and iterations, and also doesn’t require EXCEL to be set to manual calculation.

Description
There are 3 different tables (2 hidden between columns A-Z), the one visible is only the final result. One table is for iteration and resembles the table used in your earlier spreadsheet version. Then, there is one table for normalization, and then, there is the visible table where all non-zero numbers are shown in light green.

Using Excel values instead of formulas:
The VBA code copies the final results from one of the hidden tables into the light green visible one as values only, which is highly helpful to prevent any unitended Excel recalculation, e.g. when another excel sheet is opened or when the current worksheet is being saved. It happened to me several times, and my Excel data was lost (then I had to manually input the best permuation from P123 into Excel for the next iteration cycle, which is some work in case of 25 nodes.

Sensitivity settings:
The higher the number of nodes, and the more iterations have already been made, the lower the sensitivity can be set. Hence, for 25 nodes with an average node weightage of 1/25=0.04 a sensitivity of 20% will lead to a sufficiently high diversity distribution among the interations created. For only 3 nodes being weighted 0.33 in the beginning, only 80% will create a workable diversity.

Starting weights, and their usage for Reference:
The starting weights should sum up to 1.00, e.g. for 3 nodes: 0.20/0.30/0.50, or 0.05 each when having 20 nodes.
The starting weights are copied into the first column. Why does that make sense: lets say we have already used the first set of iterations in P123, and found a new max. permutation, the weightages of this new max. permuation should be used as starting weights for the next iteration in Excel. These can easily be copied and pasted directly from the light green Excel table into the orange fields, and are then used as iteration no. 1. So back in P123 during the next permuation this last (good) result does not get lost, and is shown as reference in new permuation no.1

Ralph - thank you very much for taking this project over.

Thanks
Steve

Dear Steve,

thank you very much for developing this great tool in the first place!!

@all: please let us know any comments, bugs or of course any ideas how to improve this file further.

Also, I’d like to share an additional little ranking system tool which generates random numbers for a defined number of nodes. The matrix generated can be a good starting point when optimizing a ranking system —> It creates random number combinations which would otherwise be only reached after a long series of iterations (or a lot of creativity…).
Hope it’s useful for anybody:

https://drive.google.com/file/d/0B0QWfpBUsNOKa29TclV1d0lraWc/view?usp=sharing

Steve and Ralph,

thanks for your very valuable contribution and effort.
It is people like you who are keeping the standards high!

Thank You.
Danke. Merci. Gracias. Obrigado. Arigatou.Xie-xie.

Steve and Ralph: Thank you!

Here are my initial comments. Some may sound critical but they are meant to be constructive and I’m sure others will probably have opposing views.

(1) The total of all nodes add up to 1 in this version, but there are only 2 significant digits. I feel that the weighting resolution is too coarse. I would like to see 3 significant digits (at least). One of my longer term objectives is to use the tool to create “test vectors” or test RS’s. I would start with a production ranking system but have a series of RS’s with minor amounts of random noise applied to each node weight (up to 5%). The noisy RS’s would be applied against the production port/simulation to see how the sim responds. This simply would not be possible with the optimizer as is. But adding a third decimal place would increase the size of the spreadsheet horizontally (see problem #2). So I am wondering if the sum of nodes could be re-scaled to 10 instead of 1. This would not take up any additional screen space.

(2) I find that the horizontal and vertical size of the spreadsheet is too large. Now perhaps I have a Windows setup problem, I don’t know. But I’d like to explain that I use two monitors, the first is the screen attached to my portable PC. The second is a separate 23" widescreen display. The two monitors are very convenient for using this ranking system optimizer because I can have both P123 and the spreadsheet displayed at the same time. So the problem is that with two monitors selected, Windows adjusts the effective resolution of each graphics display to be the same. I don’t get more detail on the 23" monitor than the portable PC display. I have attached what I see when I open up the spreadsheet. The active area is way too large (for me) as I don’t like messing around with scroll bars, particularly when I am doing a very repetitive job. The other issue is that even with my old spreadsheet (10 iterations), I would not be able to complete my work without getting a “Max number of requests - 500 in 6 hours”. The 20 iterations will make that cap come twice as fast :slight_smile: I think what you have is good for newbies, but I am wondering if in the end a stripped down version could be made (with no training wheels), perhaps 15x15, with the preamble at the top shrunk/minimized and fit onto the side instead of the top of the spreadsheet.

(3) I miss the lack of automation. With the last version I produced, I could click on one of the iterations, it would automatically be copied into the reference spot, the regeneration of the randomized iterations would happen, then the iteration array would automatically be copied into the clipboard. This saves a lot of steps in a very repetitive job. May be it is difficult to accomplish with the new design… All I can say is that I’m getting repetitive strain injury :slight_smile:

Suggested Enhancements:[/b]

I would like to see several PRESET buttons on the spreadsheet as follows:

(a) PRESET to equally distributed weighting. I generally start with equal distribution and work from there. I prefer to click on a button than enter the weights.

(b) PRESET to a rolling one pattern. The idea is to preset the iteration array so that one and only one node is enabled for each iteration.

(c) PRESET to a rolling zero pattern. The idea is to preset the iteration array so that one and only one node is disabled for each iteration. The remainder of the nodes have equally distributed weighting.

(d) PRESET to a randomized selection of two nodes - two nodes at random would be enabled for each iteration. The nodes would have equal weighting. This is for finding pairs of nodes that function well together.

(e) PRESET to a randomized array as per your separate spreadsheet.

Anyways, these are my thoughts. Excellent work Ralph.

Steve


I’ve always been appreciative of members’ sharing their tools or research with others here. It’s sped my learning experience along and made getting to better results easier. So thanks Ralph!

Just curious, is there a body of math or science behind this tool that makes outputs a key driver to more quickly finding the optimal set of factor weights? Or is this simply a tool to generate a set of random weights? Being an English major, I haven’t a clue. :wink:

THanks

Dear Herton,

I am not sure if I fully get what you mean, but let me give it a try :slight_smile:
Both tools (the RS optimizer as well as the random-number generator) generate random patterns, so the Excel file as such does not include any trading rules or patterns. However, once you put the matrix generated into P123 and let the P123 ranking optimizer tool do it’s job, the results show which permutation might be better than the other one. The simplest measure is the max.return, others favor the delta (sum of last and absolute of first bucket value). When creating a more complex ranking system, I found that the results translate particularly well into simulations when calculating a sort of a Sharpe-like coefficient for each permutation result, i.e. max.result (or delta) divided by standard variation (and optionally: times slope). Based on that outcome, the ‘best’ result is used as input in the Excel RS optimizer for the next iteration cycle.

Dear Steve,

thanks for sharing thoughts about further room for improvement - I like these a lot, particularly the idea of the rolling patterns. With VBA Macros, it should work to add a lot of automation as described. I think it will be possible to implement these functions within the next 10 days approximately, perhaps even earlier. I will then post it here!

Interesting…didn’t know about the iterative approach. I’ll give it a try.

Many thanks

Dear Herton,
hope my comments are of help.
I am looking forward to meet you in New York in June!

Me too.

“…is there a body of math or science behind this tool that makes outputs a key driver…”

Herton - this isn’t meant to be a criticism but I am always curious as to why people here want to follow what the academic establishment does. Profits don’t come from following the accepted thinking of the day, they come from original thought.

Steve

I’m principally motivated by a desire to learn all the capabilities of this system including tools offered by users. In this particular case, I was trying to understand if there was a mathematical methodology or practice underneath the Excel spreadsheet that goes into deriving what appears to be iterated or refinements to factor weightings.

It is essentially a fast way to an optimized solution. Every time you repeat the process you will likely end up with a different optimized solution, sometimes with different nodes zeroed out. I like to do the optimizer trick 5 times to get 5 distinct RS’s then run the 5 optimized ranking systems in parallel without any further change. This tends to give really good results without the overfit that can arise with one optimized RS.

Steve

Dear Steve, dear all,

it was quite some work and sometimes a challenge,
but I managed to update the spreadsheet and implement most of the functionality Steve had on his ‘wish list’ above :wink:

As a first step, start by entering the NODE NUMBER at the bottom in the PRESETS area, because all the coding refers to that number of nodes. Everything else is up to you to alter.

Implemented:

  1. Random number generator
  2. Equal weightage distribution
  3. Set revolving single node to 0 for each iteration
  4. Set revolving single node to 1 (=100%) for each iteration
  5. Set 2 random nodes to 50% each all others 0
  6. Set number of digits up to 5 (Steve, only 2 are shown, but up to five are copied to the dashboard)
  7. Copy a single iteration to the dashboard
  8. Copy whole matrix to the dashboard
  9. Screen width - I do not have that problem with 27’’ iMac, but the table is now usable even with small width screens (on top of that, you can always lower the Excel sheet zoom level in the Excel settings…)
  10. Some further extras

Have fun!! Any comments welcome.

https://drive.google.com/file/d/0B0QWfpBUsNOKVHhvWFhpVDQxdFU/view?usp=sharing

I just found out that theres still a bug in the digits number setting. Please disregard this particular issue, I will try to solve it, however, the digit setting should not be such an important thing, everything else hopefully works.

Ralph - much thanks. I’m starting to appreciate your “vertical style” as opposed to spreading things out horizontally. It is better for people wanting to put two windows side by side. The functionality looks great, I don’t need five digits, 3 is probably enough and I don’t think it warrants a control to be able to change the setting. The column widths need to be a little larger because I only see number signs.

Even with zoom to 75% I still can’t see the whole spreadsheet. I might try shrinking/rearranging the buttons because they are much larger than need be. Also, I’ll put the select column->generate iterations->copy into clipboard on my Christmas list.

Thanks for the great effort,
Steve

Steve - today must be Christmas in Canada - look here, the U-buttons in row no. 9 do exactly what you want:

https://drive.google.com/file/d/0B0QWfpBUsNOKVHhvWFhpVDQxdFU/view?usp=sharing

p.s.
You can simply lower the font size in the matrix, so that the numbers should become visible. When I open the file, it’s all well visible and not #-out.
Everything else is up to you to rearrange and redesign so you see all information at once as you like it.
The only thing that most probably completely destroys the functionality is to add rows or columns.

Does anyone have a copy of this spreadsheet that they’re willing to share?