With the Excel add-in Invantive Control for Excel you can use Excel formulas to incorporate Exact Online live data into your own Excel reports. This applies to Exact companies of an accountant as well as of entrepreneurs. Excel is directly linked to Exact Online for all your companies. So the data is retrieved real-time by the Excel functions from the Exact Online database and shown in Excel reports.
There are hundreds of standard formulas available through the menu and you can add your own formulas even with SQL statements on the Exact Online database.
In addition, models can also be built with so-called “blocks”. An example of this the Excel cash flow prognosis for Exact Online (consolidated across companies).
The direct link between Excel and Exact Online is available in all Excel versions from 2013 onwards on Microsoft Windows. For the Mac versions we recommend the use of Get My Report or Invantive Cloud, or running Parallels Desktop on Mac (see Itgenuty347 This version 2010 of Microsoft Office is not supported. Please upgrade to Microsoft Office 2013 or newer).
Installation Excel add-in for Exact Online
Execute the following steps to install the Excel add-in for Exact Online:
- First, make sure you have a current version of Microsoft Excel. This should be Excel 2013 or newer, although it also works with Excel 2010. It doesn’t matter whether you run a 32-bit or 64-bit version of Excel.
- You run the installation of the Exact Online Excel add-in from the download.
- Afterwards, close Microsoft Excel completely and, when in doubt, check in the Task Manager that EXCEL.EXE is no longer executing.
- Restart Microsoft Excel after this. The add-ins will be loaded and you will see “Invantive Control” flash by in the Excel splash-screen.
- The first time you have to fill in personal data. These are also used if you ask for online assistance via the button in the ribbon, but no support is available at the moment.
- When asked for a license key choose “Try all features” after which you can try out the software for 180 days free of charge and without obligation on Exact Online and over 70 other (cloud)platforms.
- During this trial period you can also use the Exact Online Power BI connector.
Next time you don’t have to fill in any form. After starting up Excel it can take up to 20 seconds before the add-in becomes visible in the ribbon as two new ribbons in Excel: “Invantive Control” and “Modeler”. You can already use Excel in the meantime.
How to create Exact Online reports with the Excel add-in?
Most users of the Excel add-in for Exact Online only use the standard Invantive Excel formulas for Exact Online in the reports. SQL usage is less common and for accountants only possible with an accountancy subscription for large offices (1.000+ companies).
You can retrieve live data from one or more Exact Online companies simultaneously and consolidate them in your reports. Even within one Excel cell figures from multiple Exact Online companies can be combined.
Invantive Control is standard software and similar to the Exact Globe Excel add-in. The Invantive Excel add-in adds extra features compared to the Exact Globe Excel add-in. In contrast to the add-in for Exact Globe you can for instance also create queries in SQL, upload data in your companies and exchange data with over 70 other data sources such as AFAS Profit or several Visma software products.
In this topic I will limit myself to the Exact Online Excel functions. There are many more possibilities and the Invantive add-ins offer enough possibilities even for multi-nationals.
The easiest way is to start with an Excel report which already contains figures manually copied from Exact Online. Make a copy of this Excel report and open the Excel file. We will first connect to the Exact Online accounting system from Excel:
- Click on “Login” in the “Invantive Control” menu.
- Open the group “Exact Online” and choose the country.
- Enter your login code and password that you also use in the browser.
- Click on "Connect:
- Enter the verification code.
- Allow the app to connect to a specific or all companies.
- Check the selected companies in the “Invantive Control” menu and add more companies if necessary.
- Enable the use of Exact Online formulas by flipping the left-most slider in the “Modeler” ribbon so it turns green. But…
- If already an Excel file was open you will automatically get a question for this:
The software is now also connected to one or more companies. Next we are going to replace the figures by Excel formulas.
Each Exact Online formula has the company as its first argument with the name “DivisionCode”. You don’t need to enter this if you have chosen only one company; the software will then automatically pick the chosen company.
Otherwise you need to enter a value. The value however is NOT the number of the company (often it’s “1” or “2”) but the real unique number which Exact Online uses internally. This is the so-called “DivisionCode” and you’ll also often see this in the URL of your browser when you switch to another company.
This number determines where the live data is taken from. You can find a list of the numbers, for example, in the companies drop-down menu. The division codes are then enclosed in round brackets after the company name. For example, in this picture it is 868043 for the administration “13 - Association”:
For this example, I will assume that you have selected exactly one Exact Online company and that no value needs to be specified in the function.
Now find a cell with numbers from Exact.
In 80% of the cases these will be figures from the general ledger and almost always balances (either on P&L or balance sheet). Invantive Control offers formulas with which you can easily retrieve a balance total up to a certain period or a mutation on a cost center/cost center (analytical accounting). Although there are hundreds of formulas available, it turns out that those “most popular Exact Online Excel formulas” from the title are actually only a few.
Note the current result of the cell and - if feasible - replace the numbers by:
I_EOL_GLACT_DESC: name of a general ledger account from the named Exact division.I_EOL_BAL_YEAR_OPEN: opening balance of a general ledger account.I_BAL_PDE_CLOSE: balance at the end of the period of a general ledger account.I_EOL_BGT_PDER_LINE_AMOUNT: budget over a series of periods of a general ledger account.
All Exact Online Excel formulas can be found in the “Modeler” ribbon under Building Blocks:
There are also variants for use with an Exact Online reporting scheme/classification structure. This involves adding up the balance of multiple general ledger accounts located beneath a classification. Usually this is done for all underlying ledger accounts but you can also limit the number of levels included. The most common variant with reporting scheme/classification structure is the period total over all general ledger accounts under the classification code. An example:
=I_EOL_BAL_PDER_CLASSIFICATION(divisioncode;financialyear;1;4;;“01”)
returns the cumulative balance over periods 1 through 4 of classification “01” (which is usually the entire balance sheet). Accountants with RGS can also use the RGS classification schemes.
In Belgium, for example, analytical accounting with cost center and cost carrier is often used. The Excel formulas for variants with cost center/cost carrier can be recognized by the abbreviations “CCE” and “CUT” immediately after “EOL” in the Excel form name, such as, for example, I_EOL_CUT_CCE_BAL_PDE_CLOSE. Here “CCE” stands for “cost center” and “CUT” stands for “cost unit”.
For complex consolidations, it is also possible to use, for example, series of general ledger accounts as described in Nieuwe Excel consolidatiemogelijkheden voor Exact Online bedrijven en grootboekrekeningen (Dutch).
The calculated figures are displayed immediately. For the first formula, this may take a few seconds. After that it is really instant: flashing fast, even for thousands of cells.
You can repeat this replacing of figures by formulas for all your reports. The end result will then look like this for example:
It is convenient to work with names. In this example, I worked with the name “fiscalyear” and “division code” in Excel:
If you ever need a flat copy of your Excel report, use the “Publish” button. The recipient will then only get the figures and not the formulas.
There are countless other clever tricks to limit the number of reports, for example by creating reports that give the right results for each administration or client cluster. If you have questions about this, ask them on the forums. We will then try to help you further together. Do you think after reading this text “help”? Please leave a comment below.
Load financial transactions via formulas
It’s also possible to create financial transactions in the ledger of one or more Exact Online administrations using Excel formulas. Please read for this topic: Mass import Exact Online journal entries directly from within Excel.