Retrieves account balances directly from QuickBooks Online
The Flash Reports Account Balance Function operates in a similar manner to any other Excel worksheet function. When executed it retrieves live finance data from the QuickBooks Online company that you are connected to.
To retrieve values from QuickBooks Online populate the function parameters as follows and then click "Create Function".
| Company | Enter the name of the company that you want to retrieve balances for. |
| Report Period | Select a named report period. Alternatively select specific dates below. |
| Start Date | The start date for the balance. |
| End Date | The end date for the balance. |
| Actual |
Select "Actual" or select one of your listed Budgets. By default the accounting method (Cash or Accrual), for all functions in the workbook, is determined based on the QuickBooks company configuration. Or it can be overridden via a Flash Reports setting. Two special values are available which will override the accounting method for this function.
|
| Account |
The Account to retrieve balances for. There are a number of possible options for this parameter:
A detailed explanation of each option is provided below. |
| Display Columns By | Tick the Set Column Value checkbox if you want to retrieve balances by additional dimensions e.g. Class or Department. |
| Column Value |
Select a value e.g. Class or Department value. Optionally enter multiple values separated by the account delimiter (defaults to a pipe |). Do not enter spaces around the delimiter. |
| Filters |
Optionally filter the results by one or more dimension values. Note the following limitations:
|
Account Parameter Options
1. Single Account Name
You can simply enter the account name that you would like to retrieve the balance for e.g. "Design Income".
2. Multiple Account Names
Enter multiple values separated by the account delimiter (defaults to a pipe |) e.g. "Design Income|Services". Do not enter spaces around the delimiter. The balances will be added together. If you have selected a different Account Delimiter on the settings screen, enter this character instead of a pipe.
3. Full Account Name
You can use the full account name which includes the parent name(s) e.g. "Landscaping Services:Job Materials:Plants and Soil".
4. Account Number
If your QuickBooks company is configured to use account numbers, you can enter just the account number for the account e.g. "50200". However please note that while QuickBooks allows you to use characters other than numbers in the account number field, currently Flash Reports only supports using numbers for this parameter.
5. Special Account Values
Flash Reports adds a number of special accounts to the list which each have a specific purpose.
Total Accounts: these can be used to obtain a summary balance in a single Account Balance function rather than needing to include all the detailed accounts and add them up in Excel.
- Flash:Total Income
- Flash:Total Cost of Goods Sold
- Flash:Total Expenses
- Flash:Total Other Income
- Flash:Total Other Expenses
- Flash:Total Bank Accounts
- Flash:Total Accounts Receivable
- Flash:Total Current Assets
- Flash:Total Other Current Assets
- Flash:Total Fixed Assets
- Flash:Total Other Assets
- Flash:Total Accounts Payable
- Flash:Total Credit Cards
- Flash:Total Current Liabilities
- Flash:Total Other Current Liabilities
- Flash:Total Long Term Liabilities
Control Totals: these values can be used to verify that your reports include all the accounts that have values. Compare the applicable report control total to the calculated value at the bottom of the report. If the balances differ you know that you are missing an account in the report.
- Flash:Control Total Profit And Loss
- Flash:Control Total Balance Sheet
- Flash:Control Total Cash Flow
Other: these accounts are required to provide a placeholder for a specific balance on a report.
- Net Income - for the Balance Sheet only. Stores the Net Income balance for the current year.
- Cash at beginning of period - for the Cash Flow only. Stores the opening cash position.