Conditional Formatting, Sorting and Ranking Columns

A screen that returns forty rows and eleven columns has not saved you any work if you still have to read all four hundred and forty cells to find the answer. This lesson is about the four AmiBroker features that turn a correct table into a usable one — and about the one design mistake that makes a table look professional while quietly making it worse.

Colour is an array, like everything else

AddColumn’s fourth and fifth arguments are the text colour and the background colour, and both accept an array. Since AFL evaluates arrays elementwise, that means one colour per bar, which in an exploration means one colour per row.

The idiom is IIf():

Fragment — not a complete formula

AddColumn( Close, "Close", 1.4, IIf( ROC( Close, 1 ) > 0, colorGreen, colorRed ) );

To reach the background colour you must supply the format and text colour positionally, because AFL has no named arguments:

Fragment — not a complete formula

AddColumn( Momentum, "ROC", 1.2, colorDefault,
           IIf( Momentum > 0, colorPaleGreen, colorRose ) );

Colours can also be computed rather than chosen. ColorHSB( hue, saturation, brightness ) gives you a continuous scale, which is how AmiBroker’s own example builds a heat-map effect from a percentile:

Fragment — not a complete formula

Rank      = PercentRank( Close, 100 );
CellColor = ColorHSB( Rank * 64 / 100, 255, 255 );
AddColumn( Rank, "100-day percent rank", 1.2, colorDefault, CellColor, -1, Rank );

That last line also demonstrates the seventh argument. barchart accepts a value from 0 to 100 and draws an in-cell bar of that percentage width, using the background colour. It is the cheapest way to make a numeric column comparable at a glance, and it costs nothing in screen space because the bar lives inside the cell that already holds the number.

⚠Warning

PercentRank( array, range ) ranks a value against that same symbol’s previous range bars. It is a time-series percentile, not a cross-sectional one. An in-cell bar built from it says “busy compared with its own recent history”, not “busy compared with the other rows in this table”. Cross-sectional ranking across symbols needs the static-variable machinery in Part 13.

Sorting from the formula

Fragment — not a complete formula

SetSortColumns( col1, col2, ... );   // up to 10 columns

Three rules, all of which bite:

• Column numbers are one-based, and they count the automatic Ticker and Date/Time columns. With default columns on, your first AddColumn is column 3.
• A positive number sorts ascending, a negative one descending. SetSortColumns( -3 ); sorts by the third column, largest first.
• Each call overwrites the previous one. Calling it twice does not produce a two-level sort; SetSortColumns( 1, -2 ); does.

SetSortColumns sets the initial sort. The reader can still click any column header to re-sort interactively, and that is a feature: you choose the order that makes the table answer its main question, and they reorder it to answer a different one.

Rank columns, and why the call order is backwards

AddRankColumn() takes no arguments and adds a ranking column based on the sort currently established by SetSortColumns. Which means the call order is the opposite of the way you would say it in English:

Fragment — not a complete formula

SetSortColumns( -6 );   // first establish the sort
AddRankColumn();        // then rank according to it

Called with no prior sort, AddRankColumn() degenerates into a plain line-number column. That is not an error and produces no warning; it is simply a column of 1, 2, 3 that looks like a rank and is not one.

This is also the one legitimate reason to call SetSortColumns more than once. Interleaving pairs lays down several independent rank columns side by side:

Fragment — not a complete formula

SetSortColumns( -6 );
AddRankColumn();        // rank by momentum, strongest first
SetSortColumns( 7 );
AddRankColumn();        // rank by volatility, quietest first

Ranks in AmiBroker count from one.

✦Tip

A rank column earns its place because it survives re-sorting. Once the reader clicks a header to sort by something else, the rank you computed is still sitting there in its own column, so they can see that the row now at the top was ranked twenty-eighth on your original criterion. Position in a list is not information; a rank column is.

Summary rows

Fragment — not a complete formula

AddSummaryRows( flags, format = 0, onlycols = 0, ... );

flags is a bit combination: 1 for TOTAL, 2 for AVERAGE, 4 for MIN, 8 for MAX, 16 for COUNT and 32 for STANDARD DEVIATION. So 31 is the first five and 31 + 32 is all six.

Three details decide whether this function helps or hurts.

Summary rows appear at the TOP of the list. Not the bottom. AmiBroker’s own example says so in a comment, and people still scroll to the end looking for the totals.

The default format prints up to fifteen digits. format defaults to 0, which means “maximum precision”. A column of prices summarised at maximum precision looks like a bug. Always pass an explicit format such as 1.2.

Restrict the columns. onlycols takes up to ten one-based column numbers — numbered exactly as in SetSortColumns, including the automatic columns. Omit it and every column gets a summary, which produces a TOTAL of a price column: a number with no meaning at all, sitting at the top of your table looking authoritative.

Fragment — not a complete formula

AddSummaryRows( 2 + 4 + 8 + 16, 1.2, 5, 6, 7 );   // avg, min, max, count for cols 5-7

Call it once. If you do call it repeatedly the flags accumulate as a bitwise OR while format and onlycols are overwritten by the last call, which is a confusing way to arrive at a result you could have written directly.

✦Tip

The COUNT row is the most useful of the six and the least used. It tells you how many rows the run actually produced, which is the first number you need whenever you are asking whether a screen behaved. Add 16 to the flags of every diagnostic exploration you write.

A table designed to be read in five seconds

Complete runnable AFL

readable-table.afl

// readable-table.afl
// Part 12 - Conditional Formatting, Sorting and Ranking Columns
//
// The same screen as the previous lesson, redesigned so that the answer is
// visible in about five seconds. Three techniques do the work: an initial sort
// chosen by the formula, a rank column that survives re-sorting, and colour
// used only to repeat something the row already says in words.
//
// Assumptions:
//   - Daily bars, Range wide enough for a 200-bar average.
//   - Column numbers below count the two automatic columns (Ticker is 1,
//     Date/Time is 2), so the first column this formula adds is column 3.
//     Turning default columns off with SetOption("NoDefaultColumns", True)
//     would shift every number in SetSortColumns and AddSummaryRows by two.

TrendPeriod     = 200;
MomentumPeriod  = 20;
LiquidityPeriod = 50;
MinTurnover     = 1000000;

Turnover    = Close * Volume;
AvgTurnover = MA( Turnover, LiquidityPeriod );
Trend       = MA( Close, TrendPeriod );
DistancePct = 100 * ( Close - Trend ) / Trend;
Momentum    = ROC( Close, MomentumPeriod );

AboveTrend  = Close > Trend;
RisingMom   = Momentum > 0;

Filter = Status( "lastbarinrange" ) AND AvgTurnover > MinTurnover;

// Colour arrays. IIf() returns one colour per bar, and AddColumn accepts an
// array in the textColor and bkgndColor slots, so the colour can depend on the
// value in the cell. Because AFL has no named arguments, reaching the fifth
// argument means writing the third and fourth explicitly.
TrendColour = IIf( AboveTrend, colorDarkGreen, colorDarkRed );
MomColour   = IIf( RisingMom,  colorDarkGreen, colorDarkRed );

// PercentRank measures this symbol against its own last 100 bars, so the
// in-cell bar says "busy for this instrument", not "busy compared with the
// other rows". Cross-sectional ranking is Part 13's subject.
TurnoverBar = PercentRank( AvgTurnover, 100 );

AddTextColumn( FullName(), "Name", 40 );                          // column 3
AddColumn( Close, "Close", 1.2 );                                 // column 4
AddColumn( DistancePct, "% from MA200", 1.1, TrendColour );       // column 5
AddColumn( Momentum, "20-bar ROC %", 1.1, MomColour );            // column 6
AddColumn( AvgTurnover, "Turnover 50d", 1.0,
           colorDefault, colorLightBlue, -1, TurnoverBar );        // column 7

// The state is spelled out in words as well as in colour, so the table is
// still readable by someone who cannot distinguish the two hues, and still
// readable after export to a plain CSV file where colour does not survive.
StateList     = "Below trend, momentum negative\n" +
                "Below trend, momentum positive\n" +
                "Above trend, momentum negative\n" +
                "Above trend, momentum positive";
StateSelector = 1 * RisingMom + 2 * AboveTrend;
AddMultiTextColumn( StateSelector, StateList, "State", 32 );       // column 8

// SetSortColumns must come before the AddRankColumn that should honour it.
// Called with no prior sort, AddRankColumn only emits line numbers.
SetSortColumns( -6 );   // 20-bar ROC, descending
AddRankColumn();        // column 9: the rank stays put when you re-sort by hand

// Average, minimum, maximum and count (2 + 4 + 8 + 16), restricted to the
// three numeric columns where those statistics mean anything. The explicit
// 1.2 matters: left at its default the summary prints up to fifteen digits.
AddSummaryRows( 2 + 4 + 8 + 16, 1.2, 5, 6, 7 );

Download readable-table.afl69 lines

How it works

The colour arrays are built once, near the top, and given names. TrendColour and MomColour are ordinary variables holding one colour per bar; passing them into AddColumn is no different from passing any other array. Keeping them out of the AddColumn call keeps the call readable, and it means the same colour rule can be reused in two columns without being written twice.

The column order follows the order a reader’s eye moves: what is it, what does it cost, the two numbers being screened on, then context. The two screening columns are the ones carrying colour, because those are the ones where a fast yes/no judgement is wanted.

The state column is the piece worth copying. AddMultiTextColumn writes the trend and momentum combination out in words — “Above trend, momentum positive” — from a zero-based selector built as 1 * RisingMom + 2 * AboveTrend. The same information is already encoded in the colours of columns 5 and 6, and stating it twice is deliberate.

The sort and rank pair puts the strongest momentum at the top and records that ranking in a column that survives the reader re-sorting by turnover.

The summary row block asks for average, minimum, maximum and count on the three numeric columns where those statistics mean something, with an explicit 1.2 format.

Expected result

=Expected result

A table sorted with the strongest 20-bar momentum at the top, a rank column running 1, 2, 3 down the page, four summary rows above the first data row, and two coloured columns whose colours agree with the words in the State column. The turnover column carries a small in-cell bar.

Test it

1. Rank test. Click the “Close” header to re-sort the table by price. The rank column must keep its original values and become non-monotonic. If it renumbers itself, you are looking at a line-number column, which means AddRankColumn() ran before SetSortColumns().
2. Colour-agreement test. Find a row where “% from MA200” is negative. Its colour should be the “below trend” colour, and the State column should say so in words. A disagreement means the colour array and the text selector are testing different conditions — a real bug that colour alone would have hidden.
3. Summary sanity test. Compare the MIN and MAX summary values against the first and last rows after sorting by that column. They must match.

Common errors

✕Common mistake

The sort applies to the wrong column. Almost always an off-by-two: the two automatic columns were not counted. Write the column number in a comment next to every AddColumn call, as the example formula does, and the error stops happening.

Summary values with fifteen digits. format was left at its default of 0.

A “TOTAL” row over a price column. onlycols was omitted, so every column was summarised, including the ones where the sum is meaningless.

The rank column is just 1, 2, 3 in row order. AddRankColumn() was called before any SetSortColumns().

Colour is a repetition, never the message

This is the design rule that matters most, and it is not an AmiBroker rule.

Between four and five per cent of people cannot reliably distinguish red from green. A table whose only signal is “the number is green” is unreadable to them, and it is equally unreadable to everybody else the moment it is exported to CSV, printed, pasted into a document, or viewed on a projector that flattens the palette. Colour is also lost entirely when a colleague reads your candidate list on a phone in bright sunlight.

The rule that follows is simple: every distinction encoded in colour must also be readable as text or as a number in the same row. In the example formula the trend state is carried three times — by the sign of the “% from MA200” figure, by the colour of that cell, and by the words in the State column. That is not redundancy for its own sake; it is what makes the colour a convenience rather than a dependency.

Two smaller habits follow from the same principle:

• Prefer a strong colour used sparingly to a table where every cell is tinted. If everything is coloured, nothing is highlighted.
• Choose colours that differ in brightness as well as hue, so that they remain distinct in greyscale. colorDarkGreen against colorDarkRed is a weaker choice in this respect than a dark colour against a pale one.

!Important

The five-second test is the only design criterion that matters here. Show the table to somebody who did not write it and ask them, without explanation, which three rows they would look at first. If they cannot answer in five seconds, the problem is the table, not them.

Extension

Add a second rank column ranking by ATR percentage ascending, so that each row carries both “how strong” and “how quiet”. Then add a third numeric column holding the sum of the two ranks and sort by it. You have just built a crude composite score — and you have also created the first genuinely dangerous thing in this part, because a composite score invites you to treat its ordering as a prediction. It is not one. It is an ordering of the rows in this table today, by a formula you invented, and Part 13 is where the question of whether such an ordering means anything gets asked properly.

What changed in your understanding

Colour, sorting, ranking and summary rows are not decoration. Each is a documented function with argument rules that catch people out: colours are arrays reached positionally, column numbers are one-based and include two columns you did not write, AddRankColumn() must follow the sort it ranks by, and AddSummaryRows() puts its output at the top with fifteen digits of precision unless you tell it otherwise. And the table that results is only useful if it still says what it means when the colour is gone.

Check your understanding

Question 1. A formula calls AddRankColumn() and then SetSortColumns( -5 ). What does the rank column contain?

AddRankColumn();
SetSortColumns( -5 );

• Ranks by column 5, descending
• Ranks by column 5, ascending
• Row numbers, because no sort was established when the rank column was added
• Nothing; the call order causes an error

Show the answer and why

Answer: Row numbers, because no sort was established when the rank column was added

AddRankColumn ranks according to the sort already established by SetSortColumns. Called first, it degenerates into a line-number column — with no error and no warning, which is what makes it worth remembering.

Question 2. Which AddSummaryRows call adds an average and a count, formatted to two decimals, for columns 4 and 6 only?

• AddSummaryRows( 2, 16, 1.2, 4, 6 );
• AddSummaryRows( 18, 1.2, 4, 6 );
• AddSummaryRows( 1.2, 18, 4, 6 );
• AddSummaryRows( 2 + 16, 0, 4, 6 );

Show the answer and why

Answer: AddSummaryRows( 18, 1.2, 4, 6 );

flags is a single bit combination, so AVERAGE (2) plus COUNT (16) is 18 in one argument. The second argument is the format and the rest are the one-based column numbers. The last option would work but leaves format at 0, which prints up to fifteen digits.

Question 3. Your exploration colours a momentum column green when positive and red when negative, and shows nothing else about momentum. What is the strongest objection?

• Green and red are the wrong colours for momentum
• The information is unavailable to readers who cannot distinguish those hues, and is lost entirely on export
• ColorHSB would be more efficient
• Colour arrays slow the exploration down

Show the answer and why

Answer: The information is unavailable to readers who cannot distinguish those hues, and is lost entirely on export

Colour is a repetition of information, never its only carrier. The same table exported to CSV, printed, or read by someone with a common colour vision deficiency must still say what it means — which is why the worked example also spells the state out in words.

Check my answersClear