Select Page

Using the IF function to create a ranking value

Optuma Forums Optuma Scripting Using the IF function to create a ranking value

Viewing 2 posts - 1 through 2 (of 2 total)
• Author
Posts
• #28711
Darren
• Topics: 46
• Replies: 322
• Posts: 368

Here’s an example of using the IF() function to rank a stock according to a technical indicator value. For those of you familiar with Excel, the IF statement is based on the same principle as it introduces decision-making in to the formula. If the condition is true then one value is given, or if it is false then another is given. The syntax used is IF(formula, true value, false value).

For example, IF (CLOSE()> MA(BARS=50),CLOSE(), MA(BARS=50)) will return the closing price if it is higher than the 50 day moving average (i.e. the true condition), or the moving average if it is lower (i.e. the condition is false).

Note that if the true and false values are not specified (i.e just the IF(CLOSE() > MA(BARS=50)) then it turns in to a true/false Boolean condition, with a value of 1 given to a true result, and zero for false. To have a green True or red False result in a column rather than a value simply use CLOSE() > MA(BARS=50) without using the IF function.

In this example the following values will be assigned to each symbol based on whether two moving averages are sloping up or not:

1 = 13 Exponential MA and 50EMA both sloping up (condition 1 is true, and condition 2 is true)
2 = 13EMA sloping up and 50EMA sloping down (true, false)
3 = 13EMA sloping down and 50EMA sloping up (false, true)
4 = 13EMA and 50EMA both sloping down (false, false)

This can be expressed in the Optuma Scripting Language as follows:

This states that if the variable MA1 (the 13EMA) is sloping up (i.e. true) it will then look at variable MA2 (50EMA) and if that is also true a value of 1 will be assigned, but if MA2 is down then it will be assigned a 2 (false).

If the initial MA1 IsUp condition is false (ie it’s sloping down) then it will look to the second nested IF statement to determine whether MA2 IsUp is true (3) or false (4).

When added to a custom column in a Watchlist the values will be calculated for each symbol – as verified by the 13EMA Up? and 50EMA Up? columns in the attached image.

For clients with a Professional or Enterprise Services subscription these values can then be grouped, and summary counts / averages applied to them. In this example of the S&P400 Midcap Index 162 members have a MA Rank value of 1, 10 with a 2, 94 with a 3, and 134 where both EMAs are sloping down:

#51587
Darren
• Topics: 46
• Replies: 322
• Posts: 368

Colouring the watchlist column labels

Using the IF statement above to calculated a ranking value of the EMA slopes in a watchlist, it’s possible to colour the watchlist label based on the value colour the watchlist label based on the value:

Viewing 2 posts - 1 through 2 (of 2 total)
• You must be logged in to reply to this topic.