Firstly, you need to run your transaction report for the period in question by clicking ‘Reports’ from the left hand menu and then clicking ‘Payment Reports’. You will then be able to click on ‘Transactions for Selected Date Range’ and then select the date range that you wish to include. Please note: To reconcile your January statement for example, you will need to include the last 3 days of December and the first 3 days of February. You can then click ‘Generate Report’.

Once your report has finished generating, click the green ‘CSV’ option at the top of the page to download the file. The spreadsheet should open automatically but if it doesn’t, you can open the file from the downloads menu of your web browser.

Once you are in your spreadsheet, the key areas are Column A (Transaction Date), Column C (Amount), Column D (Payment Method), Column O (Date paid).

Select the square in the top left hand corner on the spreadsheet as seen below:

This will highlight the entire spreadsheet and it’s important that you do this before filtering or sorting otherwise your data may become misaligned.

With your spreadsheet highlighted, click on the ‘Data’ tab at the top of your screen and then press ‘Sort’. Ensure you have the ‘My data contains headers’ box ticked and then sort your spreadsheet by ‘Date Paid’ or ‘Transaction Date’.

Next, with your spreadsheet highlighted, select the data tab at the top of your screen and then press filter as seen below.

It’s best to filter out all non-electronic payments as these will make it harder to reconcile what has been paid into your account. To do this, click on the filter arrow at the top of column D ‘Payment Method’ and ensure that there is only a tick in the Card, PayCash, PayPoint Cash and PayPal Express options.

Please note: If you have more than one bank account, you will need to reconcile these one at a time. Simply press the filter option on column S (Bank account Name) and tick the account you wish to review.

You should now be looking at a spreadsheet sorted in ascending order of ‘Date Paid’ or ‘Transaction Date’ with all non-electronic payment methods filtered out. Now you need to add a new row in between each date range by right clicking on the row number of any new day and then clicking ‘Insert Row’. You should end up with a sheet that looks like the below screenshot.

To get a daily total, highlight the payments in column C and when you drag it down make sure you select the blank cell below the bottom payment amount for each day (the one we inserted).

Then with your cells highlighted select ‘Formulas’ at the top of the page and click on the ‘AutoSum’ option. This will give you the total for the day.

Repeat this process for the remaining days and you will then be able to match up the transaction report with the monies received in your bank account.

Other things to consider

When a payment is made on ParentMail, it usually takes 2 to 3 working days to transfer to your bank account so you will need to look 2-3 days ahead on your statement.

We will not transfer less than £50, so if you have a total payment of £20 on one day, it will be held back until another £30 has been transacted.

The exception to the above rule is at the end of the month any funds will be transferred to allow you to reconcile your account.

Excel Tips

• If sorting or filtering a spreadsheet remember to select the ENTIRE spreadsheet not just the row you are interested in, if you only have one row selected you will misalign your data.
• If you are going to colour code the sheet, make sure when you save the file you change the file type from csv to an excel workbook, otherwise you will lose the colour coding. Press F12(Save as) on your keyboard.

• Click on the ‘Save as type’ option and select ‘Excel Workbook’

If you do have any queries related to account reconciliation please don’t hesitate to contact our Payments Team at pluspay@parentmail.co.uk.