bnkSEPA : Excel to SEPA Credit Transfers (SCTE) Electronic Payment

Last update: 202205

Version - 1.37.00

Project Repository: https://www.github.com/chribonn/bnkSEPA

Project Overview

This is an engine written in Python that takes a Microsoft Excel spreadsheet and converts into a password-protected file that will be processed by Financial Institution. Other financial institutions that use the same format may also benefit from this ETL (Extract Transform Load) engine.

The Excel workbook consists of the following worksheets:

• Payment Information Record - This sheet holds information of the payee account. This will be entity that will upload the file to the bank
• Credit Instruction Record - This sheet contains the transactions that are to be processed. The maximum number of transactions can be increased (this has already happened). An increase in the number of transactions will require changes to both the Excel and the Python code.
• Control - This worksheet performs the following functions:
  • It stores within it the password provided by the bank to encrypt the transmission
  • It stores the filename that will be used to package the transmission
  • It provides a summary of the posting for control / checking purposes
• Client Payee Data. This worksheet is not an integral part of the solution. When populated it allows the person filling in the Credit Instruction Record to use the Excel VLOOKUP function to copy the payee details thereby avoiding the need to manually type them in. The assumption is that this worksheet is populated from another system. VLOOKUP search is on the account number.
• Control Data (Hidden) - This worksheet consists of:
  • Versioning information
  • Author information
  • The source documentation this solution was based upon
  • The drop down lists used in some of the other worksheets

Solution Flow Diagram

When the Excel file is ready to be processed it must be zipped using 7Zip with a password. The resulting password protected zip file will then need to be processed by the python engine so as to produce the SCTE file required by the bank. This file is protected by a password that has been agreed with the financial institution.

The python engine takes the following arguments:

• --zipname - this is the name of the archive that contains the archived Excel. [Mandatory]
• --zippath - this is the directory where the zip file is located. [Default: directory used for temporary files]
• --zippass - the password of the zip file [Default: string returned by function tmp_zippass() in secrets.py]
• --bankSCTE - the password to archive the SCT file for the bank [Default: string returned by function bnk_scte() in secrets.py]

Microsoft Excel Worksheet

The Microsoft Excel Worksheet that is translated by the Python engine is located in the xl folder. It contains the following files:

• BnkSEPA.xlsm - This is the file that will be used to post the transactions and which will be used to generate the file used as input for the ETL transformation
• Backup of BnkSEPA.xlk - Backup of BnkSEPA.xlsm (normally the version before the current one. This is created automatically whenever the workbook is saved).

Macros

This Microsoft Excel workbook makes use of macros to perform some of the functions is does. When opening the downloaded file for the first time macros must be enabled in order to benefit fully from the solution. Keeping macros disabled will still generate the output for the bank; it will impact of the functionality available within Excel.

The following VBA modules are defined:

• Sheet: Credit Instruction Record
  • VBA Macro ClearSheet - This goes through all the rows in the sheet and blanks them out.
• Sheet: Control
  • VBA Macro Worksheet_Activate - When the worksheet control is activated the associated pivot table is updated.

Documentation and Notes in the XL folder

The folder documentation contains guides and instructions on how to install, run the engine. It also contains documents that describe the engineering aspects of the solution.

• Install.md - How to install the solution on your computer.
• Running.md - How to run the engine whenever you need to batch SEPA payments.
• Versioning.md - The development history of this tool.

Help the project

Click here to read how you can help the project.