Introduction
Installation
Sample 1: testing a simulation over many periods
Sample 2: testing a simulation with different sell rules
Make Graph
Advanced notes for the curious...
List of commands
What's new in version 1.03

Introduction

Stefano's P123 Manager is an Excel Add-In that will run many simulations on Portfolio123.com and will produce the results in an Excel table.
If you already know how to use the Portfolio123.com web interface, using the Add-In will be very easy.

These are the steps to use the Add-In: In order for the Add-In to gathere information about your account, you need to click the Sign In button and sign in to portfolio123 before using any functionality.

Installation

Before updating an old version of the Add-In go to Add or Remove Programs in the Control Panel and uninstall the previous version.

These are the steps to install Stefano's P123 Manager:
The toolbar for Stefano's P123 Manager will appear. If the toolbar doesn't appear, click on the Customize item of the Tools menu and check the toolbars list. Check Stefano's P123 Manager toolbar to activate it. If you are unable to install the toolbar contact Stefano in the forum.

Sample 1: testing a simulation over many periods

1) Create a new file in Excel and click the Import Simulation toolbar button to import a simulation.  The Import Simulation dialog box contains the list of your simulations, portfolios and P123 public models.

2) In the Import Simulation dialog box select the simulation or portfolio you want to import and click OK.  The active sheet name will have the "Imp" suffix added, the selected simulation will be imported into the sheet and the toolbar will now have one drop down menu containing three buttons: Add Dates, Add Value Permutations and Add Multiple Runs.

3) Click on the Add Dates toolbar button and the Set Dates dialog box will appear.

4) Set Dates dialog box allows you to define multiple periods for model robustness testing.
This is the description of the fields:
Field name Description Default value
End date The end date of the last period Today
Simulation duration The duration of each period 52 weeks
Shift Start and End date by The difference between the start date of 2 periods 4 weeks
Number of periods How many times the simulation will run 10 times
Using the default values the simulation will run on a 52 week period starting 52 weeks ago and ending today. The next simulation will run again on a 52 week period starting 4 weeks earlier, and so on for a total of 10 runs.
The Start date read only field shows the start date of the first simulation (while the End date shows the end date of the last simulation). The list shows all the simulation periods start and end dates.
Once you defined the periods you want to test your simulation on, click OK.
After clicking OK, the toolbar will have one more button, Create Permutations.

5) Click on the Create Permutations button to create a new sheet called "Sheet1 Run" containing 10 rows, one for each defined period.

6) Click on the Run All Permutations button and the Run Simulation dialog box will appear.

7) The Run Simulation dialog box allows you to specify what data to import from the server after running the simulation on each period.
You can also specify up to 3 custom columns with a formula. For example you can create a "Ret-DD" column with the formula "[Active Return]-[Max Drawdown]*1.5". The field names must match the column headers as they appear in row 2, including spaces and they are case sensitive. The Add-In will convert the formula with field names into an Excel formula, and you will be able to sort the results or to make graphics using these columns.
If you have an old Run sheet and you want to use a formula different from the one that you set when your ran the simulations you can click on Run Simulations again, enter the new formula in the dialog box and click OK. The Add-In will not run any simulation because there is no "Run" in the first cell, but will remove the old custom columns and create the new ones.
The Add-In will re-use the same simulation called Stefano's P123 Manager, but you can check the "Keep the simulations in the server" checkbox to keep all of them. The name of the simulations on the server is specified on the cell C2 of the Input sheet with a number as a suffix. The name is defined when you import the simulation, and you can change it by changing the text on the cell C2. The fisrt cell on each line contains a link to the simulation on the server. If simulations with the same name-suffix already exist they will be overwritten.
If there is a problem running a simulation the first cell of the column will show a message describing the error. If the error is on the simulation (syntax error or buy rules too restrictive) then the Add-In will skip to the next row. If the error is caused by a connection problem or the server is busy, the Add-In will wait 20 seconds and rerun the simulation.
After all the simulations have been executed the result will be in the Excel workbook.
After running the simulations the toolbar will have two more buttons: Row Height and Make Graphs

8) The Row Height button allows you to cyclically change the row height from the standard row height (useful for reading numbers) to the graphic height (useful for viewing performance graphics) to the height required to make visible the full content of the cells (useful for viewing fields containing long texts, like Sell Rule Usage.)

Sample 2: testing a simulation with different sell rules

1) Click the Import Simulation toolbar button

2) In the Import Simulation dialog box select the simulation or portfolio you want to import and click OK. For this example, I selected the public model Balanced Mid Cap.

3) Click on the Add Value Permutations toolbar button and the Add Value Permutations dialog box will appear.

4) As soon as the Add Value Permutations dialog box opens you are asked for the field to add. For this example, I selected the first sell rule called Rank 45 Loss 15.

5) After selecting the field, the current value for the field will appear in the list. You can click on the empty cell at the end of the list to add multiple values to test.
After adding the values, you can click on the OK button and continue from step 7, or you can click on the Add field button to add another field to change in addition to the first field. For this example, I selected the third sell rule called Laggard60.

6) Add the field values for the second field. You can add as many fields as you want to change at the same time as the first field.
In this example both the sell rules increase NoDays.
Click on the OK button to add the values to the simulation.

7) You can click Add Value Permutations again to add other values to test.
In this example, I added 4 values for the Rank sell rule.

8) Click on the Create Permutations button to create a new sheet containing the rows with the simulations to run. In this example, the number of rows is 20, that is the total number of permutations of the 5 couples of values for Laggard and Laggard60 multiplied by the 4 values for the Rank.

9) Click on the Run All Permutations button to run all the permutations.

Make Graph

The Make Graph button allows you to create performance graphics for any of the imported values. You can create one graphic per value relative to all the runs or select one or more parameters and create one graphic per value for each parameter.
For example after a multiple run on 10 periods with 3 different values on the Rank buy rule and 2 different values on the GARP buy rule you will be able to chose on the left side of the dialog box one or more of the values imported by the server (Max Drawdown, Active Return, etc.) and in the right side how to group the results (one single graphic containing all the results, 3 graphics, one per value of Rank, 2 graphics, one per value of GARP, 6 graphics, one per Rank-GARP combination)
In this example you can see the graphics for Active Return, Max DrawDown and Gain Stock Day for each combination of Rank Buy and GARP.

This is the result: 4 graphics are created because both Rank Buy and GARP have 2 values each (2 times 2 = 4). You can use the wheel of the mouse with the ctrl button pressed to zoom in and out.

It is possible to make graphics summarizing the results of more sheets with the following steps:
- import the simulation in the sheet "MySim Imp"
- set 50 reruns without the 10% of stocks from previous runs and the 2 values for the buy rule "MaxCorrel(40, 1) < 0.40" and "MaxCorrel(40, 1) < 0.50"
- create a copy of the sheet "MySim Imp" and rename it "MySim2 Imp"
- select "MySim Imp" and run all the simulations
- select "MySim2 Imp" and run all the simulations
- after running 200 simulations select the rows 3 through 102 on "MySim2 Run", right click - copy
- select the first cell on the first empty row on "MySim Run" (after 100 simulations it is A103), right click - paste
- click the Make Graph button.

Advanced notes for the curious...

It is possible to create the list of permutation with both a set of periods and one or more set of parameter values.
If this is the case after successfully running all the simulations, the Add-In creates average rows for each group of dates, and a new toolbar button, Show Averages, will appear. The button allows you to cyclically change from 1) only averages visible to 2) only simulation results visible to 3) both simulation results and averages visible.

The end and start dates of a portfolio don't exist. After importing a portfolio they are always set to today and 52 weeks ago.

The number of simulations is limited by the membership level of your account. If you exceed the maximum number of allowable of simulations by requesting too many permutations you will receive an error message before any runs are executed.

You cannot use two sell rules or buy rules with the same name. For example, if you have both a buy rule and a sell rule called "Rank", the permutation values will apply to the buy rule because that appears first in the list. To avoid this problem change the name of one of the rules in column B (for example "Rank Buy" and "Rank Sell" instead of both "Rank".)

To redefine the permutation values for parameters defined as a set you need to remember to manually add both the fields. 
For example, if after step 6 of the previous example you want to redefine either Laggard or Laggard60 values, you need to add both the sell rules. If you redefine the values of only one field, the Add-In will delete both the previous values and recreate a new set with the values only for the selected field.

After adding permutation values or periods, the first row of the Imp sheet will contain a number that codes each set of permutations. For example, if you have 10 periods, 5 values for a parameter and 4 values for another parameter, the first row will contain 9 number 1s (for the 9 periods in addition to the baseline period), then 4 number 2s (for the 4 values of the parameter in addition to the baseline value), and 3 number 3s (for the 3 values of another parameter in addition to the baseline value).  Column C is the baseline (containing the values of the imported simulation definition) and is the first value for the fields involved in the permutations. The other 9 (or 4 or 3) values of the permutation will be displayed on the columns with the numbers 1, 2 or 3 for the first row.
If you redefine the date range or a set of values you will add more sets of permutations which will be coded by the next available number (in this case it would be the number 4).  Also, the original set for that value will be deleted.  So, if you redefined the value of the parameter that we mentioned with 5 values the number assigned to that set (number "2") would disappear and the redefined set would be coded with the next available number (in this case, the number "4").

List of commands

This is the list of available buttons in alphabetical order.
About Stefano's P123 Manager Shows the About dialog box with informations about thd Add-In and your authorization level.
Add Dates Allows you to define multiple periods for robustness testing.
Add Multiple Runs Allows to set how many times each simulation reruns with the same parameter values. Each rerun will add to the restrict buy list the specified percentage of stocks used in the previous run(s).
Add Permutations Shows the 3 menu items: Add Dates, Add Value Permutations and Add Multiple Runs.
Add Value Permutations The Add Value Permutations dialog box allows you to define what parameter or group of parameters to test, and what values to use for the test.
Create Permutations Creates the Run sheet containing all the permutations of values defined with the Add Dates, Add Value Permutations and/or Add Multiple Runs commands.
Delete bitmaps from the sheet The bitmaps imported using the Performance graphics check box in the Run Simulations dialog box increase the file size up to 9 times. This command allows you to delete all the bitmaps in the current Run sheet in order to decrease the file size.
Delete simulations from the server Allows you to delete from the server a large number of simulations at once. To delete all the simulations created by a multiple run keeping the simulations in the server it is possible to use the Delete simulations from the server button and enter the base of the name followed by a " *".
Go to www.portfolio123.com Open the webbrowser with portfolio123.com home page.
Import Simulation Imports the list of the private simulaions and of the public and private portfolios, allows to select the one to import and imports it in Excel.
Make Graph Allows you to create performance graphics for any of the imported values. You can create one graphic per value relative to all the runs or select one or more parameters and create one graphic per value for each parameter.
Row Height The Row Height button allows you to cyclically change the row height from the standard row height (useful for reading numbers) to the graphic height (useful for viewing performance graphics) to the height required to make visible the full content of the cells (useful for viewing fields containing long texts, like Sell Rule Usage.)
Help Shows the 6 menu items: Online Documentation, Go to www.portfolio123.com, Sign in as other user, Delete simulations from the server, Delete bitmaps from the sheet and About Stefano's P123 Manager.
Online Documentation Open the webbrowser with this documentation page.
Run All Permutations Runs all the simulations defined in the Run sheet, created by the Create Permutations command. The result of the runs will be added to the Run sheet.
Show Averages Allows you to cyclically change from 1) only averages and standard deviations visible to 2) only simulation results visible to 3) both simulation results and averages/standard deviations visible.
Sign in Signs in at portfoli123.com and checks for the authorization to use the Add-In. You need to sign in before using any other functionality of the Add-In.
Sign in as other user Allows you to sign out and sign in again as another user.
Unhide Columns Shows a list of hidden columand and allows you to select the columns to unhide. The columns can be hidden either because the Add-In considers them useless or because you hid them manually.

What's new in version 1.03

It is possible to import trading statistics. The Run Simulation dialog now has one checkbox per value instead of one checkbox per group of values.

In the Run Simulation dialog box you can specify up to 3 custom columns with a formula. For example you can create a column "Ret-DD" with the formula "[Active Return]-[Max Drawdown]*1.5". The field names used in the formula must match the column headers, including spaces and they are case sensitive. The Add-In will convert the formula into an Excel formula, and you will be able to sort the results or to make graphics using this column.
If you have an old Run sheet and you want to use a formula different from the one that you set when your ran the simulations you can click on Run Simulations, enter the new formula in the dialog box and click OK. The Add-In will not run any simulations because there is no "Run" in the first cell, but will remove the old custom columns and create the new ones.

The new button Make Graph allows you to create performance graphics for any of the imported values. You can create one graphic per value relative to all the runs or select one or more parameters and create one graphic per value for each parameter.
For example after a multiple run on 10 periods with 3 different values on the Rank buy rule and 2 different values on the GARP buy rule you will be able to chose on the left side of the dialog box one or more of the values imported by the server (Max Drawdown, Active Return, etc.) and in the right side how to group the results (one single graphic containing all the results, 3 graphics, one per value of Rank, 2 graphics, one per value of GARP, 6 graphics, one per Rank-GARP combination)
It is possible to make graphics summarizing the results of more sheets with the following steps:
- import the simulation in the sheet "MySim Imp"
- set 50 reruns without the 10% of stocks from previous runs and the 2 values for the buy rule "MaxCorrel(40, 1) < 0.40" and "MaxCorrel(40, 1) < 0.50"
- create a copy of the sheet "MySim Imp" and rename it "MySim2 Imp"
- select "MySim Imp" and run all the simulations
- select "MySim2 Imp" and run all the simulations
- after running 200 simulations select the rows 3 through 102 on "MySim2 Run", right click - copy
- select the first cell on the first empty row on "MySim Run" (after 100 simulations it is A103), right click - paste
- click the Make Graph button.
You can use the wheel of the mouse with the ctrl button pressed to zoom in and out.

It is now possible to keep all the simulations on the server. The name of the simulations on the server is specified on the cell C2 of the Input sheet with a number as a suffix. The name is defined when you import the simulation, and you can change it changing the text on the cell C2. The fisrt cell on each line contains a link to the simulation on the server.
Note: if you run two different sets of simulations using the same name the links on both the sheets will point to the same simulation, ie. only the second run will point to the correct simulation.

The new button Delete Simulations allows you to delete a large number of simulations at once. To delete all the simulations created by a multiple run it is possible to use the Delete Simulation button and enter the base of the name followed by a " *".

In the lower part of the 3 dialog boxes Add Dates, Add Value Permutations and Add Multiple Runs there is now the number of permutations that would be generated with the current settings.

The Get Sell Rule Usage now works also with a large number of transactions and shows some statistics about the sell rules.

The multiple runs allows you to specify the percentage of stocks used by the previous runs to add to the restrict buy list. This allows you to use multiple runs also with simulations with many transactions.

The Get All Statistics now imports also the average gain per stock per day.

The management of the failure avoids to rerun simulations that fail because of a syntax error or too restrictive buy rules, and waits 20 seconds before rerunning the simulation when the server is busy.