Creating an Automated Personal Finances Dashboard with AWS - Part 5 (Quicksight)

G'day everyone, in the last post DynamoDB was configured and was receiving realtime transaction history.

In this post we get down to the really good stuff, creating a personal finances dashboard!

Quick side note: The purpose of this guide is technical rather than financial, so the underlying financial data has been scrambled.

Prerequisite: Getting DynamoDB data into Quicksight

For this section I will defer to a fantastic article from another dev.to poster:
Using Athena data connectors to visualize DynamoDB data with AWS QuickSight

Quicksight Configuration

Alright so now that we've got our data from DynamoDB accessible by Quicksight, it's time to create a dataset.

1. Create a new data set from an Athena data source
2. Select the catalog that you created in the Athena guide
3. Select the table that we created in our DynamoDB guide
4. Hit visualize
5. Make sure to schedule a refresh of the data, this should align with your use case

So now we have a default analysis created from a dataset, the fun begins.

Review Use Cases

It's a good time to review our use cases for the dashboard, these will drive the visualisations that we create.

1. To deep dive historical data and identify correlations
2. To gain insight into current financial state
3. To forecast future financial state and identify trends

At a first glance I think the first two are achievable in a simplified state given our current technical capabilities, the forecasting aspect will need to be addressed in a future revision. Let's break down those first two with some ideas on how we will achieve them.

Use Case: Historical data

Create visualisations
- Date, value, description, category
- 12 month and 1 week view

Use Case: Current financial state

Create weekly report
- Week in review dashboard
Create alert
- Daily spending threshold

Create Visualisations

There is an endless amount of possibilities when it comes to visualising data, but only a few that are useful.

• Pie chart
  • Break down spending by category
• Line chart
  • Break down spending by month
• Table
  • List transactions, ordered by date
• KPI
  • Track income/expenditure in key areas
  • Conditional formatting indicates 'Good', 'Bad' and 'Warning' levels of spending

12 Month Dashboard

In this image we can see a simple dashboard that I've created, I'll create another one for the 1 week view that we will use for the weekly report. Duplicating sheets and visualisations in Quicksight is quick and intuitive - you'll find yourself using it frequently as it also copies across the filters.

1 Week Dashboard

Not pictured - I included a daily KPI for spending that we will use for our spending alert.

Creating reports and alerts

Now that we've created a baseline 12 month and 1 week dashboard, we can publish the analysis. This allows us greater functionality in the alerting and reporting space.

Reports

To create a recurring report, navigate to the dashboard you published and select 'Share' > 'Email Report'

I've chosen to send a report weekly at 9am Monday morning

Formatted for desktop and selecting the weekly sheet

Select the email recipients

Alerts

To create an alert you need to have a KPI visualisation, the rest is simple.

I've created an alert that emails me when my spending goes over $70/day in this example

At this point you will have:
- Athena data source configured
- Quicksight configured
- Analysis and visualisations created
- Reporting and alerts configured

Awesome, the dashboard works - the next step is to wrap everything in CDK to make sure that we can continue developing the platform in an efficient way.