Besides reporting on data contained in Exact Online or in the other support platforms, the All Cloud Edition of Invantive Control can also upload data into respective platforms. For Exact Online, you can easily upload hundreds or thousands journal entry lines in one or multiple Exact Online companies in one go directly from within Excel using the Excel formula I_EOL_GLTXN_POST. The import is executed in real time from the data in the Excel file.
Visit The most popular Exact Online Excel add-in formulas for other commonly used Excel formulas with Exact Online.
There is no need to manually create and debug CSV or XML files. Also, large uploads are automatically and fully transparant to the user broken into appropriately sized pieces for an optimal speed and minimal API consumption within the technical limitations of Exact Online.
Please note that Exact Online uses a mixed ledger, so sales and purchase entry lines can also be uploaded as long as you keep in the moment that only one side of the entry must be provided. The lines for the other side are automatically derived from the journal’s settings.
Please note the journal entry data can also be calculated from a local sub-ledger application database using an Invantive SQL query, combining for instance a SQL Server data container with the Exact Online table UploadXMLTopics (see also How do I create an Exact Online API interface?).
Please note that the journal entry data can also be imported using the module “Upload Journal Entries” of the web application Get My Report. Documentation available at Upload Transactions - Get My Report.
Working Excel sample upload journal entries
A working sample is available to help you create your own model to upload journal entry lines.
- First download the file Exact Online - Upload Journal Entry Lines.xlsx.
- Start Invantive Control and log on to an Exact Online country.
- Change the values as desired in the baby blue cells. The sample already contains some sensible data.
- Either remove the Exact Online company from cell B4 or pick one from the partition chooser (pie chart) in the Invantive Control ribbon.
- Change the value of the cell I4 to TRUE (Dutch: WAAR) to upload the lines into the Exact Online company.
Some tips are listed in the cells I19 and below from there.
Spreadsheet Input
The following picture gives you an impression of the input of importing journal entry lines into one or more Exact Online companies:
Result of Exact Online journal import
The picture below gives you an impression of the result of importing journal entries using the Excel formula I_EOL_GLTXN_POST:
How to use the Excel formula I_EOL_GLTXN_POST?
The Excel formula I_EOL_GLTXN_POST can create a series of ledger entries in multiple Exact Online companies. These can be either memorandum entries or entries with a pre-defined contra account, such as with sales transactions.
Speed and use
The new entries are uploaded quickly and reliably in so-called “XML batches”. It is possible to process thousands of entries in one go directly from Excel and the speed can be well over 40,000 entries per hour.
In case you accidentally create an entry with the wrong information you can use the Exact Online website to quickly remove a series of entries for a combination of company, period and journal.
Control Exact Online upload
The formula has a set of parameters to control the Exact Online upload. The parameters can fall in two groups: parameters that control the behavior and parameters that contain the data for the upload.
The control parameters are:
-
process: use TRUE if the transactions are allowed to be loaded at evaluation and FALSE to have the formula not book anything. -
AccountCodeInHeader: use TRUE if the transactions are to be grouped by (customer) account as in sales transactions and FALSE if multiple account codes are allowed in a transaction as in memorandum transactions. -
ExecutionHint: see Execution hints of Invantive Control Excel-formulas
Activate loading
The loading of transactions is not done automatically to avoid loading the same transactions repeatedly by mistake. Instead, a control parameter can be used to start loading upon evaluation. To do this, give the control parameter process the value TRUE.
This is usually done by filling the process parameter with a cell reference, for example B1, and then changing the value in cell B1 as a user as shown in the figure below:
When loading has completed, the cell containing the formula shows how much data has been processed.
Contents Exact Online transaction lines
The data parameters are converted into a list of rows of transactions. The list is then grouped by a limited number of common properties. Each group becomes a separate transaction with lines. The currently available data parameters are:
-
DivisionR: a specific value for the Exact Online company or an Excel range with desired values (for loading transactions simultaneously in multiple companies such as for consolidation and inter-company), -
FinYearR: a specific value for fiscal year or an Excel range with desired fiscal years, -
FinPeriodR: a specific value for period or an Excel range with desired periods, -
JournalCodeR: a specific value for journal or an Excel range with desired journals, -
EntryNrR: a specific value for entry number or an Excel range of desired entry numbers, -
FinDateR: a specific value for entry date or an Excel range of desired entry dates, -
GlActCodeR: a specific value for general ledger account or an Excel range of desired general ledger accounts, -
GlActCodeIntR: a specific value for general ledger contra account or an Excel range of desired contra accounts, -
DescR: a specific value for description or an Excel range of desired descriptions, -
NoteR: a specific value for note field content or an Excel range of desired notes, -
CostCentreR: a specific value for cost center or an Excel range of desired cost centers, -
CostUnitR: a specific value for cost unit or an Excel range of desired cost units, -
AssetR: a specific value for asset or an Excel range of desired assets, -
ProjectR: a specific value for project or an Excel range of desired projects, -
QtyR: a specific value for quantity or an Excel range of desired quantities, -
ActR: a specific value for account or an Excel range with desired accounts (for sales transactions, choose only customers, etc.), -
OurRefR: a specific value for reference or an Excel range of desired references, -
DebitAmtR: a specific value for debit amount or an Excel range of desired debit amounts, -
CreditAmtR: a specific value for credit amount or an Excel range of desired credit amounts,
Many data parameters do not require a value. Use Exact Online to determine what data parameters require a value.
All parameters that become part of the entry accept either a fixed (single) value such as a text, or an Excel range. Each Excel range must have the same dimensions, for example 2 rows high and 1 column wide.
| Column A: General ledger account | Column B: Description | Column C: Amount |
|---|---|---|
| 8000 | Land | 25 |
| 1230 | Change land value | -25 |
The height of the Excel ranges determines the number of accounting lines, two in this example. Each part of the transaction line gets it’s value from the element at the same place in the Excel range of the parameter or, when the parameter is a single value, the value of the parameter.
Ultimately this generates a set of lines to be uploaded into Exact Online, for example by giving the data parameter DivisionR the fixed value “102673” and GlActCodeR the values 8000 and 1230:
| Company | General ledger account | Description | Amount |
|---|---|---|---|
| 102673 | 8000 | Land | 25 |
| 102673 | 1230 | Change land value | -25 |
Loading into the Sales Ledger
When loading into the sales ledger, the contra account is automatically determined by the settings of the journal. In addition, for each customer the code of the relation must not be entered on the line itself, but in the so-called “header” of the sales transaction. Invantive Control for Excel does not know with 100% certainty what type a journal requires and therefore the parameter AccountCodeInHeader should be set to TRUE when loading sales transactions into the sales ledger:
Loading sales transactions into Exact Online can be done using the sample file Exact Online - Sample - Upload Sales Entry Lines.xlsx:
Recommended Version
The use of version 22.0.190 or newer of Invantive Control for Excel is required for optimal use of this upload facility.