Cash Flow Monitoring & Financial Reporting System
Automations Data Entry, Financial Journaling, Transaction Monitoring
Services
Automations Data, Data Management, Data Entry Tools, Data Analytics
Tools
Google Sheets, Apps Script, Advanced Formula, Python, SQLite, SQL, Github Pages
Value
Automations Data Entry, Financial Journaling, Transaction Monitoring
Timeline
3 weeks
Created a structured Google Sheets financial tracking system for recording cash-in and cash-out transactions, monitoring running balance, controlling transaction categories, and preparing multi-period financial dashboards.
Problem / Trigger
Small businesses, freelancers, and independent operators often struggle to maintain a clear financial picture because daily transactions are recorded inconsistently or only summarized after the fact. Cash inflows and outflows may be tracked in separate notes, chat messages, or unstructured spreadsheets, making it difficult to monitor running balance, understand spending patterns, review payment methods, and generate meaningful financial reporting. Over time, this creates operational blind spots such as unclear cash position, weak expense control, delayed reporting, and limited visibility into short-term financial performance.
The trigger behind building this tool was the need to create a simple but structured financial tracking system that could support day-to-day transaction recording while also serving as a clean source for dashboard reporting. The goal was to transform Google Sheets into a practical finance operations tool that helps users monitor cash movement, categorize transactions consistently, and observe business balance changes in a more disciplined way.
Solution
I designed and developed a Google Sheets based financial report tool focused on transaction input, category control, balance tracking, and dashboard readiness. Instead of relying on manual recap sheets, the tool uses a structured transaction workflow where users record cash-in and cash-out activity through a guided input form. Each transaction receives an automatic ID, a transaction date, a payment method, third-party details, and an updated running balance, allowing the spreadsheet to function as an operational financial ledger rather than a passive record.
The solution separates transactional input from database storage and master reference sheets. A dedicated transaction input sheet improves usability for daily operation, while the transaction database acts as the historical source of truth. Supporting master sheets manage category options and opening balances, helping keep the financial structure organized and consistent across entries. This design makes the file more scalable for dashboard building, because the raw transaction data is already normalized and easier to aggregate.
To support reporting and presentation, I also prepared the workbook with realistic multi-month dummy data that covers a mix of income and expense activity. This allows the dashboard layer to display daily movement, monthly comparisons, quarterly summaries, and semester trends. The system is therefore positioned not only as a bookkeeping aid, but as a lightweight financial monitoring solution for operational visibility.
A real-time cash flow monitoring system that records, categorizes, and analyzes every financial transaction — built on Google Sheets, Python, SQLite, and SQL, with an interactive dashboard publicly accessible via GitHub Pages.
Google Sheets | Apps Script | Python 3.13 | SQLite | SQL | GitHub Pages |
01. Background & Problem Statement
Financial Reality Without an Accounting System
Most small-to-medium businesses manage their finances in separate spreadsheets without a connected workflow. The result: no real-time visibility into cash position, no automatic transaction categorization, and financial reports only produced when needed — not as continuous monitoring.
No Running Balance Without integrated sequential recording, the actual cash balance is only known after time-consuming manual reconciliation that is prone to error. Manual Categorization Each expense is categorized manually and inconsistently, making burn rate analysis by category inaccurate and unreliable. | Multi Payment Method Cash flow is spread across Cash, Bank Account, Credit Card, PayPal, and Payoneer — without centralized aggregation, the financial picture is always incomplete. No Historical Trends Without a central database, month-over-month Cash-In vs Cash-Out comparisons must be manually recalculated every time they are needed. |
02. Why Google Sheets + Apps Script?
Google Sheets was chosen as the operational interface because it reflects how businesses actually record finances day-to-day. Apps Script then automates the most error-prone processes: transaction ID assignment, timestamp recording, and database sheet storage — all with a single menu click.
01 | Form Entry — Daily Transaction Input Operator fills in transaction type (Cash-In/Cash-Out), category, value, payment method, and third-party details. Transaction ID is auto-generated in TRI/TRO + date + sequence format. |
02 | Apps Script — kirimDataUang() The function reads all fields from the active sheet, injects a UTC timestamp, and appends a new row to the Transaction Database sheet. This ensures no data is missed or overwritten. |
03 | Web App Export — JSON API doGet() exports the entire Transaction Database as JSON with clean key mapping. 316 transactions are available as an API endpoint that Python can hit at any time. |
03. Python's Role in the Financial Pipeline
Financial data has unique cleaning challenges: mixed date formats between datetime and date-only, currency values that need string separation, and third-party account numbers that must be stored as strings to avoid losing leading zeros.
Datetime Normalization timestamp_input is converted to YYYY-MM-DD HH:MM:SS format. transaction_date is stored as date-only YYYY-MM-DD. The year_month column is auto-derived for monthly chart grouping needs. Financial Validation Transaction values are validated as non-negative. Transaction type is validated against the enum set {Cash-In, Cash-Out}. Every anomaly is logged as a warning without stopping the pipeline. | Account Number Handling Third-party account numbers are stored as TEXT strings after cleaning the float format (.0) that Excel automatically adds to long numbers. Idempotent Design UPSERT based on transaction_id ensures the pipeline can be re-run anytime. The final_balance running balance is preserved exactly as recorded at the source. |
04. SQL as the Language of Financial Reporting
All KPIs and visualizations in the dashboard are generated by SQL queries executed directly in the browser using sql.js. Recruiters or anyone opening the dashboard can modify these queries and see results change instantly — no backend required.
$82,305 | $36,086 | $46,218 | 316 |
Total Cash-In | Total Cash-Out | Net Cash Flow | Transactions |
Core Query — Net Cash Flow by Month:
SELECT year_month,
ROUND(SUM(CASE WHEN transaction_type='Cash-In'
THEN value ELSE 0 END), 2) AS income,
ROUND(SUM(CASE WHEN transaction_type='Cash-Out'
THEN value ELSE 0 END), 2) AS expense
FROM transactions
GROUP BY year_month ORDER BY year_month;
This query generates data for the Cash-In vs Cash-Out column chart by month. Date, category, and payment method filters are injected as dynamic WHERE clauses, so all charts update synchronously when filters are applied.
05. Architecture & GitHub Actions Automation
L1 | Google Sheets + Apps Script Daily transaction entry to Transaction Database sheet, then JSON export via doGet() as a public API. |
L2 | Python ETL (pipeline_04.py) Fetch JSON, normalize datetime and currency, validate enums, upsert to cashflow_db.sqlite. Every run is logged in the pipeline_runs table. |
L3 | SQLite + 17 SQL Queries 17 queries covering KPI cards, monthly trend chart, expense breakdown donut, payment method distribution, and largest transaction tables. |
L4 | GitHub Pages Dashboard 5 interactive tabs: Overview, Dashboard with KPIs and charts, Raw Data with 316 rows and search, SQL Explorer for editing and running queries, Pipeline with run history. |
L5 | GitHub Actions (cron) Pipeline runs automatically every day at 00:01 WIB. WEBAPP_URL_04 is stored as a GitHub Secret and never exposed in public code. |
06. Demonstration Value for Data Roles
This portfolio simulates the financial data environment encountered in real companies. The demonstrated capabilities span the full spectrum from data engineering to business intelligence.
Financial Data Modeling Understanding of running balance, transaction categorization, multi-currency payment methods, and temporal aggregation (daily, monthly) is core skill for Finance Data Analyst roles. ETL Design Principles The pipeline is designed to be idempotent with graceful error handling, validation logging, and clear separation of concerns between fetch, clean, and load. | SQL for Business Reporting Writing CASE WHEN for data pivoting and parameterized WHERE injection for dynamic filtering is intermediate-to-advanced SQL competency that hiring managers test for. Transparency as Proof of Competence All 316 transactions are available in the Raw Data tab. All 17 queries can be edited directly. Pipeline run history is stored in the database. This is an open audit trail. |
