List of all BQL Functions ========================== This page documents all available BQL functions. .. Disambiguate functions and types by pretending they are in different modules (we are documenting BQL with the Sphinx Python domain). We put functions in the virtual module "fun" (thus functions must be explicitly linked like :func:`~fun.myfunction`). Types have no module such that they are auto-linked by the `.. function:` directives. .. currentmodule:: fun Simple functions ---------------- .. function:: abs(x: decimal) -> decimal abs(x: inventory) -> inventory abs(x: position) -> position Absolute value. .. function:: account_sortkey(acc: str) -> str Get a string to sort accounts in order taking into account the types. .. function:: any_meta(key: str) -> object Get metadata from the posting or its parent transaction if not present. .. function:: bool(x: any) -> bool Convert to bool value. .. function:: close_date(acc: str) -> date Get the date of the close directive of the account. .. function:: commodity(x: amount) -> str Extract the currency from an Amount. .. function:: commodity_meta(commodity: str) -> dict commodity_meta(commodity: str, key: str) -> object Get the metadata dict of the commodity directive of the currency. .. function:: 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. .. function:: 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. .. function:: currency(x: amount) -> str Extract the currency from an Amount. .. function:: currency_meta(commodity: str) -> dict currency_meta(commodity: str, key: str) -> object Get the metadata dict of the commodity directive of the currency. .. function:: 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. .. function:: date_add(x: date, y: int) -> date Adds/subtracts number of days from the given date. .. function:: 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(). .. function:: date_diff(x: date, y: date) -> int Calculates the difference (in days) between two dates. .. function:: 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. .. function:: 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. .. function:: day(x: date) -> int Extract the day from a date. .. function:: 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. .. function:: empty(inventory_: inventory) -> bool Determine whether the inventiry is empty. .. function:: entry_meta(key: str) -> object Get some metadata key of the transaction. .. function:: filter_currency(inv: inventory, currency: str) -> inventory filter_currency(pos: position, currency: str) -> position Filter an inventory to just the specified currency. .. function:: findfirst(pattern: str, values: set) -> str Filter a string sequence by regular expression and return the first match. .. function:: 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. .. function:: 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. .. function:: grep(pattern: str, string: str) -> str Match a regular expression against a string and return only the matched portion. .. function:: grepn(pattern: str, string: str, n: int) -> str Match a pattern with subgroups against a string and return the subgroup at the index. .. function:: has_account(context: str) -> bool True if the transaction has at least one posting matching the regular expression argument. .. function:: 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. .. function:: 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'. .. function:: joinstr(values: set) -> str Join a sequence of strings to a single comma-separated string. .. function:: leaf(acc: str) -> str Get the name of the leaf subaccount. .. function:: length(x: list) -> int length(x: set) -> int length(x: str) -> int Compute the length of the argument. This works on sequences. .. function:: lower(string: str) -> str Convert string to lowercase. .. function:: 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. .. function:: meta(key: str) -> object Get some metadata key of the posting. .. function:: month(x: date) -> int Extract the month from a date. .. function:: neg(x: amount) -> amount neg(x: decimal) -> decimal neg(x: inventory) -> inventory neg(x: position) -> position Negative value. .. function:: number(x: amount) -> decimal Extract the number from an Amount. .. function:: only(currency: str, inventory_: inventory) -> amount Get one currency's amount from the inventory. .. function:: open_date(acc: str) -> date Get the date of the open directive of the account. .. function:: 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. .. function:: parent(acc: str) -> str Get the parent name of the account. .. function:: 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'. .. function:: 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. .. function:: quarter(x: date) -> str Extract the quarter from a date. .. function:: repr(x: any) -> str Convert the argument to a string via repr(). .. function:: root(acc: str) -> str root(acc: str, n: int) -> str Get the root name(s) of the account. .. function:: round(num: decimal) -> decimal round(num: decimal, digits: int) -> decimal round(num: int) -> int round(num: int, digits: int) -> int Round the argument. .. function:: 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. .. function:: splitcomp(string: str, delim: str, index: int) -> str Split a string and extract one of its components. .. function:: str(x: any) -> str Convert any object to a string. .. function:: subst(pattern: str, repl: str, string: str) -> str Substitute leftmost non-overlapping occurrences of pattern by replacement. .. function:: substr(string: str, start: int, end: int) -> str Extract a substring of the argument. .. function:: today() -> date Today's date .. function:: 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. .. function:: upper(string: str) -> str Convert string to uppercase. .. function:: 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. .. function:: weekday(x: date) -> str Extract a 3-letter weekday from a date. .. function:: year(x: date) -> int Extract the year from a date. .. function:: yearmonth(x: date) -> date Extract the year and month from a date. Aggregation functions --------------------- .. function:: count(...) :: count(a: *) -> int Count the number of input rows. :: count(a: any) -> int Count the number of non-NULL occurrences of the argument. .. function:: first(a: any) -> any Keep the first of the values seen. .. function:: last(a: any) -> any Keep the last of the values seen. .. function:: max(a: any) -> any Compute the maximum of the values. .. function:: min(a: any) -> any Compute the minimum of the values. .. function:: 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.