Description

The ACCOUNTENDINGBALANCE function calculates the ending balance of one or more general ledger account(s) as of a specific period - some organizations refer to this as the PeriodEndingBalance or PeriodClosingBalance for an account.

Syntax

ACCOUNTENDINGBALANCE(Connection, Ledger, AccountClass, Account,
Subaccount,  Branch, AsOf, IncludeUnposted)

Parameters

The ACCOUNTENDINGBALANCE function uses the following parameters (see our article on Filtering Velixo Function and using Excel arrays and cell ranges as parameters):

Parameter	Required/Optional	Description
Connection	Required	The name of the connection, as specified in the Connection Manager
Ledger	Required	Ledger to use in the calculation.
AccountClass	Optional, though required if Account is empty	The account class to use for the calculation.
Account	Optional, though required if AccountClass is empty	The general ledger account(s) to use for the calculation.
Subaccount	Optional	The general ledger subaccounts(s) to include.
Branch	Optional	The branch(es) to include.
AsOf	Required	The financial period, in MM-YYYY format or A cell reference to a date
IncludeUnposted	Optional	1 - Include posted transactions only (default) 2 - Include Unposted transactions only 3 - Include Posted and Unposted transactions* (note: Transactions with a status of "On Hold" are excluded. When Unposted transactions are included, the following two statuses are included: Balanced, Unposted). While Excel for Windows allows the use of quotation marks around this parameter, Excel for Mac and Excel Online require that it be unquoted.

Examples

Given this data:

Example #1

  =ACCOUNTENDINGBALANCE("Demo", "ACTUAL", , "10200",,, "01-2019")

Description: Calculates the ending balance of posted transactions to General Ledger account #10200 (as noted in cell B6) for the 7th period of the year 2019 (as noted in cell G1).

Result: 45,042,790

Example #2

  =ACCOUNTENDINGBALANCE("Demo", "ACTUAL", , "10700",,, $D$4, 3)

Description: Calculates the ending balance of the combined posted and unposted (as noted by the last parameter: 3) transactions to General Ledger account #10700 (as noted in cell B8) as of January 15, 2019 (as noted in cell D4).

Result: 4,421,510

Limitations to filtering by date instead of financial period:

This feature cannot be used with the Net Income account
Drilldown will show transactions for the entire financial period (you will then need to manually filter on the date column)
Balances are calculated based on the total from the nearest financial period, and then adjusted based on the filtered transaction date
Dates cannot be placed directly in the function; they must be cell references
Balances will not reflect transactions within the specified date range but which have been posted to a different financial period.

Applies to

GL Module