QuickBooks® Desktop
Control Panel > Money > Admin Functions
Note: To set up QuickBooks® integration, navigate to Control Panel > Money > Setup > Money Options
ClubExpress includes the ability to export financial data from your website in a format that can be imported into QuickBooks®. The QuickBooks® Desktop exports are in Intuit Interchange Format (.IIF), which is supported in QuickBooks® for Windows. For instructions on using QuickBooks® Online, see QuickBooks® Online.
Before you start exporting transactions, credits and payments from your ClubExpress website and importing this data into QuickBooks®, we strongly suggest that you experiment with a copy of your main “company” file or with an empty file. A single import of just one month of data could make dozens of changes to multiple accounts; you should be absolutely sure that everything is properly configured and that data is imported as you are expecting. You have many 3rd party options for maintaining backup copies of your data, including ChronoBooks and Rewind Backups. If you are switching from QuickBooks® Desktop to QuickBooks® Online you should perform the same backup. Before your first export you must also configure the QuickBooks® account names for your bank accounts and financial accounts.
Activation: The monthly fee for this module is $20/month.
Note: If you are in Setup or Trial mode, the activation fee will be charged when you go live.
QuickBooks: If QuickBooks® is enabled on the Control Panel – Money – Setup –Money Options page, you will see QuickBooks® Export on the Control Panel. Select it and you will see a screen similar to the following:
To see previous exports, select the type and time period and click Search. You will see a list of matching exports.
New Export button. You will see the following popup window:
For a description of the types of QuickBooks® data, see The Five Types of QuickBooks® Data.
Select the type of export from one of the following choices:
- Club Transactions
- Members and Non-Members (QuickBooks® “Customers”)
- User Credits
- User Payments (including discount coupons)
- User Transactions
Specify the date range you want to export then click the Export button. Your browser will prompt you to save the .IIF file (Intuit Interchange Format) to your local hard disk, from where you can import it into QuickBooks®. The name of the file will describe the type of export and date range specified.
The date range is queried based on the date and time that a transaction, payment, credit or fee was actually recorded. This may be different from the date entered by the user or administrator; ClubExpress allows you to enter any date when you are recording an item (for example, to record that a check was received earlier than when the database was updated.) In the export, the date entered by the user or administrator will be in the memo field.
Note: ClubExpress and QuickBooks® do not stop you from importing the same transactions into QuickBooks® more than once. You need to be very careful to import only discrete date ranges and export types into your active club or association QuickBooks® file.
Note also that you cannot specify today’s date as the Through Date. The reason is that you might miss some transactions or payments if they occur today but after the export was processed.
The Five Types of QuickBooks® Data
When users sign up for membership or renew an existing membership, register for an event, make a donation, purchase merchandise, incur a miscellaneous charge, etc., a user transaction is generated.
A user transaction represents a commitment to pay in return for some benefit. So from the club’s standpoint, one side of a user transaction is an Accounts Receivable. The other side of the user transaction represents a positive balance in one or more Income accounts. Here’s an example:
Description | Account Type | Debit | Credit |
---|---|---|---|
Membership Dues Club T-shirt Donation |
Income | $40.00 $12.00 $5.00 |
|
Accounts Receivable | Current Asset | $57.00 |
The example membership sign-up transaction actually represents revenue in three Income accounts (therefore a Credit): membership dues, merchandise sales, and donations. The other side of the transaction is an increase in Accounts Receivable that is a current asset account (therefore, a Debit.)
In QuickBooks®, you will likely have separate income accounts for each type of income, allowing you to track income from different sources.
Similarly, ClubExpress allows you to organize different types of income into financial accounts and you can map each financial account into a corresponding QuickBooks® account.
Exporting user transactions to QuickBooks® generates credits in the appropriate income accounts and debits in your Accounts Receivable account. Members and non-members are treated as “Customers” in QuickBooks®; they are the people who purchase the products and services provided by your club or association. When you import or a user transaction, both versions of QuickBooks® also generate an open Invoice for the customer.
When users are credited for an event they were not able to attend or because they volunteered, they are given a User Credit.
User Credits represent a loss of revenue to the club and, more specifically, to an income category. An event credit is a reduction in revenue for that event, while a membership credit is a reduction in membership revenue.
One way of looking at a credit is to consider it as a future obligation or liability incurred by the club—perhaps an Account Payable. But QuickBooks® does not allow Accounts Payable to be linked to customers, only to vendors. Instead, QuickBooks® handles credits as a reduction in Accounts Receivable, or the money owed by a customer. Here’s an example:
Description | Account Type | Debit | Credit |
---|---|---|---|
Membership Revenue | Income | $10.00 | |
Accounts Receivable | Current Asset | $10.00 |
When a membership credit of $10.00 is issued, the membership income account is reduced by this amount (Debit) and the Accounts Receivable account is reduced by the same amount (Credit to Current Asset.)
In ClubExpress, you must specify a financial account when entering a credit and this financial account will map to an Income account in QuickBooks®.
User Credits are used up in a number of ways:
- Applied as part of a payment (described below).
- Refunded to the user. We do not export refunds since the credit is already in QuickBooks®. Instead, we assume that the credit is offset by a check cut by the club’s treasurer in QuickBooks® to the user.
- Deleted. We cannot export deleted credits since there is nothing to export. Instead, you need to create a transaction in QuickBooks® to reverse the deleted credit.
- Waived by the user. We export this as a reversal of the transaction above, crediting the Income account and debiting Accounts Receivable. Effectively, the club recovered the “loss” of the credit.
When users make a payment, they are satisfying an open Receivable. A user payment causes a balance to be transferred from one asset account (Accounts Receivable) to another asset account (Bank or Undeposited Funds). Here’s an example:
Description | Account Type | Debit | Credit |
---|---|---|---|
Accounts Receivable | Current Asset | $57.00 | |
Bank or Undeposited Funds | Asset | $57.00 |
Accounts Receivable had a balance of $57.00. This is offset by a credit (reduction) so the balance is now zero. Bank now has a balance of $57.00 because the payment represents money that was actually received.
In QuickBooks®, you will have a separate account for each bank account. Similarly, ClubExpress allows you to map each bank account in the system to a corresponding QuickBooks® bank account. QuickBooks® also supports the concept of Undeposited Funds, money which has been received but which may not yet physically be in your bank account.
Exporting user payments to QBD generates Payment transactions, credit balances in Accounts Receivable and a debit balance in the appropriate bank account or in Undeposited Funds. Note that the customer’s open invoices are NOT flagged as paid. There is no way for us to automatically match them so the treasurer will need to handle this task manually. (Note also that the customer/member will have a zero balance if he or she is up-to-date on payments.)
In QuickBooks®, different types of payments are handled in different ways:
- If the collected funds were received by ClubExpress (using the built-in merchant account or if you are configured for checks to be sent to us) and later remitted to you, you have the choice of showing the funds in a bank account or in Undeposited Funds.
- If the collected funds were received directly by your club (using your own merchant account, cash, a check to the club, or processed through a separate system), the export places the funds in Undeposited Funds. When you actually make the deposit, you can generate a transaction to move these funds from Undeposited Funds to the appropriate bank account(s).
- If the “payment” was recorded as a Comp or Write-Off (Bad Debt), Accounts Receivable is credited and the specified Expense Account for Bad Debts/Comps is debited for this amount. Writing off or comp’ing an expected payment is an expense to the club.
Example:
Description | Account Type | Debit | Credit |
---|---|---|---|
Accounts Receivable | Current Asset | $57.00 | |
Bad Debts / Comps | Expense | $57.00 |
In ClubExpress, if a payment includes an applied credit, we record a deposit for the gross amount less the credit amount because it was separately exported.
Example:
Amount owed: $57.00
Credit applied: $10.00 – credit memo separately exported
Amount actually paid: $47.00
Description | Account Type | Debit | Credit |
---|---|---|---|
Accounts Receivable (AR) | Current Asset | $57.00 | |
Credit Memo applied to AR | Current Asset | $10.00 | |
Payment | Asset | $47.00 |
Note that the payment type and a reference number will be shown where appropriate. In most cases, there is only one payment type per transaction. For checks, the record will say “Check” and will include the check number. For credit cards, the record will say “Credit Card” and include the last 4 digits. If the payment includes multiple payment methods (for example, credit card and check, or check and discount or credit) the payment type is “Multiple Payment Methods”; there is no way to include the details of each payment method.
When we charge a fee, the funds are deducted directly from your bank account using an ACH. Similarly, when we issue a credit, the funds are deposited directly into your bank account using an ACH.
Club Transactions are handled using a Debit to an Expense account (the expense increases) offset by a Credit to the primary bank account (the amount on deposit is lowered.) Here’s an example:
Description | Account Type | Debit | Credit |
---|---|---|---|
Website Hosting | Expense | $22.40 | |
Bank | Asset | $22.40 |
ClubExpress allows you to specify your QuickBooks® account name for each category of expense, setup fees, monthly hosting fees, miscellaneous charges and credits, postage, etc.
For QBD users, selecting this option exports the member and non-member databases. When this information is imported into QuickBooks®, it becomes “Customer” data. QuickBooks® will attempt to match incoming records against the data already in the system; if a match is made, the other data (address, etc.) will be updated. If no match is made, a new customer record is added.
Note: QuickBooks® does not allow duplicate names in its various databases (Customer, Vendor, etc.) If you get a QuickBooks®error importing this file, it may be because one of your members or non-members is also listed in your Vendor database; you will need to change one of the names so that it’s different. The same problem might occur if you import a transaction, payment, or credit file listing a customer who is not yet in the QuickBooks® Customer database but who is in the Vendor database. Again, change one of the names (usually the Vendor) so that it’s different.
Configuration Notes
QuickBooks® account names are updated in three places within ClubExpress. Be sure that all three are correctly filled out:
- Control Panel – Money – Setup – Bank Accounts
- Control Panel – Money – Setup – Financial Accounts
- Control Panel – Money – Setup – Money Options
In QBD, transaction, payment and credit data is exported using IIF files – the Intuit Interchange Format. This is a tab-delimited file format where account names are spelled out. QuickBooks® looks for an account in its chart of accounts with the exact spelling shown in the IIF file.
When you are entering QuickBooks® account names into ClubExpress, be sure that the spelling is exact. If it’s not, QuickBooks® will create a new account with the spelling from the IIF file and you will have to manually move each transaction to the correct account.
When specifying account names in ClubExpress, do not include an account number.
Example: If the account is shown as:
4110 • Membership Dues
in the QuickBooks® Chart of Accounts, specify “Membership Dues” as the QuickBooks® account name in ClubExpress.
If your account is a sub-account, specify the path to the sub-account with a colon delimiter.
Example: If the account is shown as:
4000 • Income
4050 • Donations
in the QuickBooks® Chart of Accounts, specify “Income:Donations” as the QuickBooks® account name in ClubExpress. Note that there are no spaces around the colon. Note also that the ClubExpress account name field is limited to 50 characters. If your QuickBooks® account names are longer than this, including sub-accounts, they may need to be shortened to fit.
Note: For financial accounts, you will need to add Account Names for both the financial accounts you've created as well as the master financial accounts.
QuickBooks® allows you to store and match customer names in one of two formats:
- FirstName <Middle Initial>. LastName
- LastName, FirstName <Middle Initial>.
Be sure to select the same format in both ClubExpress and QuickBooks®, to ensure that imported transactions and payments are linked to the correct customer records.
Note that QBD does not provide an update function for customer data. When you run the Member and Non-Member export, and then import the customer file into QuickBooks®, the program will match as described above. For each matching record, the data in the imported customer file will completely replace any customer data previously stored in the matched records in QuickBooks®. For this reason, be sure to do your updates in your ClubExpress website, otherwise they may be lost when you export from ClubExpress and import into QuickBooks®.
ClubExpress will export QBD Item Names if they are defined. Whenever you specify a charge in ClubExpress and link it to a QuickBooks® account, you can also specify the Item Name.
For each type of transaction, ClubExpress gives you a place to define the QuickBooks® Item Name, and this can be an Item or Service. When the import takes place, we will match on the name itself.
If you have not defined these item names in your ClubExpress configuration, use the QuickBooks® default item name associated with the financial account linked to the transaction.
If you enter an item name at the transaction or item level and it does not match the QuickBooks® Item Name exactly, a new item will be created for you and you will need to manually correct it.
ClubExpress will export class information if you are using QuickBooks® classes. Classes are used to categorize income and expenses in different ways. To enable them, follow these steps in QBD:
- Edit – Preferences
- Select Accounting in the left-side list
- Select the Company Preferences tab
- Check the “Enable class tracking” box
- Click OK
In ClubExpress, check “Use Class Tracking” on the Control Panel – Money Options page. When you do this, the Financial Account screens in ClubExpress also allow you to specify a class for each financial account. Classes are exported only for Income and Expense accounts.
Even though QuickBooks® allows you to specify a class for Accounts Receivable (Invoices and Credit Memos), ClubExpress will not export a class value for Accounts Receivable. The reason is that a user transaction or user credit might affect multiple Income accounts, each of which could be in different classes. So we cannot definitively assign an Accounts Receivable to a single class. But if you want classes assigned to the Accounts Receivable lines, you can always add them manually afterwards.
QuickBooks® requires that a customer record be defined before a payment can be recorded for that customer. But when you are first starting to use the QuickBooks® export feature, you may export user payments that do not have a corresponding user transaction record.
In this case, QBD will add the username to its Other Names list instead of the Customer list. The Other Names list can be found on the Lists menu. Other Names can be re-categorized as Customers by editing the name and clicking the Change Type… button.
If your QuickBooks® company file is configured to enable sales tax, be sure to check the box "Company file is configured to use Sales Tax". This ensures the Sales Tax is passed into the exported file.
In QBD, if this flag is set, ClubExpress will send an AUTOSTAX line with each user transaction, even the ones where sales tax is not applied (membership signup and renewal, event registration, donation, etc.) Where sales tax is applied (storefront transactions), the AUTOSTAX line will contain the sales tax amount.
If this flag is not set, sales tax is exported as a transaction line item, with the actual sales tax account specified in the split of funds.
ClubExpress does not currently export QuickBooks®products (such as, in the Storefront.) The reason is that QuickBooks® does not provide a way to update just selected fields in a product record. If we provided such an export and you then imported it into QuickBooks, you would find that much of the data that QuickBooks® stores for each product would be lost because it has no analog in ClubExpress.
An undocumented option in ClubExpress allows the system to export customers using the company/organization name instead of the individual member name. Contact us to enable this option.