100
We have connected our account online by Yodlee Bank Feeds service in Business Central, After connecting bank, it is showing real time balance.
How can online import bank statement in Business Central for Bank reconciliation purpose ?
Steven, good answer but Excel is also a party in this. It is important to make sure that the csv file has the same date format as that of Data Exchange Definition.
Steven, auto matching based on text is ambiguous. I would have liked it if it was based on the check number. With text based matching, there can be matching of wrong checks. Any idea to achieve matching based on check number?
Nick,
Try the Date Format = dd/M/yyyy
Thanks,
Steve
Here's my Data Exchange Definition:-
I've set the BC Regional Settings to English (United Kingdom). In the Data Formatting Culture column, I've tried entering 'en-GB', 'en-gb' & 'GB'. I tried setting the Statement Date to 01/04/2021. None have had any effect.
Why is it changing the month?
Nick,
You are correct the Date is the issue. We need to check the Data Exchange Definition, used on the Bank Account Card, for Bank Statement Import.
After you have the name, lets look at it. Go to Data Exchange Definition.
On the Posting Date Column Definition line, you need to define the Culture and Format. Mine us US.
Hope this helps.
Steve
I've followed the procedure for importing a CSV Bank statement into the Bank Acc. Reconciliation page of BC and it is importing. One question though on the Transaction date? In the CSV file, the date value is, for instance: 12/04/2021. Once it's imported it has been transformed into value: 1/12/2021. I'm not concerned with the fact that it has been converted into an American date. My question is why has the month been changed 04 April to 01 January? This has happened to all rows in the CSV file so if the date was 09/04/2021 it becomes 1/9/2021 and so on? Why is this happening?
Thank for reply and valuable time.
Vijay,
Here is an overview of Bank Reconciliation employing the imported Bank Statement. You perform bank reconciliation to make sure that your various business transactions and expenses are reflected correctly in the company books. You do this by comparing and matching entries in your internal bank accounts with bank transactions at your bank, and then posting the balances to your internal bank accounts to make totals available to finance managers. Bank reconciliation is also a practical way to discover and resolve missing payments and bookkeeping errors.
The following describes how to perform bank reconciliation with the Bank Acc. Reconciliation page.
Tip - You can also reconcile bank accounts on the Payment Reconciliation Journal page when you process payments. Any open bank account ledger entries related to the applied customer or vendor ledger entries will be closed when you choose the Post Payments and Reconcile Bank Account action. This automatically reconciles the bank account for the payments that you post with the journal.
Note - In the North American versions, you can also perform this work on the Bank Rec. Worksheet page, which is better suited for checks and deposits but does not offer import of bank statement files. To use this page instead of the Bank Acc. Reconciliation page, deselect the Bank Recon. with Auto. Match field on the General Ledger Setup page.
The lines on the Bank Acc. Reconciliation page are divided into two panes. The Bank Statement Lines pane shows either imported bank transactions or ledger entries with outstanding payments. The Bank Account Ledger Entries pane shows the ledger entries in the internal bank account.
Reconciling bank transactions with internal bank entries is referred to as matching. You can choose to perform matching automatically by using the Match Automatically function. Alternatively, you can manually select lines in both panes to link each bank statement line to one or more related bank account ledger entries, and then use the Match Manually function. The Applied check box is selected on lines where entries match.
Note - If bank statement lines relate to check ledger entries, you cannot use the matching functions. Instead, you must choose the Apply Entries action, and then select the relevant check ledger entry to match the bank statement line with.
When the value in the Total Balance field in the Bank Statement Lines pane equals the value in the Balance To Reconcile field in the Bank Account Ledger Entries pane, you can choose the Post action. Any non-matched bank account ledger entries will remain on the page, indicating some discrepancy that you should resolve to reconcile the bank account.
Any lines that cannot be matched, indicated by a value in the Difference field, will remain on the Bank Acc. Reconciliation page after posting. They represent some kind of discrepancy that you must resolve before you can complete the bank account reconciliation. Typical business situations that may cause differences:
| Difference | Reason | Resolution |
|---|---|---|
| A transaction in the internal bank account is not on the bank statement. | The bank transaction did not occur although a posting was made in Business Central. | Make the missing money transaction (or prompt a debitor to make it), and then reimport the bank statement file or enter the transaction manually. |
| A transaction on the bank statement does not exist as a document or journal line in Business Central. | A bank transaction was made without a corresponding posting in Business Central, for example a journal line posting for an expense. | Create and post the missing entry. For information on a quick way to initiate this, see To create missing ledger entries to match bank transactions with. |
| A transaction in the internal bank account corresponds to a bank transaction but some information is too different to give a match. | Information, such as the amount or the customer name, was entered differently in connection with the bank transaction or the internal posting. | Review the information, and then manually match the two. Optionally, correct the information mismatch. |
You must resolve the differences, for example by creating missing entries and correcting non-matching information, or by making missing money transactions, until the bank account reconciliation is completed and posted.
You can fill in the Bank Statement Lines pane on the Bank Acc. Reconciliation page in the following ways:
The Bank Statement Lines pane will be filled with bank transactions according to an imported file or stream provided by the bank.
To enable import of bank statements as bank feeds, you must first set up and enable the Envestnet Yodlee Bank Feed service, and then link your bank accounts to the related online bank accounts.
Tip - You can also import bank statement files in comma or semicolon delimited format (.CSV). Use the Set up a bank statement file format assisted setup to define bank statement import formats and attach the format to a bank account. You can then use these formats when you import bank statements in the Bank Account Reconciliation page.
The Bank Statement Lines pane will be filled according to invoices in Business Central that have outstanding payments.
The Bank Acc. Reconciliation page offers automatic matching functionality based on a matching of text on a bank statement line (left pane) with text on one or more bank account ledger entries (right pane). Note that you can overwrite the suggested automatic matching, and you can choose to not use automatic matching at all.
On the Bank Acc. Reconciliation page, choose the Match Automatically. The Match Bank Entries page opens.
In the Transaction Date Tolerance (Days) field, specify the span of days before and after the bank account ledger entry posting date within which the function will search for matching transaction dates in the bank statement.
If you enter 0 or leave the field blank, then the Match Automatically function will only search for matching transaction dates on the bank account ledger entry posting date.
Choose the OK button.
All bank statement lines and bank account ledger entries that can be matched change to green font, and the Applied check box is selected.
To remove a match, select the bank statement line, and then choose the Remove Match action.
On the Bank Acc. Reconciliation page, select a non-applied line in the Bank Statement Lines pane.
In the Bank Account Ledger Entries pane, select one or more banks account ledger entries that can be matched with the selected bank statement line. To choose multiple lines, press and hold the Ctrl key.
Choose the Match Manually action.
The selected bank statement line and the selected bank account ledger entries change to green font, and the Applied check box in the right pane is selected.
Repeat steps 1 through 3 for all bank statement lines that are not matched.
To remove a match, select the bank statement line, and then choose the Remove Match action.
Sometimes a bank statement contain amounts for interest or fees charged. Such bank statement lines cannot be matched because no related ledger entries exist in Business Central. You must then post a journal line for each transaction to create a related ledger entry that it can be matched with.
On the Bank Acc. Reconciliation page, choose the Transfer to General Journal action.
On the Trans. Bank Rec. to Gen. Jnl. page, specify which general journal to use, and then choose the OK button.
The General Journal page opens containing new journal lines for any banks statement lines with missing ledger entries.
Complete the journal line with relevant information, such as the balancing account.
To review the result of posting before you post, choose the Test Report action. The Bank Account Statement report opens and shows the same fields as at the header of the Bank Acc. Reconciliation page.
Choose the Post action.
When the entry is posted, proceed to match the bank statement line to it.
Refresh or reopen the Bank Acc. Reconciliation page. The new ledger entry will appear in the Bank Account Ledger Entries pane.
Match the bank statement line with the bank account ledger entry, either manually or automatically.
If you discover a mistake in a posted bank reconciliation, you can use the Undo action on the Bank Acc. Statement page to correct the mistake. When you undo a posted bank reconciliation, the entries will be moved to the Bank Reconciliation page and marked as Open, meaning they are not reconciled. You can then correct the bank reconciliation and post it again.
Note - In the North American version, to use the Undo feature for posted bank reconciliations and bank statements you must turn on the Bank Recon. with Auto-Match toggle on the General Ledger Setup page. The Undo feature is not available for bank statements posted from bank reconciliation worksheets.
The bank statement number used for the new bank reconciliation is taken from the bank account as is the Balance Last Statement. You can change these values before you start a new bank reconciliation. However, when you create a new bank reconciliation, Business Central checks whether the statement number is already assigned to a posted bank statement. If the number is in use, but you want the new bank statement to use it instead, you can use the Change Statement No. action on the Bank Acct. Reconciliation page.
The following are a few examples of how to fix a mistake on a posted bank reconciliation with or without using the same statement number.
You did bank reconciliations for January, February, and March. The bank statement number was 100 for March. Later, you discover that March only included entries until the 30th, which means entries for the 31st are missing. So, you need to redo the bank reconciliation for March. In this case, we'll open the Bank Acc. Statement page, choose the statement for March, and then choose Undo.
The new bank reconciliation is given the statement number 101. To reassign the number 100, choose Change Statement No. and enter 100.
Tip - Remember to set the appropriate Statement ending date (in this example, that is March 31), and edit the Balance Last Statement field.
You did bank reconciliations for January, February, June, and July. You discover that February was incorrect. Let's assume it had statement number 100. As in the Example 1, you use the Undo and Change Statement No. actions to change the statement number as in example #1 above and you can now redo February bank reconciliation.
After you post the corrected bank reconciliation for February, on the corresponding Bank Account card, the Last Statement No. field will show 100, and the Balance Last Statement field will show the ending balance for the February statement.
If the next bank reconciliation you do is for March, Business Central will assign 101 as the statement number and give it the correct Balance Last Statement.
If the next bank reconciliation you do is for August, consider changing the values in the Last Statement No. and Balance Last Statement fields on the Bank Account card before you create the next bank reconciliation, or use the Change Statement No. action and also change the value in the "Balance Last Statement" field on the bank reconciliation page.
Note - The statement number is important when you do bank reconciliations with imported CAMT files that contain statement numbers, or when you reconcile based on printed bank statements. If you just download a range of bank transactions from your online bank, the statement number is usually not important.
The Balance Last Statement is kept on the bank account to minimize mistakes when doing bank reconciliations, but it's also editable, allowing you to do your bank reconciliations in any order you want. This also means that if you undo a bank statement, the new ending balance might not be the balance last statement on the next bank statement. There's no feature that allows you to move a balance forward to all subsequent bank statements, so be aware of this when using Undo.
Hope this helps.
Thanks,
Steve
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
#1
André Arnaud de Cal...
291,280
Super User 2024 Season 2
#2
Martin Dráb
230,214
Most Valuable Professional
#3
nmaenpaa
101,156
Share
Report
All responses (
Answers (