Our customers have found that the most accurate way to record four types of transactions that are supported when importing data from Excel to QuickBooks is to sync everything into a “clearing” account in the QuickBooks Chart of Accounts. A clearing account – is an account that plays the role of the payment processor in the accounting company. We create an “Excel Bank Account” automatically during Synder setup in Chart of Accounts.
Step 1. How to set up Synder for QuickBooks reconciliation:
In case you don’t have a specific Account for Excel-uploaded transactions in QuickBooks, the app will auto-create it. Check your Synder settings to make sure you have that account selected as a Bank account in Sales, Expenses and Fees.
Step 2. Syncing online sales to QuickBooks:
As soon as you have finished the setup, you may proceed to the bulk transactions import from excel to QuickBooks or import from excel to Xero.
Go to the “import historical transactions” tab→ choose Excel from the list of payment platforms.
*Select the “excel provider” needed if you have connected excel to account for several unsupported providers.
Upload your Excel file from your PC → click on “Browse files”. We recommend you download and use our ready-made template and fill it in with the data you export from the unsupported payment processor (use step 2 -> “download template” button). Having the template filled in, you will be able to upload it to QuickBooks or upload to Xero.
Learn how to import transactions into QuickBooks from Excel in the detailed guide.
Step 3. Reconcile synced transactions in QuickBooks bank feed manually
Open For Review list in QuickBooks Banking and click on the Review button near the needed transaction. To prepare the account for reconciliation you need to record the payout transaction from your bank as a transfer from Excel Bank Account. Click on review → Record as transfer→ Account money is from Excel Bank account→ confirm.
After that you will see that the payout has been recorded as a transfer from the Excel Bank account in your Chart of Accounts and the ending balance of the account is equal to zero and which basically means that everything works smoothly and your checking is ready for reconciliation.
Step 4. Reconcile synced transactions in QuickBooks bank feed with rules(optional):
To do less manual work in QuickBooks by recording payout transactions from your Bank as transfers you may create your own rule in QuickBooks directly, so the transfers will be created automatically in the Excel Bank account.
Go to Rules in Transaction tab in QuickBooks→ new rule.
- Name the rule.
- Choose “money in” and the real Bank or Credit card connected (You may choose the accounts needed from the dropdown).
- Create a condition for the rule based on your needs. For example, if the Description → contains → e.g. “Magento” (or any other word/payment gateway name of yours that is a common feature of the needed transactions in the bank feeds).
- Then assign Transfers transaction type to the needed Clearing account for your Excel transactions in QuickBooks (e.g. Magento bank account).
- Confirm the rule.
This kind of rule will help you to automatically create a transfer in the Excel Bank account (e.g. Magento bank account) in Chart of Accounts, match it and record it in the Banking→ Categorized tab.
Voila! You are ready to reconcile your accounting!