Summary
Resolution
Opening balances are totals of the outstanding invoices and credit notes you have before you start using Accounting. To save time, you can import an opening balances file.
To import an opening balances file, you must:
- Already have entered customers or suppliers
- Set an accounts start date and VAT scheme
- Ensure that the file contains only customer or supplier opening balances. To import both, you need two separate files.
- Save the file in comma-separated value (CSV) format
- Ensure that the file uses the same column headings and formatting as in the customers or suppliers opening balances template file
If you have bank or ledger account opening balances, you can enter them manually. For more information, see Enter bank account opening balances or Entering ledger account opening balances.
Step 1: Download the CSV template file
Step 2: Prepare the opening balances file
If you already have a file, compare it to the template file before importing your opening balances. The column headings and data format in your file must match the template.
If you don't have a file, you can add your data to the template file. Leave the column headings as-is and then replace the sample data with your data, using the same format.
NOTE: The maximum file size you can import is 0.5MB. If your file is larger, split it into another file.
When preparing your file, use the tables below to ensure that your file format is correct. Use the table that applies to your VAT scheme. Remember that the header row in your CSV file must use the same headings as shown in the Field Name column below.
Customer opening balances file format
Invoice Basis
| Column | Field Name | Type | Compulsory | Notes |
| A | Type | Text | Yes | To enter an invoice, enter Invoice or for a credit note, enter Cr Note. If you have an outstanding payment on account, enter this using Cr Note. This imports as a credit note which you can allocate to an invoice or refund at a later date. |
| B | Date | Numbers | Yes | Must be in the format dd/mm/yyyy. |
| C | Customer Name | Text | Yes | Enter the customer’s name as it appears on their record. |
| D | Customer Reference | Text and numbers | Yes | Enter the customer’s unique reference as it appears in their record. |
| E | Reference | Text and numbers | Yes | Enter a reference of your choice, for example, an invoice number. |
| F | Details | Text and numbers | No | If required, enter any additional details up to a maximum of 25 characters. |
| G | Total | Number plus 2 decimal places | No | Enter the gross value of the transaction. |
Cash Basis
| Column | Field Name | Type | Compulsory | Notes |
| A | Type | Text | Yes | To enter an invoice, enter Invoice or for a credit note, enter Cr Note. If you have an outstanding payment on account, enter this using Crn. This imports as a credit note which you can allocate to an invoice or refund at a later date. |
| B | Date | Numbers | Yes | Must be in the format dd/mm/yyyy. |
| C | Customer Name | Text | Yes | Enter the customer’s name as it appears on their record. |
| D | Customer Reference | Text and numbers | Yes | Enter the customer’s unique reference as it appears in their record. |
| E | Reference | Text and numbers | Yes | Enter a reference of your choice, for example, an invoice number. |
| F | Details | Text and numbers | No | If required, enter any additional details up to a maximum of 25 characters. |
| G | Net | Number plus 2 decimal places | No | Enter the net value of the transaction. |
| H | VAT Rate | Text | No | Enter one of the following VAT rates: * No VAT |
| I | VAT | Number plus 2 decimal places | No | Enter the VAT element of the transaction. |
| J | Total | Number plus 2 decimal places | No | Enter the total gross value of the transaction. The net and VAT amounts must equal the total value otherwise the import will fail. |
Supplier opening balances file format
Invoice Basis
| Column | Field name | Type | Compulsory | Notes |
| A | Type | Text | Yes | To enter an invoice, enter Invoice or for a credit note, enter Cr Note. If you have an outstanding payment on account, enter this using Crn. This imports as a credit note which you can allocate to an invoice or refund at a later date. |
| B | Date | Numbers | Yes | Must be in the format dd/mm/yyyy. |
| C | Customer Name | Text | Yes | Enter the supplier’s name as it appears on their record. |
| D | Customer Reference | Text and numbers | Yes | Enter the supplier’s unique reference as it appears in their record. |
| E | Reference | Text and numbers | Yes | Enter a reference of your choice, for example, an invoice number. |
| F | Details | Text and numbers | No | If required, enter any additional details up to a maximum of 25 characters. |
| G | Total | Number plus 2 decimal places | No | Enter the gross value of the transaction. |
Cash Basis
| Column | Field Name | Type | Compulsory | Notes |
| A | Type | Text | Yes | To enter an invoice, enter Invoice or for a credit note, enter Cr Note. If you have an outstanding payment on account, enter this using Crn. This imports as a credit note which you can allocate to an invoice or refund at a later date. |
| B | Date | Numbers | Yes | Must be in the format dd/mm/yyyy. |
| C | Customer Name | Text | Yes | Enter the supplier’s name as it appears on their record. |
| D | Customer Reference | Text and numbers | Yes | Enter the supplier’s unique reference as it appears in their record. |
| E | Reference | Text and numbers | Yes | Enter a reference of your choice, for example, an invoice number. |
| F | Details | Text and numbers | No | If required, enter any additional details up to a maximum of 25 characters. |
| G | Net | Number plus 2 decimal places | No | Enter the net value of the transaction. |
| H | VAT Rate | Text | No | Enter one of the following VAT rates: * Standard * No VAT If you have any VAT-registered EU suppliers or suppliers outside of the EU, you should enter No VAT, and then enter the gross value of the transaction in the Net and Total columns. This is because VAT on these transactions is accounted for when you create the invoice, not when receive or make the payment. If you haven’t yet accounted for the VAT in your previous system, you can manually adjust your next VAT return to include the necessary values. |
| I | VAT | Number plus 2 decimal places | No | Enter the VAT element of the transaction. |
| J | Total | Number plus 2 decimal places | No | Enter the total gross value of the transaction. The net and VAT amounts must equal the total value otherwise the import will fail. |
Step 3: Import the customer or supplier opening balances file
- Go to Settings, then Business Settings.
- In the Opening Balances section, select Customers or Suppliers.
- Select New Opening Balance and then select Import Opening Balances.
- Select Choose File. Browse to your opening balances CSV file and then click Open.
- Click Upload.
- When prompted that the upload was successful, click OK.
VAT considerations after importing
If you use the Cash Basis VAT Scheme and you have any VAT-registered EU customers outside the EU, the VAT rate defaults to No VAT and the gross value appears in the Net and Total columns. This is because VAT on these transactions is accounted for when you create the invoice not receive or make the payment.
If you haven't yet accounted for the VAT in your previous system, you need to manually adjust your next VAT return to include the necessary values.
If you’re importing from Sage Accounts, the VAT rate appears based on the tax code you used for each transaction. If for any reason you amended the VAT rate percentage in Sage Accounts, you can click the transaction to manually amend the net and VAT amounts then Save.
NOTE: If you amend any of the values, it will adjust the Trade Debtors control account balance. This will create a difference in the Trial Balance report from Sage Accounts. When entering your nominal opening balances, you'll need to decide where to post the difference. For example, you may want to post it to Corrections.
Africa
Asia
Australia
Canada
France
Kenya
Nigeria
South Africa
Spain
United Arab Emirates
United Kingdom
United States