ACCOUNTSWITHHISTORY function
Returns an Excel array listing accounts for which there is account history during the specified period
Harry Lewis avatar
Written by Harry Lewis
Updated over a week ago

Velixo recommends the use of Microsoft 365 for this feature. Experimentally, Excel 2010 or higher may be used. See the Prerequisites for additional information.

Description

The ACCOUNTSWITHHISTORY function returns a list of accounts for which there is account history during the specified period(s)

Syntax

ACCOUNTSTWITHHISTORY(Connection, LedgerOrRange, BranchOrRange,AccountOrRange,SubaccountOrRange, StartPeriod, EndPeriod,IncludeInactive, IncludeUnposted)

The ACCOUNTSWITHHISTORY function uses the following parameters (see our article on Filtering Velixo functions):

Parameter

Required/Optional

Description

Connection

Required

The name of the connection as configured in the Connection Manager

LedgerOrRange

Optional

The ledger (or multiple ledgers in a delimited list) to be used in determining the list

BranchOrRange

Optional

The branch code (or range of branch codes) to be used in determining the list

AccountOrRange

Optional

The account code (or range of account codes) to be used in determining the list

SubaccountOrRange

Optional

The subaccount code (or range of subaccount codes) to be used in determining the list

StartPeriod

Required

The starting financial period, in MM-YYYY format

EndPeriod

Required

The ending financial period, in MM-YYYY format

IncludeInactive

Optional

TRUE/FALSE indicating whether or not to include inactive accounts and sub-account codes (default is TRUE)

IncludeUnposted

(requires Velixo v6.0.403 or higher)

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)

IncludeBranches

(requires Velixo v6.0.403 or higher)

Optional

TRUE/FALSE indicating whether or not to include the Branch IDs in the results.

Examples

Example #1

=ACCOUNTSWITHHISTORY("demo","Actual",,,,"12-2019","12-2019",1)

Description

Lists all accounts with non-zero posted history in the Actual ledger during the twelfth financial period of the 2019 fiscal year.

Result

acct_hx.png

Example #2

=ACCOUNTSWITHHISTORY("demo","ACTUAL",,"19000:23000",,"12-2019","12-2019",TRUE,1,TRUE)

Description

Lists accounts 19000 through 23000 (and their associated branch) with non-zero posted history in the Actual ledger during the twelfth financial period of the 2019 fiscal year.

Result

Results are sorted first by branch and then by account:

history_pair01.png

Applies to

GL Module

Did this answer your question?