Stock Investor Pro has plenty to offer for individual investors looking to perform fundamental analysis for long-term investing. However, no matter how powerful a stock screening program is, inevitably there will be data fields that are not listed. If you find that a data field of interest is not included in Stock Investor Pro, fear not. Users have the ability to go beyond the thousands of data points available for each company by creating their own custom fields. These custom fields can then be used for screening as well as creating custom views and reports. One feature that many users may not be familiar with is the collection of mathematical expressions that is available to perform more advanced calculations. Here we discuss how to utilize some of the more useful expressions.
Custom fields are created with the Custom Field Editor. To open the Custom Field Editor, select Custom Field Editor from the Tools menu (Tools-Custom Field Editor), click on the Custom Field Editor icon from the toolbar, or use the Alt+C key command.
Once you are in the Custom Field Editor, you can access the mathematical functions from the pull-down menu on the right side of the editor [showing ABS()]. There are a variety of functions that Stock Investor Pro uses to create custom fields.
We begin by creating a new custom field that makes use of the GrowthRate function. At the Custom Field Editor, select the GrowthRate() function from the pull-down menu that is right above the “Add Function” button. After selecting it from the list, GrowthRate() should appear in the area just above the “Add Function” button. Click on the “Add Function” button so that GrowthRate() now appears in the Expression area of the Custom Field Editor. The setup for the growth rate function is GrowthRate([x], [y], [z]), which is the growth in a data field from value [y] to [x] over [z] periods. You can see this by moving the mouse cursor over the function name in the drop-down menu, prompting a pop-up window with a brief description of the field.
To calculate the annualized growth in accounts receivable over the last five years, we examine the total change in accounts receivable from six years ago (Y6) to last year( Y1). Using years Y6 and Y1 means we are calculating the percentage change using five compounding periods: Y6 to Y5, Y5 to Y4, Y4 to Y3, Y3 to Y2, and Y2 to Y1.
In simple terms, we are calculating the annual growth rate in accounts receivable from its level in Y6 [y] to its level in Y1 [x], which covers five years or compounding periods [z]. To enter this expression into the Custom Field Editor, first make sure that the cursor is positioned between the parentheses () in the function. Then, scroll down the list of data categories until you come to Balance Sheet - Annual and click on the “+” sign next to it. This will expand this data category so that you can see all the individual data fields that make up the category. Scroll down the list of data fields until you come to Accounts receivable Y1 and double-click on it. This data field should appear in the expression area (clicking once on Accounts receivable Y1 and then clicking on the Add Field button will produce the same result). Once this is done, add a comma (,) after [Accounts receivable Y1] and then add Accounts receivable Y6 by selecting it from the list. Lastly, add another comma after [Accounts receivable Y6] and the number “5” so that the custom growth rate expression reads as follows:
GrowthRate([Accounts receivable Y1],[Accounts receivable Y6],5)
Now that you have entered in the full expression, you will want to verify that it is mathematically correct. You do so by clicking the Verify button. If the program is able to understand the expression, a box pops up saying the field is valid. The next step is to the save your new field. Click on the Save or Save As button in the Custom Field Editor, name the field, and click OK. Once you save the field, the program will ask if you want to calculate this field for all the companies in the database. We strongly suggest you choose Yes.
After the calculations are complete, close the Custom Field Editor. You can go to the Custom tab to see the value of each user-defined field (as well as the underlying calculation of each custom field) for each company in the database. You can also perform screens using this field (or any custom field) and add it to a View.
No matter what type of calculations you are performing with a custom field, having a “null” (empty) value for any data field used to create a custom field will render the entire value for a particular company null as well. The IsFieldNull expression allows users to identify null values for a particular data point for a given company. It also converts null values into a “real” number—in this case, zero.
By itself, the IsFieldNull expression is not very useful. Its true value becomes apparent when using it in conjunction with other expressions, such as an In-Line If statement (see below). It can be used in other custom fields to bypass null values and still arrive at a valid figure. The underlying assumption for using the IsFieldNull expression is that null values will be treated as zero.
The In-Line If expression is an “if-then” statement, where one action is taken if ‘something’ is true, or a different action is taken if that ‘something’ is false. The set up for the In-Line If expression is: IIF([x], [y], [z]), where the logical expression [x] is evaluated and then one of the two expressions is processed. If [x] is true, IIF() returns [y]. If [x] is false, IIF() returns [z].
For this example, we calculate a custom field that adds together cash and short-term investments for the latest fiscal quarter and divides the sum by the average number of shares outstanding for the latest fiscal quarter. However, many companies do not report short-term investments or cash separately on their balance sheets, and some do not have these items. Therefore, a company may have a null value for either one or both of these fields—rendering the entire custom field null.
When calculating a multivariate expression, it is a good idea to view it in parts. Breaking it down into more manageable pieces increases the odds of generating a valid expression. (Note that you could also create separate custom fields and then combine them into one “master” custom field. However, our example arrives at the same final figure and is less time-consuming.)
For this custom field, several calculations are taking place:
Identify whether the cash or short-term investments fields have null values; Return a zero value if the cash or short-term investments field is zero, otherwise return the actual field values; Sum the values (zero or actual value) of cash and short-term investments; and Divide the total by the average number of shares outstanding.
The completed formula is:
To create this custom field, we must open the Custom Field Editor and look at the “to-do” list above. [Open the Custom Field Editor by selecting Custom Field Editor from the Tools menu (Tools-Custom Field Editor), clicking on the Custom Field Editor icon from the toolbar or using the Alt+C key command.] The first step is to substitute zeros for null values in the cash Q1 and short-term investments fields. This involves using two IIF() and two IsFieldNull expressions in this custom field.
We verify whether cash Q1 is a null value using the IsFieldNull expression. Select the IsFieldNull() function from the pull-down menu that is right above the “Add Function” button on the right side. After selecting it from the list, IsFieldNull should appear in the area just above the “Add Function” button. If so, click on the “Add Function” button so that IsFieldNull() now appears in the Expression area of the Custom Field Editor. Next, make sure that the cursor is positioned between the parentheses () in the function. Then, scroll down the list of data categories until you come to Balance Sheet - Quarterly and click on the “+” sign next to it. This will expand this data category so that you can see all the individual data fields that make up the category. Scroll down the list of data fields until you come to Cash Q1 and double-click on it. This data field should appear in the expression area (clicking once on Cash Q1 and then clicking on the Add Field button will produce the same result).
Staying with the Cash Q1 data field, we want to create a function that will return a zero value if Cash Q1 is null or return the actual cash amount if the field is not null. This involves using the IIF() expression.
Position the cursor before the IsFieldNull expression in the Expression area of the Custom Field Editor. Select the IIF() function from the pull-down menu that is right above the “Add Function” button. After selecting it from the list, IIF() should appear in the area just above the “Add Function” button. If so, click on the “Add Function” button so that IIF() now appears in the Expression area of the Custom Field Editor. The IsFieldNull expression we just created needs to be inside the IIF parentheses, so the easiest thing to do is to delete the () following IIF and then enclose the IsFieldNull expression with another set of parentheses. This is important, otherwise the formula will come back as invalid. At this point, the custom field should appear as it does below.
The logical expression [x] in this statement is whether or not Cash Q1 is null (the IsFieldNull expression returns a zero value). Therefore, the next step is to set the IsFieldNull expression to zero by adding =0 after ([Cash Q1]), as shown below.
If Cash Q1 is null (the IsFieldNull field’s value is zero), the IIF field should return a zero value. So we add ,0 to take care of the [y] element of the expression, as shown below. When using an IsFieldNull expression and the field is not null, a value of one is returned by the field. Therefore, if Cash Q1 is not null the expression IsFieldNull[(Cash Q1])=0 is not true. Therefore, we want the IIF statement to return the actual value of Cash Q1. To achieve this, we add ,[Cash Q1] to the end of the expression.
The next step is to add to this expression a similar IIF statement using short-term investments Q1, which is also in the Balance Sheet - Quarterly data category. To complete the field, we enclose the two IIF statements in a final set of parentheses and divide the expression by Shares Average Q1 (Share Statistics data category) to arrive at the completed field below.
Remember that once you have entered in the full expression, you will want to verify that it is mathematically correct by clicking the Verify button. If the program is able to understand the expression, a box pops up saying the field is valid. The next step is to name and save the new field and then you are asked whether you want this field calculated for all the companies in the database.
Back to Stock Notebook