top of page

Wires Dashboard

OBJECTIVE

The objective of this project was to gather, clean, and create visualizations of TPB's wire data for the accounting team to monitor data/numbers on a weekly basis. Similar to the financial dashboard project, we were able to use the same tools with some minor tweaks.

Tools

  • SQL

  • Microsoft Excel

  • Microsoft Power BI

​

Data Source

  • The wire data is provided by the ACH/wire team. The way in which they collect the data involves manually collecting certain information from the  customer including their name, account number, wire amount, etc. They type all this data into an excel which is sent to me at the end of the week. This is a very manual process, which means that it involves a significant amount of time spent cleaning data due to human error when inputting the data.

Data Prep

  • The first step of this project once I receive the data is to clean it. This involves filling out or deleting blanks, converting numbers and dates into the correct formats and validating that the fields are correct, for example account numbers or names on the accounts. 

  • Once it is clean, I conduct a VLOOKUP using the account number to look into the weekly account summary database for it to extract details for each account. These details include: type of customer, top groups  and top customer types. Once all the data is complete and clean I import it into PowerBI and create relationships between the different sheets based on date and account number

Design

  • To design and sketch the graphs I wrote down some research questions to find the metrics and the best matches of graphs for the fields I wanted to show. These included:
     

  1. What is the dollar amount of wires processed this month/year?

  2. What is the amount of wires sent or received this month/year?

  3. What is the amount of wires (dollar and count) per branch?

  4. Which are the top customers and what is their $ amount/ count of wires?

  5. What is the revenue from wires (up to date)?

    My sketch below shows the thought process and some ideas that helped me decide which types of graphs were best for each question. These wires include comparison and distribution graphs.

Visualizations

  • Once I had my sketches and data uploaded and ready in Power BI I started creating the visualizations. 

    • The first page of my dashboard is an overview that shows the dollar amount and amount of wires sent and received up to the date (which is updated every Friday of every week) in comparison to the previous year. I decided to show this in a small text box so that the user can get a quick glimpse of the numbers in comparison to the previous years. 
       

    • The second page shows information of incoming wires. This page consists of four text boxes and three graphs. The text boxes show a quick insight on count, dollar amount and average amount of wires processed that week/month. 
       

      • The first graph is a donut graph which shows a comparison of the dollar amount of wires each branch receives. I chose this type of graph because we don’t care to pay much attention to the actual amounts, we just care to know what the top branch is, so it is visually easy to spot the highest.
         

      • The second graph, a bar chart, shows a comparison of dollar amounts per customer type. I chose this type of graph because in comparison to the first one, here we do care to know an estimate of the  dollar amount each type of customer receives via wires. 
         

      • The third chart is a combination of both a vertical bar chart and a line chart. The bars depict the total dollar amount received each month, while the line shows the amount of wires received. I chose this type of graph because it easily allows you to compare the growth of these two distinct variables/ categories on a single visual. To add more detail and clarity, I also included data labels above the graphs to show the actual dollar amounts and also near each point to show the count of wires. 
        ​

    • On the third page of the dashboard we have a line graph showing dollar amounts per month in comparison to the previous year. Below that, we have a breakdown of the top groups, top type of customers and top actual customer names showing the dollar amount they receive in horizontal bar charts. 

Reflection

  • Cleaning and validating the data was the most tedious and time-consuming part of this project. I realized that in the banking world, it's important to make sure that all of your data is right—otherwise, it can negatively impact your customers and the bank itself.

  • After the data processing was complete, creating the dashboard and report was pretty easy. I shared it with the accounting team, and they got really excited about it! One of their comments really made me think about how important the sketching process was: "It was very easy to digest the information with the simple yet powerful selection of the three to four types of graphs used."

  • If I hadn’t spent time thinking about the different uses of each graph and how their use would help the team really get insights, then the whole purpose of having a report is lost. Even if I didn’t use many types of graphs, the ones I did use were perfect to compare categories and amounts, as well as show distribution of numbers over time, which is exactly what the team needed to know.

bottom of page