Bank statements provided as a CSV file can be imported, creating sales receipts and (if you want to) automatically allocating them. The file formats differ from bank to bank, so Define Import is where you map the data to Horizon's settings.
Banking Import Configuration Set-up
- In the Set-up menu, click Company. The Company Set-up window opens.
- Click the Bank Details tab.
- In the list on the left, click the bank you want to set up.
- Click Define Import.
- Complete the boxes in the groups:
- Click OK.
CSV File Definition
For each data field, type the number of the column in the CSV file. This is the primary method for matching customers to transactions.
Note: The column references are not zero-based. Use a 0 if you do not want to import data or match using this information. Mandatory columns are displayed in Yellow.
- Date – transaction date.
- Description – description of the transaction. This can be searched to match the transaction to a trader or trader's bank account.
- Amount – transaction amount.
- Trader code – from the trader card.
- Sort code – sort code of the trader. Can be supplied by banks as six digits, or separated by dashes, e.g. nnnnnn or nn-nn-nn. You can use whichever format you want in Horizon, but you should be consistent.
- Account No – bank account number of the trader. Account numbers are eight digits long.
- Date format – select a date format from the list. They differ according to the order of the year, month and date, if three-letter month abbreviation is used and whether two or four digits are used to express the year.
Supported date formats:
- Date separator – the separator used between date format elements.
Note: This cannot be a space. You must type a character such as / or -.
- Header row – when selected, the first line of the file is ignored.
Receipt Creation
Sales Receipts are created according to these settings.
- Default payment type – select from:
- Bank transfer
- Cash
- Cheque
- Credit card
- Debit card
- Direct debit
- Discount / adjustment
- Internet
- Purchasing card
- Standing order
- Unknown
- Voucher.
- Allocate oldest first based on due date – Allocation is performed to the oldest transaction first. When selected, the due date is used. When cleared, the tax point is used.
- Default import to automatically allocate – There is an option to automatically allocate within the Banking Import window. When selected, that option is selected when you open the window. When cleared, the option is cleared.
- Exclude credit values in allocation – When selected, credit notes, receipts and negative invoices (transactions with negative totals) will be ignored by the allocation routine.
- Create missing banks – Bank account numbers and sort codes and a customer's trader code are typical information included in an import file. If a customer is matched, but the bank account number and sort code are missing from their Bank Details, the details from the import file can be used to create a new bank in Trader > Bank Details. When selected, banks are created, when cleared, they are not and feedback is given by the import routine.
Additional Trader Matching Options
The description field can contain information which can be used to match a transaction to a trader. This is the secondary method of matching. These settings change what is searched for within the description.
Note: These settings are used if no matches can be made using the primary method.
- Trader code – Trader code set on the Trader card. This is not case-sensitive.
- Sort code and account number on a linked bank – the sort code and account number can be searched for anywhere in the description. They must not have spaces between the numbers, but sort codes can have dashes, e.g. nn-nn-nn, if that's the format you have chosen to use.
- Sales invoice (SINV) number – often used as a reference by customers, that is then included in the description. The number must start with SINV and have no spaces.
