bnkSEPA v 2.0

 This is an engine written in Python that takes a Microsoft Excel spreadsheet and converts into a file that will be processed by the 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 SCT file required by the bank. This file is then upload to the financial institution via their secure portal.

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]

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.

 

bnkSEPA is an engine written in Python that takes a Microsoft Excel spreadsheet and converts into a file that will be processed by the Financial Institution. Instructions including source code are on GitHub.

Download

bnkSEPA has been updated to version 2.0 so as to meet the requirements of the financial institution for which it is written.

Source, documentation and supporting files are available for download from https://github.com/chribonn/bnkSEPA.

Follow This, That and Maybe, the Other: