Description
The ACCOUNTBEGINNINGBALANCE function calculates the beginning or opening balance of one or more general ledger accounts, for a specific period - some organizations refer to this as the PeriodBeginningBalance or PeriodOpeningBalance for an account.
Syntax
ACCOUNTBEGINNINGBALANCE(Connection, Ledger, AccountClass, Account,
Subaccount, Branch, AsOf, IncludeUnposted)
Parameters
The ACCOUNTBEGINNINGBALANCE function uses the following parameters (see our articles on Filtering Velixo Functions and using arrays or 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). Excel for Windows allows the use of quotation marks around this parameter. Excel for Mac and Excel Online require that it be unquoted. |
Examples
Based on this data:
Example #1
=ACCOUNTBEGINNINGBALANCE("Demo", "ACTUAL", , $B6,,, $G$1)
Description: Calculates the beginning balance of posted transactions to General Ledger account # 10200 (as noted in cell B6) as of the beginning of Period 1 for the year 2019 (as noted in cell G1).
Result: 43,925,384
Example #2
=ACCOUNTBEGINNINGBALANCE("Demo", "ACTUAL", , $B9,,, C$4, 3)
Description: Calculates the beginning balance of combined posted and unposted transactions to General Ledger account # 10800 (as noted in cell B9) for as of January 1, 2019 (as noted in cell C4).
Result: 4,454,986
Limitations to filtering by date instead of financial period:
|
Applies to
GL Module