Overview
The client uses a third-party software ‘Mackey’ for research management. ‘Mackey’ aggregates and organizes information across applications and devices. This software is responsible to combine research, notes, contacts, relevant emails, conversation, market data, word, pdf documents, and other related artifacts.
The client decided to move to the ‘Black Mountain’ system (again a third-party system) to not only manage research but also provide flexibility in workflow management.
In the ‘Mackey’ system, the workflow is described below.
- Add new deal: – Analysts set up a new deal through a ‘new deal’ template.
- Add more information – Analysts add more information to the deal through four templates: –
- Pipeline
- General Memo
- Earning
- Others
- Trading information – Trader populates trading information through the ‘Trading’ template.
Mackey Data Migration
The Mackey data is provided in a consolidated excel sheet of all note types (i.e., Pipeline, General Memo, Earnings & Others). In addition to this, the commentary data is provided in emails (.eml files).
This data needs to be populated to the Black Mountain platform through its web portal (front end).
Approach
As the input data was huge and it involves repetitive work of extracting and copying the data from an excel sheet and email archive to the web portal, it was perfectly suited to be implemented by RPA (Robotic Process Automation).
Here are the steps to be followed iteratively till the end-user accepts the delivery: –
- Data Scraping – extracting Excel sheet data and the associated email files.
- Data Reviewing – Detecting the anomalies by running rules against the source data to ensure the data integrity and communicating the result to the end-user.
- Data Cleansing – Correcting (sometimes removing) the source data after the feedback from the end-user.
- Data Ingestion – Automating the data entry to the Black Mountain portal.
- Data Verification – The entered data is extracted to Excel through Black Mountain utility, which is further verified (by using scripts) against the excel sheet we got after ‘Data Cleansing’.
- Data Validation – The end-user is informed to validate 20% random records (exit criteria) and provide ‘acceptance’ when satisfied with the result.
Technical Solution
Technology Used
Scripting – Python
Editor – PyCharm
Libraries – Selenium and Panda
Benefits
- Futuristic goal achieved – In the end, we have a completely reusable and configurable tool.
- Speedy migration – The data migration is done within 3 months.
- Zero downtime and business disruption – As soon as the data is entered and accepted into the Black Mountain system, it is made available for further operations and reports.
- Less manpower – It took 3-member team (1- Developer, 1- Quality Control, 1 – Reviewer) around 60 days to enter, verify and validate 8K records with automation. Whereas, it takes approx. 15 minutes to enter a single record manually without verification and validation.
