Exploration: Filter, AddColumn and AddTextColumn

An exploration is the only place in AmiBroker where you design the output. Everything else — charts, scans, backtest reports — hands you a format. Here you decide what the columns are, what is in them, how they are formatted, what colour they are and how they are sorted. That freedom is why explorations are the workhorse of every screening and research workflow in the rest of this course, and it is why getting the mechanics exactly right now saves hours later.

Two things do all the work: the predefined variable Filter, and the AddColumn() family.

Filter accepts bars, not symbols

The idea behind an exploration is deliberately simple. One variable, Filter, controls which symbols and quotes are accepted. Where you assign it a true value, that bar is displayed in the report.

Read that once more, because the words “symbols and quotes” are the ones that matter. Filter is an ordinary AFL array expression, evaluated once per bar like everything else in the language. A row appears for every bar where it is true, on every symbol the run examined.

Filter = Close > 50, evaluated bar by bar for one symbol

Three rows from one symbol in one week. Multiply by two thousand symbols and fifteen years and the arithmetic becomes alarming.

Bar	Mon	Tue	Wed	Thu	Fri
Close	48.20	49.90	51.40	52.10	50.60
Filter	0	0	1	1	1
Rows produced	—	—	1	1	1

Three rows from one symbol in one week. Multiply by two thousand symbols and fifteen years and the arithmetic becomes alarming. Prices in this diagram are invented for the illustration. They are not market data and nothing should be inferred from them.

The canonical examples in AmiBroker’s own documentation are all of this shape:

Fragment — not a complete formula

Filter = Close > 50;                        // every bar closing above 50
Filter = Volume > 5000000;                  // every heavily traded bar
Filter = Volume > 1.3 * EMA( Volume, 40 );  // every volume-spike bar
Filter = Close > MA( Close, 20 );           // every bar above the 20-bar average
Filter = 1;                                 // everything, on every symbol

!Important

Filter = 1; with Range set to All quotations is a request for the entire database as a table. On two thousand symbols with fifteen years of daily bars that is somewhere around seven million rows. It is a legitimate thing to ask for — it is how the documentation shows you exporting a whole database to CSV — but it is almost never what someone building a screen intended.

If Filter is never assigned at all, the exploration produces no rows. There is no implicit default and no warning.

Getting one row per symbol

A screener wants one row per symbol: the current state of each instrument. There are exactly two idioms for that, and they are not equivalent.

Idiom one — narrow the Range. Set Range to 1 recent bar(s) and write Filter = yourCondition;. Only one bar per symbol is in range, so at most one row per symbol can appear. This is the approach AmiBroker’s exploration tutorial recommends for checking just the most recent quote.

Idiom two — narrow the Filter. Keep Range wide (All quotations) and write Filter = Status( "lastbarinrange" ) AND yourCondition;. Status("lastbarinrange") returns an array that is 1 on the last bar of the analysis range and 0 everywhere else, so the conjunction reports exactly the final bar of each symbol.

Why prefer the second? Because it separates what the run computes from what the run reports. With a wide range, every indicator has its full history available, which matters when you later want to say “and it has been above its two-hundred-bar average for thirty consecutive bars” — a statement that needs those thirty bars to exist. It also makes the formula honest about its own requirements rather than depending on a combo box.

For pure AFL, AmiBroker works out the lookback its own functions need and supplies extra history automatically even under a narrow range, plus at least thirty spare bars. So idiom one is not usually wrong. It is just less explicit, and it stops being reliable the moment a formula uses a script or an external DLL, where SetBarsRequired() becomes your problem.

✦Tip

Status("lastbarinrange") is not the same as Status("lastbarintest"). The second one reflects the settings of the last backtest or optimization and is deliberately immune to scans and explorations run in between. Using it in a screen produces results that depend on what you did an hour ago.

AddColumn, in full

The exploration tutorial page shows a five-argument version of AddColumn. The function reference page shows the complete one, and it is the one to learn:

Fragment — not a complete formula

AddColumn( array, name, format = 1.2, textColor = colorDefault,
           bkgndColor = colorDefault, width = -1, barchart = Null );

Argument	Default	What it does
array	—	The values to display, one per bar. Passing Null gives an empty column.
name	—	The column caption.
format	1.2	Number formatting. See below — this is the argument people get wrong.
textColor	colorDefault	Foreground colour. May be an array, giving one colour per row.
bkgndColor	colorDefault	Background colour. May also be an array.
width	-1	Column width in pixels.
barchart	Null	0 to 100: draws an in-cell bar of that percentage width, using bkgndColor.

Columns appear in the order the AddColumn calls execute.

✕Common mistake

Because the formula runs once per symbol, an AddColumn() call placed inside an if that is true for some symbols and false for others produces an inconsistent set of columns. Always call AddColumn unconditionally, at the top level of the formula. If you want a column to be blank for some symbols, control the array you pass, not whether the call happens.

The format argument is a number, not a string

format is a number written as integer.fraction, in the same style as WriteVal(), and it does not mean what its appearance suggests.

• The fractional part is the number of decimal digits. 1.2 gives two decimals — that is the default. 1.4 gives four. 1.0 gives none.
• The integer part is a space-padding width. 6.0 gives no decimal digits but pads the formatted number with spaces up to six characters.

So in the default 1.2, the leading 1 is a padding width of one — effectively no padding — and not “one digit before the decimal point”. Once you know that, 77 in a format slot stops looking like a typo: it is a minimum field width of seventy-seven characters, which is how AmiBroker’s own example formats a long company name.

Three predefined constants replace the number entirely:

Constant	Effect
formatDateTime	Date and time formatted according to your Windows system settings.
formatDateTimeISO	Date and time as YYYY-MM-DD HH:MM:SS.
formatChar	Prints the single ASCII character whose code is in the array.

formatChar is stranger than it looks and genuinely useful. AddColumn( IIf( Buy, 66, 83 ), "Signal", formatChar ); prints B or S, because 66 and 83 are the ASCII codes for those letters. It exists so that an exported exploration can be a machine-readable signal file, which is the subject of a later lesson in this part.

∴Research note

AmiBroker 7.00’s release notes record that AddColumn gained further formats for displaying only the date or only the time portion of a DateTime value, and that StrToDateTime now accepts years up to 2037.

The constant names for those date-only and time-only formats are not published in the online function reference, which visibly lags the 7.00 release. This course therefore does not name them: check the help file bundled with your own installation, which is the authority for anything specific to 7.00. Everything above works on every version this course supports.

✦Tip

Prefer formatDateTimeISO over formatDateTime in anything you might export or share. The system-settings version writes 03/04/2026, which means the third of April to one reader and the fourth of March to another. ISO ordering is unambiguous and sorts correctly as text.

AddTextColumn, and the trap inside it

Fragment — not a complete formula

AddTextColumn( string, name, format = 1.2, textColor = colorDefault,
               bkgndColor = colorDefault, width = -1 );

The signature looks like AddColumn minus the bar chart, and the format slot here acts as a minimum field width rather than a decimal count. But the first argument is the important difference, and AmiBroker’s author stated it plainly in the function reference: AddTextColumn takes a single string, so it can only display text that does not vary bar by bar.

That makes it right for anything that is a property of the symbol rather than of the bar:

Fragment — not a complete formula

AddTextColumn( FullName(), "Name", 40 );
AddTextColumn( GroupID( 1 ), "Group", 20 );
AddTextColumn( SectorID( 1 ), "Sector", 24 );

And wrong for anything time-varying. If you need per-bar text, the function is AddMultiTextColumn(), which chooses from a newline-separated list according to a numeric selector array:

Fragment — not a complete formula

StateList     = "No signal\nBuy\nSell\nBuy and Sell";
StateSelector = 1 * Buy + 2 * Sell;   // 0, 1, 2 or 3
AddMultiTextColumn( StateSelector, StateList, "Which signal" );

The selector is zero-based: value 0 picks the first item. Note also that the caption is the third argument here, not the second, because the text list occupies second place. That inconsistency with AddColumn is a reliable source of confusing output.

The default columns

Every exploration is given two columns you did not ask for: Ticker and Date/Time, prepended automatically. They are usually what you want, and they matter more than they look, because column numbering elsewhere counts them. When a later lesson tells SetSortColumns() to sort by column 5, that 5 includes these two.

Switch them off with:

Fragment — not a complete formula

SetOption( "NoDefaultColumns", True );

Do it when you are generating a file for another program and you need the column positions to be exactly what you specified. Do not do it casually, because it renumbers every column and silently breaks any SetSortColumns() or AddSummaryRows() numbers you had already written.

A worked screen

Complete runnable AFL

exploration-columns.afl

// exploration-columns.afl
// Part 12 - Exploration: Filter, AddColumn and AddTextColumn
//
// One row per symbol, drawn from the last bar of the analysis range, carrying
// the handful of numbers worth seeing before you decide whether a chart is
// worth opening.
//
// Assumptions:
//   - Daily bars.
//   - The analysis Range is wide enough for the 200-bar average to have warmed
//     up. Range = "All quotations" is the safe choice; Status("lastbarinrange")
//     is what reduces the output back to one row per symbol.
//   - Symbols with fewer than 200 bars of history produce Null in the trend
//     columns and are therefore dropped by the liquidity test, because Null
//     compared with a number is not true.

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

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

// Filter is a per-bar truth value, not a per-symbol one. Without the
// lastbarinrange term this formula would emit one row for every bar of every
// symbol that clears the turnover floor.
Filter = Status( "lastbarinrange" ) AND AvgTurnover > MinTurnover;

AddTextColumn( FullName(), "Name", 40 );
AddColumn( Close, "Close", 1.2 );
AddColumn( Volume, "Volume", 1.0 );
AddColumn( AvgTurnover, "Turnover 50d", 1.0 );
AddColumn( DistancePct, "% from MA200", 1.1 );
AddColumn( RSI( 14 ), "RSI(14)", 1.0 );

// The automatic Date/Time column follows your Windows settings, which makes it
// ambiguous the moment you send the file to somebody in another country. This
// second column states the same instant unambiguously.
AddColumn( DateTime(), "Bar (ISO)", formatDateTimeISO );

Download exploration-columns.afl41 lines

How it works

The derived series section computes what will be displayed: turnover and its average, the two-hundred-bar trend line, and the distance from price to that line expressed as a percentage. Percentages rather than points, so that rows for a five-unit instrument and a five-hundred-unit instrument can be compared at a glance.

The Filter line does two jobs at once. Status("lastbarinrange") reduces the output to one row per symbol; AvgTurnover > MinTurnover decides which symbols get a row at all. Written this way the two decisions stay visibly separate, which matters when you are debugging.

The column block is ordered by how you would actually read it: identity first, then price, then the two measures you are screening on, then context. Formats are chosen per column — two decimals for a price, none at all for a volume figure, one decimal for a percentage where the second decimal is noise.

The final column repeats the date in ISO form. That looks redundant next to the automatic Date/Time column, and it is — until the file is exported and opened by somebody whose regional settings order the day and month differently from yours.

Expected result

=Expected result

One row per symbol that clears the turnover floor, showing that symbol’s most recent bar. The row count should be well below your universe size — if it equals your universe size, MinTurnover is not doing anything and needs raising; if it is zero, it is doing far too much and needs lowering.

Test it

1. Row-count test. Change Filter to Filter = Status( "lastbarinrange" ); with no other condition and re-run. The row count is now your universe size as the run actually saw it. Compare that with the number of symbols you believe are in the universe. A gap means Apply to is not what you think, or symbols are being dropped for lack of history.
2. Spot check. Pick any row. Open that symbol’s chart, add a 200-bar moving average, and confirm the “% from MA200” figure by eye. If the sign is wrong, the subtraction in DistancePct is the wrong way round.
3. Warm-up test. Set Range to “1 recent bar(s)” and re-run. The values should be identical, because AmiBroker supplies the lookback that pure AFL needs. If they are not, you have found something worth understanding before you build anything on it.

Common errors

✕Common mistake

Millions of rows, and AmiBroker apparently hanging. Filter was assigned a condition with no bar restriction while Range was set to All quotations. Add Status("lastbarinrange"), or narrow the Range. The run is not stuck; it is doing exactly what you asked.

Every numeric column shows two decimals when you wanted five. The format argument was omitted, so it defaulted to 1.2. Pass 1.5.

A text column shows the same value on every row of a symbol, and you expected it to change. That is AddTextColumn behaving as documented. Use AddMultiTextColumn().

The wrong column got sorted or summarised. You counted your own columns from 1 and forgot the two automatic ones.

Extension

Add a column that reports how many consecutive bars the symbol has been above its two-hundred-bar average. BarsSince() from Part 9 gives you the raw material, and the result is far more informative than the Boolean it replaces — a symbol two days into a trend and one two hundred days into a trend are not the same candidate, and a yes/no column cannot tell you which is which.

What changed in your understanding

Filter is not a symbol selector; it is a bar selector that happens to be evaluated on every symbol. The two idioms for turning that into one row per symbol are different tools with different consequences. AddColumn has seven arguments, its format number is a padding width and a decimal count rather than anything to do with significant figures, and AddTextColumn is constant per symbol by design. And two columns exist that you did not write, which every column number in the rest of this part has to account for.

Check your understanding

Question 1. What does AddColumn( Close, "Close", 6.0 ); display?

AddColumn( Close, "Close", 6.0 );

• Six digits before the decimal point
• No decimal digits, space-padded to six characters
• Six decimal digits
• A column six pixels wide

Show the answer and why

Answer: No decimal digits, space-padded to six characters

The fractional part of the format number is the decimal-digit count and the integer part is a space-padding width. So 6.0 means no decimals, padded to six characters. Column width in pixels is a separate, sixth argument.

Question 2. A formula contains Filter = Close > MA( Close, 50 ); and Range is set to All quotations, over 500 symbols with 10 years of daily data. Roughly how many rows should you expect?

• About 500 — one per symbol
• About 2,500 — one per symbol per year
• Hundreds of thousands — one per qualifying bar per symbol
• None, because Filter needs Status("lastbarinrange")

Show the answer and why

Answer: Hundreds of thousands — one per qualifying bar per symbol

Filter is evaluated per bar. Roughly half of about 2,500 bars per symbol will be above a 50-bar average, times 500 symbols, is on the order of 600,000 rows. Nothing is broken; the formula asked for exactly this.

Question 3. Which of these belong in AddTextColumn rather than AddMultiTextColumn? Select all that apply.

• FullName()
• The words "Buy" or "Sell" depending on the bar
• SectorID(1)
• A trend label that changes as price crosses its average

Show the answer and why

Answer: FullName(), SectorID(1)

AddTextColumn takes a single string and therefore cannot vary bar by bar. FullName() and SectorID(1) are properties of the symbol. Anything that changes with the bar needs AddMultiTextColumn and a zero-based numeric selector.

Question 4. You add SetOption( "NoDefaultColumns", True ); to a working exploration that already used SetSortColumns( 4 ). What happens?

• Nothing changes; column numbers are relative to your own columns
• The sort now applies to a different column, two positions earlier in your list
• SetSortColumns is ignored when default columns are off
• The exploration returns no rows

Show the answer and why

Answer: The sort now applies to a different column, two positions earlier in your list

Removing the Ticker and Date/Time columns renumbers everything. Column 4 used to be your second added column; now it is your fourth. The same shift breaks the onlycols numbers passed to AddSummaryRows.

Check my answersClear