Data Importer User Guide Section 4 - Opening Financial Position

Data Importer User Guide for Unreconciled Bank Transactions, Customer Open items, Supplier (Vendor) Open items and Opening Stock (Inventory) Position

Written By Niall Conlon (Draft Writer)

Updated at June 8th, 2021

1. Introduction

The Data Importer is a feature in which you can populate predefined Excel templates with a wide range of information that can then be brought in or imported into the system. 

For example, it is used to bring in existing Supplier or Customer details for new companies and contains Customer or Supplier Codes, names, Addresses, Contacts, bank details and more. More complex static information such as Chart of Accounts, VAT (Tax) Codes, BI Codes and more can all be imported. In addition to this, transactional information such as invoices or payroll journals can also be populated in the Excel template formats, then validated and imported.

If you wish to use this tool please contact your local support team. If you have not used this before you will have to undergo training and assistance to ensure you have the knowledge and skills to use the tool effectively.

This article is concerned with one section of the Data Importer - the Opening Financial Position.

If you need any of the other types please go to that section

Chart of Accounts

Trial Balance and Banks

Static Data

Transactions Importer


2. Important points to note

i) Processing transactions and importing simultaneously

Information can be imported at any time but it is very important to note that when you are using the Data Importer wizard from Setup > Data Importer and are in the process of importing data, no other user can be logged into the system. This is because when an import is taking place and a user is inputting transactions, then this can cause transactions to become corrupted. So, please ensure to log all other users out of the system before you import your transactions.

ii) Use the latest Import template you need

The Import Templates are available for download within the Data Importer.  The template is available to download in file format Office 97-2003 and Office 2007. It is important to note that the Import Templates need to be downloaded from each company individually. It is not recommended to import data using a template from a different company as this company may have a different setup. It is important to use a freshly downloaded template each time because the new template will contain any changes that any users have made in the system, for example, updates to the General Ledger codes, Supplier defaults, Customer information and more.


3. Types of information that can be imported

For the Opening Financial Position Data Importer you can import the following:

i) Unreconciled Bank Transactions

ii) Customer Open items

iii) Supplier (Vendor) Open items

iv) Opening Stock (Inventory) Position


4. Populating the Template

Below is a summary of the information which can be populated in each worksheet.  The column headings include a user-comment advising what information should be populated in each column.

Please note column headings or worksheet names must not be changed, added or deleted. Please do not add any new columns to the existing worksheets.

i) Make sure to read the information provided at the top of each worksheet/column - it provides supplementary information on how the data should be completed;

ii) Make sure that you download the Import Templates from the correct database;

iii) Ensure that the correct currencies, financial year and client locale are set during the creation routine, or through the main system before attempting to import data;

Download the appropriate template from the Data Importer, save it as a file on your desktop or required location, fill the required fields and save it. Use the tab ’’Generate Data File’’ to generate the CSV file ('Generate Data Upload File').  This CSV is the file you need to upload to the Data Importer in order to import the data you have populated. 


This will ensure necessary format checks are carried out, but most importantly that the CSV file used by the importer is created correctly and in the same location as the .xls file. A message will appear at the top of the spreadsheet informing you if this is the case

General Checks

i) Ensure that you populate the templates from the first row (row 5), and that no row is left blank.

ii) Make sure that no special characters are used (e.g. apostrophes, ampersands, Irish/French characters etc).

iii) Check for blanks in the Code columns.

iv) Non-numeric data should be saved according to the requirements example: date should be saved as date format into the Excel spreadsheet.

v) Make sure that you complete each field according to the requirements provided.

vi) Make sure that the numerical information is correlated with the type of the transactions.

vii) Make sure that the information is selected from the dropdown list, for example, GL codes.

viii) Ensure that there are no totals added to the bottom of any worksheet.

ix) Ensure that valid dates are entered and the financial information is accurate. 

x) Ensure that when pasting data into the template that the ‘Paste Values’ option is used - this will eliminate any formulas that have been used to calculate those values.

xi) The values need to have 2 decimals as this is what the Data Importer supports. Please round the values into two decimals before pasting them into the template (easily achieved using the =ROUND() Excel formula).

 

5. Opening Financial Position Import

Go to Setup > Data Importer > Opening Financial Position

Click 'Download Opening Financial Position Template File' hyperlink as highlighted in Red below.

Choose the 'Save as' option and choose the Location you wish to save the file in and give it an appropriate file name.

Open the file.

If Macros are not enabled please enable them.

5.1 Bank Unreconciled Transactions 

The Bank Unreconciled Transactions allows you to import all Bank transactions that have not yet been reconciled. These can be imported into a suspense account which will not affect the balance on the bank account itself (as this will already be included in the Trial Balance figure). You can also select another GL account if you so wish (for example an opening balance account) which will affect the balance on the GL account.


Bank Code (Compulsory Field) – Nominate from the dropdown the GL bank account.

Reference (Compulsory Field– Populate with the transaction reference.  This field must be populated with a reference.  The reference will appear in the bank reconciliation screen.

Date (Compulsory Field) - Populate with the transaction date.  Dates should be in the European format dd/mm/yyyy.  If the date is listed in US format (mm/dd/yyyy) please ensure that you format the date column to the European format before importing.

Description (Compulsory Field) – Populate with a transaction description.

Bank Amount (Compulsory Field) – Populate with the transaction amount.  If the bank account is a foreign currency bank account populate with the foreign currency value.  If transactions are payments populate the values as negative figures; otherwise positive.

System Currency Amount (Compulsory Field) – Populate with the value of the transaction in the system/base currency.

GL Code – Leave blank to populate the balance in the system suspense account (recommended); otherwise populate with another GL account code.

 

5.2 Supplier (Vendor) Open Items

Code (Compulsory Field) - Nominate the Supplier Account from the dropdown.

Reference (Compulsory Field) - Populate with the transaction reference.  This field must be populated with a reference.  The reference will appear in the supplier ledger.

Date (Compulsory Field) - Populate with the transaction date.  Dates should be in the European format dd/mm/yyyy. If the dates are listed in US format (mm/dd/yyyy) the system importer process will provide the following checkbox option:

"If the data provided has dates in US format (mm/dd/yyyy) instead of European format (dd/mm/yyyy) please check this box."

Description (Compulsory Field) - Populate with the transaction description.

Supplier Balance (Compulsory Field) - Populate with the value of the transaction.  If the supplier is a foreign currency supplier populate with the foreign currency value.  Supplier invoices should be entered as negative values and payments as positive.

System Currency Balance (Compulsory Field) - Populate with the value of the transaction in the system currency/base currency. The total of the system currency value for all Supplier open items should match exactly to the value for the relevant Accounts Payable Control account in the imported/to-be-imported Trial Balance.


5.3 Customer Open Items

Code (Compulsory Field) - Nominate the Customer Account from the dropdown.

Reference (Compulsory Field) - Populate with the transaction reference.  This field must be populated with a reference.  The reference will appear in the customer ledger.

Date (Compulsory Field) - Populate with the transaction date.  Dates should be in the European format dd/mm/yyyy. If the dates are listed in US format (mm/dd/yyyy) the system importer process will provide the following checkbox option:

"If the data provided has dates in US format (mm/dd/yyyy) instead of European format (dd/mm/yyyy) please check this box."

Description (Compulsory Field) - Populate with the transaction description.

Supplier Balance (Compulsory Field) - Populate with the value of the transaction.  If the customer is a foreign currency supplier populate with the foreign currency value. Customer invoices should be entered as positive values and payments as negative.

System Currency Balance (Compulsory Field) - Populate with the value of the transaction in the system currency/base currency. The total of the system currency value for all Customer open items should match exactly to the value for the relevant Accounts Receivable Control account in the imported/to-be-imported Trial Balance.


5.4 Opening Stock Position 


Item ID (Compulsory Field) – Nominate the Stock Item from the dropdown.

Location  Nominate the location of the item from the dropdown. A stock item can be nominated on 2 rows if the location ID is different.

Sub-Location - Nominate the sub-location of the item from the dropdown.

Quantity - Input the quantity of the stock item that is available at that location. This will represent the quantity on-hand in the system.