Expression functions

All function names are case-insensitive. Functions are grouped by purpose below; each table sorts on a header click and filters as you type.

Every example below is runnable

Click any example to evaluate it in your browser — bxp's own expression engine, compiled to WebAssembly. Edit the expression in the panel that opens to try variations; Esc closes it.

Logic & conditionals

Branch, fall back to the first non-empty value, and test membership.

Function	Description
IF(cond, yes, no) IF(1 < 0, 'yes', 'no')	Short-circuit conditional. Returns yes if cond is truthy, else no.
CASE(expr, m1, r1, …, default) CASE('B', 'B', 'BUY', 'S', 'SELL', '?')	Multi-branch mapping. Compares expr against each m/r pair in order and returns the first matching r; returns the trailing default when nothing matches (or "" if no default is given). Equality matches the = operator (numeric when both sides parse as numbers, else byte-exact string). Only the selected result is evaluated. Collapses nested IF(IF(IF(…))) chains.
IFERROR(expr, fallback) IFERROR(YEAR('not-a-date'), '')	Return expr's value, or fallback if evaluating expr raises a data error (not-a-number, bad date, numeric overflow). fallback is evaluated only on error. Template errors — unknown function, wrong argument count, syntax — are NOT caught: IFERROR guards against messy data, not template mistakes.
COALESCE(a, b, ...) COALESCE('', 'fallback')	First non-empty argument (empty = whitespace-only string). Returns last argument verbatim as fallback.
NULLIF(value, sentinel) NULLIF('N/A', 'N/A')	Return "" when value equals sentinel, otherwise return value. Equality is numeric when both sides parse as numbers, otherwise byte-exact string compare — mirrors the = operator. Typical use: collapse sentinel values such as "-9999", "\N", "N/A" to empty.
IN(value, v1, v2, ...) IN('BUY', 'BUY', 'SELL')	Return "true" when value equals any of v1, v2, …. Equality is numeric when both sides parse as numbers, otherwise byte-exact string compare — mirrors the = operator. Variadic 2+ args.
ISEMPTY(x) ISEMPTY(' ')	Return "true" when x is empty or whitespace-only, else "false". The safe emptiness test: a bare x = '' wrongly matches '0' (which coerces to empty in numeric context), whereas ISEMPTY checks the trimmed string length.
IS_NUMERIC(x) IS_NUMERIC('1,234.56')	Whether x holds a number: "true" or "false", never an error. Deliberately stricter than arithmetic, which treats an empty cell and the junk tokens nan / inf as zero so one bad export row cannot break a whole column — IS_NUMERIC calls all three "false", which is how you find those rows instead of silently summing them as 0. Thousands grouping is accepted (1,234.56), and a field read through [Column] has already had csv_decimal_separator_in applied, so European input is judged after normalisation, not before.

Text

Trim, case-fold, slice, pad, search, and measure strings.

Function	Description
TRIM(f) TRIM(' hello ')	Strip leading and trailing whitespace from a string.
SPLIT_PART(s, delim, n) SPLIT_PART('AAPL.US', '.', 1)	Return the n-th part of s split by delim (1-based index). Returns "" when n exceeds the part count, when delim is empty, or when n ≤ 0. When delim is not found, n=1 returns the whole string and n>1 returns "".
CONTAINS(haystack, needle) CONTAINS('Apple Inc', 'Inc')	Returns "true" if haystack contains needle, else "false".
LEFT(s, n) LEFT('AAPL.US', 4)	Return the first n bytes of s. n is clamped to [0, len(s)]; negative n returns "".
RIGHT(s, n) RIGHT('AAPL.US', 2)	Return the last n bytes of s. n is clamped to [0, len(s)]; negative n returns "".
SUBSTR(s, start, length) SUBSTR('AAPL.US', 1, 4)	Return length bytes from s starting at 1-based position start. Returns "" when start is non-positive / past end of s, or when length is negative. length is clamped to the bytes remaining from start.
UPPER(s) UPPER('aapl')	Full-Unicode upper-case conversion: works across Latin, Greek, Cyrillic, etc. (café → CAFÉ, ß → SS); unicameral scripts (CJK, Arabic, Hebrew) pass through unchanged. Invalid UTF-8 bytes pass through verbatim.
LOWER(s) LOWER('AAPL')	Full-Unicode lower-case conversion: works across Latin, Greek, Cyrillic, etc. (CAFÉ → café, Я → я); unicameral scripts (CJK, Arabic, Hebrew) pass through unchanged. Invalid UTF-8 bytes pass through verbatim.
UNACCENT(s) UNACCENT('Café Crème')	Strip diacritics from Latin text (café → cafe, ÀÉÎ → AEI, ß → ss, ø → o). Latin-scope like Postgres unaccent: non-Latin letters keep their base script (Greek Ά → Α, not A) and CJK/Arabic pass through unchanged; ligatures are NOT folded. Invalid UTF-8 bytes pass through verbatim.
STARTS_WITH(s, prefix) STARTS_WITH('US123', 'US')	Return "true" when s begins with prefix (case-sensitive byte match), else "false". An empty prefix always matches.
ENDS_WITH(s, suffix) ENDS_WITH('AAPL.PR', '.PR')	Return "true" when s ends with suffix (case-sensitive byte match), else "false". An empty suffix always matches.
LEN(s) LEN('hello')	Byte length of s (UTF-8 byte count, not codepoint or grapheme count). Empty string → 0.
LPAD(s, len, pad) LPAD('42', 5, '0')	Left-pad s with the pad string (repeated, then clipped) until it is len bytes long. If s is already len or longer it is truncated to the first len bytes; an empty pad returns s unchanged. len is clamped to [0, 65535]. Byte-based (UTF-8 byte count).
RPAD(s, len, pad) RPAD('42', 5, ' ')	Right-pad s with the pad string (repeated, then clipped) until it is len bytes long. If s is already len or longer it is truncated to the first len bytes; an empty pad returns s unchanged. len is clamped to [0, 65535]. Byte-based (UTF-8 byte count).
POSITION(needle, haystack) POSITION('Inc', 'Apple Inc')	1-based byte position of the first occurrence of needle inside haystack, or 0 when not found. An empty needle returns 1. Byte-based (UTF-8 byte offset), case-sensitive.
PROPER(s) PROPER('apple inc')	Title-case s: upper-case the first letter of every word and lower-case the rest (apple inc → Apple Inc, o'brien → O'Brien). Words break on any non-letter (spaces, digits, punctuation), like Excel PROPER. Full-Unicode via the same case tables as UPPER/LOWER; invalid UTF-8 passes through.

Pattern matching

Linear-time (ReDoS-safe) regular-expression match and extract.

Function	Description
REGEX_MATCH(s, pattern) REGEX_MATCH('AAPL US Equity', '[A-Z]+')	Returns "true" if regular-expression pattern matches anywhere in s, else "false". pattern is a regex literal: anchors ^ $, classes [...], quantifiers * + ? {m,n}, groups, and alternation \| — linear-time (no backreferences or lookaround). Unicode-scalar mode is on, so . and class ranges span whole code points, but \d/\w/\s stay ASCII — match accented letters with an explicit class like [A-ZÁ-Ž]. Pay for the cheapest tool that does the job: CONTAINS for a literal substring, IN/REMAP for whole-value sets, regex only for a real pattern.
REGEX_EXTRACT(s, pattern) REGEX_EXTRACT('Qualified Dividend AAPL 100', '[A-Z]{2,}')	Returns the first part of s that regular-expression pattern matches, or "" if there is no match. When pattern has a capture group (...), the first group's text is returned; otherwise the whole match is returned — so group a repeated alternative as non-capturing (?:...) when you want the whole run, since a capturing group under a repeat yields only its last repetition. pattern is a regex literal (same syntax + Unicode notes as REGEX_MATCH): linear-time, no backreferences or lookaround, and accented letters need an explicit class like [A-ZÁ-Ž] (not \w). Use it to pull a ticker, code, or token a literal REPLACE/SPLIT_PART cannot isolate.

Lookup & mapping

Whole-value remap, substring replace, and pre_pass table lookups.

Function	Description
REMAP(s, 'name' | k, v, ...) REMAP('VOW.DE', 'VOW.DE', 'VOW.DE.XETRA')	Whole-value lookup: if s exactly equals a map key, return that key's value, else return s unchanged. Named form REMAP(s, 'mapname') resolves a maps registry entry; inline form REMAP(s, k1,v1, k2,v2, ...) gives the pairs directly. The whole-value sibling of REPLACE (which matches substrings) — use it to remap symbols, codes or enum values.
LOOKUP([name,] key, field) LOOKUP('AAPL', 'name')	Retrieve a value stored by a pre_pass table. 3-arg form LOOKUP(name, key, field) selects the named pre_pass block. 2-arg form LOOKUP(key, field) works only when exactly one pre_pass block is defined.
REPLACE(s, 'name' | from, to, ...) REPLACE('1 234,56', ' ', '', ',', '.')	Replace substrings in s. Named form REPLACE(s, 'mapname') applies a maps registry entry's pairs. Inline single-pair REPLACE(s, from, to) replaces every occurrence of from with to (case-sensitive byte match, so multi-byte UTF-8 needles work — this is substring replace, not char-by-char). Inline variadic REPLACE(s, from1, to1, from2, to2, ...) applies the pairs in one left-to-right pass: at each position the first pair (in declared order) whose from matches wins and the emitted to is not re-scanned, so one pass replaces several tokens at once without nesting. An empty from matches nothing. Whole-value sibling: REMAP.

Numbers & money

Exact fixed-point arithmetic, rounding, min/max, and price parsing.

Function	Description
ABS(f) ABS(-12.5)	Absolute numeric value.
ROUND(f, n) ROUND(3.14159, 2)	Round f to n decimal places (half away from zero, like Excel: ROUND(2.5,0)=3). n=0 rounds to the nearest integer; n<0 rounds to tens/hundreds/etc. (n=-2 → nearest 100); n>=12 is a no-op (12 is the fixed-point scale). n is clamped to ±30.
FLOOR(f) FLOOR(3.7)	Round f down to nearest integer.
CEILING(f) CEILING(3.2)	Round f up to nearest integer.
TRUNC(x [, n]) TRUNC(-3.999, 2)	Cut x off after n decimal places (default 0) toward zero, discarding the rest rather than rounding it. This is what separates it from its neighbours, and only on negatives: TRUNC(-3.999) is -3 where FLOOR(-3.999) is -4, and TRUNC(-3.999, 2) is -3.99 where ROUND(-3.999, 2) is -4. Use it where a value must never grow in magnitude — a payout truncated to whole cents, a tax base that is never rounded up. n<0 cuts at tens/hundreds (n=-2 → multiples of 100, still toward zero); n>=12 is a no-op (12 is the fixed-point scale); n is clamped to ±30.
SIGN(x) SIGN(-12.5)	Direction of x, as a number: -1 below zero, 0 at zero, 1 above. Written out rather than compared, so a rule reads by direction instead of by threshold — SIGN([Amount]) = -1 where the source encodes a sale as a negative amount. An empty or non-numeric cell coerces to 0 here exactly as it does in arithmetic, so a 0 answer does not mean the cell held a zero; ask ISEMPTY or IS_NUMERIC when that difference matters.
MROUND(x, m) MROUND(12.37, 0.05)	Round x to the nearest multiple of m, halves away from zero (MROUND(0.075, 0.05) is 0.1). Where ROUND snaps to a decimal place, this snaps to a step of your choosing: an exchange quoting in five-cent ticks, a lot size of 25, a fee billed per started hour. The sign of m is ignored — only the size of the step matters — and m = 0 has no multiples to snap to, so it answers "". Exact on the fixed-point core, unlike dividing and multiplying back through a float.
POWER(b, n) POWER(1.05, 10)	b raised to the whole-number power n, computed exactly (POWER(1.1, 2) is 1.21, not 1.2100000001) and then rounded into the 12-digit fixed-point scale. n must be a whole number ≥ 0 and ≤ 1024 — a fractional or negative n returns "" rather than an approximation, because neither is exact on the decimal core (for a square root use SQRT; for a negative power divide: 1 / POWER(b, n)). A result too large for the numeric range is a loud NumberOverflow that IFERROR can catch.
SQRT(x) SQRT(2)	Square root of x, correctly rounded to the 12-digit fixed-point scale (SQRT(2) = 1.414213562373). Exact when the root is exact (SQRT(6.25) = 2.5). A negative x returns "" — the root is undefined over the reals, and bxp has no imaginary type — so guard with IF([x] < 0, …) if a negative input is meaningful in your data.
RAND(n) RAND(8)	A string of exactly n random digits (each position 0–9, except the first which is 1–9 so a leading zero can't be dropped by a downstream numeric import). Use it for synthetic IDs; n is clamped to [1, 65]. Cryptographically seeded — not for security tokens.
PRICE_VALUE(f) PRICE_VALUE('$1,234.56')	Strip currency symbol or code from a price string, return the numeric part (e.g. "$88744.27" → "88744.27", "€24.00" → "24.00", "24.00 CZK" → "24.00").
PRICE_CURRENCY(f) PRICE_CURRENCY('$1,234.56')	Extract currency code from a price string (e.g. "EUR", "USD").
GREATEST(a, b, ...) GREATEST(3, 7, 5)	Largest numeric value among arguments. Per-row maximum (not aggregation across rows). Arguments are coerced to numbers; empty string coerces to 0, non-numeric strings raise an error.
LEAST(a, b, ...) LEAST(3, 7, 5)	Smallest numeric value among arguments. Per-row minimum (not aggregation across rows). Arguments are coerced to numbers; empty string coerces to 0, non-numeric strings raise an error.
MOD(a, b) MOD(7, 3)	Remainder of a divided by b, with the sign of the dividend a (truncated division, like SQL/C %: MOD(-7, 3) = -1). MOD(a, 0) returns "" (mirrors the / operator's silent divide-by-zero). Exact over the fixed-point decimal core.

Dates & time

Reformat, shift, diff, and decompose dates — business-day aware. Every function here reads the same shapes: YYYY-MM-DD, YYYY-MM-DD hh:mm:ss, the T-separated ISO variant, and an ISO tail (fractional seconds, Z, ±HH:MM) which is accepted and ignored. A date function given a timestamp ignores the time half; a time function given a bare date reads midnight. Anything else is an error rather than a value — a cell the reader cannot account for never answers like a real one.

Function	Description
NOW() NOW()	Current UTC datetime as ISO 8601 string (YYYY-MM-DDTHH:MM:SSZ).
DATE_CONVERT(f, from, to) DATE_CONVERT('31.12.2024', 'DD.MM.YYYY', 'YYYY-MM-DD')	Reformat a date/time string. Format tokens: YYYY (year), MM/M (month), MMM/MMMM (month name), DD/D (day), hh/h (hour), mm/m (minute), ss/s (second), [literal] (literal characters), [*] (wildcard).
TO_UTC(ts, from) TO_UTC('2024-03-15T14:23:01+02:00', 'YYYY-MM-DD[T]hhssZZ')	Normalise an offset-bearing timestamp to UTC. from is a datefmt format including the ZZ offset token (+HH:MM/-HH:MM) or a literal Z; the parsed offset is subtracted, yielding YYYY-MM-DD hh:mm:ss in UTC. Needs no timezone database — the offset is read from the string itself.
TZ_OFFSET(datetime, zone) TZ_OFFSET('2024-07-15 12:00:00', 'Europe/Prague')	UTC offset (+HH:MM/-HH:MM) of IANA zone at local wall-clock datetime (YYYY-MM-DD[ hh:mm:ss]), DST-aware. Append it to a naive local timestamp to make it ISO-8601 tz-aware. Unknown zone → "". Within the one-hour DST-transition window the input is read as local time, so the result can be off by the offset.
TZ_CONVERT(ts, from_zone, to_zone) TZ_CONVERT('2024-07-15 12:00:00', 'America/New_York', 'Europe/Prague')	Convert wall-clock ts (YYYY-MM-DD[ hh:mm:ss]) from from_zone to to_zone, returning YYYY-MM-DD hh:mm:ss. Each zone is an IANA id (Europe/Prague), a fixed offset (+02:00), or UTC. Full DST-aware IANA conversion via the bundled tzdata. Unknown zone → "".
IS_DST(datetime, zone) IS_DST('2024-07-15 12:00:00', 'Europe/Prague')	"true" when daylight-saving time is in effect in IANA zone at local wall-clock datetime (YYYY-MM-DD[ hh:mm:ss]), else "false". An unknown or fixed-offset zone → "false".
DATEADD(d, n) DATEADD('2024-01-31', 7)	Add n calendar days to date d. Negative n subtracts. Returns YYYY-MM-DD. For business-day arithmetic (skipping weekends) use WORKDAY().
DATEDIFF(d1, d2) DATEDIFF('2024-01-01', '2024-12-31')	Calendar days from d2 to d1: positive when d1 is later. A timestamp argument is read for its date; the time half is ignored.
WORKDAY(d, n) WORKDAY('2024-01-01', 10)	Add n business days to date d, skipping Saturdays and Sundays. Negative n subtracts, and n = 0 returns d unchanged. Correct for T+2 settlement math; does NOT account for exchange holidays.
YEAR(d) YEAR('2024-03-15')	Year component of date d as a number.
MONTH(d) MONTH('2024-03-15')	Month component of date d as a number, 1-12.
DAY(d) DAY('2024-03-15')	Day-of-month component of date d as a number, 1-31.
WEEKDAY(d) WEEKDAY('2024-03-15')	ISO day-of-week for date d: Monday=1 … Sunday=7. Useful for weekend-trade detection: WEEKDAY([Date]) > 5.
QUARTER(d) QUARTER('2024-08-15')	Calendar quarter of date d as a number, 1-4 (Jan-Mar = 1). Quarters are calendar-aligned; a fiscal year starting in another month needs its own arithmetic, e.g. an April start is QUARTER(DATEADD([Date], -90)).
WEEKNUM(d) WEEKNUM('2024-03-15')	ISO 8601 week number of date d, 1-53. Weeks start on Monday and week 1 is the one containing the first Thursday of the year, so the turn of the year crosses over: 2021-01-01 is week 53 (of 2020) and 2024-12-30 is week 1 (of 2025). The number alone is therefore not sortable across years — pair it with the week's own year, YEAR(DATEADD([Date], 4 - WEEKDAY([Date]))).
DATE_TRUNC(unit, d) DATE_TRUNC('month', '2024-08-15')	First day of the period containing date d, as YYYY-MM-DD. unit is one of year, quarter, month, week or day, case-insensitive; week starts on Monday, ISO like WEEKDAY. A timestamp is read for its date and the clock half is dropped — that is what day is for. Use it to bucket rows by period: bxp never sums across rows, so the truncated date is the label the destination groups on. An unrecognised unit is a loud error rather than a passthrough, because the unit comes from the template, not from the data.
HOUR(t) HOUR('2024-03-15 14:23:01')	Hour of datetime t as a number, 0-23 on a 24-hour clock. A bare date reads as midnight, so a date-only column answers 0; any other layout has to go through DATE_CONVERT first.
MINUTE(t) MINUTE('2024-03-15 14:23:01')	Minute of datetime t as a number, 0-59.
SECOND(t) SECOND('2024-03-15 14:23:01')	Second of datetime t as a number, 0-59.
EOMONTH(d) EOMONTH('2024-02-10')	Last calendar day of the month containing date d, as YYYY-MM-DD. Useful for snapping coupon/dividend dates and month-end reporting.
NTH_DOW(year, month, weekday, n) NTH_DOW(2024, 3, 7, -1)	Date (YYYY-MM-DD) of the n-th weekday (ISO Mon=1 … Sun=7) in year/month. Positive n counts from the start (1 = first); negative counts from the end (-1 = last). Returns "" when the occurrence doesn't exist or an argument is out of range. Handy for DST boundaries — EU summer time is NTH_DOW(YEAR(d), 3, 7, -1) (last Sunday of March) to NTH_DOW(YEAR(d), 10, 7, -1) (last Sunday of October).
IS_DATE(d [, format]) IS_DATE('31.12.2024', 'DD.MM.YYYY')	Whether d is a readable date: "true" or "false", never an error. With one argument it tests the shapes every date and time function reads, so it answers "will they work on this row". With a format it answers the same question for DATE_CONVERT, using the same tokens and the same tolerance for 4-letter month abbreviations. An empty value is "false", not an error, so a blank cell reads as "no date" rather than a bad one.

Row & source context

Values drawn from the current row's position and its source file.

Function	Description
FIELDS(n) FIELDS(2)	Field value by 1-based column index. n must be a positive integer — use this when the column header is unknown or unstable; use the [ColumnName] syntax to look up by header name.
FILENAME() FILENAME()	Input file stem — the file name with its directory and the matched file_pattern_in suffix removed (the same stem used for output naming). Exports often encode account, source or period in the name, so e.g. SPLIT_PART(FILENAME(), '_', 3) extracts a field from it. Empty during stateless evaluation (no source file).
RECORD_NUM() RECORD_NUM()	1-based input record number of the current row within the file (the first data row is 1). Use it for synthetic IDs, dedup keys, or skip-first-N logic. 0 during stateless evaluation and inside the pre_pass scan.
SHEET_NAME() SHEET_NAME()	For xlsx-derived input, the configured xlsx_sheet.name the row came from; "" for native CSV/JSON input and during stateless evaluation.