Opening Financial Position Data Importer

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

Written By Grainne Reidy (Super Administrator)

Updated at July 11th, 2024

Introduction

The Data Importer lets you populate predefined Excel templates and import them into AccountsIQ. It can clear data or import or add to existing system data.  The Import Templates are available in Office 97-2003 and Office 2007 formats. 


The Data Importer can import: 

  • Static information such as Chart of Accounts, VAT (Tax) Codes, and BI Codes.
  • Existing Supplier (Vendor) or Customer records when setting up a company in the system, including codes, names, addresses, contacts, and bank details. 
  • Transactional information such as invoices or payroll journals.
Delete

Info

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.


Any user can have access to the data importer if their user profile is set with that permission. For more on user permissions, see Managing Group and Entity Users‍. User role then determines the range of available imports. Admin users have access to the full suite, while Users only have access to a subset.

 

The Data Importer for Opening Financial Position lets you import the following: 
  • Unreconciled Bank Transactions:  This allows you to import all unreconciled bank transactions.
  • Customer Open Items: This allows you to import open transactions.
  • Supplier Open Items: This allows you to import open transactions.
  • Opening Stock Position
Delete

Info

Note, we highly recommend that the Trial Balance is imported prior to the opening financial position template to avoid differences between the General, purchases, and sales ledgers. See Trial Balance and Banks Data Importer for more details.‍ 

Guidelines for successful importing

Check database setup

Before importing data, ensure that the client database has been created correctly using the company setup wizard and that the correct currencies, financial year, and client locale were selected. Verify this in the main system in advance of importing data.

Banks, customers, suppliers, and items must exist in the system before using this import.

Use separate templates for each company

You must download the Import Templates 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. 

Use newly downloaded templates each time

Use a newly downloaded template each time as it will contain any changes made in the system, for example, updates to the General Ledger codes, Supplier defaults, or Customer information. Make sure you download the Import Templates from the correct database.

See: 

 26.1 - Getting Started with Data Importers

26.10 - Importing Opening Financial Position Data

BI Code Importer‍ 

Bank Accounts Importer‍ 

Customer Data Importer‍ 
Supplier Data Importer‍ 

Items Data Importer ‍ 

Trial Balance and Banks Importer‍ 

Chart of Accounts Data Importer‍ 

Transactions Data Importer‍ 

Delete

Prepare the Template

Make sure to read the information provided at the top of each worksheet/column. It provides supplementary information on how to complete the data. 

  1. Go to Setup > Data Importer > Start > Opening Financial Position.
  2. Click the download link and save the file as a Microsoft Excel Macro-enabled file (.xlsm format).


     
  3. If Macros are not enabled, click Enable Content.

    Graphical user interface, application, Word 
Description automatically generated
     
  4. Fill in the required fields, following the Guidelines for Data Entry, below. See the sections below relating to each of these for guidance:
    • Unreconciled Bank Transactions
    • Supplier (AP) Open items
    • Customer (AR) Open items
    • Opening Stock Position
  5. Save the file.

Guidelines for Data Entry

Follow these guidelines before you import the data. The data must be prepared appropriately before attempting to import.

General:

  • The import tool cannot accept data prepared from any other workbook format. It must be downloaded from the Setup > Data Importer
  • Download the template from the entity that you want to import data into.
  • Complete each field according to any requirements provided in the worksheet.
  • Do not change column headings or worksheet names. 
  • Do not add or delete any columns in the existing worksheets.
  • Do not leave any mandatory columns or rows blank.
  • Where there are dropdowns, select data from the list.  

Formatting non-numerical data: 

  • Do not use special characters (e.g., apostrophes, ampersands, accents, etc.).
  • Save non-numerical data according to the requirements example, for example, save dates using the date format.
  • Ensure that dates are valid.

Formatting numerical data: 

  • Use the ‘Paste Values’ option when pasting data into the template. This will eliminate any formulas used to calculate those values.
  • For negative financial figures, use a minus sign.
  • Values must be two decimals. Round the values to two decimals before pasting them into the template (use the =ROUND () Excel formula).
  • Ensure that there are no totals added to the bottom of any worksheet.
  • Ensure that the financial information is accurate.  
Delete

Unreconciled Bank Transactions Template

The Bank Unreconciled Transactions allows you to import all unreconciled Bank transactions. These can be imported into a suspense account that 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 (for example an opening balance account) which will affect the balance on the GL account.

 

 

Complete the following:

  • Bank Code (Mandatory): From the dropdown, select the GL bank account.
  • Reference (Mandatory): Enter the transaction reference. This will appear in the bank reconciliation screen and cannot be blank. The reference will usually be a receipt or payment reference number.
  • Date (Mandatory): Populate with the transaction date. This must be before the go-live date.
  • Description (Mandatory): Populate with a transaction description.
  • Bank Amount (Mandatory): Enter the transaction amount in the bank account currency. If a transaction is a payment, enter the value as a negative figure. Otherwise, enter a positive value.
  • System Currency Amount (Mandatory): Enter the value of the transaction in base currency.
  • GL Code: The GL code field is not mandatory. We recommend leaving it blank to avoid affecting the bank GL balance in the general ledger.
Delete

Customer (AR) Open Items Template


All fields are mandatory and there must be no blank values. Complete the following:

  • Customer Code (Mandatory): Nominate the Customer Account from the dropdown.
  • Reference (Mandatory): Populate with the transaction reference. The reference will appear in the customer ledger. This will be an invoice number, credit note, or payment reference.
  • Date (Mandatory): Populate with the transaction date. This must be before the go-live date. 
  • Description (Mandatory): Populate with the transaction description.

Next, enter the financial figures. All values must be rounded to two decimal places and there must be no rows with zero values.

  • Customer Balance (Mandatory): Enter the invoice/payment amount in the customer account currency. 
  • Base Currency Balance (Mandatory): Enter the invoice or payment amount in base currency. 

For base currency customers, both amounts will be the same. For foreign currency customers, the amount will differ. Enter sales invoices as positive values and credit notes or payments on account as negative values. Corresponding Customer and Base Currency Balances must have the same signs. 

Delete

Info: Relationship with Trial Balance

The sum for all Customer Open Items should match the closing balances in all Accounts Receivable Control Accounts in the trial balance.

The sum of Customer Control Account Open Items should match the closing balance of the Customer Control Account in the trial balance.

Delete

Supplier (AP) Open Items Template


All fields are mandatory and there must be no blank values. Complete the following:

  • Supplier Code (Mandatory): Nominate the Supplier Account from the dropdown.
  • Reference (Mandatory): Populate with the transaction reference. The reference will appear in the supplier ledger. This will be an invoice number, credit note, or payment reference.
  • Date (Mandatory): Populate with the transaction date. This must be before the go-live date. 
  • Description (Mandatory): Populate with the transaction description.

Next, enter the financial figures. All values must be rounded to two decimal places and there must be no rows with zero values.

  • Supplier Balance (Mandatory): Enter the invoice or payment amount in the customer account currency. 
  • Base Currency Balance (Mandatory): Enter the invoice or payment amount in base currency. 

For base currency suppliers, both amounts will be the same. For foreign currency suppliers, the amount will differ. Enter supplier invoices as negative values and debit notes and payments on account as positive values. Corresponding Supplier and Base Currency Balances must have the same signs. 

Delete

Info: Relationship with Trial Balance

The sum for all Supplier Open Items should match the closing balances in all accounts payable control accounts in the trial balance.

The sum of Supplier Control Account Open Items should match the closing balance of the Supplier Control Account in the trial balance.

Delete

Opening Stock Position Template


Complete the following:

  • Item ID (Mandatory): Nominate the Stock Item from the dropdown.
  • Location:  Nominate the location of the item from the dropdown. You can nominate a stock item on two rows if the location ID is different.
  • Sub-Location: Nominate the sub-location of the item from the dropdown.
  • Quantity: Enter the quantity of the stock item that is available at that location. This will represent the quantity on-hand in the system.
  • Average Cost
Delete

Import the Template

Delete

Info: Group Companies

Create individual templates for each subsidiary you want to import into. Do not import Opening Financial Position into the Group Consolidation Entity.


Step One: Generate the Data File

When you have completed all worksheets correctly, go to the Generate Data File tab and click Generate Data Upload File to generate the CSV file. If there are errors, correct them and click Generate Data Upload File again.

You will find the CSV file in the same location as the macro-enabled Excel file. This file will be used to import all the data you added. 

Step Two: Import the Data File

Delete

Warning: During Import, log out all other users

Before you begin the upload and import process, ensure that all other users are logged out. If another user enters transactions during import, this can cause data corruption. 


In addition, go to Manage Financial Year and check that the purchase and sales ledgers are open in the month prior to going live.


  1. Go to Setup > Data Importer > Start > Opening Financial Position
  2. Click Browse to find your saved CSV template.

    Graphical user interface, text, application, emailDescription automatically generated
     
  3. Click Upload Data. If the file could not be uploaded, you will receive an error message. Make corrections in the original Excel file, generate a csv file again and upload it as before.
Delete

Import Unreconciled Bank Items, Customer Open Items, and Supplier Open Items

Once you have imported the completed data file, you are ready to apply the data to the system via the Banks Import and Open Items Import. If you log out and log back in again to continue the import, you do not have to upload the import file again as it is retained in the importer. 


When a step is successful you will be prompted to move to the next step. You can skip any step by clicking Skip Step. Steps are colour-coded according to their status:
  • Green: Step completed.
  • Red: Step not completed.
  • Greyed out: Step unavailable for selection because the data is not included in the Import Template.

Unreconciled Bank Items

  1. Go to Setup > Data Importer > Banks Import > Unreconciled Bank Items.
  2. Unreconciled items you want to import appear on the screen. If an error occurs with the data, an error message will appear on the screen. 
    • You can click Edit to make changes.
    • If any errors cannot be corrected on screen, you must first go to Validation > Clean Up and click on Clean Up. This will remove any unneeded temporary data, avoiding the risk of duplicating data from a previous import into a subsequent one. Then correct in the original Excel template, generate the CSV file again and follow the upload and import steps again. Repeat until the import passes validation.
  3. To ensure that no bank balances are affected in the system, keep 9999IMP as the importing suspense account. 
  4. To import, click Apply. If successful, you will receive confirmation.

Customer Open Items

  1. Go to Setup > Data Importer > Open Items Import > Customer Open Items.
  2. Customer Open Items you want to import appear on the screen. If an error occurs with the data, an error message will appear on the screen. 
    • You can click Edit to make changes.
    • If any errors cannot be corrected on screen, you must first go to Validation > Clean Up and click on Clean Up. This will remove any unneeded temporary data, avoiding the risk of duplicating data from a previous import into a subsequent one. Then correct in the original Excel template, generate the CSV file again and follow the upload and import steps again. Repeat until the import passes validation.
  3. If you want to clear existing customer open items, check Clear existing customer open items. If you do not check this, the importer will add to the existing open items.
  4. If the data provided has dates in US format instead of European format, check Data Format to change to European format. 
  5. Suspense account is checked by default. To prevent control account modification, all opening items are posted to a Suspense Account whose code will appear on the screen. To change the balance to post to a Control Account, uncheck.
  6. To import, click Apply. If successful, you will receive confirmation.

Supplier Open Items

  1. Go to Setup > Data Importer > Open Items Import > Supplier Open Items.
  2. Supplier Open Items you want to import appear on the screen. If an error occurs with the data, an error message will appear on the screen. 
    • You can click Edit to make changes.
    • If any errors cannot be corrected on screen, you must first go to Validation > Clean Up and click on Clean Up. This will remove any unneeded temporary data, avoiding the risk of duplicating data from a previous import into a subsequent one. Then correct in the original Excel template, generate the CSV file again and follow the upload and import steps again. Repeat until the import passes validation.
  3. If you want to clear existing supplier open items, check Clear existing supplier open items. If you do not check this, the importer will add to the existing open items.
  4. If the data provided has dates in US format instead of European format, check Data Format to change to European format. 
  5. Suspense account is checked by default. To prevent control account modification, all opening items are posted to a Suspense Account whose code will appear on the screen. To change the balance to post to a Control Account, uncheck.
  6. To import, click Apply. If successful, you will receive confirmation.
Delete

Final Clean Up

Delete
Delete

Warning

Always perform cleanup after a new import. This avoids the risk of duplicating data from the previous import in the new import. Clean-up should only be completed when a data import is complete and the files are no longer required.


If you are satisfied with your import and would like to clear any previously uploaded template before future imports:
  1. Go to Data Importer > Validation > Clean Up.
  2. Click Clean Up.Graphical user interface, text, application, emailDescription automatically generated

Delete

FAQs

I can't see the notes in the error file.

You can control the appearance of notes in Excel:

  1. Go to File > Options > Advanced.
  2. Scroll down to the display settings. We recommend you select the Indicators only, and comments and notes on hover option. This lets you see detailed notes on errors when you hover over highlighted cells.

    Graphical user interface, text, applicationDescription automatically generated
Delete