FilterData
The FilterData() function is used to filter and output a range of information according to specified criteria.
Syntax
FilterData (Range, Criteria, Columns)
| Range | The range of Excel cells to filter |
| Criteria | The mathematical expression representing the filter to apply. Columns are denoted by “C1”, “C2”, “C3” etc. Allowed mathematical expressions are restricted to the Quintessence language. Expressions can be combined using ‘&’ for ‘and’ and ‘|’ for ‘or’. Additionally, certain rows can be filtered out depending on their positions using ‘Line’, for example, “Line 3” or “Line < 20”. |
| Columns | The columns to output, for example, “C1,C2,C3”. If the value is set to “*”, all columns are returned. |
Note:
Input parameters can consist of Quintessence functions. This includes functions that return ranges in cases where a parameter value can be a range.
Examples
Example 1. Return equities that meet a criterion
Example 2. Return equities that meet multiple criteria
Example 3. Return equities that meet a criterion and show all output columns
Example 4. Return prices that are less than the five-day rolling average using RC syntax
Note:
How your system administrator configured Quintessence determines which function parameters are required. In most implementations, administrators configure functions so that certain parameters default to specific values when left blank.
Example 1. Return equities that meet a criterion
The FilterData() function is used to return the equities in the list with an historical earning yield greater than 7%.
| Range | Earning yield > 7% | ||
| Constituents | Close Price | Earning yield | =FilterData(A3:C9,”(C3 > 0.07)”,”C1,C3″) |
| CEB | 7900 | -1.32% | |
| GMP | 28135 | 7.17% | |
| KVJ | 70300 | 0.41% | |
| MBD | 7400 | 5.09% | |
| PHO | 12775 | 8.39% | |
| QGW | 20510 | 7.75% | |
| VRR | 3520 | 27.98% | |
| Range | Output | |||
| Constituents | Close Price | Earning yield | GMP | 7.17% |
| CEB | 7900 | -1.32% | PHO | 8.39% |
| GMP | 28135 | 7.17% | QGW | 7.75% |
| KVJ | 70300 | 0.41% | VRR | 27.98% |
| MBD | 7400 | 5.09% | ||
| PHO | 12775 | 8.39% | ||
| QGW | 20510 | 7.75% | ||
| VRR | 3520 | 27.98% | ||
Example 2. Return equities that meet multiple criteria
The FilterData() function is used to return the equities in the list with an historical earning yield less than or equal to 1% or greater than or equal to 7%.
| Range | Earning yield = 7% or <= 1% | ||
| Constituents | Close Price | Earning yield | =FilterData(A3:C9,”(C3=0.07)|(C3<=-0.01)”,”C1,C3″) |
| CEB | 7900 | -1.32% | |
| GMP | 28135 | 7.17% | |
| KVJ | 70300 | 0.41% | |
| MBD | 7400 | 5.09% | |
| PHO | 12775 | 8.39% | |
| QGW | 20510 | 7.75% | |
| VRR | 3520 | 27.98% | |
| Range | Output | |||
| Constituents | Close Price | Earning yield | CEB | -1.32% |
| CEB | 7900 | -1.32% | GMP | 7.17% |
| GMP | 28135 | 7.17% | PHO | 8.39% |
| KVJ | 70300 | 0.41% | QGW | 7.75% |
| MBD | 7400 | 5.09% | VRR | 27.98% |
| PHO | 12775 | 8.39% | ||
| QGW | 20510 | 7.75% | ||
| VRR | 3520 | 27.98% | ||
Example 3. Return equities that meet a criterion and show all output columns
The FilterData() function is used to return the equities in the list with an historical earning yield greater than 8%. The use of “*” returns all output columns.
| Range | Earning yield > 8% | ||
| Constituents | Close Price | Earning yield | =FilterData(A3:C9,”(C3 > 0.08)”,”*”) |
| CEB | 7900 | -1.32% | |
| GMP | 28135 | 7.17% | |
| KVJ | 70300 | 0.41% | |
| MBD | 7400 | 5.09% | |
| PHO | 12775 | 8.39% | |
| QGW | 20510 | 7.75% | |
| VRR | 3520 | 27.98% | |
| Range | Output (* = all columns) | ||||
| Constituents | Close Price | Earning yield | PHO | 12775 | 8.39% |
| CEB | 7900 | -1.32% | VRR | 3520 | 27.98% |
| GMP | 28135 | 7.17% | |||
| KVJ | 70300 | 0.41% | |||
| MBD | 7400 | 5.09% | |||
| PHO | 12775 | 8.39% | |||
| QGW | 20510 | 7.75% | |||
| VRR | 3520 | 27.98% | |||
Example 4. Return prices that are less than the five-day rolling average using RC syntax
The FilterData() function is used to return prices that are less than the five-day rolling average. This example uses Quintessence Row Column syntax to locate relative cell values for use in calculations on a line-by-line basis.
| Range | Prices less than the 5-day rolling average |
| 25604 | =FilterData(A2:A15,”R(Line)C(1) < AVERAGE(R(Line-4)C(1):R(Line)C(1))”) |
| 25369 | |
| 24684 | |
| 24970 | |
| 25402 | |
| 25639 | |
| 25626 | |
| 25725 | |
| 25540 | |
| 25273 | |
| 25457 | |
| 26450 | |
| 26312 | |
| 26310 |
RC syntax
The expression R(Line)C(1) < AVERAGE(R(Line-4)C(1):R(Line)C(1)) iterates through the array until the last row.
R(Line) refers to the current row in the iteration.
C(1) refers to the first column of the range, regardless of iteration.
R(Line-4) refers to the row that is four rows before the current row in the iteration.
R(Line-4)C(1):R(Line)C(1)) refers to the range of cells from the first column that starts four rows before the current row of the iteration to the current row of the iteration.
Therefore the expression evaluates which values in the list are less that the five-day rolling average.
Note: Line is a Quintessence reserved word that indicates the current row in an iteration.
| Range | Output |
| 25604 | 25540 |
| 25369 | 25273 |
| 24684 | 25457 |
| 24970 | |
| 25402 | |
| 25639 | |
| 25626 | |
| 25725 | |
| 25540 | |
| 25273 | |
| 25457 | |
| 26450 | |
| 26312 | |
| 26310 |