KNIME for Finance: Bank reconciliation

This is part of a series of articles to show you solutions to common finance tasks related to financial planning, accounting, tax calculations, and auditing, all implemented with the low-code KNIME Analytics Platform.

Rule number one in accounting is ensuring you always have a grasp on your company’s financial performance. You basically need to understand where money is coming from and going to. The process is called account reconciliation and it's crucial.

You only want correct values to be reported in the general ledger. By comparing financial records you can confirm they are consistent and complete. Discrepancies will uncover mistakes from simple errors through to serious fraudulent activities.

There are many different types of account reconciliation e.g., vendor reconciliation, business-specific reconciliation, intercompany reconciliation, or customer reconciliation.

This article focuses on bank reconciliation. It’s one of the most popular types of account reconciliation and compares a company’s internally recorded transactions e.g., the cash book, with externally recorded transactions e.g., bank statements. 

Get a summary of how to conduct account reconciliation in KNIME in this video and we'll walk through the solution in more detail in the write-up below.

We set ourselves the task to build a KNIME solution that handles account reconciliation in order to understand financial performance. This involves:

• Understanding where is your money coming from and going to
• Ensuring transactions are properly documented
• Aligning internal financial ledgers and statements with external financial statements.

Bank reconciliation process involves following these four steps:

Before being able to start your reconciliation process, first of all make sure to collect all available data, ensure that the data is complete, and bring it into a comparable format. 

We need to collect data from the company cash book and bank statements.

A company’s cash book is a subsidiary of the general ledger in which all cash-related transactions are chronologically recorded and it also contains an opening balance (i.e., the amount brought forward from the previous accounting period) and a closing balance (i.e., the amount  carried forward to the next period). 

A bank statement, provided by your bank, contains all account activities on your bank account, also chronologically ordered. A bank statement is usually provided once a month and states the opening and closing balances on the first and last day of the period, as well as each transaction amount debited or credited, and the total account balance.

Due to their nature, cash book data and bank statements are likely provided in different formats and file types. For example, bank statements are often provided as OFX, CSV, or even PDF, whereas cash books might be managed through accounting tools or the data is stored in a database and needs to be exported first. Collecting the data from these different sources can be challenging.

Collecting the data can be challenging due to the structure of the two records.

Whereas bank statements are usually structured as straight-forward tables (see the bank statement shown below), cash books tend to be in a more complex structure.

In general, a cash book is divided into two parts, a debit side and a credit side. Transactions recorded on the debit side refer to all sorts of receipts, so everything that increases our assets, and the transactions recorded on the credit side refer to all sorts of payments, hence everything that decreases our assets.

As a result, one data row might store multiple transactions, one on the Debit side and one on the Credit side (see the company cash book shown below).

We need to do some data wrangling to declutter the two-side structure into a simpler table structure. 

Once your data is in a comparable format, you can systematically compare each and every line of your bank statement and your cash book and check whether they each appear in both records.

Mark all matching transactions and flag those that are either missing or where the transaction amount is not matching to get a better overview.

Note. It is important to not only check whether a transaction appears in both records but also that their transaction amounts are equal. 

Now make an assessment of the situation. Calculate the difference between the cash book’s closing balance and the bank statement’s closing balance to get the unreconciled amount.

The overall aim of reconciliation is to step-wise reduce the unreconciled amount to zero which in turn means that the closing balances equal each other. 

In this step, we account for the differences detected in Step 2 and adjust the cash book and bank statement by calculating the adjusted closing balance for both records. 

There are in general three causes for such discrepancies: 

1. Omissions. These are activities on the bank account that are not yet included in the cash book. This comes from transactions of which the company does not know about until they appear on the bank statement, for example, bank fees or in case of missing receipts or non-sufficient funds. Such cases must be detected and the books must be adjusted accordingly.
2. Timing Differences. Sometimes, transactions are recorded in different time periods in the cash book and the bank statement, for example, when a payment has already been recorded in the cash book but has not yet been processed by the bank. Such timing differences usually adjust themselves in the future. 
3. Errors by the Accountant or the Bank. Those are erroneous transactions, for example, the recording of an incorrect transaction amount. Errors can happen on both ends but note that errors by the bank are rather rare. Hence, before considering this option make sure you have thoroughly checked every transaction and eliminated every other discrepancy before. 

After accounting for each discrepancy, we now have two adjusted closing balances which equal each other. The unreconciled amount should be zero. The adjusted closing balance corresponds to the True Cash Balance.

If your adjusted closing balances still don’t match, you need to go back each step and trace down the source of the error. If you haven’t closed your books properly in the previous period, you might find the error there.  

Once the adjusted closing balances are equal, and the unreconciled amount is zero, you can take it from here and prepare your journal entries. Then, the next time you do a bank reconciliation you won't face the same issues again.

For this blog post, I used sample data available on Kaggle which I added some additional synthetic entries to match the desired use case. Both records are provided as Excel files and are for the month of June, 2023 (see Figure 1 and Figure 2 below). The cash book is a single column cash book, the easiest type. Other types are double column or triple column. 

The solution workflow “Bank Reconciliation” (Figure 3) is available for download from the KNIME Community Hub and performs the following steps:

• Read the two records into KNIME Analytics Platform using one Excel Reader node each, and bring them into a comparable format such that both tables are of the following structure: Date, Particulars, Debit, Credit, Balance, CF, Description, and Transaction ID.   (Step 1)
• Compare each transaction in the two records. Transactions that are equal in both records are flagged green, transactions that are missing or show unequal transaction amounts are flagged red. The view created by this component also states the closing balances of both the bank statement and the cash book as well as the unreconciled amount (Step 2)
• Calculate the adjusted closing balances for the bank statement and the cash book (Step 3-1 and Step 3-2)
• Generate the Bank Reconciliation Statement and check whether the adjusted closing balances match after adjusting the records (Step 4)

In order to create a slightly more sophisticated solution, I decided to turn my workflow using the example data into a browser-based data app that lets the user interact easily with the underlying workflow. For example, upload their own bank statement and cash book data, or define a specific time period for the bank reconciliation to be performed. A workflow being deployed as a data app can also be shared with colleagues without them having the need to interact with KNIME Analytics Platform at all.  

To do so, I simply added two Date&Time Widget nodes as well as two File Upload Widget nodes to the workflow and encapsulated them into the “Select file & time period” component (Figure 4). 

I’ve then connected the flow variable output of the component to the two Excel Reader nodes to parse the Path variable of the selected file to the respective reader nodes. To account for the individually defined time period, I’ve added a Date&Time-based Row Filter node after each of the reader nodes and used the start-date and end-date propagated by the component to filter both records respectively. This all is part of Step 1. 

Besides the changes made to Step 1, the data reading, no other changes were required. You can see the final Data App workflow below.

In this blog post, we have demonstrated how you can semi-automate bank reconciliation on some sample data with a KNIME workflow and then interact with the data conveniently with a data app to read in your own data and define individual time periods for reconciliation. It then carries out bank reconciliation by comparing each and every transaction recorded in the bank statement to the transactions recorded in the company-owned cash book. As a result, the workflow produces a bank reconciliation statement that tells you at a glance how your books need to be corrected. 

The gif below shows the data app in action as controlled by the end user. 

The dashboard of the “Bank Statement vs. Cash Book” component (Step 2) highlights each transaction that is identical in both records and flags those that are either missing in one record or report different transaction amounts (below). This allows us to obtain an initial assessment of the situation.

The final page of the Data App shows the Bank Reconciliation Statement (shown below). It’s a summary of the detected discrepancies in our books and tells us how we need to prepare our journals in order to reflect the true financial situation of our company. 

Remember: Timing Differences correct themselves in the future, however, omissions and other errors need to be accounted for. 

In addition, it shows the unadjusted and the adjusted closing balances for both records and states the unreconciled amount. Ideally, this is equal to zero, but it could be the case that even after the bank reconciliation we still have discrepancies left in our data. If this is the case, you need to go back each and every step and detect, where you went wrong. It might be an issue carried forward from the previous period or a more severe error caused by an accountant or the bank. 

You could even enhance this blueprint workflow, for example, by adding the KNIME Reporting nodes which lets you file the composite view as a PDF report, or adding a Excel Writer to export the bank reconciliation results back to an Excel file.

In this example, I’ve used data from an Excel file. In reality, bank statements are often provided in OFX, CSV, or even PDF format, and also cash books come in various types.

If you’re using your own data with this workflow example, you might need to tweak the data reading step (Step 1) a little, so that your file structure can be properly processed. For example, to read in PDF files you can use the PDF Reader node.

As soon as you’ve adjusted the data access to fit your data structure, you can let the workflow do the rest and automate your monthly bank reconciliation.

Human error is one of the biggest pain points in the repetitive tasks that are part of account reconciliation. It's precisely these repetitive finance tasks that present the greatest opportunity for automation. With low-code/no-code data science you can create automated processes, and reduce the risk of error. Reuse the KNIME solution and schedule it to run automatically whenever you need.