3305 Main Street Ste 019 Vancouver, WA 98663

DBFinancial Function for Vista

Description

This function can return a variety of financial values that are used in the construction of financial statements. This includes the ability to report balances as of a specific period or the activity for specific periods.

Syntax

= DBFinancial ( ConnectionName , Year , Period , Entity , Group , Account , AmountType , BudgetType )

Example

=DBFinancial("Vista",2007,10,1,"01%","41000")

Returns the balance of account 41000 for period 10 of fiscal year 2007 under company 1 and consolidates all locations (part 3) under division 01 (part 2).

Arguments

Argument Description
ConnectionName When working with Vista financial data, this should be "Vista".
Year The four-digit year of the period being reported. This should correspond to the year in which the fiscal year ends.
Period The period number (1 to 12) of the period being reported.
Entity
For Vista, this argument corresponds to the company number. This may be one of the following types of values:
Single Company A single company number. When supplying a single company number, the value may be supplied as a number or as text.
List of Companies A comma-separated list of company numbers. This produces an aggregate result that would be the same as evaluating a DBFinancial function for each company in the list and adding together their results. Example: "1,2"
Range of Cells Produces the same result as List of Companies except that the list is built automatically based on the values in the cells that make up the range. Example: $G$3:$G$10
Group
For Vista, this argument corresponds Parts 2 through 6 of the GL Account Number. This can be used to identify specific accounts (by identifying the value for each part) or it can be used to consolidate. This may be one of the following types of values:
Blank A blank value indicates that only accounts that have no additional parts (in part 2 through 6) should be included.
Distinct Value Only accounts where the trailing portion of the account number (parts 2 through 6) matches the supplied value. This is useful when reporting for a specific profit/cost center.
Pattern Accounts where the trailing portion of the account number (parts 2 through 6) match the pattern will be included and consolidated. Wildcards may be used within the pattern. A percent symbol (“%”) indicates a multiple-character wildcard. An underscore symbol (“_”) indicates a single character wildcard. Examples –
  • Using two parts (Division and Location), to consolidate all Locations under Division 01, the following pattern could be used: "01__"
  • Consolidating all parts: "%"
Range of Cells When a range of cells is provided, the function will be evaluated for each value in the range and the sum of the results will be returned. Example: $H$3:$H$10
Account
Specifies the account or set of accounts to be reported. This may be one of the following types of values:
Single Account Identifies a specific Base Account (Part 1).
Filter A filter is a text expression that identifies a set of accounts to be combined, or can identify a specific account based on its individual parts. A filter can consist of one or more conditions which compare an account field to a value. Multiple conditions can be used with AND and OR operators. A common use of a filter would be to identify a range of base accounts as follows – "[BaseAccount] >= '40000' AND [BaseAccount] <= '40100'" Other fields that can be used with the filter include –
  • [GLAcct]
  • [Part1]
  • [Part2]
  • [Part3]
  • [Part4]
  • [Part5]
  • [Part6]
Range of Cells When a range of cells is provided, the function will be evaluated for each value in the range and the sum of the results will be returned. The value of each cell in the range can be a single account or a filter. Example: $A$30:$B$30
AmountType Optional - Identifies the type of financial value to be returned. This may be one of the following values listed below. If no value is supplied, the default value "Balance" will be used. This value is not case-sensitive.
  • "Balance"
  • "PeriodActivity"
  • "DebitActivity
  • "CreditActivity"
  • "YTDActivity"
  • "YTDDebitActivity"
  • "YTDCreditActivity"
  • "PeriodBudget"
  • "YTDBudget"
BudgetType Optional – When the AmountType specifies “PeriodBudget” or “YTDBudget”, this argument must be supplied in order to identify the type of budget to be returned.