To import transactions from a bank statement file using the Universal Input Sheet (UIS):
1.Download the transactions from your online banking facility as a CSV file (ensure that the first row of data on the CSV file is either the header of the transactions, or the first transaction by deleting any other data above if required).
2.Select Transaction>Universal Input Sheet>Import from:CSV File (Alternatively, you can copy the transactions from a spreadsheet and select Transaction>Universal Input Sheet>Import from:Clipboard)
3.The import dialog opens prompting you to select the CSV file; locate the file and select Open.
4.You are then presented with an Import screen; make sure Bank statement options is selected.
5.If the first row of your CSV file contains the header of the transactions, tick First row of data contains column headers.
6.You are prompted to specify the type of data corresponding to each of the columns of your bank statement, by selecting from the list above each of the columns. Select the relevant data type from each list; generally speaking you will only need to import the following types of data from your statement:
•Date
•Description/Details
•Payment
•Receipt
7.For any other columns in your bank statement that are not to be imported select Not imported.
8.Once you have specified the columns, select OK.
9.The data then appears on the UIS; by default the data in the Primary account column is Current Account, but you can change this directly on the UIS if required. This can be changed for the entire column, by selecting the column, right-clicking and selecting Set, then selecting the account you wish to change to.
10.On the UIS you need to enter data in the Analysis account column; this should be the account for the other side of the double entry for each bank transaction. To set the analysis account based on previous similar transactions, select a range of cells or the entire analysis account column, right click and choose AutoComplete from the pop-up menu (or hold down the Ctrl key and double click on a single cell). For more details on AutoComplete see AutoComplete below.
11.If any VAT needs to be accounted for, this can be entered in the VAT column, and the Analysis column will re-calculate accordingly to the net amount.
12.Alternatively, steps 6. and 7. can be bypassed by arranging the data directly on the CSV file/spreadsheet. The transactions in your CSV file/spreadsheet should be laid out in the same way as shown in the screenshot in Step 9. You can also populate the data required in steps 10. and 11. directly on the CSV file/spreadsheet. You can then either import the CSV file or copy the data from the spreadsheet and paste directly onto the UIS.
13.Once you are happy with the data to be imported, select Post.
Quotation marks in source data
Double quotation marks (") may appear if importing a CSV file which has text containing commas, e.g. Sky Mobile, UK. This is due to the format of the source data from the bank.
This can be overcome by cleaning the data in Excel using the CLEAN function. This can be done by entering the formula =CLEAN(Text) in a separate column, where Text is the data to be be cleaned:
When importing the file, the column with the cleaned data will also be imported, which should appear without quotation marks.
AutoComplete
As mentioned in step 10. you can use the AutoComplete function to populate the Analysis account column if there is data in the Details field that is the same as transactions previously posted.
The way AutoComplete works is that if a transaction has been posted before which contains a specific word in the beginning of the details field, clicking on AutoComplete in the Analysis account field will recall the same Analysis account used for the original transaction.
For example, if a previous transaction has the word Shop at the beginning of the Details field, and the Analysis account is Supplier: Tesco, then the next time a transaction is entered with the word Shop at the beginning of the Details field, selecting AutoComplete in the Analysis account field will recall Supplier: Tesco.
For the software to recognise the link, the word Shop must be at the beginning of the Details field.
The word does not need to be in isolation. For example, if a transaction contained the text Shop 123 in the beginning of the Details field, AutoComplete would recognise the Analysis account as being associated with the word Shop.
If AutoComplete does not recognise text from previous transactions, this is probably due to a slight difference between the source data and the previously entered text. This can sometimes be due to a change in formatting in the source data from the bank. For example, there may be hidden quotation marks in the source data. See Quotation marks in source data above for details on overcoming this.
