How do I create a payment report for accounting?

Two filters show the same payment lines, with one important difference.

• Modules > Payments gives one row per payment, including the provider's transaction reference. This is the overview you place next to your provider's statement.
• Booking > Search gives the same payment lines, but with the customer and booking details alongside. This is the overview that answers “by whom?”.

The payment context holds the booking, invoice and order numbers, but no customer name or e-mail address. If you want customer and payment in a single file, use the booking context. If you want to reconcile with the provider, use the payment context. For a full check, produce both and join them in Excel on booking number or invoice number.

Create a new view through Views > Edit, for example “Accounting payments”. See How do I change the columns of a filter? for the steps.

In the Payments context, include at least:

• Pay ID – unique number of the payment line, useful as a reference in questions;
• Pay date and Pay amount (plus Pay currency if you work in more than one currency);
• Pay status – paid or refunded, see the next step;
• Payment method – how the money came in or went out;
• Transaction # – the provider's reference; this is the key to your provider's report;
• Booking #, Invoice number, Invoice # and Order # – what the payment belongs to;
• Pay remarks – often holds the provider's explanation, for instance which account a refund was paid back to.

A full list of available columns is in Columns Payment.

In the Bookings context, combine the customer columns (Surname, Full name, E-mail) with the payment columns (Pay id, Pay status, Pay date, Pay price, Pay method, Pay transaction, Pay remark) and with Booking number, Total price, Paid and To pay.

Without a status filter, aborted and declined payment attempts run through your overview, and that explains most differences with the accounts. So filter on status deliberately.

Three things matter for accounting:

• Money received: status PAID.
• Money refunded: status PAID OUT. This is the refund you issue on a booking, which also appears as a refund at your provider.
• Corrections: a payment line with a negative amount. These arise from a manual correction or credit and carry no provider transaction reference, because no money moved through the provider.

To see receipts and refunds in one overview, select both statuses — but do keep the Payment status column, otherwise you cannot tell them apart.

Filter on Payment date, not on the date of the booking or the invoice. A booking made in April may have been paid in June; the accounts want to know when the money moved.

Use a fixed period when you deliver a closed month or quarter. For a recurring check, use a relative date so you never have to change the date again. Examples:

• last week: -7d through 0d;
• last month: -1m through 0d.

Check the overview before you export it. Three checks that prevent most surprises:

• The total below the Pay amount column is the sum of every row in the filter. If rows with status PAID OUT are included, those refunds count as positive amounts and the total is not your net receipts. So filter on a single status when you want to report an amount, or subtract the refunds again in Excel.
• Compare the number of rows with the number of transactions at your provider for the same period. Mind the cut-off: a payment started just before midnight may sit on the next day at the provider.
• Rows without a Transaction # did not go through the payment provider: manually recorded payments, cash, card outside the provider, gift cards, punch cards and corrections. By definition you will not find those in your provider's report, and that is correct.

If you work in the booking context, bear in mind that one booking produces several rows when it has several payments. Never sum the Total price column in that case: it appears more than once in the file.

Click Excel in the toolbar. The arrow next to the button lets you pick Excel (.xlsx) or CSV, and Custom lets you set a start and end row if the file gets too large.

If you cannot see the Excel button, your user group lacks the filter_export right. It is off by default for good reason: with this right, someone can download lists of customer data. Grant it to the group that handles the accounts, not to everyone. An administrator does this in configuration mode via System > User groups. More on this in Exporting.

If you want to do this every week, you do not have to rebuild it every week.

1. Turn the period into a relative date, for example -7d to 0d. The filter then moves along by itself.
2. Save the filter with Save filter and give it a clear name, for example “Payments last week”. Share it with the group that handles the accounts.
3. Attach your view to the saved filter through Show filter list, column View. The right columns are then there straight away.
4. To receive the overview by e-mail automatically, you can set up a filter schedule. That requires an export type; see the Export section. Ask support for help if your environment does not have an export type yet.