How does it work?
I've tried to make this application as user-friendly as possible. This means, settings are minimal. All you have to do is to copy and paste your bank statement for a given period (a month, for example) to the respective tab. Do the same for the cash book (your accounting journal entries) and define the following optional settings:
- Abbreviations. If your bank statement or your accounting entries use abbreviations, you may list them in a table. This will be useful to improve the search procedure while keeping your entries unchanged;
- Words to ignore. Some words are frequently used in descriptions but irrelevant for searching. For example, if all the payments contain the word "payment", this doesn't provide enough distinction between statements in order to facilitate the search process. Still, if you have a "payment landlord" or "payment taxes" you will have two strings different enough to provide an accurate search;
- Date flexibility. If you want to allow a certain degree of flexibility when searching for values within a time-frame, you can enter it here. For example, if date flexibility is set for 2 days, when the code searches for the amounts of January, 10 in one statement, it will look for the movements between January, 8 and January, 12 in the other.
And that's it! You will get 3 possible outcomes:
- Found amount and similar description;
- Found amount within time-frame but the description doesn't match exactly;
- Not found, when the amount wasn't found within the defined time-frame.
There's also the option to filter results in order to facilitate further analysis or corrections.
We tested this application with around 1.000 lines in each of the statements and it took a bit less than 2 seconds to complete the task in our system. I would be glad to know your comments about this.
Have a look at the image gallery below to see how the application looks and click the button 'Buy Now' to download the application instantaneously!
This spreadsheet is compatible with Microsoft Excel 2007 or above and uses macros.