How to Get Started With Payments and Finance Process Automation From Within Excel

Are manual process bottlenecks impeding your ability to grow and scale? We find that payments and finance operations that are over reliant on manual tasks are often the root cause. They can easily be overloaded to the point that they become a barrier to growth. Today, being over reliant on manual processes in payments and finance operations is no longer acceptable. There is a wide array of technology solutions now available to automate finance individual tasks, a series of workflows or entire end to end process flows. These automation solutions are varied from basic to more complex. They have matured enormously over the past few years, and are now both accurate, easy to set up, and low cost. It goes without saying that all process automation technologies are already benefiting from a dose of AI - a trend that will no doubt accelerate exponentially over the coming months let alone years. Operational excellence in process automation will increasingly become an important source of competitive advantage. So streamlining and automating operational processes should be a priority for every business. But the road to streamlining and automating operational processes is admittedly a long and difficult one. So how do you get started? The appropriate entry point will depend on many factors but let’s start with a practical first step.  Microsoft Excel continues to be the go-to application for payment and finance teams but how aware are you of the latest upgrades and developments in Excel that have turned it into a serious automation tool? Lets take a closer look.

Microsoft Excel Scripts

Microsoft has introduced two new features that combined offer a significant step up in automation. First, Microsoft Office Scripts, and second Microsoft Power Automate. Let’s look at Scripts first. Office Scripts released as part of the Office 2020 update allows you to automate many of the tasks that you can already automate using VBA. Both VBA and Office Scripts allow you to record and save actions inside an Excel workbook. But there are some significant and important differences in the way Script operates. Excel Macros and the VBA programming language that they are written in have been enabling users to automate tasks since way back in early ‘90s. Macros continue to have a role to play going forward, but there are significant drawbacks in using macros that Office Scripts addresses. First and foremost, security in Macros / VBA is complicated. VBA has the same level of access to computers as the Excel application itself.

This means that malicious code can find a way into an unsuspecting user's computer and cause a lot of damage. To help prevent malicious VBA, users are faced with endless warnings when they attempt to open macro enabled workbooks. The security controls around macros have in my experience been a major hurdle in effectively sharing and using macros. The issue stems from the fact that in VBA you store the code in modules which are saved as part of the Excel workbook. Office Scripts solves this problem by severely restricting what the code has access to. In an Office Scripts script your code can only talk to the Excel file that the code is running in. Secondly, rather than VBA, Office Scripts uses the TypeScript language, which is based on JavaScript. Thirdly, Office Scripts code is stored in a script file with an ‘osts’ extension. Script files are saved to OneDrive or SharePoint and stored separately to the Excel workbooks. Finally, a significant limitation of VBA is that it only runs in the desktop version of Excel, it cannot connect to the web. Office Scripts on the other hand can be run in both the desktop and web versions of Excel. All the above means that Office Scripts offers the automation possible with VBA within Excel, plus it can connect to the web to connect and integrate with other applications to trigger a script. This is where Power Automate comes in.

Power Automate

Microsoft announced the renaming from Microsoft Flow to Microsoft Power Automate back in 2019. Part of cloud based Microsoft Power Platform, Power Automate is a tool that helps you create automated workflows between multiple apps. It can be used to trigger Office Scripts, giving control of executing scripts outside of the workbook environment. For example, you can run a scripts on a schedule, or trigger them in response to actions such as emails. So the combination of Office Scripts and Power Automate transforms automation within XLS. For finance and payment teams this revamped XLS represents the first level of process automation.  

Developing A Processes Automation Roadmap

Not all processes should nor can be automated. Besides being manual, processes that are good candidates for automation typically share 4 common characteristics. To qualify they should firstly be repetitive and secondly time-consuming processes that are performed on a regular basis such as daily, weekly or monthly. Thirdly, they must be processes that are well understood, standardised and documented. Finally, they are prone to human error. Key entering sales data into an XLS sheet on a monthly basis is a good example of a repetitive, time consuming, standardised process where humans can easily make mistakes such as entering one to many zeros or accidently deleting a row or formula. So finance automation not only saves time and costs, but also reduces errors and ensures greater consistency.

Next Steps

Streamlining and automating operational processes will enable you to efficiently scale and profitably grow your business. Building proof of concepts and testing and learning with XLS scripts and Power Automate is a great way to get started. From there you can more systematically identify processes suitable for automation, quantify their business impact, develop and execute an implementation roadmap.

The content of this article does not reflect the official opinion of Edgar, Dunn & Company. The information and views expressed in this publication belong solely to the author(s).