Importing Bank Statements

Most banks provide a facility for their customers to download bank statements as QIF or OFX files.

MoneyWorks allows you to import these bank statements1, and can automatically code them as they are imported. It provides an easy way to preview the transactions, allowing you to modify the coding, or apply payments to outstanding invoices. You can also elect to omit transactions, or have them marked as being ready for reconciliation.

In Australia and New Zealand, you can also use Bank Feeds to fetch bank transactions automatically.

Note: Bank statements should be downloaded from your bank as QIF or OFX files. If your bank does not support these formats, they probably support simple text files such as CSV or tab delimited. These can also be imported into MoneyWorks using the standard Import Transactions command — see Importing into Other Files. However you may have to remove extraneous information (such as account number and opening balances) from the downloaded file.

To import a previously downloaded bank statement

  1. Choose Command>Load Bank Statement

The Statement Coding window will open.

This allows you to specify the bank account that you are importing, as well as other details pertinent to the import.

Bank: The MoneyWorks bank account into which you want to import the bank statement transactions

Default Income A/c The account code to use for any deposits that cannot be coded using one of the auto-allocation rules. It is good practice to use a special account for this, such as a suspense account or an “Unallocated” account. This makes finding unallocated transactions much easier.

Default Expense A/c The account code to use for any withdrawals that cannot be coded using one of the auto-allocation rules. Again it is good practice to use an “Unallocated” account code (this can be the same as that for deposits)

Source The kind of statement file to import. QIF/QBO/OFX to import Quicken interchange file or old-style Quickbooks OFX files provided by most banks. OFX-XML to import modern OFX files (in actual fact if you choose the wrong one here, MoneyWorks will work out the file type for you). Generic CSV will import a csv or tab delimited file, while Bank Feeds will fetch statements using Yodlee (after setting up a feed subscription and linking accounts). 2

Exclude items before: Any transactions in the file being imported that are dated before this date will be excluded from the import. This defaults to the date of the last transaction in the prior statement import for this bank.

And after: Any transactions in the file being imported that are dated after this date will be excluded from the import

Auto-Reconcile: If set, each transaction will be compared against existing transactions, and if found to already exist in MoneyWorks will be marked to be omitted. New transactions will be marked as being reconciled.

Swap Payee and Memo: Setting this swaps the Payee and Memo fields around, as some banks get it around the wrong way.

  1. Choose from the Bank pop-up menu the bank account for which you are loading the statement

The transaction information you have downloaded from the bank refers to one bank account. You need to indicate which one it is.

  1. If necessary, set the Default Income and Default Expense accounts

These will be remembered from your last bank statement import, but will need to be set the first time you import a bank statement.

  1. Set the cut-off dates if required

Transactions on the bank statement that are outside of the date range will be ignored. This allows you to download your bank statements and not worry about overlapping dates. When the import is accepted, the before date gets transferred to the after date, ready for the next import.

  1. If you want to have the transactions automatically marked as being reconciled, set the Auto-Reconcile option

Transactions on the bank statement that can be identified as being already in MoneyWorks will be marked to be omitted, with the existing transaction marked as reconciled; other transactions will be marked as reconciled.

  1. To import the file, click the Load Statement File button

The standard File Open dialog window will be displayed. Use this to navigate to and open your downloaded bank statement file.

Any transactions inside the nominated date range are imported and listed. Transactions are allocated to the specified default accounts, unless an applicable auto-allocation rule was found, in which case the allocation is made on the basis of the rule. This is shown in the Status column.

  1. Having made your allocations (see below), click Accept

The transactions will be created with the specified allocations.

1  MoneyWorks supports QIF, OFX (SGML and XML), QBO, QFX formats, the most common ones used by banks.

2  This menu can also be augmented with custom importing scripts to import any format you wish, including CSV-based formats (in fact the OFX-XML importer is implemented using an open-source mwscript).