Thread by @ChrisHervochon

It's #RPA Sunday and I am going to Tweet along while I try to build a bot to solve a specific problem that I'm having with a client.

Thread 👇

#CPA #accounting #automation

Use case: I have a relatively new client with an OOC chart of accounts. The original P&L consisted of 218 accounts: 4 revenue, 129 COGS and 85 OPEX.

After some convincing, we were able to get COGS down to 6 accounts, but it took me about 3 hours to do YTD reclasses using the @QuickBooks "Reclassify" tool. That's what I am trying to avoid as we migrate the OPEX accounts to our template COA.

It might take me 3 hours (or more) to build a bot, but when I'm done I'll have a tool I can reuse with other clients as we standardize COAs.

Step 1: Since I've never built this kind of bot before, and mistakes sometimes happen (accountants make mistakes?😬), I am going to back up the QBO file with @rewind. That way I can simply restore the company if things don't go as planned.

$14? Totally worth it. I am also going to run a YTD set of financials because I'm only going to be reclassing YTD transactions.

First question: can this be accomplished with either @integromat or @zapier? These are my usual go-tos. Unfortunately, no. With these tools you have to search for specific types of transactions (Expense, Bill, etc.) so I wouldn't be able to blanket reclass by account.

Second question: can you search in the reclassify tool by the fully-qualified name (FQN), or can you only use the account name? Answer: you can only search by the account name.

🤬

Why is that such a big deal? The COA is so sprawling & with so many nested accounts that I'm concerned about duplicate names. To check that, I'm going to pull an "Accounts" report in Google Sheets using @G_AcconServices. The report comes in pre-sorted, so the check will be easy.

Ok, no blood. I have two duplicate names, one on the P&L and one on the balance sheet. The two on the P&L are just parent/child rent accounts, so I'll just consolidate the child account into the parent account. No big deal.

Next step: create a simple @integromat scenario that looks like this to create all of the accounts we are migrating/consolidating to. The error handler is so the scenario continues if the account already exists and doesn't error out.

Lessons learned from that step, which I had to run multiple times: 1) QBO naming conventions for sub-types are fickle, and 2) parent accounts have to map to QBO account ID numbers, not account names.

Next step: backup the QBO file again now that I have the accounts I need. While that works, I am going to create my mapping table of old accounts to new accounts, which will take this format. Columns C and D will be dropdowns relating to the G-Accon tabs.

Ok, mapping table is done. Now, time for the fun stuff. I am going to use @electroNeek to build a #bot to read in the Google Sheet and iterate through all the accounts using the QBO reclass tool.

Worth noting, we deploy ElectroNeek via a Windows 365 instance so: 1) the whole team has access to build bots and 2) when bots run they don't clog up our desktop and we can continue to work on other things.

Another lesson learned the hard way, Windows 365 needs to have at least 8GB of RAM to run ElectroNeek bots.

Here's the 1st part of the bot. It fetches the current account & future account from the Sheets mapping table. It will loop through rows 2 to 85. This is pretty easy bc I have a finite row set. Next step: click on the appropriate elements in the reclassify tool in the browser.

Correction: also need to add in a step to get the future Class value, as I'll be reclassing those values as well. Updated it looks like this:

Thought I was done with the bot and just tested it. Nope...not done. Lesson learned the hard way: input the fully qualified name in the "reclass to" field, otherwise, you may reclass to a similarly named account that's not the target destination.

::face palm::

The bot ran in about 17 minutes, doing what would have taken me probably 2-3 hours to do manually. Unfortunately, the classes didn't update due to issues with the QBO GUI because it covers up the class field after you enter the account:

Not a big deal because I was going to have to do some manual work on the classes for this client anyway. A production version of this bot would need to solve for that, though. I tried moving the coordinates as well as trying a "Tab" hot key. No dice.

Here is what the full bot path looks like completed (not terribly complicated stuff):

All in all I spent about 8 hours, less: dinner with the family, folding laundry, taking a shower, putting together all these Tweets and watching a lot of the PGA TOUR event (did you see that playoff!?!?). Now I have a tool that will save hours worth of work each time we use it.

Hopefully, this thread will inspire other #accountants, #CPAs and tech nerds to tackle some RPA. Let me know 👇 in the comments if this was helpful and what you think.