There is a thread titled “Ranking your ranking systems”. In that thread, there is a post by @sraby (Ranking your ranking systems - #10 by sraby) that mentions a spreadsheet named “List of Factors.xlsx” that is a list of all of the factors in P123 that existed at the time the spreadsheet was created. I am wondering how to use that spreadsheet. I know there are instructions in the spreadsheet but I do not understand them.
I am interested in this spreadsheet because I think it is the source of others mentioning the ranking (performance) of the top 100 or top 200 factors.
I am posting this thread separate from the other thread because I do not want to disrupt the flow of the other thread.
Hi Cary. I’m not sure which file you are looking at, but it is probably a version of files I have posted over the years starting around 2005. The list of factors is read by a script which does a ranking system performance test for that one factor then repeats the test for every factor in the list and saves the ‘bucket’ results for every factor to an Excel file. So you can see how the factor performs for the universe and date range used in the test. It was not a complete list even back then and doesn’t have all the good factors we have added lately. If I ever update it, I’ll post it.
This thread has a version of that file and a way to run it without knowing how to write any code:
FYI - the lines related to Estimates in this version of the file have a lot that fail because the factors were changed on the site and I never updated that file. The script will still run, but those bad factors will waste your API credits. Also, download the Excel file from that Google Drive location above if you want to see an example of the end result. Be sure to ‘enable content’ when you open it in Excel - then go to the Charts tab and the chart will change each time you select a different row/factor. This has been useful, but will probably be obsolete once AI is released.
Thank you so much for your explanation, Regrettably for me, I did not understand the explanation. I fear that the script is for the API capability, which I have never used. So until I learn how to use the API, is there no way for me to make sense of the spreadsheet?
Hi Cary,
If your not going to use that file with the API, then the only tab that you need to look at is the charts tab. The Charts tab has the results for all the factors tested. It looks at the the return in each bucket and comes up with a score for each factor (row) in the sheet. Then it ranks those scores in column BB. Lower is better.
Also, when you click on a factor, it should automatically update the chart to show the buckets for that factor. BUT when I download this file from that thread you mentioned, the VBA code that updates that chart is gone. So the chart is static and useless. I tried downloading the same file from named as ScoredFactors_Template from this location: https://drive.google.com/drive/folders/10P3ZnGVOFQeCjpXx_oFuNHhAprQ8X3Df?usp=sharing%5B/url%5D
The VBA code is there, but I cant figure out why Excel wont let it run. I tried all the VBA and Macro settings I could find with no luck. We never had issue with this before, so it must be a new ‘feature’ from Microsoft in Office 365 that flags downloaded files as dangerous. Maybe your settings in Excel will let it work for you.
Ah, I see now. I need to be using Excel. Alas, I do not have Excel installed on my computer. Instead, I have Mozilla Open Office. I found a converter on-line that would convert the .xlsx file into the .ods format. But the VBA code did not survive the conversion. In column BB where the rank is supposedly found, I have “#NAME?”. I suppose that I could calculate the actual growth based on the contents of columns, but I think the best way is to install Excel on my computer.
I have an update on this thread and some new questions. First the update: the file “List of factors.xlxs” finally made sense to me. In my Open Office version of the file, the graph in the worksheet “Chart” had 18 columns in it instead of 20. I falsely concluded that those columns represented years and the cumulative performance of the years. But I finally discovered that the first 2 points of data for a given row were not displayed on the bar graph, only data points 3 - 20. Also, in my version of the spreadsheet I had numerous versions of “#NAME?” and “#DIV/0” error messages. When I figured out that many of the “#DIV/0” errors were caused by cells containing references to rows that contained no data and fixed those references, then a whole lot of the data displayed sensible numbers.
So now to my questions: (1) how many API credits does the script take to produce the data for 1 factor? (2) from where does the script get the list of factors to try? You mentioned that “[i]t was not a complete list even back then and doesn’t have all the good factors we have added lately”, so how do I make the list complete? I doubt the list of factors produced by examining the factors as provided by P123 “LEARN AND CONNECT” => “RESEARCH” => “Factor Reference” would include the kinds of factors such as the factor under “Category” called “Momentum” and “Formula” of “sma(120)/sma(240)”. So where would I find those factors?
For each factor, the script has to change the ranking system and then run rank performance. Each of those operations is 1 credit. So each factor costs 2 API credits.
The instructions found at the link I gave on 11/19 explain where the script gets the factors from. They are in an Excel file. For formulas that use functions like the sma(120)/sma(240) you mentioned - there is no way to automatically generate a list of formulas. I wrote them all over the last 15 years. Every time I would think of some new formula or see an interesting formula in the forum, I would add it to my list. That factor list is a personal project of mine and not a Portfolio123 project and I have not updated that list in a year or more.
But you can find new FACTORS in the reference that were added lately like SUEQ1 for example. The Latest Release and Announcements categories of the forum will tell you all the factors released recently and you can choose which of those you want to add to your version of the list.