← 返回列表
report2026-04-137 分鐘閱讀

Fund Daily Report Dashboard — Detailed Report

pythonflaskplotlyexceldashboardfund-operations

Project Overview

Project Name: TGIF-I Fund Daily Report Dashboard (Portfolio Tracker)

Purpose: Automate the fund's daily Excel reports into an interactive web dashboard for the fund management team to access real-time fund performance.

Tech Stack: Python Flask + vanilla JavaScript + Plotly.js + openpyxl

Data Source: Interactive Brokers daily Portfolio Management_yyyy.mm.dd.xlsm files (approximately 125 files, covering October 2025 to April 2026)

Current Scale: ~5,354 lines of code (9 core files)

Development Timeline

Phase 1 — Foundation Dashboard (March 6, 2026)

Commit: ccbb433 — "Add Portfolio Tracker web dashboard for TGIF I fund daily reports"

New Code: 1,693 lines (10 new files)

This was the project's starting point. Built a complete Flask web application from scratch that parses Interactive Brokers daily Excel reports and presents them as an interactive web page.

Core Features:

  1. Dual-Format Excel Parser (excel_parser.py, 717 lines)

    • Old format parsing (October 2025): Earlier Excel files had simpler structure with only basic holdings and NAV data
    • New format parsing (November 2025 onwards): IB updated the report template, adding Portfolio Daily Report, Cumulative and Daily Ret. and other worksheets
    • Auto-detection of file format (by checking if Portfolio Daily Report sheet exists)
    • Parsing 3 different return sheet variants (Variant A/B/C), since IB generated Cumulative and Daily Ret. sheets with inconsistent column layouts across different periods

  2. JSON Cache System (preprocess.py, 51 lines)

    • On first launch, scans all .xlsm files in Input/ directory, serializes parsed data to app/cache/data.json
    • Supports expiry detection: compares cache file mtime with latest input file mtime, auto-rebuilds when stale
    • Startup time reduced from minutes (parsing ~98 files each time) to instant loading on cache hit

  3. Flask API Service (server.py, 46 lines)

    • GET / — Renders dashboard homepage (Jinja2 template)
    • GET /api/dates — Returns all available dates
    • GET /api/daily/date — Returns complete data JSON for specified date
    • POST /api/rebuild-cache — Rebuild cache without server restart

  4. Frontend Dashboard

    • Date picker: Dropdown covering all 97 trading days
    • Key Statistics cards: NAV, fund allocation ratios, leverage, etc.
    • Stocks table: Ticker, quantity, average price, market price, P/L %, daily change
    • Options, Cash, Delta Analysis, NAV History tables
    • Plotly.js return curves: Since Inception / YTD / MTD period switching
    • Global color coding: green for positive returns, red for negative

Phase 2 — Advanced Analytics (March 6, 2026)

Commit: dbfccde — "Add Phase 2 dashboard: benchmarks, correlation, exercise analysis, VaR%, delta NAV%"

Changes: +656 lines, -50 lines (6 modified files)

Major expansion of the dashboard's analytical depth:

  1. Benchmark Index Comparison (parsing Return Charts - ALL worksheet)

    • Added NDX (Nasdaq), SPX (S&P 500), MXCN (MSCI China), HSI (Hang Seng) benchmarks
    • Return charts with benchmark dashed lines and endpoint value annotations

  2. Correlation Matrix (parsing Correlation Matrix worksheet)

    • 1M / 3M / 6M / 12M period switching with interactive crosshair hover highlighting

  3. Options Exercise Analysis (parsing Scenario - Excercised worksheet)

    • Contract details table and margin requirement summary

  4. VaR Risk Metrics — Added VaR 1D/1W/1M percentages to Key Statistics

  5. Advanced Options Statistics — Theta/NAV ratio, highest/lowest option premium contracts, total Short Call / Short Put premiums

  6. Delta Analysis Enhancement — Added Delta NAV% column and Plotly.js donut chart

  7. Critical Bug Fixes

    • Fixed cache loading issue in server.py
    • Disabled Flask debug mode (zombie python3.13.exe processes on Windows)
    • Corrected YTD data source: discovered the Cumulative and Daily Ret. sheet's YTD column actually contained Since Inception data

Phase 3 — Complete Overhaul (March–April 2026)

Uncommitted changes: +2,945 lines, -249 lines (6 modified files + 2 new files)

The largest iteration, covering a full UI redesign, mobile responsiveness, new data source integration, and major business logic improvements:

UI Complete Redesign — "Overpay Gold Dark" Theme

CSS expanded from 376 to 1,199 lines — a complete rewrite:

  • From GitHub-style dark theme to private banking "Overpay Gold Dark" theme
  • Warm black #0D0D0F background + champagne gold #C9A84C accent
  • DM Sans (body) + JetBrains Mono (numbers/code) fonts
  • Subtle ambient gold radial-gradient glow effects
  • Design tokenized with CSS variables for maintainability

Dark/Light Mode Toggle

  • Complete light theme variable override
  • localStorage preference persistence
  • CSS View Transitions API for smooth transitions
  • Auto re-render of Plotly charts on theme switch

Full Mobile Responsive Design

Three breakpoints: 1200px / 768px / 480px + prefers-reduced-motion accessibility

  • Mobile Sticky Header Animation: Two-row to one-row compact transition using CSS --p progress variable + JS transform compensation
  • Chart Touch Gestures: Custom touch tooltip system (touchstart/touchmove/touchend) since Plotly's Fx.hover doesn't render on mobile
  • ResizeObserver: Auto re-render on orientation change

NAV Data Source Revolution — IB CSV Integration (nav_source.py, 285 lines)

A major improvement in data accuracy:

Background: Excel daily report NAV data included deposits/withdrawals, not reflecting true investment performance (TWR, Time-Weighted Return).

Solution: Parse IB Portfolio Analyst CSV reports directly, extracting Time-Weighted Returns that exclude cash flow impact. Covers SI/YTD/MTD comprehensively.

XYLD Benchmark Integration (xyld_source.py, 108 lines)

Added fifth benchmark: XYLD (Global X S&P 500 Covered Call ETF) — the most comparable strategy benchmark for the fund's covered call approach.

Global Benchmark Refactoring

Built global benchmark series in server.py so even old-format files (October 2025) without Return Charts - ALL can display complete benchmark comparison charts.

Options Premium Amortized Cost Feature

New "Options Premium Amortized Cost" tab in Stocks table:

  • Amortized Cost = Original Avg Price - (Cumulative Premium / Shares Held)
  • Shows the true holding cost after factoring in covered call premium income

Delta Analysis Stacked Bar Chart

Separates "pure stock NAV %" from "options delta effect" in a stacked visualization.

From Excel to Web — The Transformation

Original Excel Reports

The fund uses Interactive Brokers' Portfolio Management Excel files as daily reports. Each file contains 7 worksheets covering Portfolio Daily Report, Portfolio Summary, Return Charts, Cumulative Returns, Correlation Matrix, Scenario Analysis, and MTD history.

Pain Points Solved

  1. Daily manual file opening — eliminated
  2. Historical comparison — instant access to any date
  3. Interactive charts — hover values, period switching, benchmark overlay
  4. Mobile access — responsive design for on-the-go viewing
  5. Data consistency — corrected YTD data errors, unified to TWR methodology
  6. Cash flow impact — removed deposit/withdrawal effects from return calculations

Technical Challenges

Multi-Format Excel Parsing

IB generated files with inconsistent formats across different periods. The parser auto-detects old vs. new format and handles 3 variants of the return sheet column layout.

Windows Zombie Processes

Flask debug=True on Windows creates unkillable python3.13.exe child processes. Permanently disabled debug mode.

Plotly Percentage Display

Plotly's textinfo: 'label+percent' calculates relative slice percentages. Delta Analysis needed absolute NAV percentages. Solved with texttemplate: '%\{value:.0f\}%'.

Mobile Chart Interaction

Built complete custom touch tooltip system: touch coordinate to data index mapping, custom DOM tooltip, spike line, and marker dots — since Plotly's built-in mobile hover doesn't work.

Chart Label Anti-Overlap

When 6 benchmark lines have similar values, endpoint labels overlap. Implemented a sorting + push-apart algorithm using Plotly's l2p() coordinate conversion and dynamic right-side padding.

Code Statistics

| File | Lines | Responsibility | |------|-------|----------------| | excel_parser.py | 1,023 | Excel parsing core (old/new format, 7 worksheets) | | app.js | 1,522 | Frontend DOM updates, tables, charts, mobile | | dashboard.css | 1,199 | Complete UI design system (dark/light/responsive) | | charts.js | 565 | Plotly.js return charts (6 lines, touch tooltip, anti-overlap) | | server.py | 348 | Flask API + global benchmarks + amortized calculation | | nav_source.py | 285 | IB CSV parsing (NAV + TWR returns) | | index.html | 253 | Jinja2 template | | xyld_source.py | 108 | Yahoo Finance XYLD data source | | preprocess.py | 51 | JSON cache builder | | Total | 5,354 | |

Current Usage

Daily Workflow

  1. IB generates Portfolio Management_yyyy.mm.dd.xlsm after market close
  2. File placed in organized input directory
  3. Run app/run.bat or python3 app/server.py
  4. System auto-detects new files, rebuilds cache
  5. Access http://localhost:5000 for current and historical data
  6. Shareable via ngrok for remote users

Data Coverage

  • Date range: October 6, 2025 — April 8, 2026 (growing)
  • Files: 125 Excel files + 13 IB CSV reports
  • Asset coverage: Stocks + Options + Cash — complete multi-asset class view

Report Date: April 13, 2026