• blog/projects

batch2sepa is a tool to make paying (or requesting funds for) invoices in bulk through a connection between Xero and European banks with the SEPA standards.

Here’s a brief overview of its history. It was my first tool at asm33, with its first commit in March 2019.

It integrates with Xero (a major cloud accounting platform) and enables users to prepare SEPA-compliant¹ XML batch payment and deposit files.

Problem

If you are running a firm which deals chiefly with invoices that must be manually paid (like the ones you get from your bookkeeper or tax advisors), you will know very well that paying anything more than 10 invoices through your bank’s app is just a slog. At higher numbers, there are other problems like errors in data entry too.

Many large firms use tools to connect their bookkeeping administration (the ground truth) to their bank.

However, a large accounting platform, Xero, doesn’t provide such integration with European banks. It would be very helpful to have, so I built it.

Solution

There are several proprietary solutions to this, like bill.com. Such solutions target perhaps a different class of customers than a small public accounting firm and its clients—like major firms which have payments in scales vastly larger than 1,000 invoices going in and out a month. They provide additional services like integrations with business intelligence services. It’s quite useful, if you need it…

Europeans have another tool at their disposal: ISO 20022.

ISO 20022 is a series of message formats, declared with XML schemas, to be used in data exchange between financial institutions.

It covers a very large variety of actions such as CorporateActionMovementConfirmation002 (‘sent by an account servicer to an account owner or its designated agent to confirm the posting of securities or cash as a result of a corporate action event’).

It’s truly comprehensive, but most of it is of little use to us mortals. Instead, the very special one is PAIN. That’s right. It really lives up to its name, not because of the standards, but its implementations (more on that later). SEPA has decided that it would like to use this standard in particular.

pain.001.001 (001), which is the CustomerCreditTransferInitiation message, enables us to declare transfers from the debtor account (i.e. you) to the creditor (e.g. your accountant).

Most banks in the EU accept XML files in this format to be uploaded freely by their clients to make payments in bulk.

This standard is by no means new, and many European bookkeeping tools already support this, and deeper integrations (Exact Online, a popular one in the Netherlands, directly submits these files to your bank in your name after establishing an authorized connection) exist too.

On the other hand, the firm I was working at the time had already heavily invested in Xero, which did not have such a strong European presence and in turn, no 001 support.

Two birds with one stone

If you implement pain.001.001, you kind of get its evil twin for free, pain.008.001 (008). 008 is the exact opposite of 001, which enables a creditor to request funds from their debtors’ accounts. This process is called a ‘direct debit’.

Direct debits are beloved for their convenience. You cannot forget to pay your bills if your creditors can just take the cash from your pocket!

Xero has batch payments for accounts payable (AP, invoice you owe) and accounts receivable (AR, invoices you send out).

The API endpoint is the same and it’s effectively just a flag on the direction of the payment. This means that you can process the same batch payment from the API response and just fill out the respective 001 or 008 template with the same set of fields. 008 does require mandate metadata, on the other hand, which you will have to collect and provide to the tool (batch2sepa has an internal storage for this metadata, for the users’ convenience).

Banks will also let you upload these XML files in the same way, and then execute the payment requests for you. Magical!

Mandates

For our non-European friends, maybe a little bit of background is helpful.² Direct debits are a heavily regulated financing product in the EU. It is extremely popular to the extent that nearly half of all bank transactions in the Netherlands are direct debits.

A creditor can withdraw funds from your account without you giving their explicit consent to your bank. You have a separate agreement (called a mandate) with your creditor which entitles them to direct debits.

In case of a dispute over funds withdrawn, the bank will ask your creditor to supply a valid mandate. However, abuse is not common as your bank will be very strict on your newfound powers and can easily track the funds and the underlying request.

There are various other rules on pre-notifying your debtors etc. that I won’t go into detail, but it’s so popular because it’s convenient and safe by means of tight regulation.

The reason why I mentioned it to be a financing product is that most banks execute it as a very short-term loan to you. For example, when you request 10 payments of €100 each for a certain execution date, the bank will give you the €1,000 on that date. However, if one request for €100 ‘bounces back’, the bank will return the funds in 3-5 days depending on the type of the direct debit agreement. You will net €900 and will have to get in contact with your customer yourself to settle the request.

I also know that the US has direct debits through the ACH Network, but I am not sure if it’s also a financing product.

Development

The initial draft took roughly 2 weeks to get going, which already included a connection to the bookkeeping tool and Dutch-bank-compliant exports.

A lot of iteration has happened on it since then, including:

• direct debits,
• exports to exotic bank formats,
• CSV imports for migrating clients’ mandate information,
• and billing.

Designing the process

The process of getting from invoices in your administration to a batch payment file that you can readily upload to your bank must be designed such that it is convenient, fast, and easy to learn. Otherwise you won’t be able to compete with letting the intern pay the bills manually (I was the intern).

In turn, I wanted to leverage Xero’s built-in batch payment support. Xero allows you to select multiple invoices in your administration and put them in a separate transaction object which marks these invoices as paid against a certain bank account.

From this batch object, you leave Xero to go to batch2sepa and take out your freshly-baked pain out of the oven (haha, get it?)

Implementing it

batch2sepa makes an API call to request all outstanding batch payments and shows you the unreconciled ones (these are the ones which you have not linked against your bank statement yet, as in, they are not yet paid/received).

From these batch payment objects, you can collect information on which invoices are in this batch, what their amounts are, to which contact/bank account they are to, etc.

Xero doesn’t have a way to store bank accounts with SWIFT/BIC attached, in order to avoid storing a ton of IBANs, batch2sepa uses the format IBAN/BIC in the contact’s bank account field. If it encounters a new contact, it asks you to fill out their bank account details. Once all fields are complete, you can download the XML file, ready to go!

(Again with mandates, because there are not other custom fields in Xero, batch2sepa stores mandate and other metadata internally. These are anonymous by means of tenant-specific UUIDs.)

Stack

batch2sepa is a straightforward Flask application. I wasn’t much of a web developer back then but I fell in love with Flask due to its simplicity. It’s low-level enough that I didn’t have to learn much besides how HTTP internals work.

Using Redis for the storage, I was able to avoid a lot of data and migration work. It doesn’t execute any queries besides key-value ones, so it was a good choice.

It relies on a variety of packages. pyxero (which didn’t have batch payment API endpoint support at the time, so I had to include it myself with a few extra lines) for the Xero API, Jinja2 (comes with Flask) for XML templating the pain files, lxml for formatting the Jinja exports based on the bank’s needs, argon2-cffi for passwords, clevercsv for CSV imports of client mandate information, and zipencrypt for… funky bank requirements. These are some major ones I enjoyed using, truly resting on the shoulders of giants.

Also, shout-out to python-stdnum for having coverage for IBAN, SWIFT/BIC, and AT-02 (direct debit creditor ID) validators!

It’s deployed behind Nginx with a Let’s Encrypt certificate on a €5 server and serves nearly 100 active organisations! It’s an absolute beast of a script, really.

Bank-tech

An ISO standard should be respected as-is, but banks love flavouring their SEPA files. Among all requests I got from banks to make batch2sepa compliant with them, some fun ones are:

• Number of spaces is not correct, export it with a 2-spaces-per-level indentation and not 4-spaces (you thought programmers were bad with their indentation choices?)
• File must be exported with a different extension (not .xml, but .sct), and put in a ZIP file (.scte) to be encrypted with ZipCrypto (which is broken) with a password unique to that client.
• We do not process the address fields, so you must exclude them (it’s an optional field, they can choose to ignore it).
• Same as above, with several other fields which are now… toggles in the export settings.
• Filling out a field with a default value because the bank claims so in its SEPA DD manual, only to find out that it’s not a default value and clients must populate it themselves (found out after making it a special default export settings for their bank).
• Bank partly complies with the specification of allowed characters but batch2sepa didn’t (so this one is on me), only found out after a client tried to make a payment to a Polish firm with many accented characters in its name (most banks seemed to blindly accept UTF-8 instead of the standard [A-Za-z\d+?\/-:\(\).,'\s]+, at least until this sanitization mistake!)

Usage

Click the image below to go to a full-page screenshot of the ‘features’ page of batch2sepa. It does a very good job of outlining how it integrates with Xero, and what the process from bookkeeping to bank looks like.

As mentioned above, most banks do accept the exported XMLs as-is, but the exact process is different for them all and the user is tasked with figuring that out. This is something I wanted to write content marketing articles over for a while but never ended up doing.

Branding and design

batch2sepa went through a few iterations in its name, from Batch to SEPA to the stylized batch2sepa. I tried to prepare a distinct red-to-purple colour scheme for it:

The interface is built up with Bootstrap since its MVP. The screenshots from above make it pretty obvious, hah!

Last musings

batch2sepa was my first real-life battle-tested tool, and likely the one that has saved the highest number of man-hours. That’s ultimately the goal here, to make sure that mundane tasks are easy.

It most certainly succeeded in its goal, and was a great exercise for me to understand what it takes to build a finance/accounting B2B SaaS tool.