How To: Reconcile Shopify Payments via Import
Table Of Contents
Creating a dedicated Shopify bank account in Khaos Control allows you to reconcile Shopify transactions quickly and accurately using Shopify's transaction export file.
The reconciliation process matches Shopify transactions against banked sales in Khaos Control using the Shopify order reference, transaction date and payment amount.
Performing the Shopify Reconciliation
Prepare the Shopify Export
- Export your Shopify transactions from Shopify.
- Open the exported file in Excel.
- Save the file as a CSV (.csv) file.The file is now ready to be imported into Khaos Control.
Import and Match Transactions
- Open the [ Accounts | Bank Accounts ] screen.
- Select your Shopify bank account from the Bank Account drop-down list.
- Click Go.
- Press Alt + E or click Edit to enter edit mode.
- Open the Other Actions menu by:
- Pressing F9, or
- Clicking the drop-down arrow next to the green Add button.
- Select:
Import
└─ Match Shopify CSV Payments...
- Browse to and select the Shopify CSV file.
- The Match Shopify CSV Payments dialog will appear.
- Khaos Control will process each line in the file and attempt to match transactions using:
- Shopify Order Number
- Transaction Date
- Payment Amount
- Where a match is found, the Recon checkbox will be ticked automatically.
- Press Ctrl + S or click Save.
- Complete the reconciliation process as normal.
How Transaction Matching Works
Khaos Control stores the Shopify order reference in the Ass. Ref. field of the bank account transaction.
The Shopify transaction file is expected to contain order numbers in the following format:
Plain Text
#xxxx
For example:
| Shopify CSV | Khaos Control Ass. Ref. |
|---|---|
| #1077 | Shop1077 |
If:
- The order number matches,
- The date matches, and
- The amount matches,
Khaos Control will automatically mark the transaction as reconciled.
After Reconciliation
Once the reconciliation has been completed, you may need to account for Shopify fees and settlements.
Record Shopify Fees
There are two common approaches.
Option 1: Enter a Supplier Invoice
If you require a purchase invoice for Shopify fees:
- Raise an invoice against your Shopify supplier.
- Use the Shopify Fees nominal account.
- Pay the supplier from the Shopify bank account.
This reduces the balance held within the Shopify bank account.
Option 2: Post a Journal
If no supplier invoice is required:
- Create a journal between:
- The Shopify Fees nominal account, and
- The Shopify bank account.
Transfer the Shopify Settlement
After processing fees:
- Check the remaining balance in the Shopify bank account.
- This balance should equal the amount transferred by Shopify into your main bank account.
- Journal the value from the Shopify bank account to your current account.
- Once completed, the Shopify bank account balance should clear for that settlement period.
Skipped Transactions
After importing, Khaos Control displays a summary of any transactions that could not be matched.
Common reasons include:
Order Not Found
The Shopify order number in the CSV file does not match the order reference stored in Khaos Control.
Already Reconciled or Date Mismatch
The transaction:
- Has already been reconciled, or
- The transaction date does not match.
Amount Mismatch
The value in the Shopify export file differs from the value recorded in Khaos Control.
Tips
- Always save the Shopify export as a CSV before importing.
- Reconcile transactions regularly to simplify fee and settlement postings.
- Ensure Shopify order references are imported correctly so automatic matching can occur.
- Investigate skipped transactions before completing the reconciliation process.