Tutorial

This example demonstrates building a basic screener for S&P 500 stocks in Modelshop. 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. Any “EXAMPLE” or forward looking information provided is fabricated. Do not use this for investment decisions!**

This example will provide the steps to:

  1. Create and open a new model.

  2. Import data from an Excel spreadsheet.

  3. Create a data model.

  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

A common starting clusterPoint for new Modelshop models is an existing spreadsheet. With this in mind spreadsheets can be easily consumed by Modelshop.

An example workbook is provided for this tutorial and should be downloaded to your local machine. The workbook contains 4 sheets.

  1. 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.

  2. 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.

  3. Sector Outlook: A list of sectors in the S&P 500 along with a corresponding outlook. It is example information that is frequently produced by investment analysts.

  4. 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.

Create and open a model

Creating and opening a model is quick and easy. Click on the large plus icon under “My Models” and enter a desired model name. In our case, we’ll name the model “Stock Tutorial”. Once finished, click the open button and you’ll be taken to your brand new model.


Import existing data from an Excel spreadsheet

Since our model is empty, we can now import the example workbook as a starting clusterPoint for the model.

  1. Click on “Data” and select “Import Wizard” from the dropdown.

  2. Select the type of connector, in this case “File”.

  3. Locate the Stock Tutorial.xlsx file on your computer or download it now. Once selected, you will see that the “Excel file” type was automatically detected.

  4. Notice it has detected and selected all the tabs in the workbook. You can click around and preview each.

  5. Select “Load” in the upper right corner of the window to import the data and exit the wizard.

Once the data import has completed, notice the tree in the left pane now lists each of the four newly imported sheets.


Add New Fields with Custom Calculations

We can now add new calculated fields to the model. This section will add new fields utilizing custom calculations, starting with the Dividend Yield:

  1. If not selected already, click on the “Stock Fundamental” datalist in the Model Tree.

  2. In the grid toolbar, click on the button called “Create Field”. You will see a field editor pop up on your screen. This is where we will enter the details for our new field.

  3. Enter the name “Dividend Yield”.

  4. Select “DECIMAL” from the “Data Type” dropdown.

  5. In the expression field, type in dividend / price. As you type, fields or parameters that are available to choose from will be provided in a popup.

  6. Click the “Save” button to add the new field and exit the editor.


Following the steps above, create a few more calculated fields:

Price Earnings Ratio

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

Price Book Ratio

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

Data Relationships

Creating Data Relationships

So far we have only created a few calculations using data from a single Datalist (or sheet from our spreadsheet). However, there is information in the other Datalists that could be useful for our stock evaluation exercise. In this section we will create relationships between our datalists for use in more calculations.

  1. Choose “Link Wizard” from the “Link” Menu

  2. Notice there are 2 Suggested Links.

  3. Choose the Suggested Link for “Stock Fundamental -> Price Target”. Notice it has detected the “Symbol” field for both lists, which is common.

  4. Select “Make Link” to accept the link between these two Datalists.

  5. Repeat with the Suggested Link for “Stock Fundamental -> Sector Outlook”.

  6. Press the “Done” button to leave the wizard.

The “Symbol” and “Sector” fields are now highlighted in blue text, signifying the links were successfully created.


Using Data Relationships

The new links we created can be used in a variety of ways. Let’s perform a few actions to better understand what they do for us.

First, we can look at the links using the “Data Detail” pane:

  1. Select any row in the “Stock Fundamental” Datalist.

  2. Press the arrow icon on the “Data Detail” pane, located on the right side of the Datalist grid.

  3. Notice “Symbol” and “Sector” fields have a dropdown, which can be used to access “linked” data from the Datalists they were linked to.

  4. Also note that you can select other rows in the Datalist and the data changes to show the details of those records.


We are not simply limited to looking at linked data. We can also use them in new calculations:

  1. Add a new field the same way we did previously, by clicking the “Create Field” button in the grid toolbar.

  2. Name the field “Expected Return”, give it a “DECIMAL” data type and a format of “0.00%” since it will be a percentage return estimate.

  3. Type symbol. and hit tab. You will now be presented with a dropdown of fields. Select “Price Target”.

  4. Before finishing the expression, take a moment to note the value in the “Result” box. It should be returning the price target for the record you have selected.

  5. Add the expression to calculate the expected return (symbol.priceTarget - price) / price

  6. When finished, click on the “Save” button to add the field and exit the editor.


You can see from above that we can access any data using the “Symbol” and “Sector” links. However links in Modelshop are two way links. So not only was a link created from “Stock Fundamental” to “Price Target” and “Sector Outlook”, but a link was also created in reverse. Let’s take a look at what that means.

  1. Navigate to the “Sector Outlook” Datalist.

  2. Open the “Data Detail” pane, once again on the right hand side of the Datalist grid.

  3. Since links are two ways, it automatically creates a new field called “stockFundamentalList”. It consists of an array, of all the records in the “Stock Fundamental” Datalist that are linked to that specific Sector record.

  4. Let’s create a new column using this field, stockFundamentalList. Add a new INTEGER field, called “Count”. For the expression, simply enter stockFundamentalList.size(). Upon saving, you will now see a new field with a count of the stocks in each sector.


Rules

Rules can be created for event detection in Modelshop. They apply boolean logic to each record in a Datalist. Like calculations, rule expressions can utilize built-in functions, conditions and links. Additionally, rules can be assembled in sets, or “Rulebases”. They can further be segmented using names, codes, and classes. Finally, rules can return contextual text using templates and embedded expressions and/or can trigger further actions.

In this Stock Screener example, we have a Datalist called “Strategy” containing a few basic parameters (screens) to define a typical stock “Value” or “Growth” Strategy.

Rule Results

When a rule or rules fire for a specific record, it is often useful to access this information from inside the Datalist. This is easily achieved with in Modelshop due to a built in field on all Datalists called ruleResults that holds all rules that have fired for a given record. This field can be used in other calculations and queried.

Even though we have not yet added any rules, let’s create two new fields that will catch any screening rules that fire for each of our stocks.

  1. Navigate to the “Stock Fundamental” Datalist.

  2. Add a new field called “Value Rules” with a data type of TEXT.

  3. Enter ruleResults.findAll{it.ruleClass == "Value"} as the expression. This will query all the the ruleRules, find the ones that with a rule class of “Value”, and return only those records.

  4. Finally, since many rules can fire on a single record, select “Array” in the options. Which tells Modelshop this field is an array of TEXT items.

  5. Save to exit the editor.

Repeat these steps to create another new field, called “Growth Rules” that queries rules with a class of “Growth”


As could be expected, “Value Rules” and “Growth Rules” are both blank since we have yet to define any rules. Let’s change that.

Creating a Rulebase

  1. Right click on the “Stock Fundamental” Datalist in the Model Structure Tree, then select “Rulebase” from the “New” dropdown.

  2. Name the rulebase. The default is fine in this example.


Creating a Rule

  1. Navigate to “Stock Fundamental Rulebase” using the Model Tree on the left hand side of your screen.

  2. Click on the green plus icon to create a new rule. A rule wizard will pop up on your screen where you’ll be able to customize your new rule.

  3. In the “Rule Name” input, enter Min Return.

  4. In the “Rule Class” input, enter “Value”. You can select from previously used classes, or create a new one.

  5. Click into the “Trigger Expression” input and enter model.strategyList.find{it.name == “Value”}.expectedReturn > expectedReturn This expression performs a query on the strategy list, finding the record where the name field has a value of “Value” and then gets the strategy’s expectedReturn threshold. It then compares it to the expectedReturn of each record to determine if the rule should fire.

  6. Add a rule code of “MIN_RETURN”

  7. When finished, click the “Save” button. You’ll see your new rule added to the list and the datalist grid below will be updated with any rules fired.

You may also navigate back to the “Stock Fundamental” datalist and look at the field “Value Rules” - which some arrays will now be populated with the fired rules.


Repeat the steps above, to create some additional rules:

  1. Max PE: priceEarningsRatio == 0 || model.strategyList.find{it.name == "Value"}.priceToEarnings < priceEarningsRatio

  2. Max BV: priceBookRatio == 0 || model.strategyList.find{it.name == "Value"}.priceToBook < priceBookRatio

  3. Min Yield: model.strategyList.find{it.name == "Value"}.dividendYield > dividendYield

  4. Sector Outlook: 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.

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

Look for “Buys”

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

  1. Create a new field called “Buy”.

  2. Select “BOOLEAN” from the “Data Type” dropdown.

  3. In the “Calculated Expression” column, type in valueRules.size() == 0


Creating a Custom View

It would be useful to see all of the Stocks which are meeting our Buy screen in one place. To achieve this, Modelshop allows us to create additional views into a Datalist. Meaning, we can hide unneeded fields, filter, group, or apply special formatting. Let’s create a new view that only shows the stocks meeting our buy criteria.

  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”.


Create a Cube

Now we should create some summary data for our stock screen to answer some high level questions. What is the expected return for our buy stocks? How about for each sector? Let’s create a cube to answer these questions.

  1. Navigate to the “Stock Fundamental” datalist in the Model Tree on the left hand side of your screen.

  2. Right click on “Stock Fundamental” and hover over “New”, then proceed to click “Cube”. Default name is fine.

  3. At this clusterPoint, you should be taken to your new cube.

Dimensions allow us to select the values we would like to summarize on. For instance, we would like to see summary values for Buy stocks vs Non-Buy stocks.

Facts are the actual summary calculations, such as the average of the expected return.

  1. Click the side pane that says “Dimensions”.

  2. From the dropdown, choose, “Buy”.

  3. Click the “Facts” pane.

  4. Select “Expected Return” and “Mean”.

You should now see an average expected return for both buys and non-buys. You can add additional dimensions as well, such as sector, to create other summary points.


Create a Chart

To visualize the information, we can create a chart.

Navigate to the “Stock Fundamental” datalist on the Model Tree on the left hand side of your screen. As we did in the past for creating a view and a rulebase, the procedure is identical. Perform a right click and navigate from New to Chart. You will be asked to provide a name for the chart. We’ll stick with the default; click “Save” to proceed. At this clusterPoint, a chart wizard should pop up on your screen. We’ll create a chart based on the cube we’ve previously created.

  • For the “Data Source” input - select “Cube:Stock Fundamental Cube”

  • For the “Series Type” input - select “COLUMN”

  • For the “Category Field” input - select “Sector”

  • For the “Value Fields” input - select “MEAN:Expected Return”

  • For the “By Field” input - select “Buy”

When finished, click on the “Save” button. The editor should close and the chart be loaded automatically.