You will need:
- EX-SEPA from https://www.roru.de/ (20,-€ one-time license per account, German Interface).
- A download of your member data from brevo. Go to the list, select contatcs, then click “select all” at the top right of the list (if more than displayable), then choose download from the more actions menu on the top left of the and find the link to the file in the message menu on the top.
- Your bank account and your debitor ID.
Next Steps:
- When importing the CSV to Excel (or alike) make sure encoding is recognized as UTF-8, that post code is treated as text (so leading “0” do not disappear) and date is recognized as date.
- Make sure all letters in the IBANs are CAPITOLS.
- Remove incomplete lines and withdraw these manually
- Create missing Mandate references (Ex-SEPA: Mand-ID) from Postcode and entry date using this excel form (replace DE with Country Code and columns F with Postcode columns and E with Entry Date column)
=”MF-DE01″&F2&”00″&TEXT(E2;”TT-MM-JJJJ”) - Germany: Create missing BICs from the BLZ file delivered with Ex-SEPA. You must first extract the BLZ from the iban in a BLZ column using:
=NUMBERVALUE(RIGHT(LEFT(J2;12);8))
then lookup the BIC in the BLZ table:
=VLOOKUP(L2;BLZ!A:H;8;FALSE) - Calculte the yearly fee (Ex-SEPA: Betrag) from the monthly one, and half the fee if member joined in second half of the year:
=IF(E2<DATE(2025;6;30);M212;M26) - Create a nice text to be used for the withdrawal, dependent on whether it’s a half or full yearly fee and wether It’s an indivudual person or an organisation:
=IF(ISBLANK(P2);””;” – “&IF(E2<DATE(2025;6;30);”mit “&IF(I2=”MEMBER_ORGANISATION”;”eurer”;”deiner”)&” Unterstuetzung fuer mehr, bessere und bezahlbare Nachtzuege. Danke!”;”halber Jahresbeitrag wg. Eintritt im 2. Halbjahr. Danke fuer “&IF(J2=”MEMBER_ORGANISATION”;”eure”;”deine”)&” Unterstuetzung!”)) - Save the personal data file, then open ex-sepa,xlsm and select the saved personal data file in B13.
- Fill in the other fields in column B
- Click on the “Sepa Datei erstellen” button. RCUR is o.k. (as the mandate is for recurring debits)
- Upload the file to our bank.