Automated Monthly Reporting System

The monthly reporting cycle required a set of recurring reports to be produced, checked, and circulated by hand. This work replaced that manual cycle with an automated pipeline: SQL extracts the operational data, Python transforms and prepares it, and Power Query handles the final preparation where it is better suited. Alongside the pipeline sits a Streamlit application backed by MySQL, which gives the people who need the numbers direct access to them instead of a static file emailed to them.

Monthly reporting was a recurring, manual, and error-prone effort. The same queries were re-run each cycle, results were copied between tools, and the people who needed the information received a static export rather than a way to interrogate it. The cost was not only the hours spent re-keying and formatting — it was that the analysis could not keep pace with the questions being asked of it.

What made it hard

• Separated the cycle into extraction, transformation, and presentation, so each stage could be automated with the tool best suited to it rather than forcing one tool to do everything.
• Distinguished between the reports that were genuinely recurring and the one-off questions that were being folded into them — automating the former and leaving the latter as analysis.
• Identified that the Streamlit application was the structural fix rather than a nice-to-have: removing the static-file dependency removes the re-keying problem at its source.
• Treated the MySQL backend as the foundation, so the application and the pipeline read the same data and cannot disagree.

A layered pipeline with a shared data foundation. SQL extracts from the operational database; Python performs the transformation and aggregation; Power Query handles the final preparation and formatting where that is the better tool. A MySQL database sits beneath the reporting application so that the Streamlit interface and the automated pipeline read from exactly the same data. The result is that the reporting cycle stops being an event and becomes a background process.

Extraction

SQL queries pull the operational data needed for each recurring report.

Transformation

Python performs aggregation, cleaning, and the calculations that recur every cycle.

Preparation

Power Query handles final shaping and formatting where it is the more effective tool.

Delivery

A Streamlit application on a MySQL backend gives direct, interactive access rather than a static export.

• Catalogued the recurring reports and mapped each one to the source tables and transformations it required.
• Rewrote the recurring logic as SQL queries and Python transformations so that each cycle runs without manual intervention.
• Built the MySQL database and application layer that lets staff query reporting data directly rather than requesting an extract.
• Designed the Streamlit interface around the questions the reporting stakeholders actually asked, rather than mirroring the old spreadsheet.
• Rolled the pipeline out over successive reporting cycles, verifying output against the manual figures before retiring each manual step.

• 12+ recurring reports automated and running without manual re-execution.
• SQL extraction with Python transformation as the core pipeline.
• Power Query for final preparation where suited.
• Streamlit application on a MySQL backend giving direct query access.
• Reporting output verified against manual figures before each manual step was retired.

• Reduced operational costs by 20% through the reporting application built on this work.
• Removed the monthly reporting bottleneck by turning a scheduled manual event into a background process.
• Replaced static file distribution with self-service access, so follow-up questions no longer require a new extract.

Screenshots for this project have not been published yet.