The SI.CLOSINGBALANCE function returns the closing balance of a GL account (at the end of the specified reporting period or date).
Syntax
SI.CLOSINGBALANCE(
Connection,
AccountGroup,
AccountCode,
StartDate,
EndDate,
LocationId,
ReportingBook,
Dimensions,
UserDefinedDimensions)
Parameters
The SI.CLOSINGBALANCE function uses the following parameters:
Parameter | Required/Optional | Description |
Connection | Required | The name of the connection as configured in the Connection Manager |
AccountGroup | Optional if AccountCode is specified | The name of the account group for which to return the aggregated closing balance |
AccountCode | Optional if AccountGroup is specified | The code of the general ledger account, or multiple account codes defined by a Velixo range expression |
StartDate | Required | The start date (inclusive) in YYYY-MM-DD format or A cell reference to a date |
EndDate | Required | The end date (inclusive) in YYYY-MM-DD format or A cell reference to a date |
LocationId | Optional unless the company has multiple base currencies | The ID of the entity or location |
ReportingBook | Required | The reporting book ID ("ACCRUAL", "CASH", or a user-defined book) |
Optional | A two-column Excel range, where the first column contains the name of a predefined analytical dimension (e.g. department, customer, project etc.), and the second column contains the semicolon-separated list of values or that dimension. For user-defined dimensions, use the UserDefinedDimensions argument. | |
UserDefinedDimensions | Optional | A two-column Excel range, where the first column contains the name of a user-defined analytical dimension, and the second column contains the respective value (or a semicolon-separated list of values) for that dimension. For predefined analytical dimensions like customer, project etc., use the Dimensions argument. |
Example
=SI.CLOSINGBALANCE(
"Demo",
,
"40000",
"2022-05-01",
"2022-05-31",
"300",
"ACCRUAL",
$C$6:$D$8)
Description
Returns the closing balance of the account "40000 - Revenue" at the end of May 2022, for entity "300", filtered down by dimensions specified in the C6:D8 range (for example, by Department and Employee).
Result
$124 025 522.12
Applies to
⚠️ Velixo NX only
ERP: Sage Intacct