Many of the administrative tasks required of analysts are prime opportunities for automation. In this blog we'll use Robotic Process Automation ("RPA") to automate a simple stock price analysis, demonstrated below.
What is RPA?
RPA involves the development of software robots that are deployed to complete repetitive pre-defined tasks. It's benefits can be viewed in a very similar light to those provided by manufacturing robots and hence there's a healthy ecosystem of businesses offering RPA as a solution.
Building Your Robot Analyst
Our robot will be designed to collate and convert the historical closing share prices for a particular ticker into a professional looking PowerPoint slide. For implementation, we will leverage UiPath and simple VBA scripts. Only free sources for the share price will be used; however, it could be easily adapted for Bloomberg integration. Instructing the robot to email the ppt slide is another simple extension.
Implementation Walkthrough
To build the RPA solution we must translate the process a human analyst would undertake into a set of pre-defined deterministic rules. This can be summarized by three distinct stages.
- Obtain the data
- Process the data
- Present the data
Stage 1: Obtain the data
We must first provide the robot with the ticker of stock we want to analyze. This can be achieved using an input dialog block.
Subsequently our robot needs to open the browser and query the data. For this example we'll use Yahoo Finance. The required commands are pictured below.
To obtain this downloaded file we can run the following in a Read block.
It's also worth instantiating variables for the current time and a date string so you can uniquely differentiate files.
Our robot's next task is to re-open the browser window and download the ticker's corresponding logo so it can be used in the ppt.
Stage 2: Process the data
With the raw data obtained, our Robot must now initiate the processing phase. This is achieved by instantiating an 'excel application scope' block and including a nested 'write range' and 'execute macro' block.
Our first macro will be used to create the line chart, note the values should be customized based on the format of your data.
At this stage our robot needs to populate any of the required variables not included in the raw data download such as the market cap and logo.
Stage 3: Present the data
Our robot has now done all the hard work. All that is left is constructing the PowerPoint, this can be achieved by instructing it to trigger a simple vba script.
Summary
Many of the tasks undertaken by financial analysts can be automated. In this blog we built a robot using UiPath and VBA scripts to automate a simple stock price analysis flow.
Continue the discussion
Feel free to send me an email!
📧 will@willbosler.com
