When you transition to Business Finance Assistant from another accounting solution, you may want to import existing journal entries, especially if you change solutions during a financial year. This guide will give you tips and help you through this process.
Requirement - Match the Chart of Accounts
In order to import journal entries, the Chart of Account structure in Business Finance Assistant must match that of your previous accounting solution. This can be done by editing your Chart of Accounts. To match Business Finance Assistant’s Chart of Accounts, you may need to:
- Create additional accounts
- Create additional account families
- Change some account numbers
- Remove unnecessary accounts
Once your Chart of Accounts match, you can continue. Alternatively you can Upload a Chart of Accounts. If you have any difficulty matching your chart of accounts, please contact our support experts.
Step 1: Retrieve journal entries from your previous accounting software
Each accounting solution has a different method of retrieving journal entries to be processed by Business Finance Assistant. Consult the user guide of your accounting software in order to retrieve transactions in a tabular format, such as CSV (comma-separated values).
Some tips:
Do not export 'forecast' or 'draft' journal entries
All transactions imported will always be considered realised, so do not retrieve forecast or draft entries from your prior solution. If you do not have this option, you may need to remove them in the second step.
Each row in the table refers to one part of the accounting transaction
Make sure you export your entries in a format that has each part of the journal entry on separate rows.
When Business Finance Assistant imports the entries, one journal entry will always be built from two or more lines in the table. In the example below, you can see that the first two rows of the table comprise of the first entry, and the third and fourth rows comprise of the second entry.
| Transaction ID | Journal Code | Date | Document number | Account number | Entry label |
| 1 | MO | 1/09/2022 | IMP-001 | 11100 | First journal entry - debit |
| 1 | MO | 1/09/2022 | IMP-001 | 10100 | First journal entry - credit |
| 2 | MO | 1/09/2022 | IMP-002 | 11100 | Second journal entry - debit |
| 2 | MO | 1/09/2022 | IMP-003 | 10100 | Second journal entry - credit |
Required Information in the Export
- Date of the journal entry
- Journal code for the accounting entry that the entry is recorded in
- One row per debit/credit line in each journal entry
- A Transaction ID for grouping the debit and credit lines
- Amounts formatted with or without negative sign (-) to denote whether the line is a debit or credit
- Amounts must be in base currency
- Account number of each side (debit/credit) of the entry
- Label describing the journal entry
Step 2: Edit the table headers to match the Business Finance Assistant import format
Using Google Spreadsheet, Microsoft Excel, or your favourite spreadsheet program, open the file coming from your previous accounting solution for editing.
The first row of the table must contain headers so Business Finance Assistant can identify what it contains. Each header must match one of the values below.
|
Transaction ID |
ID number of the journal entry. This is used to specify which debit and credit lines contain a journal entry during import. This number will not be stored in the transaction, as Business Finance Assistant will generate new transaction IDs upon importing, based on any already-existing transaction IDs in the system. |
|
Journal Code |
Short code of the journal this entry is to be recorded in. If the journal does not already exist in Business Finance Assistant, it will be created automatically. |
|
Date |
Date of the journal entry. Must be the same for each debit or credit line containing the same journal entry. |
|
Document number (optional) |
A reference number for the business object that this transaction is related to |
|
Account number |
A valid account number from the chart of accounts - cannot be a family account |
|
Entry label |
Less than 64 characters |
|
Amount |
In base currency. Positive for credit, negative (-) for debit - number format must not have a currency symbol included |
|
Pmt Reconc (option) |
An optional column for noting which invoice a payment is associated with. |
Step 3: Export the table to CSV file
Business Finance Assistant accepts uploads in CSV (comma-separated-value) format. The importer will correctly determine the delimiter upon upload. (comma, semicolon, or Tab)
Step 4: Import the prepared CSV file
- Choose the date format that matches the file you will upload
- Choose the file from your file system
- Click the 'Import' button
Troubleshooting error messages
The Business Finance Assistant Journal Entry Importer does many checks to make sure you don’t import accounting entries incorrectly. If ANY errors are encountered, the import is CANCELLED and you must correct the errors and try to upload again. Here are some of the error messages that may be displayed and common solutions to fixing them.
The selected file has to be a CSV file
The filename does not end in '.csv' and the importer is ignoring it.
- Solution: check that you are actually importing a CSV file. If it IS a CSV file and is missing the '.csv' at the end, add that to the file name.
Wrong header or headers missing
The headers listed in the error were not found in the imported file.
- Solution: Check that you set up the file properly.
Journal not found
The data entered on line xxx for the column ‘Journal Code’ doesn’t exist in the Business Finance Assistant account.
- Solution: Check the journals in the Business Finance Assistant account and create any that are missing. View your journals in https://businessfinanceassistant.lloydsbank.com/#journal/list
Account not found
The account in the given line and column was not found in the Chart of Accounts.
- Solution: Check to make sure that your chart of accounts matches that of the previous accounting software. Create accounts, or change account numbers as necessary until you do not encounter this error. View your chart of accounts at: https://businessfinanceassistant.lloydsbank.com/#accounting/search
Account number not valid
The account number in the given line and column could not be read.
- Solution: Check that the account number to be sure it doesn’t contain any strange characters.
Account number not initialized
Account number is required for each line in the imported file and the given line and column did not contain an account number.
- Solution: check your file to be sure every line has a value in the account column.
Number format error
The value in the ‘Amount’ column on line xxx was not a recognised number.
- Solution: Do not include currency symbols. Be sure the amount is formatted as follows: ‘1,234.56’
Date format error
The date on line xxx does not match the setting selected in the importing tool.
- Solution: Be sure you selected the correct date setting in the importing tool. If it seems correct, check the CSV to make sure the values are correct. Some spreadsheet programs manipulate date settings when exporting to CSV. Check the 'Number' formatting of this column in your spreadsheet app.
Journals are not the same within the same transaction
For the given transaction ID in the source file, a mix of accounting journals was used.
- Solution: every part of an accounting transaction must occur within the same journal. Check this transaction to be sure the same journal is specified on each part.
Dates are not the same within the same transaction
For the given line and column in the source file, a mix of dates were used.
- Solution: every part of an accounting transaction must occur on the same date. Check ID xxx to be sure the same date is specified on each part. Make sure all the dates match the format chosen in the importing tool.
Entries for accounting transaction are not balanced
The sum of debits and credits in the journal entry are not balanced.
- Solution: check the values in the ‘Amount’ column for all parts of this transaction. Adding up all the values should end up in ‘0.00’. Remember that Credits must be positive ‘1,000.00’ and Debits must be negative ‘-1,000.00’
An error occurred during the import: Column 0
You have uploaded a file that Business Finance Assistant does not recognise. Do not try to upload an XLS (Excel format) file to Business Finance Assistant.
- You must save the file as .CSV (comma-separated values) instead. If the file you uploaded is a CSV there is probably an error in the file, such as missing characters, or the file is truncated.
Accessing the import tool