Data Collection, Cleansing, Transformation and Automate Report

This project involves integrating data from multiple sources, including a database, a web-based reporting platform, and client-maintained Excel files. The client currently gathers data manually from these platforms and repeatedly consolidates it before generating reports for accounting purposes. The objective is to develop an automated solution that centralizes data collection, streamlines integration, and enables scheduled updates, improving efficiency and accuracy in reporting.

Utilize Microsoft SQL Server Management Studio (SSMS) to collect data from multiple sources, including databases. Develop SQL queries to extract relevant data while incorporating calculations to minimize record volume and streamline the data cleansing process, enhancing efficiency and data accuracy.

Ensure data accuracy by validating records, removing errors, and verifying the inclusion of necessary attributes. Utilize Excel and Python to handle large data volumes efficiently. Once data validity is confirmed, integrate multiple data sources using Microsoft Power Query, applying transformations such as attribute renaming and calculations to achieve the desired report layout.

Automate the scheduling of data exports from the database, web platform, and the client's working Excel file, saving all files to a designated folder. This process ensures that the client only needs to refresh the Excel file each month, with the finalized monthly report updating automatically.

Significant Time Reduction: Processing time decreased from 4 hours per update to just 10 minutes, enabling the flexibility to update reports weekly instead of monthly.

Improved Accuracy: Eliminated manual data entry errors, increasing data accuracy to 99.5%

Enhanced Productivity: With reduced processing time, the client can now focus on analyzing report results and providing valuable insights for management review.