The QA manager reached out to me. She told me one of her employees spends a week every month to compile a few power point presentations. She described the task as ineffective and asked if I could take a look. I did and built a nice Excel file that made her life a little better.
Project goals:
- Build an automated power point presentation from data in excel
Data sources:
- Sales force report
- MS Dynamics report
- ERP sales report
Description of the solution:
I developed a custom VBA based excel file. These were the main steps of the file:
- Clear the current database
- Copy the updated data files from the directory
- Merge all the files into one sheet
- Create a unified database where under one headline will appear the data from many columns.
- The first output is a summary table of the monthly data
- The summary table is added to the historical data table
- Excel graphs, tables and year to date data is updated
- Power point files are connected to the file
User interface:
- The user needs to replace the source files in the directory on their server
- The user needs to press the upload button, the code will run and populate a message that will notify the user if the code ran or if there was an issue. In the end the user will receive a data message that is supposed to give a clue if the data is ok. For example, it can show the total number of complaints and sales.
- The user should correct the data and press the upload button again.
- Assuming no data issues appear, the user will view the file’s main outputs:
- A very detailed spreadsheet showing all the data
- The historical data
- All the graphs and tables to be used in the power point
- After the VBA code was completed the user can open the power point files, click the update button and all the data is updated.