📈 Portfolio Tracker (PostgreSQL) — Overview¶

A Flask web application for tracking investment portfolios across multiple brokers with interactive visualisations, XIRR calculations, and market data integration — backed by AWS RDS PostgreSQL.

Features¶

🏦 Multi-Broker Support: Moomoo, Tiger, WeBull (and extensible for more)
📊 Interactive Dashboard: Portfolio NAV, allocation charts, monthly returns, transaction calendar
📈 XIRR Calculation: Money-weighted annualised return using external cash flows
💱 Multi-Currency: SGD/USD toggle with automatic FX rate conversion
💰 Money Market Fund Tracking: Subscribe/redeem flows with balance approximation
📁 CSV/TXT Import: Automated parsing of broker-specific file formats
📉 Yahoo Finance Integration: Historical prices and FX rates
🔐 User Authentication: Secure registration and login
☁️ AWS RDS PostgreSQL: Managed single-instance PostgreSQL database in the cloud

Architecture¶

Infrastructure¶

┌─────────────────────┐
│  Developer Machine  │
│    (Windows)        │
│  - Write code       │
│  - Git commits      │
│  - SSH to EC2       │
└──────────┬──────────┘
           │ SSH / Git
           ▼
┌─────────────────────┐      ┌─────────────────────┐
│   EC2 Instance      │──────│  RDS PostgreSQL     │
│   (Amazon Linux)    │ VPC  │    Database         │
│  - Gunicorn WSGI    │◄────►│  - Same VPC         │
│  - systemd daemon   │      │  - Private access   │
└─────────────────────┘      └─────────────────────┘

The application runs on an AWS EC2 instance as a systemd-managed Gunicorn service, connecting to an RDS PostgreSQL database within the same VPC for low-latency, secure database access.

Database Tables¶

Table	Purpose
BROKER	Trading platforms (Moomoo, Tiger, WeBull)
PORTFOLIO	Logical grouping of transactions
TRANSACTION	Securities trades (stocks, options, ETFs) — only filled orders
FUND_TRANSACTION	Money market fund subscribe/redeem operations
EXTERNAL_CASH_FLOW	Deposits/withdrawals to/from brokerage accounts
BALANCE_SNAPSHOT	Periodic manual balance recordings
DAILY_PRICE	Yahoo Finance historical price cache
FX_RATE	Daily foreign exchange rates

Key Modules¶

Module	Responsibility
`db.py`	Database connection, CSV parsers, CLI commands
`market_data.py`	Yahoo Finance integration (prices, FX rates)
`calculations.py`	XIRR, NAV, allocation, performance metrics
`portfolio.py`	Flask routes and JSON API endpoints
`config.py`	Environment-based configuration loader
`auth.py`	User authentication (registration, login, sessions)

Technology Stack¶

Layer	Technology	Purpose
Language	Python 3.13	Application runtime
Web Framework	Flask	Routes, templates, request handling
Database	AWS RDS PostgreSQL	Managed single-instance relational database
DB Driver	psycopg2	PostgreSQL adapter for Python
WSGI Server	Gunicorn	Production-grade application server
Charts	Chart.js (CDN)	Pie charts, bar charts, doughnut charts
Financial Charts	TradingView Lightweight Charts (CDN)	Time-series line charts
Templates	Jinja2	Server-side HTML rendering
Market Data	Yahoo Finance API	Historical prices and FX rates
Process Management	systemd	Daemon lifecycle, auto-restart
Cloud Infrastructure	AWS EC2 + RDS	Compute + managed database
Source Control	Git + GitHub	Version control

API Endpoints¶

Endpoint	Description
`GET /api/performance-summary?currency=SGD`	Portfolio performance metrics
`GET /api/nav-history?currency=SGD&interval=7`	NAV time series
`GET /api/allocation?currency=SGD`	Asset allocation breakdown
`GET /api/currency-allocation?currency=SGD`	Currency allocation breakdown
`GET /api/monthly-returns?currency=SGD`	Month-over-month returns
`GET /api/transactions-calendar?year=2026`	Transaction count per day
`GET /api/irr?currency=SGD`	XIRR calculation
`GET /api/holdings?currency=SGD`	Current holdings with P&L
`GET /api/fund-balance?currency=SGD`	Money market fund balances
`GET /api/cash-flows?currency=SGD`	External deposit/withdrawal totals
`GET /api/transactions?limit=50`	Recent transactions list

Application Structure¶

app_portfolio_tracker_aurora/
├── __init__.py           # Application factory
├── auth.py               # Authentication routes
├── portfolio.py          # Portfolio management routes + API
├── db.py                 # Database utilities + CSV parsers
├── calculations.py       # XIRR, NAV, allocation metrics
├── market_data.py        # Yahoo Finance integration
├── config.py             # Environment-based configuration
├── schema.sql            # PostgreSQL schema (tables, views, indexes)
├── requirements.txt      # Python dependencies
├── csv_data/             # Broker CSV/TXT import directory
├── static/
│   └── style.css
└── templates/
    ├── base.html
    ├── auth/
    │   ├── login.html
    │   └── register.html
    └── portfolio/
        ├── index.html            # Dashboard
        ├── create_portfolio.html
        ├── view_portfolio.html
        ├── add_transaction.html
        ├── upload_csv.html
        └── analytics.html

Visualisation Stack¶

The dashboard uses a combination of charting libraries:

Chart.js — Pie charts for asset allocation, doughnut charts for currency allocation, bar charts for monthly returns
TradingView Lightweight Charts — Financial time-series line charts for NAV history
Custom HTML/CSS — Calendar heatmap for transaction activity

Screenshots¶

Coming soon

Screenshots of the dashboard, charts, and portfolio views will be added here.