How to reconcile Square Income
This help guide article covers how to reconcile Square Income. Square is similar to SumUp and Zettle in that as well as enabling organisations to accept income payment by card, it's also possible to be used as a till system, with both cash and card transactions being recorded.
Within ExpensePlus there are 2 choices for how to reconcile Square Income (you will need to pick the one that will work best for you):
- Option 1: Splitting payouts manually using the 'mixed income' option - This can be a good option where you have only a small number of Square Transactions or all transactions are typically either just card donations or relate to just one event (and you wish to bulk together similar income transactions and do the same with fees).
NOTE: Option 1 can't be used if you are using Square as a till system through which you are also recording cash income.
- Option 2: Adding Square as an account and uploading and reconciling transactions - this option enables you to upload and reconcile the transactions that form part of your Square payouts in a highly automated and efficient way using the quick-match feature in ExpensePlus and avoiding the need to try to manually split payouts into gross income and fees.
IMPORTANT: You should not reconcile payouts from Square as 'Income Received'. Square charges transaction fees, so recording the net amount that arrives in your bank account simply as income is incorrect from an accounting perspective. Instead, choose one of the above options - both of which enable you to ensure the gross amount of income collected via Square is recorded as income, and the Square transaction fees are correctly recorded as expenditure.
Option 1: Splitting payouts manually using the 'mixed income' option
Reconciling Square payouts appearing on your main bank account statement
When you reconcile your uploaded bank transactions, select the Square payout on the left-hand table of your match transactions screen, click the + button, and then, in the popup that appears, select the transaction type as 'mixed income'.
Within the 'Enter Mixed Income' screen, enter the details for the transactions in the Square payout:
- GASDS Donations - for any contactless card donations where the amount given is £30 or less
- Income (other) - for all other income, such as ticket / book / cafe sales (along with contactless card donations greater than £30 which are not eligible for GASDS)
- Less Expenditure - for the total of the fees deducted (typically grouped into a single expenditure line)
NOTE: Typically, the named donation option isn't used, since most donations will be contactless card donations which can be claimed under GASDS (removing the need to try and manually track at the point of donation which donor with which card number gave which amount).
Any card donations over £30 are not eligible for GASDS and should be entered instead in the 'Income (other)' section.
Note: if you are finding the process of splitting payouts as described above tedious or time-consuming, then we'd recommend you consider option 2 which is quicker and doesn't require manual data entry.
Option 2: Adding Square as an account, and uploading and reconciling transactions
Reconciling Square payouts appearing on your main bank account statement
When you reconcile your uploaded bank transactions, select the Square payout on the left-hand table of your match transactions screen, click the + button, then, in the popup that appears, select the transaction type as 'Payout from Payment Processor', and then select your Square account.
Note: you will need to set up Square as a payment processor account before you can do this - click here to find out how to do this.
Note: This first step may temporarily make your account balance of your Square account balance in ExpensePlus go negative (until you upload your Square transactions).
Downloading Square transactions
(1) Go to transaction report
(2) Select the date range
(3) Select the 'Payment Method'
IMPORTANT: Square does not include a payment method column in the download, so if you are using Square as a till system and accepting payments via both card and cash, it's important that you filter by 'payment method' and download two seperate files for the date range you require (one that contains just the 'cash' transactions, the other for the 'card' transactions).
(4) Select the 'Export' option in the top right of the screen, then select 'Item Detail CSV'
IMPORTANT: Do NOT select the 'Transactions CSV' or the 'Items Summary CSV' as these will not upload to ExpensePlus. Your download should look like this example file (the important columns are highlighted in blue).
Top tip: the item detail CSV includes a helpful breakdown of the items sold, enabling you to view sales at an item level within ExpensePlus, and enabling you to reconcile transactions sold against the different income categories it relates to e.g. Drinks, Hot Food etc. This is particularly useful if you are using Square as a till system e.g. for a cafe or bookshop.
WARNING: Once downloaded, don't edit or resave the file, or change the file format from .CSV to .XLS/.XLSX, as it may stop the file uploading to ExpensePlus properly.
Importing Square transactions
Within the bank reconciliation screen, select your Square account and click 'Upload transactions'.
The upload process works similarly to a regular bank account.
As part of the upload process, you will be asked to select the payment method of the transactions within the upload file.
IMPORTANT: If you are using Square as a till system and accepting payment via both card and cash, it's important that you upload transactions paid via card separately from those paid via cash (otherwise your transaction descriptions will confusingly state the incorrect payment method).
Adding the monthly Square Transaction Fee
The Item Details CSV report from Square doesn't include the fees deducted and therefore at the end of each month, you will need to manually upload a single transaction for the total fees deducted by Square for that month.
Top Tip: to find the total monthly fees deducted by Square by:
Log in to your Dashboard > select Account & Settings > Business > Fee Invoices, then simply click on the invoice for the month you wish to view.
To manually upload a single outgoing transaction to your Square account within ExpensePlus for the total fees deducted by Square for that month see here: How do I manually enter bank transactions?
Reconciling Square transactions
The bank matching process for Square transactions works similarly to a regular bank account - with the ability to remember and quick-match transactions.
This means that once you have reconciled Square transactions for the first month, you won't need to manually enter these transactions for future months. Instead, you can simply click the 'quick match income' button at the top of the match transactions screen.
Where transactions haven't been previously reconciled and auto-remembered:
GASDS Donations
To reconcile contactless card donations that are £30 or under:
(1) select the transaction in the left-hand table of the match transactions screen;
(2) click the '+' button to the right of the transaction;
(3) select the 'GASDS Donation' option in the popup and enter the details.
NOTE: Contactless card donations over £30 are not eligible for GASDS and should be entered instead as 'Other Income'.
Income Received
To reconcile income for an event or sales income:
(1) select the transaction in the left-hand table of the match transactions screen
(2) click the '+' button to the right of the transaction
(3) select the 'Income Received' option in the popup and enter the details
Top tip: shorten the 'Description to Remember' field to just the first part of the description as this will enable all future sales / event income to auto-match.
Card/Payment Process Fee
To reconcile the Square transaction fee:
(1) select the transaction in the left-hand table of the match transactions screen
(2) click the '+' button to the right of the transaction
(3) select the 'Card/Payment Processor Fee' option in the popup and enter the details
Recording Square CASH that is banked (not relevant for most organisations)
NOTE: This is only applicable where Square is being used as a till system through which cash is being recorded.
If you are using Square as a till system where both cash and card sales are being recorded, then your Square balance will be made up of both cash held and card transactions that have been collected and awaiting payout to your bank account.
If you wish to bank cash from your till, then to record this within ExpensePlus, you need to go to the Bank Deposit screen, and record the cash being banked in the 'Plus Cash Added from Petty Cash / Till System' section.
This will automatically create an entry in your Square account in ExpensePlus, causing your Square balance to reduce - reflecting that the money has been banked.
Why is my Square statement not uploading?
- Category (each transaction should contain this)
- Item (each transaction should contain this)
- Qty (each transaction should contain this)
- Net Sales (each transaction must contain an income amount)
- Date (each transaction must contain a date)
(Additional columns can be present but won't be imported).
- The file format should be .CSV
To better understand the Bank Reconciliation functionality, please watch the module overview video here.