This article describes step-by-step how to Import a Bank Statement into the Cashbook Utility.
| Important Note |
|---|
The Cashbook Utility is able to import bank statements in two formats:
Please do not open the downloaded statement in Microsoft Excel and then save it from within Excel, as Excel tends to modify dates and numeric value formats when saving to the CSV format. This will cause the import to fail. |
| Important Note for ABSA clients |
|---|
| In order to down-load your statement in CSV format you need to make some changes to your Internet Explorer's settings. See StateDownloadFixXPSP2.doc for details. |
| Important Note for Standard Bank clients |
|---|
| If you use Standard Bank online banking for business, you need to download the "Single Account (CCYYMMDD - 100 bytes)" file. Then select "Business (CSV)" from the Bank Statement Layout dropdown on the import screen. |
Steps
- Select Import Statement off the Statement menu.
- The "Import Bank Statement" Dialog appears.
- Change the Import settings as needed (See below for details on each setting).
- Click the "Browse..." button and locate the downloaded bank statement. Please contact your bank for details on downloading a bank statement.
- Click the "Import" button.
- An "Import Complete" message will appear once the import is complete.
- Click the "Close" button.
- The imported transactions will now be displayed in the Bank Statement Grid.
Import Settings
Bank Statement Layout
This setting specifies which bank statement format to expect when importing the transactions from the specified file.
In most cases the "Microsoft Money (OFX)" layout will be used. If your bank does not provide your statement in this format make use of one of the other available layouts.
Use Custom Layout
Click the "Use Custom Layout" check box if you want the Cashbook Utility to import transactions from a custom layout file.
This feature allows for transactions to be imported from third-party programs.
You could for example capture transactions using Microsoft Excel and then import them into the Cashbook Utility where the Auto Allocation feature will handle the allocation of account codes, etc.
See Import Custom Bank Statement for more information.
Reference Prefix
The Cashbook Utility allows a Prefix of up 5 characters to be specified for the reference. The remaining characters (Pastel allows for 8 characters in the reference) will be filled with a number range starting at 001 and ending at 999. This will provide a unique reference range of your choice for each batch of transactions.
You can use dd and/or mm as special characters in the prefix to have the application set the prefix to today's day and or month values. E.g. setting the prefix to ddmm will result in a reference prefix of 1304 if today's date is the 13th of April.
You can also append # characters to the prefix to specify how many digets the reference number should have. E.g. ABC#### will result in a reference number ABC0001, ABC0002, etc. You can combile the date characters and the diget characters to create a running reference for a day. E.g.: ddmm#### will result in 13040001, 13040002, etc if today's date is the 13th of April.
To add a Reference Prefix select one of the available options. These options are:
- No Prefix
- General
- Payments and Receipts
No Prefix
If you choose not to add a prefix you will be able to enter a reference for each transaction after the import is complete.
If you do not provide a reference the Cashbook Utility will automatically add a reference when sending the transactions to Pastel. The reference will be 8 characters in length. Payments will start with P and Receipts with R and the balance of the 8 character filled with a serial number between 0000001 and 9999999.
General
One prefix will be assigned to both Payments and Receipts.
Example: If you specify a prefix of "PG10/" the Cashbook Utility will generate
the reference as follows:
"PG10/001"
"PG10/002"
"PG10/003"
"PG10/004"
"..."
Payments and Receipts
Payments and another Receipts will be assigned their own prefix.
Example: If you specify a prefix of "P.10/" for Payments and R.10/ for Receipts
the Cashbook Utility will generate the references as follows:
Payments
"P.10/001"
"P.10/002"
"P.10/003"
"P.10/004"
"..."
Receipts
"R.10/001"
"R.10/002"
"R.10/003"
"R.10/004"
"..."
Defaults Tab
Duplicates
Selecting "Ignore" will cause the Cashbook Utility to not import a transaction that seems to be a duplicate of a transaction already imported. Selecting "Prompt" will cause the Cashbook Utility to ask you whether or not to import a transaction.
Duplicate Test: The Cashbook Utility compares the transaction Date, Amount and Description to determine if a transaction is already imported. Only transactions imported before the statement being imported are check as there are cases where identical transactions could exist on a statement.
Map Customers
Click the "Map Customers" check box if you want the Cashbook Utility to search the transaction description for a Pastel Customer Account Number and automatically allocate the transaction to the Customer's account.
For this feature to work the Account Number must be the only text in the description or it must have a space before and after it.
Example 1:
The Transaction description contains only the account number:
"ABC001"
Example 2:
The Transaction description contains the account number and other text:
"ABC001 INV9876"
"PMT ABC001 INV9876"
Map Suppliers
Click the "Map Suppliers" check box if you want the Cashbook Utility to search the transaction description for a Pastel Supplier Account Number and automatically allocate the transaction to the Supplier's account.
For this feature to work the Account Number must be the only text in the description or it must have a space before and after it.
Example 1:
The Transaction description contains only the account number:
"ABC001"
Example 2:
The Transaction description contains the account number and other text:
"ABC001 INV9876"
"PMT ABC001 INV9876"