The Transaction Import feature allows you to quickly generate multiple Receipt, Payment and Transfer records by uploading statements from your bank or online payment systems.
This tool can significantly reduce manual entry and administrative overhead, and we highly recommend that all users become familiar with the import process.
- Transaction Import – Overview Tutorial
- Mapping Import Columns
- Cost Code & Member Matching Options
- Importing Data from Online Payment Systems
Transaction Import – Overview Tutorial
The tutorials below provide a step-by-step guide for running a Transaction Import from a Bank Statement. These tutorials are essential for anyone planning to use this feature.
Additionally, if you need to import transactions from online payment systems like PayPal, SumUp, or GoCardless, please watch the “Importing Data from Online Payment Systems” video.
The Transaction Import feature comes in two versions: Standard and Advanced:
Standard Transaction Import
The Standard Transaction Import feature allows you to easily upload transactions from bank statements or online payment systems, creating multiple Receipt and Payment entries in one streamlined process. This highly automated feature is suitable for most organisations, and we recommend that new users begin with the Standard version.
Click the the 3-dots icon to expand (Enter full screen). Use Space bar or Enter to move slides.
Click to see the Source Data Keyword & Mapping Guide used by the Standard Transaction Import
Advanced Transaction Import
In certain cases the Advanced Transaction Import process may be more appropriate. The Advanced Transaction Import provides additional options and, significantly, allows you to manually map your source data columns to specific Clubtreasurer fields, rather than relying on the automatic mapping algorithm.
The Advanced option is recommended for the following situations:
- The Standard Import is unable to accurately identify and match your source data columns
- The first row of your source data does not contain column headings
- You are importing online payment data that requires the import to create separate Receipt/Payment batches with distinct gross amount and fee amount transactions.
Click the the 3-dots icon to expand (Enter full screen). Use Space bar or Enter to move slides.
Import Mapping Columns
When you import your data using the Advanced Transaction Import method you will need to manually ‘map’ the source columns to the “Target” columns to tell Clubtreasurer how to handle each column’s data. In some cases, the import process will automatically map the columns where it finds a name match, but in many cases you will need to manually select the appropriate value from the Target column.
👉 Note that the Standard Transaction Import method applies the similar criteria except that the column mapping is done automatically. See Standard Mapping Guide
The minimum mandatory Target columns required to import your data are:
Date | Details | Amount*
*Amount can have single or dual columns
| DATE | DETAILS | AMOUNT |
|---|---|---|
| 10-JAN-2024 | John Smith Membership Income | 25.00 |
| 11-JAN-2024 | Building Maintenance Costs | -1050.00 |
| DATE | DETAILS | PAID OUT | PAID IN |
|---|---|---|---|
| 10-JAN-2024 | John Smith Membership Income | 25.00 | |
| 11-JAN-2024 | Building Maintenance Costs | 1050.00 |
Full list of columns that can be imported
| Target Column | Column Description | |
|---|---|---|
| DATE | Required | Map this column to your bank statement’s Date column |
| DETAILS | Required | You can also use the alternative DESCRIPTION or MEMO mapping columns but you must only use one of these in your import. |
| AMOUNT or PAID-IN / CREDIT AMT and PAID-OUT / DEBIT AMT | Required | *Used where the statement has a single Amount column *Used where the statement has separate Dr/Cr Amount columns. (You must map both columns if using this option). |
| Paid/Pmt Ref | Optional | Paid-in/Payment Reference Details |
| Vouch/Inv. No. | Optional | Voucher or Invoice Number |
| Comments | Optional | Comments |
| Cost Code | Optional | You can import Receipt & Payment Cost Codes in your source data if required. Imported Cost Codes MUST start with their 4-digit codes (e.g. “4002 Membership Income“) which the import process will use to match to the correct Cost Code. If no match is found it will return the Default Cost Codes in the Import Transactions page (or be overwritten by the Cost Code Matching, if selected). |
| Events & Sub-Events | Optional | You can import Events & Sub-Events during the import process. Imported Event names must exactly match the existing Events/Sub-Events already setup in Clubtreasurer, otherwise the import will fail for that line. (Not case-sensitive) You can leave the Event field blank for row(s) that you do not want to define an Event. In this case, the system will use the Default Event during the Import Transactions process. |
| Funds | Optional | You can import Funds during the import process. Imported Funds names must exactly match the existing Funds already setup in Clubtreasurer, otherwise the import will fail for that line. (Not case-sensitive) You can leave the Fund field blank for row(s) that you do not want to define an Fund. In this case, the system will use the Default Fund during the Import Transactions process. |
| Accounts | Optional | In most cases your import batch will be for a specific Account which you select in the create Draft Rows page. However, it is possible to import different Accounts for each row if you have mixed import batches.. Imported Account names must exactly match the existing Accounts already setup in Clubtreasurer, otherwise the import will fail for that line. (Not case-sensitive) You can leave the Account field blank for row(s) that you do not want to define an Account. In this case, the system will use the Default Account during the Import Transactions process. |
| Members | Optional | You can import Members during the import process using the Members’ Reference ID (“REF ID“). Importantly. your mapped source data column must contain the REF ID as the first characters in the entry which can be followed by a space + the member name (or any data) for your own reference: Valid Entries (REFID + Name) ✔️ “1234” ✔️ “1234 Joe Bloggs” Invalid Entries: ❌ “Joe Bloggs 1234” (REF ID must be first chars) ❌ “1234JoeBloggs” (Missing space between REF ID and space) When you create your Draft Rows: The import process will attempt to match the REF ID to Active members (matches to Archived/Inactive members will return a blank entry). Do not check the Apply Member and Membership Matching box when you are importing the members. (If you do, the process will ignore the imported Member rows but will still try to match the missing member rows, if any) If you have set up Accounting Rules for Member Sections, these will also be applied for your imported members. |
Matching Options (Standard & Advanced)
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 which can dramatically speed up the import process.
Cost Code Matching
When the “Apply Cost Code 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 cannot be matched will use the default Cost Codes.
You can change all Cost Codes as before by editing rows in the Draft Transactions table.
👉 The matching algorithm is an approximation and may not always provide an accurate match in all cases. You should always review your imported rows and manually change incorrectly matched Cost Codes in the Draft Transactions table.
Member & Membership Matching
Organisations using the Membership Management application will see “Member” and “M’ship Billing Line” columns in the Draft Transactions table.
When the Apply Member & Membership Matching option is selected, the Import process will try to match Members to your imported rows based on the source data Details column.
The matching algorithm attempts to match Members and Membership Billing Lines in the following order:
Matching Process #1
Billing Line / Invoice No.
Returns Member and Billing Line/Invoice when the import process matches an existing unpaid Billing Line in the imported Details column. This is the most accurate method and we recommend asking your members to add the Billing Line/Invoice No. to their payment references. (Your members can see the Invoice No. on their invoices and/or statements)
This is the only matching process that returns the Member AND Billing Line/Invoice and is therefore the recommend process.
eg. Source Details: “Burt Bison ref123456” would match to Billing line/Invoice 123456 and return both the Member and Billing Line.
Matching Process #2
Transaction Import Reference
If no match is found in Step 1. then the import process will try to return the correct Member record by finding an exact match within the imported Details column and the Transaction Import Reference value stored against the member record.
The Transaction Import Reference is a unique ID that can be used to identify a specific member when included in the source data Details columns.
Examples include:
– Direct Debit/Faster Payments Mandate
– External Payment system reference
– Alternative member names
You can store up to 3 x Transaction Import References per member.
eg. Source Details: “Bison DD009998877” would match to member Burt Bison if he has “DD009998877” Transaction Import Reference stored in his member record.
Matching Process #3
Member Name Matching
The final matching process is used if #1 and #2 are not successful. It uses a ‘fuzzy logic’ process to find the nearest matching member name based on the Details column.
eg. Source Details: “B.Bison” would match to member Burt Bison*
*CAVEAT: The matching algorithm is an approximation and may not provide an accurate match in all cases. It is very sensitive to the quality and/or completeness of the source data – eg. the example above might also return “Bessie Bison” or “Bill Bison” as other close matches. You should always review your imported rows and manually change or remove incorrectly matched Members and Billing Lines in the Draft Transactions table.
Matching Billing Lines / Invoices
When Members are matched by Processes #2 and #3, the process will also try to select the oldest unpaid Billing Line/Invoice that matches the imported line amount, otherwise it will return “Unallocated Membership Transaction” or “Non-Membership Transaction” (based on an import process option). You can then manually update this in the Draft Transactions table and select the correct Billing Line / Invoice.
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
Learn how to import transactions from platforms like PayPal, Stripe, GoCardless, and SumUp.
The Challenge with Payment System Exports
Unlike standard bank statements, online payment systems typically provide multiple amount columns for a single transaction. A single row in your export file might contain:
- Gross Amount: The total paid by the customer.
- Fee: The processing or transaction fee.
- Net Amount: The final amount deposited into your account.
To maintain accurate records in Clubtreasurer, these should be imported as separate transaction lines so you can track both your income and your processing costs.
The Solution: Multi-Pass Importing
The most efficient way to handle these files is to run the import process twice using the same source file, mapping a different amount column each time.
- Pass 1: Map the Gross Amount to record your total income.
- Pass 2: Map the Fee column to record your expenditure.
👉 The import process will match the pairs of transactions and create a single Receipt & Payment containing separate transaction lines for the Gross Amount (receipt) and Fee (payment) amounts.
Video Tutorial: Step-by-Step Guide
Watch the video below to see how to use the import tool to split these amounts accurately and quickly.
You must use the Advanced Transaction Import for this process.
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.)