Stock Portfolio Tutorial

This example demonstrates building a basic screener for S&P 500 stocks. Using a combination of fundamental stock data, fabricated price targets, and a simple value investment strategy you will learn the basics of building a Modelshop model.

This example will provide the steps to:

Step Description
1 Create and open a model.
2 Import existing data from an Excel spreadsheet.
3 Create data relationships.
4 Add new data fields and populate with custom calculations.
5 Create a rulebase and corresponding rules.
6 Create methods to view data.
7 Create a cube and charts from cube data.

Example Data Source

The file Stock Tutorial.xlsx is provided for this example and should be downloaded to your local machine.

A common pattern in Modelshop is to use an Excel spreadsheet as a starting point since users often have much of their data in spreadsheets already. Luckily, existing spreadsheets can be easily imported into the Modelshop model. This example demonstrates the ease in which multiple sheets from one Excel workbook can be imported. It contains 4 sheets with the following data:

Sheet Data Set Description
Stock Fundamental A list of the constituents in the S&P 500 Stock Index and their corresponding ticker symbol, sector, price, dividend and a few other fundamental data points.
Price Target A list of price targets for each stock in the S&P 500. It is "EXAMPLE" predicted price targets for stocks often produced by the research department of a brokerage house or investment manager.
Sector Outlook A list of sectors in the S&P 500 along with a corresponding outlook. It "EXAMPLE" information that is frequently produced by investment analysts.
Strategy A list of simple forward looking strategies used in screening stocks. It includes thresholds on some popular valuation metrics, like Price to Earnings Ratio and Price to Book Ratio, as well as some minimum expected return numbers.
**Any "EXAMPLE" or forward looking information provided is fabricated.  DO NOT USE for real live trading actions.**

Create and open a model

Creating and opening a model is quick and easy and is summarized below.

Step Description
1 Click on the “Create New Model” icon from the left-most side of the icon bar.
2 Give the model a name.
3 Click on the "Update" button. The model will be added to the model list.
4 Double Click on the model name to open the "Model Browser" window. The new model will be empty.

Icon Bar: Create New Model

Import existing data from an Excel spreadsheet

Many new Modelshop users have historically used Excel spreadsheets and have lots of available data in them. Existing spreadsheets can be easily imported into the Modelshop model.

Step Description
1 Click on "Data" and select "Import" from the dropdown. (Be sure that you have double clicked on the model name from the model list and are in the Model Browser window.)

Dropdown Data Import

The "Import" dialog will open:

Step Description
1 Browse to the where you saved the Stock Tutorial.xlsx file. Once selected, you will see that the "Excel file" type was automatically detected.

Screenshot

Step Description
1 Click “Next” to open the "Media" tab where there are a variety of options that select specific data, sheets, rows, etc to be imported from an Excel spreasheet.
2 There will be a check in the "Import" column next to the data sheets described above.

Excel Check Sheets

Step Description
1 Click on “Finish” to complete the import. Other options are available and are described in the User Guide.

Once the data import has completed, the Model Structure pane will show a tree that contains a line item for each of the imported sheets.

Imported Sheets

Examine the imported data

Explore the data by clicking around the various lines in the tree. The data will appear just as it would in a typical spreadsheet.

Stock Fundamental Data

Create Data Relationships

As is common with data sets in spreadsheets, associating one data set to another is an important feature. With Modelshop, one way to do this is graphically.

Step Description
1 From the Model Browser window, click on the "Edit" button which puts the Model Browser window into Edit mode.
2 Click on the model name, "Stock Screener", at the root of the tree.
3 Click on the "Model Graph" tab.

Edit Stock Relationships

In the Model Graph pane you will see a colored box for each of the Data Lists that were imported.

The Stock Fundamental data and the Price Target data have the common data field called "Symbol". The "Symbol" field will be used to link the 2 data sets together.

Step Description
1 Double click the box for the “Stock Fundamentals” list and then click on the “Price Target” list to connect them. The "Add connection" dialog will open.
2 Select "Symbol" from the drop down menu for the data "Field" for both the Stock Fundamental "Source" data and the Price Target "Target" data.

Add Connection

Step Description
1 Click on "Finish" to complete the connection.

Create another connection by repeating the same steps from above, this time linking "Stock Fundamental” list and the “Sector Outlook” list, this time using the “Sector” field from the dropdown lists on both the source and the target lists. When both connections have been completed, the Model Graph will show connections betweeen the data sets with connection labeled with the name of the field that is connected.

Stock Model Graph

Verify the connections between the Stock Fundamental data set and the Price Target and Sector Outlook data sets. The data in the “Symbol” and “Sector” field columns are blue. This indicates that they are linked fields.

Step Description
1 Be sure that you are still in the Model Browser Edit Mode.
2 Click on the Stock Fundamental line in the Model Structure tree.
3 Click on the "Data" tab.
4 Hover over one of the "Symbols" to see the associated data from the Price Target data
5 Hover over one of the "Sector" items to see the associated data from the Sector Outlook data

Stock Data Hover

Step Description
1 Click “Save” from the icon bar to make sure that data is not lost. As with all data entry, it is recommended to "Save" your work on a periodic basis.

Stock Model Save

Add new Fields with Custom Calculations

New Data Fields can be added directly to the model. This section will add four new fields utilizing custom calculations.

Step Description
1 Be sure that you are still in the Model Browser Edit Mode.
2 Click on the Stock Fundamental line in the Model Structure tree.
3 Click on the "Fields" tab which will display the various fields from the Stock Fundamental data set.
4 Click on the "Create new field" icon which will add a new entry line at the bottom of the data set.
5 In the "Name" column, enter the new field Dividend Yield. The entry will appear yellow until saved.
6 Select "FLOAT" from the "Data Type" dropdown.
7 In the "Calculated Expression" column, type in dividend / price.
As you type, fields or parameters that are available to choose from will be provided in a popup. Note that the parameters are case sensitive

Add Field

Repeat the steps 4 - 7 from above and add the following parameters:

Step Description
4 Click on the "Create new field" icon which will add a new entry line at the bottom of the data set.
5 In the "Name" column, enter the new field Price Earnings Ratio. The entry will appear yellow until saved.
6 Select "FLOAT" from the "Data Type" dropdown.
7 In the "Calculated Expression" column, type in
if (EPS <=0) { return null } else { return price / EPS }

This calculated expression shows the complexity Modelshop can utilize while still understandable to the user. The expression checks if the calculated EPS is less than or equal to 0, if it is, then the data entry is filled with "null" (nothing). Otherwise, if EPS is greater than 0, then the calculated Price Earnings Ratio is added to the data set.

Repeat the steps 4 - 7 above and add the following parameters:

Name = Price Book Ratio Data Type = FLOAT
Calculated Expression defined as:

if (bookValue <=0) {
        return null
} else {
        return price / bookValue
}

Repeat the steps 4 - 7 above and add the following parameters:

Name = Expected Return
Data Type = FLOAT
Calculated Expression defined as:

(symbol.priceTarget - price) / price
Step Description
1 Remain in the "Edit" mode in the Model Browser window and click on the "Data" tab.
2 Notice that the fields that were added now appear in the data.

View Added Data Fields

Create a Rulebase and Corresponding Rules

Rules can be created to filter data to meet a specific criteria. A group or list of rules is a rulebase.

In this Stock Screener example, the imported data set from the initial Excel spreadsheet that has not yet been utilized is the "Strategy" data set. That data contains specific values assigned to some parameters to define a typical stock "Value" or "Growth" Strategy. View that data using the following steps:

Step Description
1 Be sure that you are still in the Model Browser "Edit" Mode.
2 Click on the Strategy line in the Model Structure tree.
3 Click on the Data tab.
4 The "Value" and "Growth" strategies have specific data assigned to fields which will be used to create rules.

View Strategy

In this example, rules will be created applying the "Value" strategy to the Stock Fundamentals data set.

Rules can be assigned a name so that you can create a display that shows which rules are met or not. Names can be added and displayed similar to any Data Field.

Step Description
1 Make sure that you are in the Model Browser "Edit" mode.
2 Click on the "Stock Fundamental" line in the Model Structure tree.
3 Click on the "Fields" tab which will display the various fields from the Stock Fundamental data set.
4 Click on the "Create new field" icon which will add a new entry line at the bottom of the data set.
5 In the "Name" column, enter the new field Value Rules. The entry will appear yellow until saved.
6 Leave the default "ANY" in the "Data Type" dropdown.
7 In the "Calculated Expression" column, type in ruleResults.findAll{it.ruleClass == "Value"}.ruleName. Note that the parameters are case sensitive.

Add ValueRules Field

!!! note "ruleResults" expressions are a special case. Every data list by default has an object called "ruleResults". This object is automatically populated with the results of a rulebase. In this example, the ruleName will be displayed in the "Value Rules" column then the rule has not been met.

Creating a Rulebase

Step Description
1 Make sure that you are in the Model Browser "Edit" mode.
2 Click on the "Stock Fundamental" line in the Model Structure tree.
3 Click on the "Compute" in the menu bar and select the "Create Rule" from the dropdown.

Create Rule

A rulebase must be named. Since a rule is part of a rulebase, clicking on the "Create Rule" causes the "Create Rulebase" dialog to appear.

In this example, you can leave the default name in the textbox.

Name Rule

Creating a Rule

Step Description
1 Make sure that you are in the Model Browser "Edit" mode.
2 Click on the "Stock Fundamental Rulebase" line in the Model Structure tree.
3 Click on the "Rules" tab which will display the various fields from the Stock Fundamental data set.
4 Click on the "Create new field" icon which will add a new entry line at the bottom of the data set.
5 In the "Rule Name" column, enter Min Return.
6 In the "Rule Class" column, enter "Value".
7 Click in the "Trigger Expression" field and a dialog will open.
8 In the "Trigger Expression" dialog, enter the expression: model.strategyList.find{it.name == "Value"}.expectedReturn > expectedReturn. Note that the parameters are case sensitive.

Add Rules

The "Min Return" rule compares the value of the "Expected Return (expectedReturn)" from the "Stock Fundamentals" list with the "Expected Return (expectedReturn)" from the Value "Strategy". If the number from the "Strategy" data is greater than the "Stock Fundamentals" number, then the rule has been met and the "Rule Name" will be displayed in the "Value Rules" column in the "Stock Fundamentals" data.

Repeating steps 4-8 above, create the following four rules:

Rule Name Rule Class Trigger Expression
Max PE Value priceEarningsRatio == 0 || model.strategyList.find{it.name == "Value"}.priceToEarnings < priceEarningsRatio
Max BV Value priceBookRatio == 0 || model.strategyList.find{it.name == "Value"}.priceToBook < priceBookRatio
Min Yield Value model.strategyList.find{it.name == "Value"}.dividendYield > dividendYield
Sector Outlook Value sector.outlook == "Underweight"

Similarly, for the "Max PE" rule, the "Price Earnings Ratio (priceEarningsRatio)" from the "Stock Fundamentals" list is compared with the "Price To Earnings (priceToEarnings)" from the Value "Strategy". If the "Stock Fundamentals" number is zero OR greater than the "Strategy" number, then the rule has been met and will be displayed in the "Value Rules" column.

Step Description
1 Click “Save” from the icon bar to make sure that the newly entered rules are not lost.

Stock Model Save

View the applied Rules

Step Description
1 Make sure that you are in the Model Browser "Edit" mode.
2 Click on the "Stock Fundamental" line in the Model Structure tree.
3 Click on the "Data" tab which will display the various fields from the Stock Fundamental data set.
4 Notice that the new Field "Value Rules" is displayed and the data in column are the rule names that are met.
5 Looking at the highlighted stocks, notice that the "Capital One Financial" stock does not have any rules that were met (explained below).

View Applied Rules

Recall that the Value "Strategy" had the following requirements:

Value Strategy

View the Sector Outlook data

Step Description
1 Make sure that you are in the Model Browser "Edit" mode.
2 Click on the "Sector Outlook" line in the Model Structure tree.
3 Click on the "Data" tab which will display the various fields from the Sector Outlook data set.
4 For the Value Strategy, the outlook for Financials and Consumer Staples are highlighted.

Sector Outlook Data

Evaluating the rules:

It is always good to verify your data to make sure the rules you have set up are doing what you intended. Using 2 stocks from the "Stock Fundamental" data, choose Campbell Soup (CPB) and Capital One Financial (COF) as examples.

Symbol Value Rules Expression Calculation Met
CPB Min Return model.strategyList.find{it.name == "Value"}.expectedReturn > expectedReturn 5% > 1% Yes
Max PE priceEarningsRatio == 0 || model.strategyList.find{it.name == "Value"}.priceToEarnings < priceEarningsRatio 15 < 30.36 Yes
Max BV priceBookRatio == 0 || model.strategyList.find{it.name == "Value"}.priceToBook < priceBookRatio 1.40 < 12.38 Yes
Min Yield model.strategyList.find{it.name == "Value"}.dividendYield > dividendYield 2.5% > 2% Yes
Sector Outlook sector.outlook == "Underweight" "Underweight" Yes
COF Min Return model.strategyList.find{it.name == "Value"}.expectedReturn > expectedReturn 5% > 10% No
Max PE priceEarningsRatio == 0 || model.strategyList.find{it.name == "Value"}.priceToEarnings < priceEarningsRatio 15 < 9.38 No
Max BV priceBookRatio == 0 || model.strategyList.find{it.name == "Value"}.priceToBook < priceBookRatio 1.40 < 0.71 No
Min Yield model.strategyList.find{it.name == "Value"}.dividendYield > dividendYield 2.5% > 3% No
Sector Outlook sector.outlook == "Underweight" "Overweight" No

There are three key points to observe from the table above:

Item Description
1 If the rule was met, then the rule names were included. See the Value Rule column in View Applied Rules
2 The rules were created to "screen out" undesired characteristics of a stock. In other words, if a rule was met, then that is a reason that we would NOT want to choose that stock.
3 If all of the rules were met, then the Rule Value column is blank and has no entry for the given stock.

Item 3 can be used as a key to the main function of stock screening. When there are no rules listed in the Value Rules column, that indicates that the stock is desirable and should trigger a "Buy" action.

"Buy"

A new field can be added to the "Stock Fundamentals" to show the screened stocks that are recommended for a "Buy".

Step Description
1 Be sure that you are still in the Model Browser "Edit" Mode.
2 Click on the Stock Fundamental line in the Model Structure tree.
3 Click on the "Fields" tab which will display the various fields from the Stock Fundamental data set.
4 Click on the "Create new field" icon which will add a new entry line at the bottom of the data set.
5 In the "Name" column, enter the new field Buy.
6 Select "BOOLEAN" from the "Data Type" dropdown.
7 In the "Calculated Expression" column, type in
valueRules.size() == 0

Add Buy Field

This expression requires that there be nothing in the Value Rules column in the Stock Fundamentals data.

Create a Custom View

There are many methods to choose relevant data. One way is to create a filter. In the example, to view only those stocks that are designated a "Buy" create the following filter.

Step Description
1 Be sure that you are still in the Model Browser "Edit" Mode.
2 Click on the Stock Fundamental line in the Model Structure tree.
3 Click on the "Data" tab which will display the various fields from the Stock Fundamental data set.
4 Click on the "Buy" column dropdown.
5 Select "Filters" from the dropdown.
6 The Filters dialog will open and select "True".

Add Filter

After closing the "Filters" dialog, the window will now display only the stocks that were not screened out by the rules created to comply to the "Value Strategy".

View Filtered on Buy

Create Cubes and Charts

A cube is a multidimentional data source. Its benefits are that data is aggregated in measurements and dimensions so that you can organize and utilize data to make more informed decisions. In Modelshop, the data can easily be summarize using cubes and charts.

In the example, you can see how data for the recommended "Buy" stocks compare to the entire population of the S&P 500.

Step Description
1 Be sure that you are still in the Model Browser "Edit" Mode.
2 Click on the Stock Fundamental line in the Model Structure tree.
3 Be sure that you are still on the "Data" tab.
4 Click on the "Create New Item" icon and select "Cube" from the dropdown menu.

Create New Cube

The Cube Naming dialog will appear. Leave the default name and click the "Save" button.

Name New Cube

The Model Browser window will have the following features:

Item Description
1 A new Cube item in the Model Structure tree.
2 Be sure that the "View Layout" tab is selcted in the Cube pane.
3 The layout will have a "Facts" box where Fields from the Data List can be added.
4 The layout will have a "Dimensions" box where Fields from the Data List can be added.
5 A "Grand Total" item will be shown which will allow you to see filtered items relative to the entire Cube data set.

Cube View

Adding data to Cube

To add facts into the cube, click anywhere in the “Facts” box and a dialog will open:

Step Description
1 For the "Field" dropdown, select "Dividend Yield".
2 For te "Fact Type" dropdown, select "Mean".

Add Cube Facts

Repeat this process to add the additional new "Facts" to the cube:

Field Fact Type
Price Earnings Ratio Mean
Expected Return Mean

The cube pane will now show the Mean for each of the Data fields that were selected. The "Grand Total" data represents all of the stocks in the "Stock Fundamentals" list.

View Fact Grand Totals

Step Description
1 Click in the "Dimensions" box and the "Field" dropdown dialog will appear.
2 Scroll down and select "Buy".

The cube pane will now show not only the Mean data for all of the S&P 500 stocks listed in the Stock Fundamental data list, but will also categorize them by the "Buy" recommendation was "true" or "false".

You can see that the recommended "Buy" stocks have a higher mean for Dividend yield, lower mean for PE Ratio, and a greater mean Expected Return than either the stocks what were screened out, as well as the entire population.

Great, this is exactly what we would hope for!

View All Cube Data

Step Description
1 Click “Save” from the icon bar to make sure that the newly entered rules are not lost.

Stock Model Save

Create a Chart from Cube data

To visualize the inforamtion, select the "Chart" button in the Cube pane icon bar.

Chart CubePane

The dialog to name the chart will appear. Leave the default name and click on the "Save" button. Upon doing so, the "Add Series" dialog will appear. Select the following parameters for the respective dropdown menus:

Parameter Dropdown selection
Type Column
Category Field buy
Category Axis BOTTOM
Value Field MEAN: Dividend Yield
Value Axis LEFT

As soon as you select "LEFT" for the Value Axis, the pane will update and display the Dividend Yield chart where you will see the following:

Item Description
1 The new "Stock Fundamental Cube Chart" item in the Model Structure tree.
2 Be sure that the "View Layout" tab is selcted in the Cube pane.
3 The Mean Dividend Yield for the screened out stocks (Buy = false) and the mean for those that were recommended as buys. When you hover over the "true" column, the value of the "meanDividendYield" will appear.

Div Yield Chart

You can add the other data by clicking in the "Series" box and adding the following:

Parameter Dropdown selection
Price Earnings Ratio
Dropdown selection
Expected Return
Type Column Column
Category Field buy buy
Category Axis BOTTOM BOTTOM
Value Field MEAN: Price To Earnings MEAN: Expected Return
Value Axis RIGHT LEFT

Notice the following in the updated chart:

Item Description
1 The LEFT axis is scaled for the mean Dividend Yield and the meanExpectedReturn.
2 The RIGHT axis is scaled for the meanPriceEarningRatio.
3 Hover over the columns and the values will be shown.
4 If you click on one of the items in the legend, that item will be removed from the chart and have a strike-through the name. If you click on a name that has a strike-through, it will reappear in the chart.

View Cube Column Chart

To present the information from the graph in printed form, select the "Save as image" icon from the Cube pane icon bar. This allows you to save the Chart as a .png to insert into presentations, fund reports, or client reports.