Aug
24

Formula 101 – #4 Relational Operators, Boolean Operators and Decision Functions

There are times that we want to display values based on certain decision making process. For example, we may want to show the size of the gap if the market gap up, and zero if that is not the case. This is a job for the relational operators, boolean operators and decision functions.

This tutorial is longer than the previous ones due to the fact that there are three (3) closely related concepts being introduced here. Take your time to read this through.

What are relational operators?

Relational operators are similar to the arithmetic operators that you have already learned in the first tutorial, which operate on two items at a time.

There are six (6) relational operators in total,

> greater than comparison
< less than comparison >= greater than or equal to comparison
<= less than or equal to comparison
= equal to comparison
<> not equal to comparison

Unlike the arithmetic operators you have seen, these operators return only 2 possible values. They return 1 when the comparison evaluates to true and 0 when the comparison evaluates to false.

Here are some comparison examples in formula,

Close > PrevClose

Open >= Close

Close > Open

High - Close < 0.1 High - Open > Close - Low

For the last two examples, we are actually comparing the calculated results, not just the 2 items next to the relational operator. The reason is that the relational operators have lower priority than the arithematic operators.

What are boolean operators?

Now, what if you want to specify more complex conditions, say, not just the close is greater than the open, but the close must be greater than the previous close too? We’ll need the boolean operators to connect multiple relations together.

There are 2 basic boolean operators.

The and operator returns 1 when both items its applied to are not zero, otherwise, it returns 0.

The or operator returns 1 when one of the two items it applied to is not zero. If both items it applied to are zero, then it returns 0.

Here is an example of using the boolean operators in combination with the relational operators,

Last > Open and Last > PrevClose or Last < Open and Last < PrevClose

Due to the fact that the or operator has a lower priority than the and operator, which in turn has a lower priority than the relational operators, the above formula will evaluate to 1 if the last price is greater than both open price and previous close, or, if the last price is less than both open price and previous close. For all other cases, it will return 0.

What are decision functions?

Decision functions are designed to utilize the results obtained from boolean expressions to help choosing calculation results among multiple choices.

By using decision functions we are no longer bounded to return values directly obtained from the calculations, instead, we are able to derive indirect answers based on relations we have identified to be more useful then the raw information.

The if() function

If function requires 3 parameters. Its general form is like the following,

if (condition, true_result, false_result)

The first parameter condition which can be any expression evaluated to 0 or 1, then based on this value, either the second or third parameter will be returned as the result of the if function.

For the second parameter true_result to be returned, the condition cannot return zero. That means, if the condition is a relation or a complex boolean expression, it is evaluated to true and returning the value 1.

For the third parameter false_result to be returned as the result of the if function, the condition must be evaluated to zero.

Sounds very confusing, right?

Lets take a look at a few examples to clarify the point.

Here is the first example,

if (DayClose > PrevClose, DayClose, PrevClose)

This formula is doing exactly the same thing as the MaxList function when used the following way,

MaxList (DayClose, PrevClose)

For both formulas above, we are trying to get the greater value between the current day close and the previous close.

Now lets look at another example,

if (Open > PrevClose, (PrevClose + Open) / 2, 0)

In this formula, we check if the current open is greater than the previous close (i.e. gap up), if so, we would like to know the midpoint between these 2 prices because it is a useful support price level.

Here is another example,

if (Open > PrevClose, (PrevClose + Open) / 2, if (Last > PrevClose, PrevClose, 0))

This is a more complex version of the previous example. After it is determined that the Open is not greater than the previous close, a nested if function is used to check if the last price is greater than the previous close, if so, we still want to show the previous close as our support price level.

The choose() function

The choose function is designed to assist users who is interested in making selection among multiple conditions and calculated results.

The general format of the choose function is like this,

choose (condition1, result1, condition2, result2, ..., otherwise_result)

What it means is that the first parameter condition1 is evaluated, if it returns 1, then the result obtained from the second parameter result1 is used as the result of the choose function.

If condition1 returns 0, then the next condition will be evaluated. In this case, if condition2 is evaluated to 1, then result2 will be chosen as the result for the choose function.

This process continues until all condition parameters are checked through, then, the parameter otherwise_result will be used as the result of the choose function.

Here is the choose function version of the last example in the previous section,

choose (Open > PrevClose, (PrevClose + Open) / 2, Last > PrevClose, PrevClose, 0)

Looks more clear and easier to read.

We can even format it the following way to make it even easier to read,

choosen (
Open > PrevClose, (PrevClose + Open) / 2,
Last > PrevClose, PrevClose,
0)

Remember that there is no restriction as to how you format your formulas when you type them, so write it in a way that helps you read them easily is very important.

Putting It All Together

Here is a formula showing a very common usage of the choose function,

choose (NetPct > 1.5, 3, NetPct > 1, 2, NetPct > 0, 1, NetPct > -0.5, 0, -1)

If you put this formula into the quote window, you can then color code the column based on the scoring you have assigned to the symbol based on its Net Percent changes. You will then be able to tell which symbol is meeting certain criteria at a glance.

Here is how the quote window looks like before color coding.

formula101 part4 pre code

Here is the color rule settings.

formula101 part4 color setup

The quote window after using color code.

formula101 part4 post code

Summary

The relational operators, boolean operators and decision functions are usually used in combination all the time. They provide a way to distill the raw data and allowing you to obtain the information you really want based on your criteria.

Exercise

1. What is the formula for color coding a symbol green that gaps up and rising for more than 1%, yellow that gaps down and dropping for less than 0.5%, and black (blank) for all other cases?

2. What is the formula for color coding a symbol green when the last price is within 5% of today’s traded range from the high, and red when the last price is within 5% of today’s traded range from the low, and black for all other cases?

Answers for Previous Exercise

1. The function that finds the smallest number among multiple parameters is MinList. The formula is just

MinList (DayClose, PrevClose)

2. The most important function here is the maketime function which allows you to construct the time in a readable form. Here is the formula,

(frac (NTnow) - maketime (9, 30, 0)) * 1440

Discuss this article.

Leave a Comment

Blog Developed
By ContentRobot