Building a BotBy
Robotic process automation (RPA) has grown to be an accepted part of the accounting and finance landscape. Much has been written about how the technology can free up staff from mundane, manual processes to focus on more value-creating activities.
As Loreal Jiles noted in the IMA® (Institute of Management Accountants) Statement on Management Accounting Transforming the Finance Function with RPA, “Organizations that have successfully implemented RPA at scale have seen exponential operational efficiency, elimination of undesirable manual work, and millions of dollars in financial savings.”
RPA is also a key component of the data analytics supply chain, which includes data collection using RPA; preparation using extract, transform, and load (ETL) tools; and analytics and visualizations (see Figure 1). After using RPA, data wrangling and data visualization are the two follow-up steps to clean, join, and summarize raw data, which can then be used to tell a story.
While RPA first gained traction at large corporations, implementing RPA doesn’t need to be a complex, involved undertaking. The tools and technology for effective RPA have evolved to the point where they’re now easily available and accessible to companies of any size and individuals. To show how you can begin your RPA journey, we’ll walk you through the development of a bot to automate a basic task and outline some of the risks involved in working with RPA.
For any accounting or finance professional interested in incorporating RPA solutions into an organization, getting started doesn’t need to be intimidating. The day-to-day work of accountants can be plagued with repetitive, monotonous tasks that present potential opportunities for automation. Whether it’s extracting invoice information into databases, logging on to information systems, or getting information from the internet, these processes can be automated with RPA and performed in batches rather than individually, drastically reducing the time required to complete them while cutting down related expenses. At the same time, RPA increases accuracy and precision, ultimately improving data integrity.
One common task that can be easily automated with RPA is entering data from an Excel workbook into preformatted PDF forms. For instance, with a manual accounts receivable billing process, an intern or associate might be tasked with transferring invoice data for each customer from an Excel file into a preformatted master invoice PDF file. The PDF will then be distributed to the respective customer for billing. This exact process is repeated for each customer, taking hours to complete.
Any similar process that requires repeatedly transferring data from an Excel spreadsheet into a PDF form (or forms) is ripe for automation. To carry out the task manually, users would open the Excel file and view the data table. From there, they’d open the PDF, type information into the respective fields, and save the completed PDF file. This process would be repeated for all documents until the task is finished.
Due to the task’s repetitive nature, users can complete this task much more easily with RPA. Instead of manually copying data from the Excel files to the PDFs, it’s possible to design a bot to automate this process. Let’s see what this entails.
CHOOSE THE SOFTWARE
Before you can begin creating the bot, you need to first select an RPA tool to use. For our demonstration, we’re going to use a tool from UiPath—specifically, the UiPath Studio. We chose to illustrate an example using UiPath due to the availability of a no-cost version and the ability to design a bot without the need for coding expertise. UiPath offers many options designed to execute different parts of the basic components of RPA: recorder, development studio, plugin/extension, bot runner, and control center. We’ll focus on UiPath Studio, which is a flowchart-based modeling tool that provides a visual interface where users can design bots.
We have no vested interest in UiPath specifically—we encourage you to explore the available tools and choose the one that best fits your needs and resources. Another popular RPA option is Power Automate, a Microsoft product that’s similar to UiPath in that it’s a flowchart-based software that can be accessed via a mobile application, desktop application, or web browser extension.
While there are two different free trial options, Power Automate doesn’t offer a version that’s fully free, like UiPath. Blue Prism is another flowchart-based software, but it doesn’t provide any free version for users who may only need to perform simple RPA tasks and only offers a desktop application, whereas UiPath and Power Automate provide both a desktop and a mobile application. Finally, Automation Anywhere is a script-based software that requires a stronger programming background for RPA tasks. See Table 1 for a comparison of these four solutions.
In addition to the software, UiPath and the other RPA vendors provide other offerings to support the RPA community and help users. For example, UiPath also has a forum, marketplace, and academy available for users. Through forums, users can ask and answer questions related to challenges with RPA projects. They allow for collaboration with other UiPath users, build a community, and promote self-learning. The marketplace allows individuals to purchase built automation processes that can then be customized to fit their needs. The academy offers free training and courses. With this, inexperienced individuals can become proficient in designing RPA solutions, develop skill sets to apply to their professional work, and earn certifications to demonstrate competencies.
To access UiPath, individuals and teams have two options. The Community Edition is designed for developers and small teams. Always free, this edition allows for the use of two studios and three bots at a time. The only support provided is through user forums. Updates to the software are managed by UiPath. The Enterprise Edition is designed for enterprise deployments for large businesses. It comes with premium support and contains unlimited studios and bots. A 60-day free trial is available.
Because it’s free, the Community Edition serves as an excellent preview version. To get started, create a free account on uipath.com. From your account, you can download UiPath Studio to your computer. After installation and upon opening UiPath, select your license preference. In this case, log in with the free Community Edition. (Here is where you could also choose to activate a license for the Enterprise Edition.) Now you’re ready to begin designing a bot.
BUILD THE PROCESS
The Excel-to-PDF process we’ll automate is to take data and information from an Excel workbook to complete W-4 tax forms, a standard form that many will be familiar with. The bot will perform a series of activities to read the necessary information from an Excel file (e.g., the records of three employees), input the data into the respective places in the W-4 PDF form, and save the completed PDF file.
The Excel file in our RPA steps contains a worksheet named “TaxInfo.” Within the worksheet, the data table includes the first and last names of three employees under the column headers “FIRST” and “LAST.” The bot is also capable of handling a large volume of data, as the same bot design can process the W-4 tax forms for 1,000 employees (i.e., 1,000 rows of employee records in the Excel file).
Manually, this would involve six basic steps:
- Open the appropriate Excel file.
- Identify the individual data components (e.g., first name and last name) that will need to be entered into the PDF.
- Open the interactive PDF file.
- Enter the information into the entry fields in the PDF file.
- Save the PDF file with a unique file name.
- Close the PDF file.
To build a bot in UiPath essentially requires converting those steps into instructions that the software can follow (see Figure 2). Here are the steps for building the bot (download copies of the Excel, PDF, and bot files to follow along):
Step 1: Create the bot file and set up the flowchart. Open UiPath Studio and create a new Process. You will be prompted to enter a name and optional description. Once those are set, click Create. Within the Studio window, go to New and select Flowchart. A dialog will appear asking you to name it. To keep it simple, we’ll leave the default name of Flowchart, but in general, a good practice is to rename it so that it describes the overall objective of an RPA task. Click Create. The flowchart will now appear in the Design panel in the middle of the Studio window.
Step 2: Read the Excel data table. To do this, we need to add a sequence that will carry out the steps of the process: Click on the Activities tab located at the bottom of the left-hand panel. From the list of activities, double-click Sequence to add it to the Design panel. Within the Design panel, click an anchor point at the bottom of the Flowchart icon and drag it to the Sequence, connecting the two boxes.
The Sequence will contain all the activities that make up the bot. There are two main parts: (1) opening the Excel file and defining variables and (2) inputting data from Excel to PDF and saving the files. Double-click the Sequence box to view it. A plus sign and message will appear to “Drop Activity Here.” Click the plus sign to bring up a list of activities.
First, we need to open the Excel file and read the data, so we want to use “Read Range” under “Workbook.” Along the top of the list, type Read Range to find it in the drop-down menu and double-click to add it to the Sequence box.
Within Read Range, copy the file path of your Excel file by pressing Ctrl+Shift and right-clicking the Excel file, then select “Copy as Path.” Then paste the file path into the first input box (Ctrl+V). The bot should automatically add quotation marks to the file path. Now UiPath knows what file to use for its data.
In the second row, you need to indicate the name of the Excel worksheet and the range of the data in Excel; for instance, type TaxInfo in the first input box to specify the worksheet, and type “” in the second input box so the bot will read all the cells containing data in a worksheet.
Next, we’ll store the data in Excel as a data table in the bot. Click Read Range, locate the DataTable field in the Properties pane (on the right-hand side). Right-click the field and select Create Variable, then type in Taxtable (no quotation marks) as the variable name and press Enter. (See Figure 3 for the design of the Read Range tool and its properties.)
Step 3: Store data fields in Excel as variables. Next, we need to identify the data within the file so that the bot can grab it. Click the plus sign to add another activity. This time, we want to add “For Each Row” in Data Table. This is where we’ll identify and name the values that will be entered into the PDF document.
At the top of the For Each Row box, set it to ForEach row in Taxtable. In the body, add “Get Row Item” in “Data Table.” In the input file of Get Row Item, type in row, and in the Properties pane of the tool, type in “FIRST” for ColumnName. Right-click the Value field and select Create Variable, then type in FIRSTNAME as the variable name and press Enter. This step will allow the bot to read the fields in the first column of the Excel data and store the values for subsequent activities. (Figure 4 shows the design of the For Each Row tool, and Figure 5 contains the properties of the Get Row Item tool.) Repeat the same process to create a variable for the column representing employees’ last names.
Before moving to the bot design for the PDF inputs, make sure that the scopes of the three variables are broad enough that subsequent activities can reference those variables. Click on the Variables tab at the bottom and change the scope of all three variables to Flowchart (see Figure 6).
Step 4: Identify the data inputs in the PDF. Click the plus sign right after the last Get Row Item container and add a Sequence named “PDF Inputs.” Click the plus sign inside the Sequence and add a Start Process tool. Inside the Start Process tool, copy and paste the file path of your PDF file into the first input box.
Next, open the PDF file. Keep it open and displayed in the background, behind the UiPath application window. Returning to the flowchart, click the plus sign right after Start Process and search for “Send Hotkey.” Click the “Indicate on screen” selector and select the PDF window. Under the Key drop-down list, select Tab as the input key. This step anchors the input area onto the PDF window, and the Tab key sends the cursor to the first input field in the PDF form.
Next, search for the Type Into tool and use the “Indicate on screen” selector to find the location that we want to input employees’ first names. In the text entry field of Type Into, we’ll type in FIRSTNAME as the input value representing the values of the variable we just created. Repeat the process for the LASTNAME variable.
Step 5: Save the PDF file with a unique file name. Add a Send Hotkey tool. Within the tool, select the “Ctrl” and “Shift” selection boxes, and add an s in the Key drop-down list. This step is to save the PDF document with a different file name using the Ctrl+Shift+S shortcut to open the “Save As” dialog.
Return to the flowchart and add a Type Into tool. Within the tool, indicate the input field by selecting the “Indicate on screen” selector and typing the following as the input text: “W4Form ” + LASTNAME. This specific input text allows us to save the tax forms with different PDF file names.
Add a Send Hotkey tool and select Enter from the Key drop-down list. Close out the Save As window in the PDF document.
Step 6: Close the PDF application and run the bot file. Return to UiPath. Search for the Click tool and add it immediately after the last Send Hotkey. Use the “Indicate on screen” selector to select the “X” button, which will close out the PDF document.
Inside the Click tool, click the button that looks like three horizontal lines and select Edit Selector. Locate the “title” input field in the “Edit Attributes” area and modify the input field as *.* so that the bot can close out any open PDF document, regardless of its file name. Click OK. Now you can close the PDF file and save the bot file by clicking the Save icon at the upper left.
Using the drop-down menu in the Debug File icon, select Run File to run the bot. You’ll see that the bot creates three W-4 tax forms for three employees, with each containing their first and last names in the respective input areas.
RISKS OF RPA
As with any new technology or process, using RPA comes with risk. Being prepared for the risks—both expected and unexpected—can help mitigate any negative impact.
First, the timeless adage “garbage in, garbage out” applies. If a bot has a systematic error in a data input field, that error will carry over to all the looped documents. Second, certain bots might not be agile enough to cope with system updates. These might include updates that change the appearance of websites, PDF readers, or even system updates from Windows 10 to Windows 11. Third, the majority of RPA applications require the standardization of the database or the input documents. Handling handwritten documents and unstructured data, such as images and graphs, is also difficult. Finally, RPA doesn’t eliminate the need for human involvement. For example, most bots can’t deal with exceptions without including if-then statements, which requires certain levels of human judgments regarding the design of skipping and branching rules.
Aside from application risks, there are also organizational risks. Ultimately, the success of RPA depends on the success of internal controls and proper planning and communication. There must be effective internal controls for RPA within each of the five components of the Committee of Sponsoring Organizations of the Treadway Commission’s Internal Control—Integrated Framework: control environment, risk assessment, control activities, information and communication, and monitoring (see Bernice Lemaire—Harvey and David Harvey, “RPA Internal Controls Support Audit Readiness,” Journal of Government Financial Management, Summer 2020). For example, proper training of individuals and retaining those with RPA expertise enhance the control environment.
There must be proper segregation of duties (as control activities) to define the bots’ access and authorization rights. For the information and communication component, organizations should maintain adequate documentation about the design of the bots and the implementation and operation of the bots. Organizations should also periodically audit the bots (as a monitoring procedure) and perform risk assessments about the data integrity of the outputs from the bots.
The IT governance of robots can be challenging, and risks must be evaluated and managed (see Loreal Jiles, “Govern Your Bots!” Strategic Finance, January 2020). RPA governance should include preventative, detective, and corrective controls to quickly identify and mitigate any risks that arise. For example, there should be policies regarding the standardization of the inputs and outputs of the RPA processes and risk management procedures about handling exceptions and bot errors. Governance procedures should be relevant and align with the RPA operating model and stakeholder needs.
REAP THE BENEFITS
Implementing RPA solutions provides a number of benefits. For one, by transitioning away from manual entry, RPA eliminates certain human errors, such as typos. As a result, the input accuracy and overall integrity of the data are improved.
Another advantage is the increased efficiency of business processes. Bots can perform repetitive, manual tasks much faster than humans can. Doing so saves a considerable amount of time by eliminating redundancies and allows employees to focus on areas that require more complex thinking and provide more value, such as analytics. Along with this comes increased productivity and cost savings.
If you haven’t already begun exploring the possibilities of RPA for automating basic and manual accounting and finance processes, we hope this practical illustration will inspire you. While representing a change in the work of accounting and finance professionals, RPA is merely one step forward into the future. Coming next is intelligent process automation (IPA), which combines RPA with AI and machine learning.
This technology advances RPA from collecting and processing data to analyzing it and making complex decisions. Companies and professionals that embrace and utilize IPA will realize increased benefits with increased productivity and added value to customers. IPA will further change the accounting and finance landscape, redefining the problems being solved and the technologies and skills we use to solve them. Getting started with RPA can help you and your organization stay on the right path.