XGLP

XGLP - Amount of GL transaction for period

Description

This function returns the sum of all transaction amounts of the general ledger accounts between two specific dates.

Parameters

Accounts [Optional]

General Ledger Account Number or Name for which the sum of all

transactions are requested.

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

See the 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 control further 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.

Vendors [Optional]

Vendor name for which the sum of all transaction amounts is requested.

Customers [Optional]

Customer name for which the sum of all transactions amounts is requested.

Employees [Optional]

Employee name for which the sum of all transactions amounts is requested.

Inventory [Optional]

Inventory items number for which the sum of all transaction amounts is requested.

Projects [Optional]

Project name for which the sum of all transaction amounts is requested.

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.

Currency [Optional]

Currency code for which the balance is requested, for example, CAD or USD.

Connections [Optional]

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

Usage

All past and future transactions can be considered in this function. You just need to mention the Beginning of the first transaction date and the End of the last transaction date. The function XGLP will calculate the sum of all transaction amounts between those dates.

Examples

It is strongly recommended to use Excel cells address (ex. A1:B19) containing the information of the parameters requested in all XLGL functions, such as: accounts, dates, departments, vendors, customers, employees, etc. 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 parameters are manually written in the arguments function window.

Returns the sum of all transaction amounts made in account number 1020, excluding the opening balance of the first year

=XGLP("1020")