About the Author
David Kroll, Lead Solutions Engineer at Ashling Partners
My Journey into RPA
I’ll admit it, I’m relatively new to the RPA universe. I came from the BPM (Business Process Management) world, where process optimization was focused on efficient task routing, with deep integration into backend systems and services. Simply put, most of my previous jobs involved orchestrating the flow of work between human actors and the data they needed to make decisions. While this is crucial for many businesses, it doesn’t make the menial, time-consuming effort of completing a repetitive task any easier. My story begins with learning how to build a robot to make my life easier outside of work.
You’ve probably heard passing tales of strange folk, you know, the ones who are overly obsessed with fantasy sports? Yep, that’s me. It’s the perfect hobby: I get to nerd out over stats while still doing something socially acceptable (watching sports on TV). Anyway, last year I had an ambitious idea to improve league participation; I was going to send out weekly newsletters, summarizing the head-to-head results, interesting stats, and bringing in some playful banter. This was going to be awesome.
(The actual newsletter was much longer than this preview image – about 1250 words) It sure was awesome at first – and my league loved it. But holy smokes, it was taking some serious effort to assemble. Top scorers for each position every week; top free agents, waiver claim orders, matchup results, and point totals for each player and team – it really took some sweat. I was constantly shifting between my spreadsheets, open tabs, and copy-pasting between windows. I cursed my double monitor layout, but it wasn’t enough. I felt my inefficiency as I bottlenecked the process with my limited processing power. I was a human cyclone of wasted energy, time, and effort. I began to push the newsletters out later, and later, and eventually, it became a chore that was no longer fun.
(The positive feedback drove me forward despite knowing I couldn’t keep up the pace)
I also noticed that despite my efforts to the contrary, I wasn’t perfect. I’d send out the newsletter, and some of the points would be wrong, team names would be misspelled, or I’d have incorrect stats in the tables. I probably spent 30 minutes just fixing mistakes. Underwhelmed at the final quality and with nobody to blame but myself, I threw up my hands in frustration and walked away.
Fast forward a few months, and I’d learned some fresh skills at Ashling Partners – and it was time to put them to good use! That’s right, I had my RPA wrench, and it was payback time.
UiPath: The Story of Work
“RPA: Robotic Process Automation – The automation of time-consuming tasks with the power and flexibility of software robots.”
The Big Idea
I started by creating a new project container and then an RPA sequence (a series of steps). I set up some quick user inputs so that I could tell the robot which gameweek stats I wanted. I even included a fun tagline.
Onward I went, snagging data off the Fantrax site with an attended robot – centralizing all of my data within my UiPath Studio project. Once it’s centralized, you can do all sorts of technical wizardry using available .NET capabilities. I started to get excited as my data flowed in, my blood was pumping. Yes, I could automate this!
(An example of the target element on the left showing the scores from each Gameweek)
(The Data Scraping feature exists within UiPath Studio – along with many other methods of extraction.)
Once I extracted the team scores, I noticed they weren’t coming in correctly – the decimals on the point values was getting thrown into new lines! “122.5” was breaking off into “122” and “.5” on separate lines. If I had been better with the UI Element selector, I probably would have fixed this during the scrape – but I decided to do post-processing instead.
Thankfully, I was able to loop through each row and apply a lovely RegEx created from memory during my lunch <cough>found online</cough> to remove the issue from my extraction:
And with that, my issue was resolved – no need to bother my technical mentor Dann Deaver. Though I’m pretty sure he blocked my number after hearing I was starting this crazy exercise on a Sunday night.
From here, I merged the Scores datatable with the TeamName datatable, with winners in even positions and the respective loser in the odd position, with their corresponding scores (ex. Team pjh144 (122.5 pts) beat Shawflank Redemption (92.5pts)).
This is one of the most impressive areas of UiPath RPA – the ability to work with and manipulate the data once it is extracted. If I’m ever having trouble with my automation, I know that if I can just get the data into Studio, I’ll be able to use developer code libraries (UiPath Packages) and Intellisense (Code Hinting) to get it sorted.
The Process Continued
Other RPA tools (I’m looking at you, Power Automate Desktop) make this more difficult than it should be. To its credit, PAD can execute VB, JS, and PowerShell code. It just doesn’t have Intellisense, a critical tool for development speed and accuracy that is built into modern IDEs like Visual Studio. Variables aren’t even color-coded. It feels like programming in Notepad, so that’s a hard pass.
I still had much more to do,so I fetched more off Fantrax: Including the Waiver Wire Claim Order, as well as downloading player stats (for this, I used UI automation to download the spreadsheet and then Excel Application Scope to read in the file and delete it when finished). This allowed me to highlight high performers each week – even breaking out who was on a taken team or an available free agent. Given the fact I already lost my first game, maybe I should be paying more attention to these outputs.
Moving on to the document generation portion. My word template’s first page looked like this – the idea was to have the RPA search for the relevant placeholder (i.e. {GameWeek} and replace it with the corresponding text from the datatable.)
This method preserves my template and allows me to make modifications to it in the future. I cloned my template first template, which is a function I wish would work on some of our company rockstars.
Next, I set up a second activity to actually generate the document – and added annotations so that I’d be able to make sense of it after I completely forget about this project next week.
After that, I populated each and every placeholder in the template with the data that I had extracted. This was probably the most time consuming part , it took a few hours (in other words, about as long as it took me previously to make this newsletter manually).
Goal
Admittedly, I could have built better loops to execute this faster, but I was so close to being done and didn’t want to refactor anything. I could see my victory on the horizon, and the dream of increased runtime efficiency wasn’t going to stop me. With that final configuration, my newsletter was created.
Bear witness to the epic feeling of automating away literally hours of wasted time! Somewhere in the distance, echoing off my empty energy drink can, I heard my wife cheer.
Automation Checklist
- Getting input from the user before running the job (check)
- Extracting table data from a web page (check)
- Manipulating that data to make it work for me (check)
- Downloading, extracting, and deleting an excel file (check)
- Filtering that extracted data for output (check)
- Reading in a word template and generating a clone of that document. (check)
- Populating newly cloned word file with tons of variable data (check).
- Feeling like a rockstar for the day (double check)
- Pushing one button and saving 2 hours of work every week… Priceless.
Did you know that RPA is just one part of our flywheel for intelligent automation? Each step saves considerable time and money. Using RPA, we can orchestrate other technologies like OCR, AI/ML, and BPM to deliver holistic automations for your business – let us know how we can help!
Next up… building an AI/ML model to plan my draft picks without breaking a sweat 😉
Do you have UiPath Studio? Download the package to check it out
