XUSFinancial - Total amount of GL for a given period

Description

 This function is only available for Sage 50 US Edition

This function returns the sum of all transactions amounts for selected General Ledger account(s) between two specific dates. If the beginning of the period is omitted, it will return the balance of the account(s).

Parameters

Accounts [Optional]

General Ledger Account Number or Name for which the sum of all transactions is requested.

If this parameter remains empty, the Current Earnings value will be reported.

See Accounts Parameter for more information on how you can use this parameter.

Beginning [Optional]

Beginning date of the period for which the amount is requested. If omitted, it will return the balance of the account(s).

When entering dates in Excel, use the format for your computer's regional settings, or use the ISO format (YYYY-MM-DD).

End [Optional]

Ending date of the period for which the amount is requested. If omitted, it will use the latest transaction date.

When entering dates in Excel, use the format for your computer's regional settings, or use the ISO format (YYYY-MM-DD).

Departments [Optional]

Ending part of the General Ledger Account Number for which the sum of all transactions is requested. Use this parameter to further control the accounts to be used by looking at the end of the account number.

If this parameter remains empty, only the Accounts parameter will be used.

See Departments Parameter for more information on how you can use this parameter.

Debit/Credit [Optional]

Use Debit to return the positive sum value of a debiting account or Credit to return the positive sum value of a crediting account.

Use As Debit to return the amount as a Debiting amount or As Credit to return the amount as a Crediting amount.

Connections [Optional]

Sage 50 Connection to query. If left empty, the default connection will be used.

Usage

You can control the accounts to be used by specifying the Accounts and Departments parameters. The Accounts criteria will be applied to the beginning of the account number, while the Departments criteria will be applied to the end of the account number.

XUSFinancial can be used to return the balance of an account(s) or the sum of all transactions for a given period. To get the balance of an account, simply omit the Beginning parameter. To get the sum of all transactions for a given period, specify the Beginning and End parameters.

Examples

It is strongly recommended to use Excel cells reference (ex. A1:B19) containing the parameters requested in all XLGL functions. This practice adds flexibility to your worksheet and will ensure the coherence of your results. Moreover, the use of quotation marks (") is essential when the text and date parameters are specified directly in the function.

Return the amount of all accounts starting with 4, between April 1st and June 30th, 2024:

=XUSFinancial("4", "2024-04-01", "2024-06-30")

Return the balance of all accounts starting with 401 and ending with -01, as of June 30th, 2024:

=XUSFinancial("401",, "2024-06-30", "-01")

Remarks

XUSFinancial uses Sage 50 buffered amount for the period set in your company (usually month, but can also be 13-period). When using starting and ending dates, they must match the period set in your company. If the period is not set, the function use the closest date.