With Invantive Control, you can easily consolidate and report on general ledger accounts and multiple Exact Online companies. This can be done using hundreds of additional Excel formulas for Exact, but also via mass downloads from more than 1,000 linked Exact APIs. This consolidation and reporting takes place in Excel, but in real time with the most up-to-date financial data from multiple administrations.
For EUR 59 per month, you get Exact consolidation for up to 100 administrations for 2 users, including Power BI integration. In this article, you will learn more about the new possibilities for creating consolidated figures using Excel functions.
The new features for Exact consolidation are available from the Invantive Control Excel add-in. version 20.1.325.
The easiest way to experience the possibilities for consolidating financial or operational data with Power BI is to view the Power BI examples.
An example of consolidated cash flow with Exact and Excel can be found at Excel cash flow prognosis for Exact Online (consolidated across companies).
Consolidation of multiple Exact administrations
With the Excel add-in Invantive Control, you could already consolidate numbers from multiple Exact administrations per general ledger account or general ledger accounting group. This consolidation works for all chart of accounts, both according to traditional chart of accounts according to Brouwer, the Dutch standard RGS system and also according to foreign systems such as in Belgium. You can also specify cost centres and cost objects during consolidation. Excel formulas such as I_EOL_BAL_PDE_CLOSE are used for this, as described in, for example, Cumulative Profit/Loss Excel formulas per Financial Period and The most popular Exact Online Excel add-in formulas.
The aforementioned I_EOL_BAL_PDE_CLOSE Excel formula retrieves balances from Exact, for example from a general ledger account at the end of a period for the specified administration, including any opening balance. The I stands for ‘Invantive’ and the abbreviation EOL for ‘Exact Online’. All Exact Online formulas therefore start with I_EOL_. There are different Excel formulas for NMBRS, Visma Net Financials and others.
The unique number of the Exact Online administration is always the first value of the Excel formula. You can see this ‘DivisionCode’ in the URL, for example, when you switch between companies in Exact Online. After switching companies, it appears in the URL after
_Division_=
List of General Ledger Accounts
The new version adds to these financial consolidation options the standard option of using Excel formulas on the financial balances to specify not only one specific general ledger account, but also multiple general ledger accounts and even series. For example, you could already use I_EOL_BAL_PDE_CLOSE to retrieve the balances for 2020 from general ledger account 8000 with:
I_EOL_BAL_PDE_CLOSE(; 2020; 1; 12; “8000”)
However, from now on, you can also retrieve the figures from general ledger accounts 8001 and 8015 from the standard selected Exact Online company using:
I_EOL_BAL_PDE_CLOSE(; 2020; 1; 12; “8001,8015”)
In practice, this looks as follows:
exact online multiple general ledger accounts in excel formula.xlsx (20,4 KB)
Range of general ledger accounts with ‘-’
You can retrieve a continuous series of general ledger accounts from the chart of accounts of the relevant Exact Online company using ‘-’, whereby the left or right side may be omitted.
You can obtain the balances of all accounts 8000 to 8015 using:
I_EOL_BAL_PDE_CLOSE(; 2020; 1; 12; “8000-8015”)
General ledger accounts with wildcards “*” and ‘_’
You can retrieve a series of general ledger accounts from the chart of accounts of the relevant Exact Online company using ‘wildcards’: the asterisk ‘*’ for any series of characters and/or the ‘_’ for a specific character.
You can obtain the balances of all revenue accounts using:
I_EOL_BAL_PDE_CLOSE(; 2020; 1; 12; “8*”)
or if you have a 4-digit account scheme with:
I_EOL_BAL_PDE_CLOSE(; 2020; 1; 12; “8___”)
If you wish to exclude a specific ledger account, you can deduct its balance from it again:
I_EOL_BAL_PDE_CLOSE(; 2020; 1; 12; “8*”) - I_EOL_BAL_PDE_CLOSE(; 2020; 1; 12; “8999”)
Combining general ledger account filters
The balance data is retrieved from the company in real time as soon as you calculate the formula. You can also combine multiple series with wildcards, as in:
I_EOL_BAL_PDE_CLOSE(; 2020; 1; 12; “4*,7*”)
This specification of general ledger accounts also works with Excel formulas where you can specify cost centres and/or cost units. For all formulas, including those with cost centres and/or cost units, you cannot use wildcard sequences or combinations for multiple companies, cost centres or other selection criteria. For each individual Exact Online company, you must therefore still perform an addition as in this formula for the two companies 123 and 456:
I_EOL_BAL_PDE_CLOSE(123; 2020; 1; 12; “4*,7*”) + I_EOL_BAL_PDE_CLOSE(456; 2020; 1; 12; “4*,7*”)
Project, item and other Exact Online Dimensions
The financial numbers in Exact Online companies also have other dimensions, such as project code, item or customer. An example of this is the balance of a project on all general ledger cards in the costs. Exact Online does not offer a quick way to determine the balances of dozens or hundreds of projects. However, this is possible with the Excel formula I_EOL_TXN_AMOUNT_DC:
This formula determines the balance in the currency of the Exact company’s accounts across a range of dimensions:
- company
- posting date from/to
- general ledger account(s)
- customer/supplier code
- item code
- project code
- asset code
- cost centre/cost unit
- currency code
- journal
- VAT code
- customer/supplier reference
- reporting year
- reporting period from/through
- document number from/through
This allows you to retrieve the total revenue of an item or a customer from the general ledger.
Please note, however, that the calculation sometimes requires retrieving many transactions from Exact Online. We therefore do not recommend using tens of thousands of formulas on a large administration or on hundreds of administrations at the same time; it is better to use the ‘blocks’ in Invantive Control or Invantive’s Power Query connector for Exact Online.
Update December 2021
It is now also possible to specify general ledger account ranges for budgets. See Exact Online consolidatie van budgetten (Dutch).