My First (Automated) Dashboard! The Overview

Recently, I really wanted to start a data analysis project to learn some new things, expand my skillset, and show off some cool stuff I already know. However, it was a challenge for me to find (the time to actually do this — LOL) a problem or dataset that I was really passionate about. So, I decided to take my own financial data and create my very first dashboard to help me track my finances, visualize my purchases, and analyze my own spending.

My initial outline of what I wanted to accomplish looked like this:

1. I wanted to create a database for my spending data where it could easily be queried, updated, and analyzed.
2. Then I wanted to create a process to import this data into my database after I download it and then export it for the Excel dashboard.
3. Then I wanted to build an interactive Excel dashboard that would update automatically when new data was exported from SSMS.

In order to start this project, I rounded up some software. Here are the main components used in my data analysis project that I installed on my laptop, Excel being the only one that I paid for:

• SQL Server Management Studio 18
• SqlLocalDB
• BCP
• Microsoft Excel (I did use the Power Pivot add in, but it isn’t really needed)
• PowerShell

Additionally, I downloaded my recent spending data from my credit card company in the form of csv files and got to work on prototyping out what I wanted to build.

After some prototyping that included writing SQL code, PowerShell scripts, and watching YouTube videos on Excel dashboards I realized what I wanted to build was 100% possible so I hit the ground running. Here is a screenshot of what I ended up with:

What do you think? What could be improved? What is your opinion on the colors and spacing? Are the graphs informative? Does the visualized data tell a story?

This dashboard allows you to filter for month, year, and card number on the fly to be more specific with the visualized data. This way I can track my spending for the entire year, year to year, or narrow it down to a particular range of months.

Here is a live look at the file: Dashboard.xlsx (Due to how I set up the data connection, the filters aren’t working so I’ll have to work on that.)

For my very first dashboard I was pretty happy with how it turned out. The last thing I made to visualize data and display some graphs was a crappy PowerPoint presentation and I’ll never do that again now!

Are you interested in creating one for yourself? Good! Because I’m going to walk you through how I built this one from start to finish. In my next blogpost we’ll cover the SQL, PowerShell, & SSMS aspects of the project.

Thanks for reading!