Product documentation
In This Topic
    Bank Statements
    In This Topic

    Use of Standard Bank Statements

    Standard Microsoft Dynamics 365 for Finance and Operations has functionality for Bank Statement and even something called Bank Reconciliation. Even though it has good functionality, if a customer does not use lockboxes and/or checks, they might not truly require it.

    We focused on developing our own solution within the General Journals because many customers do not use lockboxes and/or checks.

    But it is nice to have a dedicated Workspace, and use GER and other functionality in the area of Bank Statements. And that is why we have switched our primary focus to the support of importing files into the standard Bank Statements. We still support the importing directly into the General Journals, but we won't optimize that further.

    The importing into the Bank Statements is supported by the Generic Electronic Reporting framework (GER), and takes care of the initial import and validation of the bank file. After a successful import, the user processes it to a General Journal, which still takes care of the reconciliation/settlements.

    Bank Management workspace 3 tiles have been aded to the Bank Management workspace
    Imported bank statements Shows the number of bank statements with status 'Imported'.
    Journalized bank statements Shows the number of bank statements with status 'Journalized'.
    Open banking journals Shows the number of banking journals that haven’t been posted yet.

     

    Bank Statement formats

    CAMT053

    A standard import format for CAMT053 is available in the Dynamics Banking solution. All European banks use the same format, so no bank-specific formats are needed. It is an XML file, which allows for specific tags for specific information.

    If you need more information on CAMT053, this may be a starting point: https://www.sepaforcorporates.com/swift-for-corporates/a-practical-guide-to-the-bank-statement-camt-053-format/

    The mapping of the formats (both MT940 and CAMT053) is done in Bank statement import methods for general journal.

    CAMT053 batch transactions

    If a CAMT053 holds batch transactions, the number of transactions found, is stored on the Bank Statement tab, in addition to the Payment Information (using tag PmtInfId). The Payment Information, amongst others, holds the journalnumber of the outgoing payment journal. The bridging ledger account associated with this journal is used as ledger account in the bank journal line.

    Note: the system validates the number of transactions and the total value. If either differs between the imported statement and the found payment journal, the user will have to manually set the ledger account and fill out the payment journal through list / functions / update batch payment journal.

    On posting the bank journal, the standard functionality of processing bridging transactions is called for every voucher in the found payment journal.

    MT940 / MT940S

    Four MT940 and four MT940S formats are available in the Dynamics Banking solution. Those are the formats used by the major Dutch banks. The MT940 is the older variant, while the MT940S (=Structured) holds IBAN information, and ‘structures’ information like invoice-data, bank account, which makes it better to find.

    Supported banks:

     

    The banks are replacing the MT940(S) format with the CAMT053 format, but in some cases CAMT053 is not yet available, and sometimes the banks charge a premium.

     

    Additional remarks MT940 formats

    MT940 'standard'

    Though the MT940 format is a standardized format, banks tend to interpret this standard in slightly different ways. Therefore, it can be necessary to adapt the standard import routine in the system, in order to get the data in the right field.

    Refinement MT940S

    As more and more customers start using MT940S the variety in content increases. Most electronic banking software contains parameters, of which our customers have no understanding. Per situation an appropriate solution will need to be found.

    RABO EREF

    In the RABO-format the tag 'EREF' is used to find the voucher in the case of a return payment.

     

    BAI2

     The BAI2 format is primarily used in the United Kingdom and countries of the Commonwealth. It is a rather basic file, which does not have as much information as the CAMT053 or the MT940. As a result, not all reconciliation scenarios can be supported.

     

    Importing into the Standard Bank Statements

    Electronic Reporting

    The Electronic Reporting framework is standard Microsoft Dynamics 365 for Finance and Operations functionality, and allows users to configure formats for both incoming and outgoing electronic documents. Amongst many others, Bank Statements are an example of these documents.

    The standard Microsoft documentation on ER can be found here.

    The documentation on the setup of the mapping can be found here.

    Use the hyperlink to create an Import Format. CAMT053 is part of the ISO20022XML. There are other formats available too, and if there is a need for a specific format, it can be created without having to do real development.

     

    Bank statement formats

    In Cash and bank management / setup / Advanced bank reconciliation setup / Bank statement format a link is specified between the import format configuration and the Statement name. The latter is what the user will select when importing the statements.

    Bank Statement import

    Start in the workspace of Bank Management. In the left-hand tiles, use the large tile that reads 'Import Bank Statements'. It opens a dialog, into which the Bank Account needs to be filled out, the import format to be used, and the file to be imported.

    After importing, the result can be found under tile 'All Bank Statements'. It shows a list of Bank Statements, and clicking on one, opens the details.

    We won't use any of the standard reconciliation functionality, because it is rather limited. Instead, the import is validated and if all is correct, they are transfered to a General Journal. This transfering happens by clicking 'Create bank journal' in the upper menu bar. The system will use the new Journal Name on the Bank Account to create the journal. The new journal can be opened, using menu button "Bank journal(s)". Then the reconciliation functionality of Banking can be used.

    Validation

    Microsoft Dynamics 365 for Finance and Operations checks for gaps and overlaps between Bank Statements. It also compares the calculated ending balance with the imported ending balance, and the actual number of lines to the number specified in the header.

    Import banking statement via Azure storage folder

    Sometimes you may want to have the possibility to import more than one bank statement at one time and at a pre-defined point in time.
    When you setup a default import folder and archive folder for Banking statements via Microsoft Azure File Storage, it will allow you to then to importing the statement periodically via a batch job.

    After the Microsoft Azure File Storage has been created you then need to define the default Azure File Storage locations in General Ledger > Ledger Setup >Bank Reconciliation Setup > Import setup.

    Import setting

    General

    Validate button

    Validate the connection with Azure File Storage

    Azure

    Storage account
    Azure storage account name
    Storage key Azure storage key
    File share File share name
    Import folder Import folder name of the location where the Banking statement will be imported from
    Archive folder Archive folder name of the location where the imported file(s) will be moved to after being processed

    Note Folder/file location locations very per individual customer setup in Azure. Azure setup is the reponsiblity of the customer/partner. If needed a permier service can be offered for assistance in the setup. Contact HSO Innovation for further details. 

    Once the setup has been completed it is now possible to upload Bank statement to the Azure file location.

    When all the bank statements are present in the Azure Import file location go to the workspace of Bank Management. In the left-hand tiles, use the tile that reads 'All Bank Statements'. It opens the Bank Statement form, on the top of the form click on the “Import Folder” button.

    This will open the Import Bank Statement dialog. Here you can see the Import and Archive folders that have been predefined in the Import parameters. You also have the possibility to set up a recurring batch job to import the bank statements.

    Standard Bank Statements Journal Status: a second status field has been introduced to show the user if there are already journals for a statement, and what the status of those journal are.
    Imported The Bank Statement has been imported, and no journals have been created yet.
    Journalized One or more journals have been created, but they have not ben posted yet.
    Posted The created journals have been posted.
    Bank Management workspace 3 tiles have been aded to the Bank Management workspace
    Imported bank statements Shows the number of bank statements with status 'Imported'.
    Journalized bank statements Shows the number of bank statements with status 'Journalized'.
    Open banking journals Shows the number of banking journals that haven’t been posted yet.

    Bank Journals

    Using the 'Create bank journal' button in the Bank statement form copies the lines to a General Journal. Depending on the value in 'Split bank statement at # of lines' a single Bank statement can result in multiple Bank journals. In the dialog the user is able to indicate if Microsoft Dynamics 365 for Finance and Operations should search for Companies, Accounts (Customer, Vendor or Ledger) and immediately start the Reconciliation process.

    Create Bank Journal Periodic process: A new menu item that allows a user to Create bank journals across Bank Statements. By default, this is filtering on Bank Statement status 'Imported'. It can be set up in a batch job and process in the background. Functionality is similar to the menu item 'Create bank journals' in the Bank Statement form.
    Delete bank journals When Banking Journals have been created via Banking statements is only possible to delete the banking journal via the button Delete Banking journal. If a banking journal has been created directly in Dynamics Banking journal then it is only possible to delete the journal in Dynamics banking journal.