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:
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).
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.
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.
Introduction OpenVPN allows client computers to tunnel into a server over a single UDP or TCP port securely. This HOWTO article is a step-by-step guide that explains how to create the server and client OpenVPN configuration files that makes this possible. In the process this article explains how to create the public key infrastructure (PKI) so that a client can securely communicate with the server. OpenSSL is the foundation for the security functionality of OpenVPN. For this tutorial you will need the following software: OpenVPN. You can download the latest version of OpenVPN from https://openvpn.net/index.php/open-source/downloads.html EasyRSA is the tool people use to create the Public Key Infrastructure (PKI) for OpenVPN. Download the latest release of EasyRSA from https://github.com/OpenVPN/easy-rsa/releases . There is not installation required. Extract the contents of the archive into a folder. OpenVPN is available on various platforms. The generation of the
Microsoft 10 took a step backwards in the right direction. It brings back the start menu button in the lower left hand side of the screen. This was removed by Steve Sinofsky and his team. From a project management approach this was a case of ignoring the input of one important component of any successful project; the users. The lesson from Windows 8 is that one simply cannot and should not dictate to users what they need; in a market that is more customer-centric users want to state the features, feel and functionality of the products they will ultimately use. Well Mr Sinofsky is now part of Microsoft’s history. With Windows 10 we got back the start button. Tiles that appeared in Windows 8 live on but constitute an alternative to the product UI. Time will tell whether the tiles feature will survive into future incarnations of the product. The important thing is that anyone who upgrades from Windows 7 to Windows 10 will not feel lost and isolated. One problem I've encountered