Automate Bank Reconciliations using Pandas library in Jupyter notebooks

We, accountants, generally are very very calm people most of us think. But there are some things which can stretch the calmness of even the most patient accountant. Imagine going through thousands of lines of seemingly never-ending monthly bank statements and trying to match it against your bank ledger reports… This alone delivers how frustrating and time-consuming this process can get.

Most of us still rely on Excel for preparing such reports, however, no matter how good one can be in Excel — it is still too manual and rigid for me in doing bank recs. I decided to find my salvation using some other tool and that’s when I found the love of my life — Pandas library in Python. I know you guys love Pandas too and in fact, it is one of the most widely used out there.

In few lines of code I could achieve automatic matching of bank statements with bank ledger, identify unmatched records and even have fun along the way. It saves us more than an hour for doing bank recs using Excel. The only manual thing here is showing the path to where the files are and in blink of an eye, BOOM, the report is generated and saved!

Below I share steps and full code in Jupyter notebook using sample test bank statement and bank ledger numbers.

I will be using sample test bank statement and bank ledger report for easy explanation:

Sample of bank statement

Sample of bank ledger report

As we can see, we have difference of $14 between two reports where we will use Pandas to find all matching and un-matching entries and generate reconciliation report. Bank recs are done using range of techniques which differ from company to company, however, one of the most used techniques is “date+amount” concatenation technique to match the transactions. Where transaction dates and debit/credit amounts are concatenated in separate cell for each reports and then looked up for matches. It would look something like below:

Example: Date+Amount concatenation done in Excel in preparation for matching

Let’s move to coding now!

1. Import pandas library and read our csv files:

Files are ready to work with

2. Second step will involve pre-processing of our files to prepare for matching using ‘date+sum’ method.

Note that we have two separate columns done as part of pre-processing. Now this one is a simple example, but in real statement/GLs there are number of steps that must be made to prepare our data for matching.

3. Now, we can match these two files and see figures missing or posted incorrectly in our bank GL:

Only two entries out of five in Bank statement were posted correctly in GL. Corrective entries must be done for other unmatched transactions.

4. The matching is done, however, let’s present this data properly and ‘reader-friendly’ — let’s work more on ‘status’ column to give non-finance users more understanding and at the same time we can replace ‘NaN’s with proper zeros:

Let’s open and see our saved csv file:

Obviously, doing this with real bank statement and GL might take more steps in pre-processing or getting the data ready — but the end process is always same — to match and find missing/wrong transactions. You can also modify and add extra steps in your preparation and help to automate yet another manual process. Start small , do it all!