This project delves into the connections between various platforms to enable users to effectively track their daily expenses and gain actionable insights into their financial health. It harnesses the capabilities of Fivetran, Google Sheets, BigQuery, and Looker Studio to provide a comprehensive and reliable solution for managing finances with ease and precision.
Introduction: Link to heading
In today’s fast-paced world, efficiently managing personal finances is essential for maintaining financial stability and peace of mind. With this goal in mind, I developed an interactive Financial Expense Tracker (FET) web application. This application not only allows effortless input of new expense and income data but also provides a dynamic Business Intelligence (BI) dashboard integrated with Google Data Studio, also known as Looker. Continuously reflecting updated financial data entered by users, this dashboard enhances financial oversight and decision-making.
Financial Expense Tracker Web Application: Link to heading
The FET app gives users a convenient platform to monitor and manage their financial activity. Designed for simplicity and functionality, the application offers intuitive features to streamline expense tracking.
The main content area includes a form for adding new data, along with options for selecting the Account, Category, and Transaction Type. When users submit new data through the form, the application sends a POST request to a designated endpoint. This endpoint, a Google Sheets script deployed as a web app, processes incoming HTTP requests and stores the submitted data in the Google Spreadsheet.
Google Sheets serves as the foundational data storage solution for the FET app. Its familiar interface and collaborative capabilities make it ideal for managing financial data. Each user’s expenses and income are recorded in a dedicated sheet within the document, ensuring integrity and accessibility.
View (live demonstration)
Leveraging Fivetran for Connecting Sources & Destination: Link to heading
Fivetran is a modern, cloud-based automated data movement platform designed to extract, load, and transform data between a wide range of sources and destinations.
We set up a new Fivetran connection to select our data source. For this demo, we used the Google Sheets data source that the FET app writes to. This Google Sheets connection serves as the main repository for storing and managing users’ financial data. By integrating Fivetran into our Financial Expense Tracker application, we ensure seamless synchronization between Google Sheets and our database, enabling consistent data updates.
We selected Google BigQuery as the destination for our data. BigQuery acts as the backbone of our data processing infrastructure, enabling fast and scalable analysis of larger datasets. Connecting our Google Sheets data to BigQuery unlocks advanced analytics capabilities, including real-time reporting and trend analysis. Fivetran also provides controls to protect sensitive data through hashing or blocking methods.
Once the data has synced, we can apply transformations using Fivetran’s built-in tools. At this stage, the prepared data is connected to a BI tool for analysis—Google Data Studio, also known as Looker Studio.
Visualizing Insights with Looker Studio: Link to heading
Looker Studio provides a powerful platform for visualizing and analyzing data from Google BigQuery. Our dynamic dashboard offers users a comprehensive overview of financial status and trends. From tracking available balance and total transactions to analyzing expense categories and income sources, the dashboard helps users make informed financial decisions with confidence.
Users can visualize their expense data through the FET app, gaining insights into their spending patterns and financial habits. By clicking the “Show Dashboard” link in the footer, users can toggle the visibility of this interactive visualization to better understand their financial situation.
Below is a simplified diagram showing how data moves between the components used in this project:
Conclusion: Link to heading
In conclusion, our Financial Expense Tracker application leverages the synergies between Fivetran, Google Sheets, BigQuery, and Looker Studio to offer a complete solution for managing finances effectively. By providing seamless data integration, reliable storage, advanced analytics, and intuitive visualization, we empower users to take control of their financial health. Whether tracking daily expenses, analyzing spending patterns, or monitoring trends, our application simplifies the process and enhances the overall financial management experience.