InsightXL: Function Reference

The tables below provide an overview of all financial functions available with InsightXL. You can find these functions in the Insert Function wizard in Excel under the Acumatica category.

Functions for Financial Entities: Lists and Descriptions

Name Description
COMPANYDESCR (Tenant, Company)

Returns the company description for the specified company.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company <single value or array>: The company to retrieve the description for.
BRANCHLIST (Tenant, Company)

Returns the list of branches for the specified company.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company <single value or array>: The company to retrieve the description for. If omitted, defaults to ?.
BRANCHDESCR (Tenant, Branch)

Returns the branch description for the specified branch.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Branch <single value or array>: The branch to retrieve the description for.
ACCCLASSDESCR (Tenant, AccountClass)

Returns the account class description for the specified account class.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • AccountClass <single value or array>: The account class to retrieve the description for.
ACCCLASSFORACC (Tenant, Account)

Returns the account class for the specified account.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Account <single value or array>: The account to retrieve the account class for.
ACCCLASSLIST (Tenant, AccountClass)

Returns the list of GL account classes within the specified range.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • AccountClass <single value, list, range, array or wildcard>: The account class's value, list, range, or wildcard to retrieve the list of account classes for. If omitted, defaults to ?.
ACCLIST (Tenant, Account, AccountClass, IncludeInactive)

Returns the list of GL account numbers that belong to the specified range of accounts or are assigned to the specified account classes.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Account <single value, list, range, array or wildcard>: The account's value, list, range, or wildcard to retrieve the accounts for. If omitted, defaults to ?.
  • AccountClass <single value, list, range, array or wildcard>: The account class's value, list, range, or wildcard to retrieve the accounts for. If omitted, defaults to ?.

An array may be used for only one of the arguments.
ACCDESCR (Tenant, Account)

Returns the account description for the specified account.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Account <single value or array>: The account to retrieve the description for.
SUBACCLIST (Tenant, SubAccount, IncludeInactive)

Returns the list of GL subaccounts within the specified range.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Subaccount <single value, list, range, array or wildcard>: The subaccount's value, list, range, or wildcard to retrieve the list of the subaccounts for. If omitted, defaults to ?.
  • IncludeInactive <TRUE/FALSE>: TRUE if inactive subaccounts are included; otherwise, FALSE (default).
SUBACCDESCR (Tenant, SubAccount)

Returns the subaccount description for the specified subaccount.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • SubAccount <single value or array>: The subaccount to retrieve the description for.

Functions for Financial Entities: Expands

Name Description
EXPANDCOMPANY (Tenant, Company, AddDescr)

Returns the array of companies and their descriptions.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company <single value, list, range, or wildcard>: The company's value, list, range, or wildcard to retrieve the array of companies for. If omitted, defaults to ?.
  • AddDescr <TRUE/FALSE>: TRUE to include the description of each company; otherwise, FALSE (default).
EXPANDBRANCH (Tenant, Company, Branch, AddDescr) Returns the array of branches assigned to the specified company and their descriptions.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company <single value or array>: The company to retrieve the array of branches for. If omitted, defaults to ?.
  • Branch <single value, list, range, or wildcard>: The branch's value, list, range, or wildcard to retrieve the array of branches for.
  • AddDescr <TRUE/FALSE>: TRUE to include the description of each branch; otherwise, FALSE (default).
EXPANDLEDGER (Tenant, Ledger, AddDescr) Returns the array of ledgers and their descriptions.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Ledger <single value, list, range, or wildcard>: The ledger's value, list, range, or wildcard to retrieve the array of ledgers for. If omitted, defaults to ?.
  • AddDescr <TRUE/FALSE>: TRUE to include the description of each ledger; FALSE (default).
EXPANDACCCLASS (Tenant, AccountClass, AddDescr) Returns the array of GL account classes within the specified range and their descriptions.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • AccountClass <single value, list, range, or wildcard>: The account class's value, list, range, or wildcard to retrieve the array of account classes for. If omitted, defaults to ?.
  • AddDescr <TRUE/FALSE>: TRUE to include the description of each account class; otherwise, FALSE (default).
EXPANDACC (Tenant, Account, AccountClass, IncludeInactive, AddDescr) Returns the array of GL account numbers within the specified range of accounts or account classes, along with their descriptions.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Account <single value, list, range, or wildcard>: The account's value, list, range, or wildcard to retrieve the array of accounts for. If omitted, defaults to ?.
  • AccountClass <single value, list, range, or wildcard>: The account class's value, list, range, or wildcard to retrieve the array of accounts for. If omitted, defaults to ?.
  • IncludeInactive <TRUE/FALSE>: TRUE if inactive accounts are included; otherwise, FALSE (default).
  • AddDescr <TRUE/FALSE>: TRUE to include the description of each account; otherwise, FALSE (default).
EXPANDSUBACC (Tenant, SubAccount, IncludeInactive, AddDescr) Returns the array of GL subaccounts within the specified range and their descriptions.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • SubAccount <single value, list, range, or wildcard>: The subaccount's value, list, range, or wildcard to retrieve the array of subaccounts for. If omitted, defaults to ?.
  • IncludeInactive <TRUE/FALSE>: TRUE if including inactive subaccounts; otherwise, FALSE (default).
  • AddDescr <TRUE/FALSE>: TRUE to include the description of each subaccount; otherwise, FALSE (default).
EXPANDFINPERIOD (Tenant, Company, FinPeriod, UseMasterFinCalendar) Returns the array of financial periods within the specified range.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve the array of financial periods for. If omitted, defaults to ?.
  • FinPeriod <single value, list, range, or wildcard>: The financial period's value, range, or wildcard to retrieve the array of financial periods for.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.

Functions for Amount Types

Name Description
BEGBALANCE (Tenant, Company, Branch, Ledger, AccountClass, Account, SubAccount, FinPeriod, UseAccountCurrency, UseMasterFinCalendar)

Returns the beginning balance in the base currency or the account currency of the specified GL accounts for the specified branch and financial period.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve data for.
  • Branch <single value, list, range, or array>: The branch or branches to retrieve data for.
  • Ledger <single value or array>: The ledger to retrieve data for.
  • AccountClass <single value or array>: The account class or classes to retrieve data for.
  • Account <single value, list, range, array or wildcard>: The account or accounts to retrieve data for.
  • SubAccount <single value, list, range, array or wildcard>: The subaccount or subaccounts to retrieve data for.
  • FinPeriod <single value, array or range>: The financial period or periods to retrieve data for.
  • UseAccountCurrency <TRUE/FALSE>: TRUE to return the amount in the account's currency; FALSE (default) to return the amount in the company's base currency.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.

An array may be used for only one of the arguments at a time.
ENDBALANCE (Tenant, Company, Branch, Ledger, AccountClass, Account, SubAccount, FinPeriod, UseAccountCurrency, UseMasterFinCalendar)

Returns the ending balance in the base currency or the account currency of the specified GL accounts for a given branch and financial period.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve data for.
  • Branch <single value, list, range, or array>: The branch or branches to retrieve data for.
  • Ledger <single value or array>: The ledger to retrieve data for.
  • AccountClass <single value or array>: The account class or classes to retrieve data for.
  • Account <single value, list, range, array or wildcard>: The account or accounts to retrieve data for.
  • SubAccount <single value, list, range, array or wildcard>: The subaccount or subaccounts to retrieve data for.
  • FinPeriod <single value, array or range>: The financial period or periods to retrieve data for.
  • UseAccountCurrency <TRUE/FALSE>: TRUE to return the amount in the account's currency; FALSE (default) to return the amount in the company's base currency.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.

An array may be used for only one of the arguments at a time.
DEBIT (Tenant, Company, Branch, Ledger, AccountClass, Account, SubAccount, FinPeriod, UseAccountCurrency, UseMasterFinCalendar)

Returns the debit balance in the base currency or the account currency of the specified GL accounts for a given branch and financial period.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve data for.
  • Branch <single value, list, range, or array>: The branch or branches to retrieve data for.
  • Ledger <single value or array>: The ledger to retrieve data for.
  • AccountClass <single value or array>: The account class or classes to retrieve data for.
  • Account <single value, list, range, array or wildcard>: The account or accounts to retrieve data for.
  • SubAccount <single value, list, range, array or wildcard>: The subaccount or subaccounts to retrieve data for.
  • FinPeriod <single value, array or range>: The financial period or periods to retrieve data for.
  • UseAccountCurrency <TRUE/FALSE>: TRUE to return the amount in the account's currency; FALSE (default) to return the amount in the company's base currency.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.

An array may be used for only one of the arguments at a time.
CREDIT (Tenant, Company, Branch, Ledger, AccountClass, Account, SubAccount, FinPeriod, UseAccountCurrency, UseMasterFinCalendar)

Returns the credit balance in the base currency or the account currency of the specified GL accounts for a given branch and financial period.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve data for.
  • Branch <single value, list, range, or array>: The branch or branches to retrieve data for.
  • Ledger <single value or array>: The ledger to retrieve data for.
  • AccountClass <single value or array>: The account class or classes to retrieve data for.
  • Account <single value, list, range, array or wildcard>: The account or accounts to retrieve data for.
  • SubAccount <single value, list, range, array or wildcard>: The subaccount or subaccounts to retrieve data for.
  • FinPeriod <single value, array or range>: The financial period or periods to retrieve data for.
  • UseAccountCurrency <TRUE/FALSE>: TRUE to return the amount in the account's currency; FALSE (default) to return the amount in the company's base currency.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.

An array may be used for only one of the arguments at a time.
TURNOVER (Tenant, Company, Branch, Ledger, AccountClass, Account, SubAccount, FinPeriod, UseAccountCurrency, UseMasterFinCalendar)

Returns the turnover amount in the base currency or the account currency for the specified GL accounts for a given branch and financial period.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve data for.
  • Branch <single value, list, range, or array>: The branch or branches to retrieve data for.
  • Ledger <single value or array>: The ledger to retrieve data for.
  • AccountClass <single value or array>: The account class or classes to retrieve data for.
  • Account <single value, list, range, array or wildcard>: The account or accounts to retrieve data for.
  • SubAccount <single value, list, range, array or wildcard>: The subaccount or subaccounts to retrieve data for.
  • FinPeriod <single value, array or range>: The financial period or periods to retrieve data for.
  • UseAccountCurrency <TRUE/FALSE>: TRUE to return the amount in the account's currency; FALSE (default) to return the amount in the company's base currency.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.

An array may be used for only one of the arguments at a time.

Functions for Financial Periods

Name Description
FINPERIODLIST (Tenant, Company, FinPeriod, UseMasterFinCalendar)

Returns the list of financial periods within the specified range.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve the list of financial periods for. If omitted, the default company is used.
  • FinPeriod <single value, list, range, array, or wildcard>: The financial period's value, range, or wildcard to retrieve the list of financial periods for.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar should be used; otherwise, FALSE.
FINPERIODSTART (Tenant, Company, FinPeriod, UseMasterFinCalendar)

Returns the Excel date value for the start date of the specified financial period.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve the financial period's start date for. If omitted, the default company is used.
  • FinPeriod <single value or array>: The financial period to retrieve the start date for.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.
FINPERIODEND (Tenant, Company, FinPeriod, UseMasterFinCalendar)

Returns the Excel date value for the end date of the specified financial period.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve the financial period's end date for. If omitted, the default company is used.
  • FinPeriod <single value or array>: The financial period to retrieve the end date for.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.
FINPERIODBYDATE (Tenant, Company, Date, UseMasterFinCalendar)

Returns the financial period for the specified date, if it exists in the company's calendar.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve the financial period for. If omitted, the default company is used.
  • Date <single value or array>: The date to retrieve the financial period for.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.
FINPERIODBYPERIOD (Tenant, Company, Year, FinPeriod, UseMasterFinCalendar)

Returns the matching financial period for the specified year and period number.

Arguments:

  • Tenant: The tenant to retrieve data for.
  • Company: The company to retrieve the financial period for. If omitted, the default company is used.
  • Year <single value or array>: The year to retrieve the financial period for.
  • FinPeriod <single value or array>: The financial period number to retrieve the financial period for.
  • UseMasterFinCalendar <TRUE/FALSE>: TRUE (default) if the master financial calendar must be used; otherwise, FALSE.
An array may be used for only one of the arguments at a time.

Examples:

  • FINPERIODBYPERIOD(,, "2025", 1, FALSE) returns 01-2025
  • FINPERIODBYPERIOD(,, "2025", 0, FALSE) returns 12-2024
  • FINPERIODBYPERIOD(,, "2025", -1, FALSE) returns 11-2024
  • FINPERIODBYPERIOD(,, "2025", 13, FALSE) returns 01-2026

Argument Syntax

The following table shows syntax examples for argument types used in functions.

Argument Type or Parameter Examples of Syntax
<single value>

A single text or numeric value entered directly: "CAPITAL"

A cell reference containing a single value: A1
<list>

A list of comma-separated values: "PRODRETAIL, PRODWHOLE, SERVWEST"

A list of values in an Excel range: A1:A8
<range>

All accounts whose numbers fall between two values: "10000:40000"

All accounts except the specified values: "*,-22000,-24000"
<wildcard>

A pattern where ? matches any single character: "7??00"
<array> A reference to all values in a spilled array, starting at the specified cell : A53#. The function is applied to each value in turn, returning a matching array of results.
Date

A date value in MM/DD/YYYY or DD Month YYYY format, regardless of regional settings:

  • 12/25/2024
  • 25 December 2024
FinPeriod

A financial period in MM-YYYY format: "10-2024"
FinPeriod <range>

A range spanning multiple financial periods, from start to end: ("10-2024:10-2025")
MasterFinCalendar

A Boolean value indicating whether to use the master financial calendar: TRUE if the master financial calendar must be used; otherwise, FALSE
Absolute cell reference

A reference to a fixed cell that does not change when formulas are moved or copied: $A$1
Absolute column reference

A reference where the column is fixed but the row can change when formulas are moved or copied: $A1