PSP integration best practices for reconciliation purposes

Connecting other Payment Service Providers (PSP) to Actuals contains two steps:

Analyze the possible integration methods (API, SFTP, others)

Analyze the reporting possibilities

This document contains information that helps to identify the desired reports to use to integrate other PSP's. U can use this report as reference when asking your PSP for a financial reconciliation export. For financial reconciliation purposes we are mainly interested in all transactions lead to an impact of the ‘balance’ of the merchant at the PSP. These 'balance affecting' transactions generally consist of 'payments', 'refunds', 'chargebacks', 'costs', 'currency conversions' and 'payouts'.

Generic requirements

Get all 'balance affecting' transactions (including payments, refunds, chargebacks, costs, currency conversion, payouts)

Have a way to validate that we are complete (like balance mutation vs. transactions amount sum or settlement sum vs settlement transactions, so we can validate we the Actuals platform has processed every transaction)

Includes references to perform reconciliations against orders and payouts (like order_references and settlement_references)

Different datasets

Different PSP's provide different datasets. Through experience we learned that they mainly result in three methods we can use to perform reconciliation:

PSP statement (bank statement like) method

Settlement detail method

Payment details + Settlement details combination method

Since there is no standard available across PSP’s like there is inside banking (CAMT.053 or MT940) it can be hard to identify the contents of the reports provided. In the methods below we’ll dive a deeper into reports we see more often and how then can be implemented into our platform.

Method 1 (PSP statement (bank statement like)

If a PSP can provide a PSP Statement report (also known as balance transactions, bank like statement or ledger transactions report) that on a daily basis can provide transaction details for all transaction that impact the balance of that merchant at the PSP this is the desired integration method.

We can define these transactions as ‘balance affecting’ transactions. These reports often contain a balance after or balance before column. If a payment service provider provides a report that gives us information into the change of that balance over a certain period over time on transaction level we are interested in that report. It is important to validate that we have the information in the currency the transaction did happen. Because most likely we have to match the payment against an order and that order is made in the currency the customer payed. The order then also gets invoiced in the currency the customer payed the transaction.

Example PSP statement report:

id	Timestamp	Entry type	Reference	Currency	Balance before	Amount	Balance after
123	2024-01-01 01:00	capture	ORDER_1	EUR	20	100	120
124	2024-01-01 02:00	capture	ORDER_2	EUR	120	50	170
125	2024-01-01 03:00	capture	ORDER_3	EUR	170	200	370
126	2024-01-01 04:00	refund	ORDER_4	EUR	370	-90	280
127	2024-01-01 06:00	fee_retained	FEE_2023-12-31	EUR	280	-10	270
128	2024-01-01 06:00	settlement	SETTLEMENT_2023-12-31	EUR	270	-230	40

👍

Benefits ▪️Match against orders and settlement ▪️Only one report required ▪️Daily immutable data ▪️Easy verification if you are complete (mutation vs. lines)

👎

Downsides ▪️None

Method 2 (Settlement detail)

When we are not able to get information for the preferred method we see that almost all PSP’s can generate a Settlement detail report. This report contains a specification of all transactions that have been settled (paid out) to the customers bank account. A downside to this report is that it isn’t as up to date as the first solution. This report is dependent on the settlement interval and will exclude transactions that haven’t been settled yet (paid out) to the customers bank account.

Example settlement detail

id	Timestamp	Entry type	Reference	Currency	Amount
123	2024-01-01 01:00	capture	ORDER_1	EUR	100
124	2024-01-01 02:00	capture	ORDER_2	EUR	50
125	2024-01-01 03:00	capture	ORDER_3	EUR	200
126	2024-01-01 04:00	refund	ORDER_4	EUR	-90
127	2024-01-02 00:00	fee_retained	FEE_2024-01-01	EUR	-10
128	2024-01-02 00:00	settlement	SETTLEMENT_2024-01-01	EUR	-250

👍

Benefits ▪️Only one report required ▪️Easy verification if you are complete (settlement vs. lines)

👎

Downsides ▪️Freshness, reconcile payments only after transactions have settled ▪️Lack of insight in payed not settled transactions, because of extracting payments data from settled transactions ▪️Risk of having missing currency conversion information

Method 3 (Settlement detail + payment detail solution)

Sometimes its possible to get a payments export besides the settlement detail export. This allows us go to circumvent one of the main downsides of using the settlement detail report only, to get better insights into the payments regardless of their settlement status. This allows us to match the invoices to the payments export and match the payouts to bank statements. In order to know if all transaction have been settled we require one additional matching package to match the payments from the payments report with the payments mentioned on the payout report.

Example settlement detail report

See example in method 2.

Example payment detail report

id	Timestamp	Entry type	Reference	Currency	Amount
123	2024-01-01 01:00	capture	ORDER_1	EUR	100
124	2024-01-01 02:00	capture	ORDER_2	EUR	50
125	2024-01-01 03:00	capture	ORDER_3	EUR	200
126	2024-01-01 04:00	refund	ORDER_4	EUR	-90

👍

Benefits ▪️Match against orders and settlements

👎

Downsides ▪️Reconcile only after transactions have settled ▪️Multiple reports required ▪️An additional matching package required to validate transaction flow from payment to settlement

Other