Rev 2 Ranking System Analyzer Spreadsheet

I've just put the finishing touches on a new version of my ranking system analyzer spreadsheet.  This post describes the new functionality of Rev. 2 in greater detail.

Today I would like to show off my latest EXCEL spreadsheet.  The market data behind the spreadsheet is (unfortunately) still collected manually using the Portfolio123 screener backtester.  When I get the functionality to the point where I really like it (Rev 47?), I'll be contacting Marco at Portfolio123 to see about automatically collecting the backtest data and compiling it into the spreadsheet.  It should be possible since they already have an EXCEL spreadsheet that does some simulation interactions with Portfolio123.

I should mention that you can get the spreadsheet here.  Just join Club Montauk and you can then download it.

As previously mentioned, the data for a ranking system must first be collected.  One does this by creating a screen and deciding upon a universe and also a benchmark.  Do not set any buy rules.  Now go to the backtest page and set up for daily backtest, zero comm./slippage and maximum date range.  Now run the backtest and download the results for the following number of stocks:  (0 (entire stock universe), 5, 10, 20, 40, 80, 160).   With a couple of simple mouse actions the downloaded spreadsheets can be copied/pasted into the Ranking System Analyzer on the appropriate sheets.  Then we are ready to start.

Parameter menu selection

This screenshot shows the basic parameters that the user can tinker with.  The parameters are centered around the Minimum Acceptable Return (MAR).  The choices are Fixed annual percentage gain and relative MAR. 

Minimum Acceptable Return Type

By clicking on "MAR Type" you can select either fixed or relative Minimum Acceptable Return.  The following set of screen shots presume that fixed annual gain percentage is selected.

Fixed Return Per Annum

The fixed return per annum options are 10% - 40% per annum.  I generally select 25% as this is the goal I set when I started this crusade.  Click here for more info.

5 Year Minimum Acceptable Return

This graph shows my ideal equity curve that I am shooting for - 25% per annum over five years.

Performance Ratio

The last parameter that requires setting is the performance ratio.  Choices are SMS_Sortino and SMS_Sharpe.  (I generally use SMS_Sortino.)  The SMS_Sortino and SMS_Sharpe are calculated using the Minimum Acceptable Return for comparison.  Most software packages / web-sites use Risk Free Return (RFR), typically T-Bill interest, to calculate Sharpe and Sortino ratios.  Since I am not trying to compare diverse portfolios with an accepted measuring stick I don't feel the need to use RFR.  Instead I use fixed annual gain or alternatively the benchmark stock index or custom stock universe for performance comparison.

Ranking System Performance

 

Once all of the parameters are set up as described above, you will see a graph similar to this one, displaying 1 year, 5 year and all-time (since inception) SMS_Sortino ratio versus number of top ranked stocks (5, 10, 20, 40, 80,160).  As can be seen here, all port sizes holding more than 5 stocks have a negative SMS_Sortino for the last year, implying a losing venture.  This is somewhat expected of course.

60 Day Excess Return

The graph above is a new addition that I find extremely useful.  It plots the 60 excess return of the top ranked stocks versus the MAR (25% per annum in this case) expressed as a percentage gain (loss).  As can be seen the loss is almost 60% (relative to MAR) with the 160 stock portfolio being the worse.

60 Day Excess - 5 Stock Portfolio

By manually removing some of the plots on the graph I managed to display only the 5 top ranked stock performance.  The loss is still approximately 50% versus MAR.  This is not looking too good.

Relative Minimum Acceptable Return

 
Now I am going to get into some more interesting details.  Instead of running a fixed MAR I am now switching to a relative MAR.  I will start by selecting Benchmark.  The benchmark I set up via the Portfolio123 screener was the S&P500.

 Relative Mar Performance Graph

From the graph above you can see that the Ranking System looks better all around and particularly the 1 year performance is positive.  This give a glimmer of hope that a fully hedged portfolio of top ranked stocks may make a pretty good investment strategy.

60 Day Excess Return - Relative MAR (S&P500)

However when I look at the 60 day excess return percentage I see that there is an unacceptable drawdown of over 40% relative to the benchmark S&P500.  What this says is that there would be a very high drawdown even with a 100% (ideal) index hedge.  With this type of hedge the profits would be minimal so a 40+% drawdown would NOT BE GOOD.

60 Day Excess Return - Relative MAR (Custom Universe)

The next step is to look at the results using the baseline universe as the relative MAR.  As can be seen the drawdown is slightly better but still an unacceptable 35%.

Universe Minus MAR 60 Day Excess Return

The last graph I want to show today is the Universe minus the MAR 60 day excess return percentage.  As can be seen there is a lot of bouncing around between the custom universe and the benchmark stock index (S&P500).  This tells me that a hedge has to be well-matched against the stock universe.  (I need to ponder this a little more).  Another thought is that there are 10%-15% regular swings between the universe and index.  There may be a possible strategy here.  Consider a 5 stock port that buys in when the stock universe is 5% below the index and cashes in when the universe is 5% above.  Looks like a future project!

So where am I heading with the Ranking System Analyzer???

For revision 3, I hope to split out the data into a file, separate from the analysis tool.  Then I want to be able to compare the results of 5 or more ranking systems within one spreadsheet and also average the performance of multiple ranking systems to see if the SMS_Sortino improves with multiple portfolios with different Ranking Systems.

From there, I want to start incorporating sims and comparing the performance ratio of sims versus ranking systems.  And ultimately I want to compare multiple sims and also averaging the performance of sims.

I'm looking for help in this work.  Drop me an EMAIL if you want to do some of the work. 

Steve, the Stock Market Student

Comments

[Richard Stooker]

Hi, Steve,

I found this post fascinating. It reminds me of what lengths people go to, to find the "Holy Grail" of trading.

You do enjoy all these details, and I can't argue with what people enjoy doing, but does this really enhance your results?

I realize you're not alone. The concept of beating the market is so attractive and compelling that people do almost anything -- including ignoring all the evidence that the stock market is nearly impossible to predict.

I didn't say "efficient" -- it's not efficient in any connotation I associate with that word. But I believe it is unpredictable.

I realize nobody wants to believe that they're simply beating their head against the wall . . .

But how much better off would your portfolio be if you simply spent all the time you've devoted to this spreadsheet program to making more money in your day career or through some part time job or business -- and then invested all that extra cash in either an index fund or -- much better, to my way of thinking -- stocks that pay dividends?

If you are ahead of the game, congratulations. If not, good luck getting there.

Terreno Realty