How and why I built A reporting tool

About me

I'm a Data Analyst the prefers Excel, SQL and Python for my day-to-day duties. I have over 15 years of experience with Excel. It is often the best solution when creating something technical that the General Public can use in a professional environment.

The problem I wanted to solve

I needed something that could help an evolving client with a growing client base to keep tabs on their daily activities. It had to be easy to use, share and maintain in a secure IT environment.

What is A reporting tool?

The end solution was using Excel VBA to create a semi-autonomy reporting tool that multiple users can leverage at the same time. We also to static variables and made them dynamic to allow for easier updates by the user. The old way required a data analyst to modify SQL scripts; far beyond what the average layman would be expected to do. So the end solution made it easy enough for anyone that is familiar with using mobile apps and inputting data into a spreadsheet to use.

Tech stack

Excel VBA, SQL. Very simple stack.

The process of building A reporting tool

Building the SQL scripts to provide the data. Write the VBA code that would process it then design the user interface to display the info and make it easy for the user to leverage the data and maintain the reporting parameters.

Challenges I faced

Finding a way to store the data used by the Excel app while keeping it private and not using additional resources. That is just one amongst many, but that solution was the most novel. We used a mailbox in outlook. It allowed multiple users to use the app at the same time while keeping the information in a secure cloud environment; best of all, no extra lift on our end.

Key learnings

Outlook makes for a great means of storing data securely. When it came time to share, there was no need to involve IT to push installs or whatever. Users simply had to open a copy of the file from a secure shared folder. Updates could be pushed, but the PM decided that users should just download the latest copy. It works, that was the kicker and has been kept up to date by the client, so score 1 for adoption too.

Tips and advice

When the client does not know what they want and is asking for something new, create something familiar. Start with informing them of all that is possible and refine the end solution to what time and resources can afford.

Final thoughts and next steps

Excel is still relevant. Adding some user-requested features is now on the table. Just need to prioritize and refine ideas before jumping into pet project requests.