Import your bank statement data

• Tim Fouracre
• Thursday, 15 January 2009
• Bank Import Tool

The bank statement import tool is the recommended method for entering your accounting data into Clear Books. 

The way it works is simple:

1. Your bank / credit card provider should let you download your statements as a file to your computer. This could be in a number of different formats (CSV, Excel, Quickbooks, Quicken, MS Money), but we accept most of them.
2. Once you have downloaded the file from your bank, you then upload it to Clear Books by selecting the appropriate bank account and making sure that it does not overlap with any previous uploads.
3. With the bank statement replicated in Clear Books, you simply go through each line of the statement, categorising the transaction so that Clear Books can enter them in to your accounts.

In this post I will go through the steps required to complete a bank statement import in more detail.

1. Download your bank statement data
2. Upload your bank statement file to Clear Books
3. Import your transactions into Clear Books

Step 1: Download your bank statement data

Log into your online banking and navigate to the page that lets you view your statements. Select the date range that you wish to download the statement for, but never include the current day as this may still have pending transactions that have yet to appear. To be safe we recommend that you do not download data from the previous three days.

There should be a link to download the statement data. If it gives you a choice of file format, then QBO is our preferred format, but other formats such as QIF, CSV, OFX should also work. Save the statement file to your computer. We recommend you create a folder on your computer to keep your historic statement files as most banks remove old statements from their websites after a few months. Although it is not essential, it may also help if you give the files a descriptive name, e.g.

mbna-paul-2009-01-06.qif

In this example, I have used the name of the credit card company, whose card it was, and the date the statement was created as part of the filename. Clear books does not require this, but it might help you identify the files in the future.

Step 2: Upload your bank statement file to Clear books

Log into Clear Books and navigate to “Money > Import Tool”  by clicking the Money tab in the menu. In the Import Tool section you will see a drop down box containing a list of all your bank accounts. Select the account you are going to be uploading a statement for. If you do not see the relevant bank account in this list then you can add it via the add bank account form on the “Money” page.

On the next page, you will need to fill out the following fields

• File – Select the file you downloaded from your local hard drive.
• File type – Select the the format of the file (this should match the 3 letter file extension, e.g. .QIF).
• Default Payment Method – If you are uploading a credit card statement, select the person who’s statement you are uploading. If they are not listed in this box, then you should add them on the “Account Holders” section of “Update Account Details” page. You can get to here by going to the Money page, and then clicking “edit details” next to the relevant bank / credit card account.

Step 3: Import your transactions into Clear Books

Once you have filled those out, press “upload”. You will then be presented with a page that gives you two options, either 1) Proceed to manage bank import or 2) Narrow Down date range. In most cases you can just skip straight to manage bank import.

You only need to narrow down the date range if you know specifically that the file you have uploaded overlaps with a previously uploaded file. The “narrow down” function simply deletes records from the imported data that are outside of the specified date range.

By now you should be at the “Manage Statement” or “Manage Bank Import” screen for the data you just uploaded. This step is used to select which bits of data should be imported into the accounts, and how they should be classified.

The top half of the page contains a list of transactions, and the bottom half of the page is a form that lets you choose what to do with the currently selected transaction. You will notice that the currently selected transaction is highlighted with a green background in the list.

You can move up and down the list by clicking on the various transactions, but they will not be entered into the system until you submit the form below.

Once you have selected a transaction, you have 4 main options:

1. Invoice – This is the most commonly used option. Use this to enter most sales or purchase invoices onto the system, and also things like staff wages.
2. Transfer – Use this to record any money transfers that you have performed between your own accounts. E.g. from your current account to savings account, or from your current account to your credit card account (when you pay a credit card statement).
3. Refund – Use this to enter any refunds on to the system.
4. Multiple – Use this if you wish to record a payment that corresponds to multiple invoices.

We will cover the above options in greater detail in a future post, but for now I will explain how the Invoice form works.

The fields you must fill out are as follows

• Name – The name of the supplier, customer or member of staff who the money is going to / from.
• Account – The relevant accounting category, e.g. for South West Trains in the example above, this will be “Travel”
• VAT Rate – The rate of VAT on the transaction, for Train fares I would select: VAT Exempt.
• Invoice Date – Both the amount and the date will have automatically been taken from the transaction on the bank statement, however you can adjust the date if you know the invoice was raised on a different date (for VAT purposes).
• Project – If you are using projects to track income / costs, select the relevant project from the drop down. This is optional.
• Invoice Number / Reference – If you are entering a supplier invoice, you should enter their invoice number in this box, otherwise you can either leave it blank.
• Description – This will have been automatically taken from the bank statement, but you may wish to add further detail in here to describe the item.

Once you have filled out those details, simply press “Create Transaction” and, if there are no errors, the system should automatically display the next transaction that needs to be entered.

If you have followed these instructions you will have successfully used the bank import to load your accounts data on to Clear Books. After you have entered a few transactions, you may notice that the system starts to learn patterns, and it will begin to suggest invoice details for you. This is where you will really save a lot of time!