Automate your Accounting Account Reconciliations

Here is a scenario of Jill automating her account reconciliations. She goes through the process step by step by using basic problem-solving.

Jill has 40+ account reconciliations she has to complete every month. These account reconciliations use the same standard template, but each reconciliation requires different support. She has to take the reconciliation file from last month, carry it forward to the current period, and add subledger detail to support the current balance. This has to be done AT LEAST 40 times.

There is an alternative approach. There is a bunch of manual touchpoints that can be automated when completing an account reconciliation.

Can we automate the downloading of detail? Yes

Can we automate the inserting of detail into the account reconciliation file? Yes

Can we update the periods to reflect the current month? Yes

Can we automate the tie-out between the subledger and general ledger? Yes

Can we automate the sending of the account reconciliation to the reviewer? Yes

Jill does her research and realizes that most of her account reconciliations can be automated. What is the best way for her to do this? She has multiple options.

She can use Sikuli to complete the whole account reconciliation process from beginning to end or use Sikuli, VBA, and Python at different touch points of the process. If all the account reconciliations have a standard template, then the automation can be developed one time and can be used on any recon.

Let’s help Jill automate one of her reconciliations:

Source: Account Recon 1.xlsx

One of the first things Jill does is update the dates inside the reconciliation to reflect the right period. Jill would need to automatically have the dates placed inside the excel cells that require a data. One way she can do this is have a prompt box pop up asking for the dates that need to be entered. Another way is to have VBA/Sikuli/Python look at Jill’s computer to get the current date and time. These scripts are able to interact in anyway with the computer. This includes extracting information from files, websites, and even computer background processes.

Since Jill’s reconciliations are due the month after the current month’s ledger is closed, she will need to take the current month and year.

VBA:

Excel has formulas to grab today’s date. If today was April 6, 2019,

=TODAY() would return 4/6/2019.

If we want only the period and month,

=TEXT(TODAY(),”MM/YYYY”) would return 04/2019.

If we wanted the last day of the month,

=EOMONTH(TEXT(TODAY(),”MM/YYYY”),0) would return 4/30/2019

With VBA, we can have the formula automatically placed in the cell we want, then converted to a value, so it does not automatically update once someone else opens the file. In the module, Jill has this code written to change the rec period to the previous month.

Sub Dates()

 

Range(“B4”).Formula = “=EOMONTH(TEXT(TODAY(),””MM/YYYY””),-1)”

 

Range(“B4”) = Range(“B4”).Value

 

End Sub

That its! The periods are updated as simply as that. A macro button can be created and placed anywhere in the workbook run the code.

Range(“B4”) is referencing cell “B4” on the excel worksheet that is active. The “.Formula” is telling excel that we are planning to insert a formula into this cell.

Note: Formulas should be in double quotes in VBA. If quotes exist in the formula, add double quotes around those quotes. It seems silly, but VBA programming language requires this for the script to run.

Jill’s biggest hardship in the whole process is that she has to bring in the account detail from the ERP into each account reconciliation. She has to query the detail for each account for the current month. She examines all her options and sees that Sikuli-an automation platform, is able to achieve this. If you search the site, you should find specific steps on how to set up and use Sikuli. 

Large organizations usually have multiple ERP’s that need to be accessed to get the subledger data. Jill has multiple ERP’s she has to get data from including PeopleSoft, NetSuite, and Paragon. She wants to focus on coding a bot fetching detail from these three systems before integrating other systems in her pull. Jill decides the best course of action is to first build out a process plan explaining her process.

Note: While building a process plan, it is recommended to write down any mouse or keyboard interactions (Mainly clicking or typing).

Logically, without applying code, Jill writes out her process step-by-step:

1. Open account reconciliation file
2. Look at summary tab to identify which ERP subledger data is sourced from
3. Click on Internet Explorer
4. Wait for webpage to open and load
5. Type ERP URL in address bar
6. Type username and password
7. Click on the login button
8. Click on Subledger Query from Menu dropdown
9. Type period, year, and account #
10. Click on “Generate Report” button
11. Wait for file to download
12. Save report on the network drive
13. Open report
14. Copy detail from first row to last row
15. Paste detail in account reconciliation subledger detail tab.
16. Save account reconciliation file
17. Close account reconciliation file
18. Move on to next account reconciliation

Now, the FUN part, Jill will now translate the process into code.

Many corporations utilize web applications such as Blackline to maintain reconciliations. Jill would have to upload all her reconciliations once she completes them. Since Jill understands that Sikuli is a great tool to use with web applications, she wants to write a script that uploads each account reconciliation excel file to Blackline. This would save her a lot of effort and give her more time to focus on analysis rather than repetitive mundane work.

Logically, without applying code, Jill writes out her process step-by-step:

1. Click on Internet Explorer
2. Wait for webpage to open and load
3. Type Blackline URL in address bar
4. Wait for Blackline webpage to load
5. Type username and password
6. Click on the login button
7. Wait for Blackline dashboard to load
8. Search for specific account
9. Open summary page for the account
10. Scroll down through the webpage
11. Click on the “upload file” button
12. Attach account reconciliation excel file

By breaking it down, it is easy to convert this to code.

1. click(Internet Explorer icon)
2. wait(image of internet explorer start page,60)
3. type(blackline.com)
4. wait(image of blackline homepage)
5. type(username)
   type(Key.TAB)
   type(password)
6. click(submit button)
7. wait(10)
8. type(account #)
9. type(Key.ENTER)
10. for x in range(0,9):
    type(Key.PAGE_DOWN)
11. click(image of upload button)
12. click(image of account recon excel file)

Now we can translate the code into what it’s actually doing:

For step 1: Sikuli is clicking on the Internet Explorer icon that is located on the desktop to open the browser.

For step 2: Sikuli is waiting for an image to appear on the screen. Any image that shows up after the page is loaded can be used. We also add a “,60” to let Sikuli know that if the image does not appear after 60 seconds, then proceed to the next line of code.

For step 3: We assume that the address bar is automatically asking for a URL, so Sikuli is going to type the URL. If URL box is not active when internet explorer open, we would just have to add the code “click(image of address bar)” to be able to type URL.

For step 4: We are telling Sikuli to wait for the blackline homepage to load. If you knew how a long webpage took to load, images are not needed. We can simply write “wait(20)” and Sikuli would know to wait 20 seconds before moving on to the next line of code.

For step 5: We are using the type command to have Sikuli type into the input boxes on the website that is asking for a username and password. The reason why we have the “type(Key.TAB)” in between the code that types out the username and password is so it moves on to the password input box.

For step 6: Sikuli is simply clicking the “submit button” that it sees on the screen. Remember that as soon as Sikuli gets to that line of code, it will be scanning your screen for that image. Make sure your images are viewable at the point in time Sikuli runs a line of code.

For step 7: Rather than waiting for an image to appear, we have Sikuli wait 10 seconds, then continue on to the next line of code. If the website loading time is unsteady, it is recommended that you use an image that appears after the page loads for the wait requirement.

For step 8: We make an assumption that once we are logged into Blackline, the search bar is active. If that is not always the case, we need to take advantage of the click command to have Sikuli click on the search bar before typing.

For step 9: We are telling Sikuli to push the “Enter” key. This will search for the specific account searched.

For step 10: We are telling Sikuli to “Page Down” on the keyboard nine times. This causes the webpage to scroll down. The reason for this is to allow Sikuli to see the upload file button on the screen.  If we do not know how many “Page Down’s” are required, then another option would be to use a while statement like below:

while not exists(image of upload file button):

type(PAGE_DOWN)

Note: Indentation in Sikuli follows the same rules as Python. Any lines of code written under a “while”, “for”, or “if” control statement must be indented for the script to be executed properly.

Once Jill was able to sit down and lay out her process from beginning to end, writing out the code was a piece of cake. The code is phrasing out the process in a technical way. It might look a bit different and be a bit harder to understand, but will continuously run the process the way Jill outlined.