Modern Bank Reconciliation - End to End Automation from Bank SFTP to D365 Reconciliation

In version 10.0.36 Microsoft released feature: Automatic importing bank statement from SharePoint folder. https://learn.microsoft.com/en-us/dynamics365/release-plan/2023wave2/finance-supply-chain/dynamics365-finance/automatic-bank-statement-importing

Reference -

Set up advanced bank reconciliation import by using Electronic reporting - Finance | Dynamics 365 | Microsoft Learn

Description: Touchless Bank Reconciliation Process

• Automated Delivery: The bank sends an email with the statement attachment to a dedicated mailbox / SFTP.
• Power Automate Routing: A flow automatically detects the email / SFTP, extracts the file, and saves it to a pre-defined folder in SharePoint.
• Batch Import: A recurring batch job in Dynamics 365 "pulls" the file from the SharePoint folder into the system at set intervals (3min, 10 min or even a day as per user requirement).
• Touchless Execution: By enabling the "Reconcile after import" option, the system automatically validates the statement, creates a worksheet, and runs matching rules, completing the reconciliation without manual intervention.

Below is Complete Flow

SharePoint and Power Automate enable touchless bank reconciliation by automating the end-to-end process of receiving, importing, and matching bank statements within Dynamics 365 Finance. This integration eliminates the need for manual file uploads and initial reconciliation steps.

The Role of Power Automate

• Power Automate acts as the delivery mechanism that brings bank statements into the system's reach.

• Triggering the Flow: A flow is typically configured to monitor a dedicated treasury mailbox.

• Automatic File Retrieval: When a new email arrives—for instance, one with a subject line containing "bank statement" and an attachment—Power Automate automatically picks up the file.

• Transfer to SharePoint: The flow then saves that attachment directly into a specific folder in SharePoint, such as a "Main" folder designated for new imports.

Achieving a "Touchless" Process

• The transition from a simple file import to a "touchless" experience is completed through several automated steps within Dynamics 365:

• Automatic Batch Import: Instead of a manual "push," Dynamics 365 uses a batch job that runs at set intervals (e.g., every few minutes) to "pull" new statements from the SharePoint folder into the Erp.

• Reconcile After Import: By enabling the "Reconcile after import" option, the system automatically validates the statement, creates a new reconciliation worksheet, and runs the default matching rules as soon as the file is uploaded.

• Advanced Matching Rules: The system uses enhanced matching rules—such as one-to-many matching, clearing of bridging transactions, and automatic voucher generation—to reconcile lines without human intervention.

7. Power Automate

Using power automate to move files from email to SharePoint folder:

You can refer to the link: Save my email attachments to a SharePoint document library | Microsoft Power Automate.

• Check from email address: set the from email (Email of Bank):

• In Create file step, set the SharePoint link:

Save and test to make sure that Flow is run successfully.

SharePoint folder

Create 4 folders in SharePoint to handle this process flow as below:

1. Main
2. Imported
3. Imported with Warning
4. Failed

2. Configure the connection between D365 and SharePoint

Path: Go to Organization administration > Document management > Document management parameters.

Enter the SharePoint link in Default SharePoint server field:

Click Test SharePoint connection to make sure that it is connected successfully.

Now there is Catch, we need to test the interactive session as well as the batch session

1. Interactive session
2. Batch session

Note: - Configure document management - Finance & Operations | Dynamics 365 | Microsoft Learn

3. Configure Document types link to the SharePoint folder

Path: Go to Organization administration > Document management > Document types.

Create 2 document types corresponding with 2 folders in SharePoint:

In the SharePoint Address field, click Edit to choose the folder:

Now we have to create 4 document types:

• BRS_Main: link to Main folder in SharePoint.
• BRS_Imported: link to Imported folder in SharePoint.
• BRS_Imported with Warning: link to Imported with Warning folder in SharePoint.
• BRS_Fail: link to Fail folder in SharePoint.

Note: - We faced issue when doing this activity manually from the front end, so we used data management.

We create

4. Import Electronic Reporting Configuration from Dataverse Repository for Advance bank reconciliation formats

Path: Go to Organization administration > Workspaces > Electronic reporting.

Click Repositories in Microsoft provider -> Open ->

Import the following 4 files 3 are related to Reconciliation formats and 1 is related to Mapping to destination

1ABR bank statement Mapping to destination & ABR MT940 format (Choose ABR because It is the latest format):

After imported successfully, you can check the format in the Reporting configuration:

5. Configure Bank account

Path: Go to Cash and bank management > Bank accounts > Bank accounts.

In the Reconciliation FastTab, set the parameters as the screenshot:

• Statement format: Create a statement format link to bank statement format from electronic reporting.
• Reverse debit credit mark: This option displays correctly Debit/ Credit side between D365 and bank statement file.
• Reconcile after import: If select = yes, the bank statement will be reconciled automatically based on value in Default matching rules set field after import successfully.

6. Electronic reporting source

Path: Go to Organization administration > Electronic reporting > Electronic reporting source.

It is used to configure the mapping between bank statement format and SharePoint folder where placing bank statements and Bank statement archiving:

• Name: This is a text field. Type a value.
• File name mask: extension of a file in SharePoint's folder:

*.txt: filter only files that have the .txt extension.

If it is blank: Read all files in the folder.

In Settings:

• Enable: select = Yes
• Document type for input sources: Select a document type for Bank statement file
• Document type for imported files: Select a document type for Archived Bank statement.

8. Setup Batch job to get the file from SharePoint and import bank statements automatically

Path: Go to Cash and bank management > Bank statement reconciliation > Bank statements.

Click Import statement and set the parameter as the screenshot:

• Select Batch processing = Yes
• Click Recurring to set up the recurrence.

The configuration is completed. Now you can try to send an email with the attached bank statement file (MT940) from the email setup in Power Automate and see the result.

Process Flow

• When the bank sends an email with the attached bank statement file (MT940) to the specific email, Power Automate will run and move the file to the SharePoint folder:

• Batch job run to get the file and import it to D365. Check the result in the history log:

Path: Go to Organization administration > Electronic reporting > File states for the sources.

• In the SharePoint folder, the bank statement file has been moved to Archiving folder:

• The bank statement import successfully:

IV. Conclusion

This is a great feature for the advanced bank reconciliation process. I have some key notes below:

When enabling this feature, standard behaviour does not allow to upload bank statement files manually for the statement format that is linked to SharePoint. In case you want to upload manually, you need to duplicate the bank statement format (ABR MT940) in electronic reporting for import automatically from SharePoint and upload manually. Do steps as following:

1. Create new format that is derived from original format:

Click Create configuration.

Select the format and change status to Completed:

2. Go to Cash and bank management > Setup > Advanced bank reconciliation setup > Bank statement format.

Setup new bank statement format for new format created in previous step:

3. In Bank statement form, click Import statement => select a value in Bank account field, then select the value created in step 2 in Statement format field => Click Browse to choose the file => OK:

• If you want to set up different folders for different banks in SharePoint, you need to separate bank statement format for each bank (Duplicate the bank statement format (ABR MT940) in electronic reporting).

Thanks to Ramit Paul Karthik S Ramita Chaurasiya

Automatic importing bank statement from SharePoint folder and combining with Power Automate for an end-to-end solution | LinkedIn - Nguyễn Hoài Thắng