List of all BQL Functions¶

This page documents all available BQL functions.

Simple functions¶

abs(x: decimal) → decimal¶
abs(x: inventory) → inventory
abs(x: position) → position: Absolute value.

account_sortkey(acc: str) → str¶: Get a string to sort accounts in order taking into account the types.

any_meta(key: str) → object¶: Get metadata from the posting or its parent transaction if not present.

bool(x: any) → bool¶: Convert to bool value.

close_date(acc: str) → date¶: Get the date of the close directive of the account.

commodity(x: amount) → str¶: Extract the currency from an Amount.

commodity_meta(commodity: str) → dict¶
commodity_meta(commodity: str, key: str) → object: Get the metadata dict of the commodity directive of the currency.

convert(...)¶

convert(amount_: amount, currency: str) -> amount
convert(amount_: amount, currency: str, date: date) -> amount
convert(pos: position, currency: str) -> amount
convert(pos: position, currency: str, date: date) -> amount

Coerce an amount to a particular currency.

convert(inv: inventory, currency: str) -> inventory
convert(inv: inventory, currency: str, date: date) -> inventory

Coerce an inventory to a particular currency.

cost(...)¶

cost(pos: position) -> amount

Get the cost of a position.

cost(inv: inventory) -> inventory

Get the cost of all positions in an inventory. Returns an: inventory with as many positions as there were currencies by which the positions in the original inventory were acquired.

currency(x: amount) → str¶: Extract the currency from an Amount.

currency_meta(commodity: str) → dict¶
currency_meta(commodity: str, key: str) → object: Get the metadata dict of the commodity directive of the currency.

date(...)¶

date(year: int, month: int, day: int) -> date

Construct a date with year, month, day arguments.

date(x: date) -> date
date(x: object) -> date
date(x: str) -> date

Convert the argument to a date. The argument should be: a string in the format YYYY-MM-DD. Date objects are passed unchanged. Objects are converted to None.

date_add(x: date, y: int) → date¶: Adds/subtracts number of days from the given date.

date_bin(stride: relativedelta, source: date, origin: date) → date¶

date_bin(stride: str, source: date, origin: date) → date

Bin a date into the specified stride aligned with the specified origin.

As an extension to the the SQL standard date_bin() function this function also accepts strides containing units of months and years.

Arguments:

stride: A string representing a time interval, e.g. ‘1 day’, ‘1 month’,
‘1 year’; Make sure to use single quotes in the first argument, as double-quoted strings are parsed as column names for backwards compatibility.

source: The date to bin; origin: The start of the binning interval. relativedelta: Relative time interval, as generated by interval().

date_diff(x: date, y: date) → int¶: Calculates the difference (in days) between two dates.

date_part(field: str, x: date) → int¶

Extract the specified field from a date.

Arguments:

field: Date part to extract, for example, ‘year’, ‘month’, ‘week’, ‘day’. Details below.
since the UNIX epoch.

x: The date to extract the field from.

Details:
The ‘field’ argument can be any of

‘weekday’/’dow’, ‘week’, ‘month’, ‘quarter’, ‘year’, ‘decade’, ‘century’, ‘millennium’, or

‘epoch’: returns the number of seconds since the UNIX epoch.

‘isoweekday’/’isodow’, ‘isoyear’: The ISO 8601 week number or year, which might differ from the conventional understanding around New Year’s eve.

Make sure to use single quotes for ‘field’, as double-quoted strings are parsed as column names for backwards compatibility reasons.

date_trunc(field: str, x: date) → date¶

Truncate a date to the specified precision. Example: date_trunc(‘month’,: date). Make sure to use single quotes in the first argument, as double-quoted strings are parsed as column names for backwards compatibility reasons.

day(x: date) → int¶: Extract the day from a date.

decimal(x: bool) → decimal¶
decimal(x: decimal) → decimal
decimal(x: int) → decimal
decimal(x: object) → decimal
decimal(x: str) → decimal: Convert the object to a decimal number.

empty(inventory_: inventory) → bool¶: Determine whether the inventiry is empty.

entry_meta(key: str) → object¶: Get some metadata key of the transaction.

filter_currency(inv: inventory, currency: str) → inventory¶
filter_currency(pos: position, currency: str) → position: Filter an inventory to just the specified currency.

findfirst(pattern: str, values: set) → str¶: Filter a string sequence by regular expression and return the first match.

getitem(d: dict, key: str)¶
getitem(d: dict, key: str, default: any): Get one item from a dict object if it exists, otherwise a default value.

getprice(base: str, quote: str) → decimal¶

getprice(base: str, quote: str, date: date) → decimal

Fetch a price. Arguments: Base currency, e.g. ‘EUR’; Commodity name (string);: Date: Price as of this date. Default: Latest price.

grep(pattern: str, string: str) → str¶: Match a regular expression against a string and return only the matched portion.

grepn(pattern: str, string: str, n: int) → str¶: Match a pattern with subgroups against a string and return the subgroup at the index.

has_account(context: str) → bool¶: True if the transaction has at least one posting matching the regular expression argument.

int(x: bool) → int¶
int(x: decimal) → int
int(x: int) → int
int(x: object) → int
int(x: str) → int: Convert the object to an integer number.

interval(x: str) → relativedelta¶

Construct a relative time interval.

Arguments:

x: A string of the form ‘N unit’ where unit is one of ‘day’, ‘month’, ‘year’
(Plural forms are also accepted). Examples: ‘1 month’, ‘-20 days’.

joinstr(values: set) → str¶: Join a sequence of strings to a single comma-separated string.

leaf(acc: str) → str¶: Get the name of the leaf subaccount.

length(x: list) → int¶
length(x: set) → int
length(x: str) → int: Compute the length of the argument. This works on sequences.

lower(string: str) → str¶: Convert string to lowercase.

maxwidth(x: str, n: int) → str¶

Convert the argument to a substring. This can be used to ensure: maximum width. This will insert ellipsis ([…]) if necessary.

meta(key: str) → object¶: Get some metadata key of the posting.

month(x: date) → int¶: Extract the month from a date.

neg(x: amount) → amount¶
neg(x: decimal) → decimal
neg(x: inventory) → inventory
neg(x: position) → position: Negative value.

number(x: amount) → decimal¶: Extract the number from an Amount.

only(currency: str, inventory_: inventory) → amount¶: Get one currency’s amount from the inventory.

open_date(acc: str) → date¶: Get the date of the open directive of the account.

open_meta(account: str) → dict¶

open_meta(account: str, key: str) → object

Get the metadata dict of the open directive of the account.: With one argument, returns all metadata as a dict object. With two arguments, returns the value of a specific metadata key.

parent(acc: str) → str¶: Get the parent name of the account.

parse_date(string: str) → date¶

parse_date(string: str, frmt: str) → date

Parse date from string (first argument). Without second argument,: the ‘dateutil’ library is used to parse the string, and can deal with several time stamp formats. The optional second argument specifies the format as in the ‘datetime’ library, for example: ‘%Y-%m-%d’ to parse ‘2022-01-20’.

possign(x: amount, account: str) → amount¶
possign(x: decimal, account: str) → decimal
possign(x: inventory, account: str) → inventory
possign(x: position, account: str) → position: Correct sign of an Amount based on the usual balance of associated account.

quarter(x: date) → str¶: Extract the quarter from a date.

repr(x: any) → str¶: Convert the argument to a string via repr().

root(acc: str) → str¶
root(acc: str, n: int) → str: Get the root name(s) of the account.

round(num: decimal) → decimal¶
round(num: decimal, digits: int) → decimal
round(num: int) → int
round(num: int, digits: int) → int: Round the argument.

safediv(x: decimal, y: decimal) → decimal¶
safediv(x: decimal, y: int) → decimal: A division operation that traps division by zero exceptions and outputs zero instead.

splitcomp(string: str, delim: str, index: int) → str¶: Split a string and extract one of its components.

str(x: any) → str¶: Convert any object to a string.

subst(pattern: str, repl: str, string: str) → str¶: Substitute leftmost non-overlapping occurrences of pattern by replacement.

substr(string: str, start: int, end: int) → str¶: Extract a substring of the argument.

today() → date¶: Today’s date

units(...)¶

units(inv: inventory) -> inventory

For all position in the inventory, strip the information about: at which cost they were acquired. The result is another inventory.

units(pos: position) -> amount

Get the number of units. Returns the amount, stripping cost.

upper(string: str) → str¶: Convert string to uppercase.

value(...)¶

value(inv: inventory) -> inventory
value(inv: inventory, date: date) -> inventory

Coerce an inventory to its market value.

value(pos: position) -> amount
value(pos: position, date: date) -> amount

Convert a position to its cost currency at the market value.

weekday(x: date) → str¶: Extract a 3-letter weekday from a date.

year(x: date) → int¶: Extract the year from a date.

yearmonth(x: date) → date¶: Extract the year and month from a date.

Aggregation functions¶

count(...)¶

count(a: *) -> int

Count the number of input rows.

count(a: any) -> int

Count the number of non-NULL occurrences of the argument.

first(a: any) → any¶: Keep the first of the values seen.

last(a: any) → any¶: Keep the last of the values seen.

max(a: any) → any¶: Compute the maximum of the values.

min(a: any) → any¶: Compute the minimum of the values.

sum(...)¶

sum(a: amount) -> inventory

Calculate the sum of the amount. The result is an Inventory.

sum(a: inventory) -> inventory

Calculate the sum of the inventories. The result is an Inventory.

sum(a: decimal) -> decimal
sum(a: int) -> int

Calculate the sum of the numerical argument.

sum(a: position) -> inventory

Calculate the sum of the position. The result is an Inventory.