How to build an import file for unsupported card processors

Wave provides integrated credit card processing, giving you an easy and convenient way to allow your customers to pay their invoices by credit card, and automatically handling all the ensuing bookkeeping steps.

If you are unable to use Wave's integrated credit card processing, however, or simply prefer to use another processor, you will still want to record your transactions in Wave. Read on to learn the best ways to do this.

How to enter card payments manually

Before we look at speeding up your bookkeeping of card payments from unsupported payment processors using imports, it's worth taking a moment to walk through how you bookkeep card transactions manually. This will help you understand the processes that you are about to streamline with imports.

Bookkeeping Card transactions in your Checking Account

The simplest way to record your card transactions is just to post the gross payment amounts and the merchant fees as income and expense transactions in your Checking (or other main bank) account.

Let's assume you have received a card payment for $300, and that the merchant fees are $9. You could enter this simply as follows:

Income: $300 of Sales - paid into Checking Account

Expense: $9 of Merchant Fees - paid from Checking Account

Deposit: $300 of Sales - paid into Checking Account

Withdrawal: $9 of Merchant Fees - paid from Checking Account

When your card processor pays you, they will pay you the net amount of $291. You already have recognized this amount, however ($300 - $9 - $291), so there is no need to enter it again from your bank statement. (If you have connected your bank account to update Wave automatically, simply delete the $291 deposit transaction from Wave when it syncs up from your bank.)

This process works fine if you only have a few transactions, but if you have many transactions it can become confusing to have all the gross payment amounts and merchant fee deductions associated to your bank, particularly as none of these amounts will ever appear on your bank statement. What appears is the net amount, after deducting merchant fees from gross processing amounts. If this is becoming an issue for you, you may want to consider bookkeeping your card transactions in an 'Undeposited Funds' account.

Bookkeeping Card transactions in an Undeposited Funds Account

An 'Undeposited Funds' account is a notional or imaginary account that you use to capture the detail of multi-part transactions, in such a way that only the true net amounts ever appear in your bookkeeping for your real, Checking account (or whatever you call your main 'real' bank account).

You can read in detail here about using an Undeposited Funds account to bookkeep credit card receipts, fees and net deposits. In summary, however, using the same transaction detailed above, you would see:

Income: $300 of Sales - paid into Undeposited Funds

Expense: $9 of Merchant Fees - paid from Undeposited Funds

Transfer: $291 from Undeposited Funds to Checking.

Deposit: $300 of Sales - paid into Undeposited Funds

Withdrawal: $9 of Merchant Fees - paid from Undeposited Funds

Transfer: $291 from Undeposited Funds to Checking.

Importing Card Transactions Data

The following examples assume that you are bookkeeping transactions simply in your Checking account.  Follow along, then view the final section at the end of this document to learn how you would format a file to import into an 'Undeposited Funds' account.

Wave provides the ability to Upload a bank statement into Transactions. While this is designed - as the name suggests - for uploading bank statements when you are unable to, or do not wish to, connect your bank for automatic synchronization, there is nothing to prevent you using it for other kinds of import - like credit card transactions!

Step 1 - Export Data from your Card Processor

Each card processor has its own series of steps to create an export file, so you will need to explore to find how to do this with your card processor. Whatever the steps, the outcome you are looking for is a file (or files) that lists transactions with gross payment amount, merchant fee, and net amounts that your card processor has transferred to your bank. You will most likely be able to extract this data as a .CSV file ("comma-separated values"), which is a format spreadsheet, database and text editor programs can read.

Step 2 - Manipulate the Data into the right format for Wave

Wave supports two formats for .CSV data: a 'three-column' import where deposits and withdrawals from your bank are listed in the same column, and a 'four-column' import where deposits and withdrawals are listed in separate columns.  The four-column format is better for our purposes, as it better separates the column for income/refunds from the column for merchant fees/rebate of merchant fees.

This article can't go into teaching spreadsheet skills, so depending upon your existing skill level you may need to manipulate your data manually, or you may be able to create some formulas or even macros to organize the data for you.  Either way, you want to end up with a file that looks like this (assuming you are using Excel):

As you can see, the file consists of a row with headings at the top, and then individual rows of transactions. (The headings can be named whatever you want, but please keep them to a single word each, with just simple text characters.) The first column contains transaction dates; then descriptions; then the gross amounts (deposits / income) and the fees (withdrawals / expenses). The fees are shown as positive numbers; a negative number in this field would mean a rebate of fees.  'Gross' and 'Fee' amounts do not appear on the same line.

Note that while your spreadsheet displays .CSV files just like native files, when you save a .CSV file it is really just text.  If you open your .CSV file in a text editor, like TextEdit on the Mac or Notepad on a PC, it should look like this:

Tip: If you are ever having trouble uploading a .CSV file to Wave, try opening it in a text editor program to check that no extraneous data. If you are still having trouble, refer to this additional help article.

 Step 3 - Import the Data into Wave

After you have prepared and saved your data file, it is time to import it into Wave.  Log in to your Wave Account, and go to the Transactions page. Click Upload a Bank Statement. Now choose the file you have prepared, and select the Payment Account you wish to import the transactions for.

After you have prepared and saved your data file, it is time to import it into Wave. Log in to your Wave Account, and go to the Transactions page. Click on the arrow to the right of Add Transaction and select Upload a Bank Statement. Now choose the file you have prepared, and select the Payment Account you wish to import the transactions for.

 Step through the import wizard, confirming the Date, Deposit and Description fields, the Upload.

Important: Be careful to select the column that increases the balance of your chosen payment account as your 'Deposit' field.

Step 4 - Categorize your Transactions

After your import completes, simply categorize your transactions as usual.  Your Income transactions will be categorized into your preferred 'Sales' category; your Expense transactions will be categorized as 'Merchant Fees'.

After your import completes, simply categorize your transactions as usual. Your Income transactions will be categorized into your preferred 'Sales' category; your Expense transactions will be categorized as 'Merchant Fees'.


 

Importing Card Transactions to Bookkeep Using an "Undeposited Funds" Account

Modifying the Import File

If you bookkeep card transactions using an 'Undeposited Funds' account, the basic steps remain the same. You will want to create your file with some additional rows, however.

The example above uses the same underlying transactions as before, but four additional rows have been added, at rows 2, 3, 6 and 7 (the actual position in the file is not important; this arrangement is just for clarity in the description).  

On Row 3, we have a new transaction that will be a withdrawal from the Undeposited Funds account of $291 - the value of Order #292 net of merchant fees. Comparing back to the explanation of manual bookkeeping with an 'Undeposited Funds' account, you can probably see how this is exactly the process we would follow when bookkeeping the transaction directly into Wave.

Row 2 may seem a little weird at first - it is a deposit in the same amount.  Although we will upload this into the Undeposited Funds account also, this transaction will actually be moved to the Checking account, and will become the other half of a transfer from Undeposited Funds to Checking. (Again, look back at the manual bookkeeping steps to see how this is exactly the same process.

Rows 6 and 7 are exactly similar to Rows 2 and 3, recording the net amount for Order #263 coming from the payment processor. Tip: If you have linked your Checking Account to Wave, you may prefer not to create the additional 'Deposit' transactions, represented above on lines 2 and 6, and simply wait for these amounts to appear in your Transactions when funds are deposited to your bank.  Note also that this example shows a deposit into your bank for each order; in reality you may have one deposit for several orders.  Follow the 'real' transactions between your card processor and your bank to keep everything clear.

Importing the File to Wave

The process for importing your file is no different, however note that in this case the selected Payment Account will be your 'Undeposited Funds' account.  Not 'Checking'.

Changing the Payment Account for Deposits to 'Checking'

If you prefer to create your file exactly as shown above, with both 'sides' of the transfer from Undeposited Funds to Checking, you will need to locate the transactions that represent deposit into Checking (rows 2 and 6 in the example spreadsheet above) and Edit the Payment Account from 'Undeposited Funds' to 'Checking'.

Recognizing the Transfer from 'Undeposited Funds' to 'Checking'

After editing the payment account for your 'Deposit' transaction into 'Checking', or after the 'Deposit' side arrives automatically via your banking connection, you will select the two sides of the transfer and click the 'Transfer' button to complete your bookkeeping.

Refer again to the separate help article on using an Undeposited Funds account to bookkeep credit card receipts, fees and net deposits if you need a refresher on these steps.

 


 

High five!

If you've made it this far, congratulations on staying with us.  You are now officially a Wave Imports Ninja!