Import bank statements (MT940)

The purpose of importing bank statements is to perform reconciliation of payments sent to the bank, and payments received versus the cash amounts shown on the bank statement. The bank reconciliation is an important part of a company’s internal controls over its assets.

The most common formats of electronic bank statements are MT940, BAI2 and CAMT.053.

The following steps to import electronic bank statements will be required: 

  1.  The TMS(*) system picks up the bank statement file 
  2.  The TMS(*) system imports, and transfers the bank statement file to the SFTP repository  
  3.  Logic Apps picks up the file, works out the encryption, and transfers the file
  4.  Logic Apps transfers the file to Azure blob storage and a SharePoint repository   
  5.  D365 F&O picks up the file and imports it through the data management framework

Setting up bank statement import using format MT940 

Export resource files 

  1. Open Application explorer in Visual studio  
  2. Locate Resources and filter by BankStmtImport 
  3. Copy following resources to a local folder 
Resource 
SampleBankCompositeEntity.xml
MT940TXT-to-MT940XML.xslt
MT940XML-to-Reconciliation.xslt
BankReconciliation-to-Composite.xslt

Example:

Select SampleBankCompositeEntity.xml, right click and choose Open designer

Let the cursor on the name of the opened file to see where the resource file is stored

Copy the file to the local folder

Create the data project Bank statement MT940

  1. Navigate to the Data management workspace, then select Import  

  1. Enter the name of the data project 
  2. Select Add file 

  1. Select XML-Element in the Source data format field 
  2. Select Bank statements (target entity: BankStatementImportEntity) in the Entity name field 

  1. Click Upload and select the SampleBankCompositeEntity.xml file 
  2. Click View map 
  3. Click View map again 
  4. On the Map source to staging page, choose TRANSFORMATIONS
  5. Choose New and Upload file
  6. Choose MT940TXT-toMT940XML.xslt
  7. Repeat step until you have all the three resources in following order
Order
MT940TXT-to-MT940XML.xslt
MT940XML-to-Reconciliation.xslt
BankReconciliation-to-Composite.xslt
  1. Click on Apply transforms 

Setup Bank statement format MT940

  1. Navigate to Cash and bank management > Set up > Advanced bank reconciliation setup > Bank statement format 
  2. Click New 
  3. Enter a Name et Description
  4. Add the processing group setup in the previous steps

Configure the bank account to use Advance reconciliation option 

  1. Navigate to Cash and Bank management > Bank accounts, then select a Bank account to view details. 
  2. Under Reconciliation tab, set the Advance bank reconciliation option to ‘yes‘. This is a one-time setup, the system doesn’t allow to undo/change once set to yes.

TMS = Treasury Management Systems

One thought on “Import bank statements (MT940)

  1. Hello Antoine and thanks for this post. I am trying to follow the steps you indicated to import MT940 or CAMT53 files in an automated way by using the DataManagement API but I always have nothing in staging tables.
    I am able to import it manually by using the Data Management workspace but when using external tools to call the DataManagement API (with LogicApps or RIS), nothing is imported in staging…
    I have added the XML tag to encapsulate the data and the DataPackage does contains the right ressource files (the xslt) but still nothing is imported.

    Is there something specific to do in the DataPackage to be able to import Bank Reconciliation files using the DataManagement API ?
    Thanks in advance

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: