Clubtreasurer’s Transaction Import feature allows you to upload transactions from your bank statements or online payment systems to quickly create multiple Receipts & Payment transactions.
- Transaction Import – Overview Tutorial
- Mapping Import Columns
- Automatching Options
- Importing Data from Online Payment Systems
- Other stuff
Transaction Import – Overview Tutorial
This tutorial shows you the basic steps required to run a Transaction Import from a Bank Statement and is mandatory viewing for anyone wanting to use this feature. Please also see the Importing Data from On-line Payment Systems video below if you need to import transactions from on-line payment systems such as Paypal, SumUp, GoCardless, etc..
Click the the 3-dots icon to expand (Enter full screen). Use Space bar or Enter to move slides.
Mapping Import Columns
When you import your data you need to map the source columns to Clubtreasurer’s Target Columns. The import process will automatically map the columns where it finds a name match but in many cases, you will need to manually map them by selecting the appropriate value from the Target column.
The minimum mandatory columns you need to import are:
- DATE
- DETAILS (or use alt. DESCRIPTION; MEMO)
- AMOUNT (use for single amount columns) or PAID-IN/OUT AMOUNT (for separate amount columns)
| Target Column Name | Mandatory? | Description |
|---|---|---|
| DATE | Yes | Map this column to your bank statement’s Date column |
| DETAILS | Yes | Map this column to your bank statement’s Details column. You can also use the alternative DESCRIPTION or MEMO mapping columns but you must only use one of these in your import. |
| AMOUNT | Yes* | *Used where the statement has a single Amount column |
| PAID-IN AMOUNT PAID-OUT AMOUNT | Yes* | *Used where the statement has separate Dr/Cr Amount columns. (You must map both columns if using this option). |
| Paid/Pmt Ref | No | Paid-in/Payment Reference Details |
| Vouch/Inv. No. | No | Voucher or Invoice Number. |
| Comments | No | Comments |
| Cost Code | No | You can import Receipt & Payment Cost Codes in your source data if required. These must be 4-digit Cost Codes (e.g. “4002”) which the import process will try to match. If no match is found it will return the Default Cost Codes in the Import Transactions page (or be over-ridden by the Cost Code Auto-matching, if selected). |
Auto-Matching Options
As well as the Default Import values in the final Transaction Import page, you also have options that use algorithms to automatically match Cost Codes and Members that can dramatically speed up the import process.
Cost Code Auto-Matching
When the “Use Cost Code Auto-Matching?” option is selected and you click the “Create/Refresh Draft Import Rows…..” button, the import process will attempt to automatically assign Cost Codes to the imported rows based on matching row descriptions to existing historic transactions. Rows that fail auto-matching will use the default Cost Codes and will be highlighted in red.
You can change all Cost Costs as before by editing rows in the Draft Import Rows table.
CAVEAT: The matching algorithm is an approximation and may not always provide an accurate match in all cases. You should therefore review your imported rows and manually change incorrectly matched Cost Codes in the Draft Import Rows table below.
Members & Membership Auto-Matching
Organisations using the Membership Management application will see “Member” and “M’ship Billing Line” columns in the Draft Import Rows table.
When the Match Members & Membership option is selected, the Import process will try to match Members to your imported rows based on the Details column in the source data.
When Members are matched, the process will also select the oldest unpaid Billing Line if the amount matches, otherwise it will select “UNALLOCATED MEMBERSHIP RECEIPT”
CAVEAT: The matching algorithm is an approximation and may not provide an accurate match in all cases. You should always review your imported rows and manually change or remove incorrectly matched Members and Billing Lines in the table below.
TIP: If you need to make your Member matching more accurate you can apply a Transaction Import Reference to your Members. The Import Transactions process uses this reference to more accurately match imported transaction lines to the correct Member record by trying to find an exact match with the imported Details line and value in this box. Examples:
– Direct Debit/Faster Payments Mandate
– External Payment system reference
– Alternative member names
For more details on importing Membership payments and Transaction Import Reference please see Managing Billing & Payments – Importing Membership Payments #4 tutorial for more details.
Importing Data from Online Payment Systems
This is a demo of how to import transactions from online payment systems such as PayPal, GoCardless, SumUp.
Unlike bank statements, online payment system downloads will often have multiple amount columns for each transaction line, eg. a gross amount; a transaction or processing fee; and a net amount. When you import these into Clubtreasurer you will usually want to import these as separate transaction lines.
The following tutorial video shows you how to easily do this by running the import process two (or more) times.
Import Update: When mapping source columns we now recommend also mapping your TRANSACTION_ID (or equivalent) to Vouch/Inv No. column in Clubtreasurer. The TRANSACTION_ID is a unique reference number for each imported line and will ensure the imported transactions are accurately matched together. Note this is highlighted in the video tutorial but is not demonstrated.
Other Stuff
How to split & copy lines during Import Transaction
This quick video shows you how to split/copy and edit lines during the Import Transaction process (1:19)