Part 1: Setup the Advanced Bank Reconciliation Import Process | Microsoft Dynamics 365

Hello People!

In this article, I'll cover how you can set up the Advanced bank reconciliation import process in Microsoft Dynamics 365.

The Advanced bank reconciliation feature lets you import bank statements. The imported bank statement can then be automatically reconciled from bank transactions within Microsoft Dynamics 365.

Here are the steps in the advanced bank reconciliation flow.

1. Set up a bank statement import.

• Import bank statements through the data entity framework.
• Three typical bank statement formats are built-in: ISO20022, BAI2, and MT940.

2. Import Bank Statement & define the bank reconciliation matching rules.

• A reconciliation matching rule is a set of criteria that are used to filter bank statement lines and Microsoft Dynamics 365 Finance bank transaction lines during the reconciliation process. Depending on your business practice, you can set up more than one matching rule to automate and optimize your reconciliation process.

3. Reconcile bank statements with Dynamics 365 bank transactions.

• Perform automatic matching and creation of reconciliation journals.
• View bank statements and Dynamics 365 bank transactions side by side.
• Automatically post Dynamics 365 bank transactions if they appear on a bank statement but don't appear in Dynamics 365.
• Generate a reconciliation statement.

Follow the steps mentioned below to set up the Advanced bank reconciliation import process:

Step 1: Download setup files from Visual Studio

In the example, I'll be setting up the import of MT940 bank statements. Following files are required to set it up:

1. SampleBankCompositeEntity.xml
2. MT940TXT-to-MT940XML.xslt
3. MT940XML-to-Reconciliation.xslt
4. BankReconciliation-to-Composite.xslt

You can refer to the Microsoft Documentation to inquire about the files required for the other formats (e.g. ISO20022, BAI2).

Open the Resources Section in Visual Studio

Click on Resources.

Search for the relevant Bank statement import setup files (highlighted above).

Click on the file you need to download.

Step 2: Setup the import of MT940 bank statements (Data Management)

• Go to Workspaces > Data management.
• Click Import.

• Enter a name for the format, such as MT940.
• Set the Source data format field to XML-Element.
• Set the Entity name field to Bank statements.
• To upload import files, click Upload, and then browse to select the SampleBankCompositeEntity.xml file that you saved earlier.
• After the Bank statements entity is uploaded and the mapping is completed, click the View map action for the entity.

• The Bank statements entity is a composite entity that consists of four separate entities. In the list, select BankStatementDocumentEntity, and then click the View map action.

• On the Transformations tab, click New.
• For sequence number 1, click Upload file, and select the MT940TXT-to-MT940XML.xslt file that you saved earlier.
• Click New.
• For sequence number 2, click Upload file, and select the MT940XML-to-Reconciliation.xslt file that you saved earlier. Note: Transformation files are built for the standard format. Because banks often diverge from this format, you may have to update the transformation file to map to your bank statement format.
• Click New.
• For sequence number 3, click Upload file, and select the BankReconciliation-to-Composite.xslt file that you saved earlier.
• Click Apply transforms.

Step 3: Create Bank Statement Format

After the format processing group is set up, the next step is to define the bank statement format rule for MT940 bank statements.

• Go to Cash and bank management > Setup > Advanced bank reconciliation setup > Bank statement format.
• Click New.
• Specify a statement format, such as MT940.
• Enter a name for the format.
• Set the Processing group field to the group that you defined earlier, such as MT940.
• Set the File type field to txt.

The import setup for Advance Bank Reconciliation is complete now. This is just the initial configuration, it will get more tricky as we move on. I'll cover the following points in my next article:

1. How to import a bank statement.
2. How to define matching rules & transaction code mapping.
3. Reconcile bank statements with Dynamics 365 bank transactions.

I'll also discuss the challenges faced in implementing the process of Advanced bank reconciliation. Stay tuned for the next article!

Feel free to share any feedback.

Muhammad Ahmad Khan

ERP Consultant

ahmad-khan17@hotmail.com