SI.CLOSINGBALANCE
Overview
The SI.CLOSINGBALANCE function returns the closing balance of a Sage Intacct GL account (at the end of the specified reporting period or date).
Syntax
=SI.CLOSINGBALANCE(ConnectionName, AccountGroup, Account, FromDate, ToDate, LocationId, Books, Dimensions, UserDefinedDimensions, IncludeChildDimensionBalances)Arguments
The SI. CLOSINGBALANCE function uses the following arguments:
Argument | Required/ Optional | Description |
| Required | The name of the connection as configured in the Connection Manager |
| Optional if | The name of the account group for which to return the aggregated closing balance. Computational Account Groups are not supported. |
| Optional if is specified | The code of the general ledger account, or multiple account codes defined by a Velixo range expression |
| Required | The start date (inclusive) in YYYY-MM-DD format or a cell reference to a date |
| Required | The end date (inclusive) in YYYY-MM-DD format or a cell reference to a date |
| Optional unless the company has multiple base currencies | The ID of the entity or location |
| Optional | The reporting book or global consolidation book to be used for retrieving the balance. One or more user-defined adjustment book IDs may be included
|
| Optional | Either:
The SI.DIMENSIONSfunction can make referencing dimensions much easier. |
| Optional | Either:
You can specify dimension values using ranges, wildcards, and other techniques. |
| Optional |
The  |
Examples
Revenue and Expense accounts
Consistent with Sage Intacct's Trial Balance and Account Balances reports, this function may always return the same results as the turnover for the specified date range.
To calculate the ending balance of a revenue or expense account as of a particular date, calculate the account turnover from the beginning of the applicable financial year until one day before the starting date of the report, and add that value to the account turnover of the period.
Example 1
=SI.CLOSINGBALANCE("Sage", , "10100", "2022-04-01", "2022-04-30", "100", "ACCRUAL", $C$6:$D$8)
Description
Returns the closing balance of the account "10100 - Cash" at the end of April 2022, for entity "100", in the Accrual book, and filtered down by dimensions specified in the C6:D8 range (for example, by Department and Employee).
Result
441,692.00
(this example uses cell references for some of the arguments)
Example 2 - Dimension Array
For examples of the use of a dimension array (available in version 2023.8 and higher), see : Example 2 - Dimension Array in the SI.OPENINGBALANCE function.
Example 3 - Empty Account Group
Assuming this configuration within Sage Intacct:
No accounts are assigned to the Accumulated Depreciation account group
=SI.CLOSINGBALANCE("Sage", "Accumulated Depreciation",,"2019-01-01","2019-12-31",
"100", "ACCRUAL")
Description
The various Velixo balance functions (SI.OPENINGBALANCE, SI.TURNOVER, SI.CLOSINGBALANCE, and SI.BUDGETTURNOVER) will return zero when there are no accounts assigned to the referenced account group.
Results
Example 4 - using Include Child Dimension Balances with Location
Below are balances where the Dimensions and UserDefinedDimensions are not used, but where a Location is specified with the IncludeChildDimensionBalances argument:
Example 5 - Include Child Dimension Balances or not
Below are balances for a specific Location, where Dimensions and UserDefinedDimensions are not used.
Customer 10007 is defined as a child account of customer 10001.
In row 6, when excluding the balance for customer 10001, you can choose whether or not to also exclude the balance for child account 10007.
Rows 3 and 7 use two techniques (blank and "*") to achieve the same result.