PDF HTML FlashPaper

Custom Field Editor

Overview of the Custom Field Editor

The Custom Field Editor is a tool that was created to help you in the creation of Custom Fields in Stock Investor Pro. This editor will help you create an algebraic formula that is syntactically correct and then apply it to every company in the database. These custom fields can then be used for screening as well as creating custom views and reports.

To open the Custom Field Editor, choose the Custom Field iccon from the toolbar or select Tools from the main menu and then Custom Field Editor (Tools--Custom Field Editor). You can also open the Custom Field Editor by simultaneously pressing the Alt and C keys (Alt + C). Following any of these steps will open the Custom Field Editor window:

Custom_Field_Editor.gif

The Custom Field Editor has several elements, which we explain here:

Expression Box

This is the area where the actual algebraic formula will appear.

To build a custom field simply start by clicking on the appropriate operator ('(', '+', '*', etc.), selecting the appropriate field from the pickers, or typing a constant value. As you do these things you will notice a formula appear in the Expression Box. Remember that the Expression Box is nothing more than a text box. Once a field or operator is added using the pickers, you can edit the text as if you were working in a text editor. This is helpful for removing mistakes or adding in missing operators or parenthesis but can also be the problem for syntax errors.

Operator Picker

This picker will place the selected operator in to the Expression Box. Operators that are valid are:

  • '+' - Addition
  • '-' - Subtraction
  • '*' - Multiplication
  • '/' - Division
  • '(' - Open parenthesis
  • ')' - Close parenthesis

Field Picker

Throughout the Stock Investor program you will be presented with the need to select a particular financial field (i.e., Company Name, Earnings per Share Y1, PE, etc.) from the database. Whenever this is necessary you will be given a standard tree consisting of data fields Definitions inside the categories like the following:

Field_Picker.gif

In order to view the fields inside, click on the + beside the field category (i.e., Company Information, Price & Share Data, Custom Fields, etc.). Once a category is open you can choose the actual field you are want by double clicking on it or by highlighting it and clicking the Select button.

Verifying a custom field formula

This is a useful function to ensure that the formula is inputted correctly in the Expression Box. Although it cannot account for financial logic errors in a calculation, it can check that all open parentheses have a closing parentheses, that the field names entered are typed correctly and do exist in the database and if there are missing or duplicate operators or fields.

Save and Save As

These buttons will invoke the Save command. If the custom field is new you will be presented with a window asking for you to give the field a Name (which is required) and a Description (which is optional). When done select the OK button. Immediately after saving a custom field you will be prompted to calculate the field. Although you have a formula, it still needs to be applied to each company in the database before a value of some kind will be placed in that company's record. You can say 'No 'to the calculation, but you will need to come back and calculate the field before you run a screen or rank utilizing it. By not calculating the field, you will most likely receive zero companies passing a screen that utilizes it.

Calculate

This action will cause Stock Investor to take the formula for the currently edited custom field and apply it to each company in the database. This is required before you can actually use a custom field in a screen or rank. Until this is done there will be NO VALUE in the actual company records. You will normally be requested to calculate a custom field upon saving it. However, if you said 'No' to the calculation process at that time, this will allow you to come back and calculate the field at a later time.

Special Mathematical Expressions

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 will discuss how to utilize some of the more useful expressions.

Custom fields are created in Stock Investor 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 in the lower right side of the Custom Field Editor:

Expressions_Pull_down.gif

Growth Rate

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 Custom Field Editor, 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 Y6 to Accounts Receivable Y1. This 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 (the [y] variable in the GrowthRate function) to its level in Y1 [x], which covers five years or compounding periods [z].

To begin calculating this growth function, select GrowthRate() 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. If so, click on the “Add Function” button so that GrowthRate() now appears in the Expression area of the Custom Field Editor:

GrowthRate_I.gif

To enter this expression into the Custom Field Editor, first make sure that the cursor is positioned between the parentheses () in the GrowthRate function. Then, scroll down the list of data categories in the Field Picker until you come to Balance Sheet - Annual and click on the “+” sign next to it:

GrowthRate_II.gif

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):

GrowthRate_III.gif

Once this is done, add a comma (,) after [Accounts receivable Y1] and then add Accounts receivable Y6 from the Field Picker, following the same steps we used when adding Accounts receivable Y1. Lastly, add another comma after [Accounts receivable Y6] and the number “5” so that the expression reads as follows:

GrowthRate_IV.gif

Now that you have entered in the full growth rate expression, you will want to verify that it is mathematically correct. You do so by clicking the Verify button. If Stock Investor is able to understand the expression, it will prompt you that the field is valid:

GrowthRate_V.gif

The next step is to the save your new field. Click on the Save button in the Custom Field Editor. At the Save Custom Field window, you can name the field and an optionable description of the field:

Save_Custom_Field.gif

When you are done, click OK.

Once you save the field, the program will prompt you 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 of Stock Investor and see the value of each user-defined field (as well as the underlying calculation of each custom field) for each company in the database:

Custom_Tab.gif

You can also perform screens using this field (or any custom field), add it to a View, or use it to rank the Stock Notebook.

Is Field Null

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 value for a particular data point for a given company. It also converts null values into a “real” number—in this case, zero.

The setup for the IsFieldNull expression in Stock Investor is as follows: IsFieldNull([x]). If the value of [x] is null, the field will return a zero (0) value. Otherwise, a value of one (1) is returned.

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

Is Field Null Expression in In-Line If Statement

For this example, we are going to calculate a custom field that takes the sum of cash and short-term investments, both for the latest fiscal quarter, and divides this by the average number of shares outstanding for the last fiscal quarter.

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 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 final “master” custom field. However, this example arrives at the same final product and is less time-consuming.)

For this custom field, several calculations are taking place:

  • Verify whether the cash or short-term investments fields are null values;
    • Return a zero value if either the cash or short-term investments fields are 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.

Working our way backward, the completed formula is: (IIF(IsFieldNull([Cash Q1])=0,0,[Cash Q1]) + IIF(IsFieldNull([Short-term investments Q1])=0,0,[Short-term investments Q1]))/[Shares Average Q1]

We must return to the Custom Field Editor and look at the “to-do” list we created earlier. The first step is to determine whether cash Q1 and short-term investments Q1 are null. 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. 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).

Figure 5. If cash Q1 is null, a zero will be returned.

CLICK ON IMAGE TO SEE FULL SIZE.

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

Figure 6. If cash Q1 is null (zero), the IIF field will return a zero value.

CLICK ON IMAGE TO SEE FULL SIZE.

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]) (Figure 5).

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 (Figure 6).

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 in Figure 7.