Portfolio Intelligence Dashboard

A self-hosted portfolio analytics platform that parses Interactive Brokers (IBKR) Flex Query CSV reports and provides interactive dashboards for portfolio monitoring, performance tracking, and trade analysis.

Features

Portfolio Overview

At-a-glance KPI cards showing total portfolio value, daily P&L, unrealized/realized gains, and allocation breakdown by asset class.

Holdings & Positions

• Stock and ETF positions with real-time mark price, cost basis, unrealized P&L, and % of NAV
• Sortable and filterable table with gain/loss color coding
• Supports equities, ADRs, ETFs, bonds, and NY registry shares

Performance & Benchmarks

• Daily NAV chart — portfolio value over time, sourced from IBKR Flex Query daily NAV data
• Benchmark comparison — overlay SPY, QQQ, BRK.B on the same initial capital basis (data from Yahoo Finance)
• TWR (Time-Weighted Return) — accurate return metric that neutralizes deposit/withdrawal impact
• P&L breakdown — unrealized P&L, realized P&L (YTD), dividends, and interest income
• Trade-based fallback — when daily NAV is unavailable, reconstructs portfolio line from trade data with time-spread unrealized P&L

Options

• Option positions with strike, expiry, put/call, delta, and Greeks
• Short/long classification with aggregated view

Trading Panel

• Full YTD trade history with FIFO realized P&L per trade
• Filter by stock trades, option trades, or time window (YTD / MTD / custom)
• Server-side pagination for handling 500+ trades efficiently

Additional Pages

• Macro Dashboard — macroeconomic indicators with bilingual (EN/CN) support
• AI Agent — GPT-powered portfolio analysis and Q&A

Tech Stack

Layer	Technology
Frontend	React 19, TypeScript, Vite, Tailwind CSS v4, shadcn/ui
Charts	Recharts (line, bar, area), responsive + zoom
State	TanStack Query (React Query), tRPC React hooks
API	tRPC (end-to-end type safety), Express.js
Database	MySQL 8.0, Drizzle ORM
Data Source	IBKR Flex Query CSV parser, Yahoo Finance API
Auth	JWT (jose), cookie-based sessions
Deploy	Docker Compose (app + MySQL)

Architecture

┌─────────────────────────────────────────────┐
│                  Browser                     │
│  React + TanStack Query + tRPC Client        │
│  Tailwind CSS / shadcn/ui / Recharts         │
└──────────────────┬──────────────────────────┘
                   │ tRPC (HTTP)
┌──────────────────▼──────────────────────────┐
│              Express Server                   │
│  tRPC Router (type-safe procedures)          │
│  IBKR CSV Parser → DB ingestion              │
│  Yahoo Finance → Benchmark prices            │
│  FIFO P&L Calculator                         │
└──────────────────┬──────────────────────────┘
                   │ Drizzle ORM
┌──────────────────▼──────────────────────────┐
│             MySQL 8.0                        │
│  portfolio_snapshots / holdings / options    │
│  daily_nav / dividends / cash_flows         │
│  trades (in summaryJson)                     │
└─────────────────────────────────────────────┘

Data Flow

1. Upload — User uploads an IBKR Flex Query CSV via the Settings page
2. Parse — Multi-section CSV parser extracts: daily NAV, holdings, options, trades, dividends, cash flows, and performance summary (TWR)
3. Store — Parsed data is persisted to MySQL via Drizzle ORM as a timestamped snapshot
4. Query — tRPC endpoints serve aggregated views: portfolio overview, performance chart, trade history
5. Display — React components render with full type safety end-to-end

Quick Start

Prerequisites

• Node.js 18+ and pnpm
• Docker and Docker Compose (for MySQL)

1. Clone & Install

git clone https://github.com/kevinlisun/portfolio-analyst.git
cd portfolio-analyst
pnpm install

2. Configure Environment

cp .env.docker .env
# Edit .env with your settings:
#   DATABASE_URL=mysql://root:portfoliolocal@127.0.0.1:3306/portfolio
#   SESSION_SECRET=<your-secret>

3. Start Database

docker compose up -d mysql

4. Run Database Migrations

pnpm drizzle-kit push

5. Start Development Server

pnpm dev

Open http://localhost:3000.

Docker Deployment

docker compose up -d

This starts both the MySQL database and the app container on port 3000.

IBKR Flex Query Setup

The app parses IBKR Flex Query CSV reports. For the best experience, include these sections in your Flex Query:

Section	Purpose
NAV Summary	Daily portfolio value (Cash, Stock, Options, Total)
Performance Summary	TWR, starting/ending value, realized P&L
Open Positions	Current holdings with mark price and cost basis
Trades	Trade history with FIFO realized P&L
Dividends	Dividend income records
Cash Transactions	Interest, deposits, fees

Example data is provided in example_data/.

Project Structure

portfolio-analyst/
├── client/                  # React frontend
│   ├── src/pages/           # Page components (Home, Performance, Holdings, etc.)
│   ├── src/components/      # Shared UI components (shadcn/ui)
│   └── src/lib/             # tRPC client, formatters, utilities
├── server/                  # Express + tRPC backend
│   ├── routers.ts           # tRPC procedure definitions
│   ├── csvParser.ts         # IBKR Flex Query CSV parser
│   ├── tradingProcedures.ts # FIFO P&L calculator, trade filtering
│   ├── yahooFinance.ts      # Yahoo Finance benchmark data
│   ├── db.ts                # Database access layer
│   └── _core/               # Auth, LLM, env, storage
├── drizzle/
│   └── schema.ts            # MySQL table definitions
├── shared/                  # Shared types and constants
├── example_data/            # Sample IBKR CSV files
└── docker-compose.yml       # Docker deployment config

License

MIT