Portfolio 04
Initializing sql.js engine...
04 / Cash Flow Monitoring & Financial Reporting
Live · sql.js · SQLite

Pipeline Architecture

💰
Google Sheets
Transaction entry
→
⚙️
AppScript
doGet() → JSON
→
🐍
Python
Clean + load SQLite
→
🗄️
SQLite
cashflow_db.sqlite
→
🔍
sql.js
Browser query
→
📊
Dashboard
Real-time viz

Problem Statement

Without a dedicated accounting system, tracking cash flow across multiple payment methods (Cash, Bank, PayPal, Payoneer, Credit Card) is error-prone. No running balance visibility means decisions are made blind.

Solution Built

A real-time cash flow monitoring system with auto-categorized transactions, running balance tracking per payment method, and multi-dimensional financial analysis across time, category, and third-party dimensions.

Data Collected

316 transactions covering Cash-In (project payments, gross income, investments, loans) and Cash-Out (salary, rent, consumption, transportation, entertainment) with full third-party account mapping.

Business Impact

Net cash flow trending, burn rate analysis by category, ending balance per payment method, largest transaction identification, and daily average income vs expense tracking for financial health monitoring.

Tech Stack

Google SheetsGoogle AppScriptPython 3.13SQLitesql.js (WebAssembly)Chart.jsGitHub Actions
Total Cash-In
—
total income
Total Cash-Out
—
total expense
Net Cash Flow
—
in minus out
Ending Balance
—
latest record
Total Transactions
—
all entries
Avg Daily Income
—
per active day
Avg Daily Expense
—
per active day

Monthly Cash-In vs Cash-Out

Expense Category

Payment Method

Top 10 Largest Expenses

DateCategoryDescriptionValue

Latest 20 Transactions

DateTypeCategoryValueBalance
Saved Queries
Custom Query
No results

Pipeline Stats

—
Transactions
13
Columns
—
Pipeline Runs

Last Runs

Python Pipeline (pipeline_04.py)

# Fetch + clean 316 transactions response = requests.get(WEBAPP_URL) payload = response.json() # Normalize datetime, derive year_month r["year_month"] = r["transaction_date"][:7] # Validate: value not negative, enum types if r["value"] < 0: warnings.append(...) # Upsert by transaction_id (idempotent) cur.execute(UPSERT_SQL, rec)

AppScript Export

// Reads Transaction Database sheet // Exports 12-column financial records const keyMap = { 'Transaction ID': 'transaction_id', 'Transaction Date': 'transaction_date', 'Type of Transaction': 'transaction_type', 'Final Balanced': 'final_balance' // ... 8 more columns }

GitHub Actions Schedule

# Runs every day 00:01 WIB cron: '1 17 * * *' env: WEBAPP_URL_04: ${{secrets.WEBAPP_URL_04}} run: | cd 04-cash-flow python pipeline_04.py \ --url "$WEBAPP_URL_04"