Bank statement reconciliation is a pivotal yet often a dreaded task for accountants. Many find themselves grappling with the tedious process of gathering data, cleaning and transforming it, matching records, and finally generating file(s) adhering to a predefined template for ERP system uploads. It’s a time-consuming and boring task that can test one’s patience. However, this changes dramatically when it’s automated using R Language.
As someone who despises manual, time-wasting tasks, I started a small project to revolutionize this process. My first choice? Microsoft Excel’s Power Query. A powerful Excel engine to extract, transform, and load data (ETL). With it, I managed to reduce the entire process cycle time from nearly a full day to thirty-five minutes—a remarkable improvement, to say the least, but not yet satisfactory to me!
There were still moments when Power Query struggled, particularly when faced with the heavy task of converting Long data into Wide formats and calculating specific serial indices for over 80,000 rows. It became evident that Power Query had its limits.
But I couldn’t help but ponder the possibility of a more efficient solution. Enter my savior: the R programming language. With the assistance of my trusted package, data.table, and a few other essential packages, I crafted a script that astonishingly produced the same results in less than two minutes! The speed and efficiency were truly extraordinary.
To further enhance the automation experience, I developed a Power Automate (desktop) flow that executed the script without the need for the user to open R or RStudio. Here, I encountered a challenge—Power Automate lacks a ready-made action for running R scripts like it does for Python and VBScript. However, it does provide a workaround by connecting to the Command Prompt and seamlessly executing R scripts via command lines.
In the end, it was a successful project by integrating R with Power Automate. While I hold a special place in my heart for Excel, particularly Power Query, I’ve come to adore R for its unparalleled speed and efficiency in handling vast amounts of data.
NB It took me a while to put together the right command line to send to CMD for it to be able to execute R scripts. If you need help with it, leave a comment below.
