Financials Data Analytics Pipeline
Periodic gathering of newly reported financials data and computation of fundamental ratios
Problem Statement
An Indian fund manager wanted to automate the process of tracking and gathering newly reported data of all public companies. Additionally, ~1K fundamental ratios for each company needed to be computed and maintained in an easy-to-query database.
Challenges
- The raw data was only available through an excel plugin, so specialized automation approach was required
- There is no ready framework available for the computation of the large number of custom fundamental ratios
- An easy and user-friendly way of redefining the fundamental ratios was needed
- The database needed to have a temporal component so that the ratios could be tracked over time
- The latency of the database needed to be low for development of analytics applications on top of it
Solution
Summary
I built a python-based excel automation tool for data extraction, modules for data gathering through APIs, designed a creative framework of ratio definitions that enabled easy revision, and used a combination of PostgreSQL and MongoDB for data storage and easy querying.
Addressing of Challenges
- Control of the Excel DOM through Python to control data refreshes and setup of fail-safes and redundancies to ensure accuracy of the extracted data
- Extensive categorization, definition formalization and dependency orchestration for each fundamental ratio. This helped in structuring the approach to develop the framework to allow for easy updating of ratio definitions and adding or removal of ratios
- Splitting of data management between SQL and NoSQL databases helped reduce latency
Deliverables
- Company Financials Data extraction automation tool
- Stock Price Data extraction tool
- Fundamental Ratios computation framework and modules
- Database setup on-premises
Data Extraction from Excel using Python
Ratios Computation Framework
Ratios Computation Sample Code
Results & Impact
Reduction in time spent on manual data extraction
Have a similar challenge?
Let's discuss how we can develop solutions for your specific use case.