Understanding Worldpay settlements

[BenF]

I'm just wondering whether other people have difficulty getting their heads round the data that is spewed out by RBS-Worldpay or whether it's just me?

I've been using worldpay as my Payment Service Provider for online sales for several years. Around a year ago, they suffered a major system "upgrade" which resulted in complete redesign of the transfer statements, invoices and reports. Since then, I've found it really laborious to extract the data needed for bookkeeping.

I did have a long email conversation with their support staff, which went round in circles and failed to make any progress at all.

The data that I need is, for any given period,
(1) the total amount charged to customers.
(2) The total of Worldpay's charges, fees & commissions (including commissions of other entities involved in the transactions) and
(3) the amounts remitted to me
(4) the end-of-period balance of my account at Worldpay.

Sounds simple ha? Well, it seems impossible. There is certainly no balance available. Many of the reports are useless because they are "event-based" showing the same transaction several times over. The "statement" is useless because it includes refunded amounts into the totals. The support staff suggested I use the Transfer Batch Report which would be great except that it shows the settled amounts NET of commission and fails to show how much commission has been taken.

I did find a way to nearly get the figures I need but it's very laborious, particularly in view of the low number of transactions actually processed per week. I used the word "nearly" because there is still a discrepancy between the charges, fees and commissions shown on the Worldpay documents and the actual deductions.

If anyone has a good way of tracking their account at Worldpay, please do let me know. One added complication in my case is that I have set a threshold of £100 for transfers, so this adds complexity to the weekly transfer report with transfers to and from a deposit account.

[openmind]

Do you have an example report we could see? Obviously blank out the sensitive information...

[BenF]

I'll try.

I've split this post into separate bits because I'm having difficulty getting it past the anti-spam filter!

I've taken the Transfer Batch report and chopped out chunks leaving the headings (tab separated - I hope that's going to work) and 3 rows of the body. Then I've pasted below that the corresponding individual transfer report and invoice for one of those rows.

Merchant Batch ID Period Begin Date Period End Date Transfer Initiated Currency Amount Transferred To Merchant Amount Transferred To Deposit Settled Amount Refunded Amount Charged Back Amount Other Amount

[BenF]

Now the body of that report:
WPACC64491189 97 2009-09-13 22:00:00 2009-09-20 22:00:00 2009-10-13 22:50:27 GBP 0.00 -19.37 19.45 0.00 0.00 -0.08
WPACC64491189 98 2009-09-27 22:00:00 2009-10-04 22:00:00 2009-10-27 22:54:40 GBP -134.68 47.80 87.90 0.00 0.00 -1.02
WPACC64491189 99 2009-10-11 22:00:00 2009-10-18 22:00:00 2009-11-10 22:49:48 GBP 0.00 -29.42 29.50 0.00 0.00 -0.08

[BenF]

Now the transfer report for the 134.68 transfer on 27 Oct:
Administration Code:WPCOM98413 Merchant Code:WPACC64491189 Batch Details
Batch Id:98 Settled Purchases Between:2009-09-27 and 2009-10-04 Transfer Date:2009-10-28 Transactions (Gross) Currency #Transactions Value SETTLED GBP 5 92.05 Total Transaction Value GBP 92.05 Deductions Merchant Commission GBP -4.15 Monthly Invoice (Invoice number WR7830449) GBP -0.35 Deposit Adjustment GBP 47.80 VAT on Transaction Charges (Invoice number WR7830449) GBP -0.67 Total Deductions GBP 42.63 Transfer PAYMENT TO MERCHANT INITIATED GBP 134.68 For definitions of these charges and help about transfers please see And finally, the Invoice which comes with that transfer report:

Administration Code:WPCOM98413 Merchant Code:WPACC64491189 Tax Point:Oct 28, 2009 Tax Invoice Date:Oct 28, 2009 Tax Invoice Number:WR7830449
Number Invoice Currency Amount (Invoice Currency) Tax Currency Amount (Tax Currency) Transaction Service Charge (TSC)
GBP 4.15

Funds Transmission Cost (FTC)
GBP 0.35

Sub Total
GBP 4.50

VAT at 15.0%
GBP 0.67

Total
GBP 5.17

Note: GBP 1.02 will be deducted from the balance due when we next transfer funds to your account. This amount will be recorded in your Transfer report. Note, other charges or fees detailed in this invoice are deducted at transaction level and will not be shown as separate items in your Transfer Report. Note the last column of the Transfer Batch report is cunningly called "Other amount". That seems to contain the commission total.

[George101]

Depends whether you want a detailed breakdown of all the transactions individually.

I think the easiest way to get the date you need is this:

Run the Transfer Batch Report as an Excel spreadhseet then lop off the parts you don't need. As long as you put 0 in the Batch Id field you should get all the data you need on what was sent to you.

This won't show the charges on the summary page, or at least not all of them.
To get charges run the Detailed Invoice History (I think that's what it's called, it's near the top of the list), also as an Excel. Run it on All Invoice Periods and put a % symbol in the Invoice Number field. This will show all invoices. This is a REALLY detailed breakdown of your invoices. Again lop off the bits you don't need.

Put the 2 together and you should have a fairly accurate picture of just what they've sent you, and exactly what they've charged you for in relation to those batches.

Of course this is only for summary info. If you want to view these details for each transaction individually that's bloomin' hard, but possible.