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

Fund Operation Agents — Detailed Report

pythonclaude-apiautomationnavfund-operationspdf-generation

Project Overview

Project Name: Fund Operation Agents (Fund Operations Automation System)

Fund: TradeMaster Global Investment Fund I (TGIF I), USD-denominated Offshore Fund Company (OFC)

Share Structure: Class B (1.5% management fee / 20% performance fee, 1 investor, 1,000 shares), Class C (0.5% management fee / 10% performance fee, 5 investors, 4,745.0434 shares)

External Fund Administrator: Amicorp Fund Services

Broker: Interactive Brokers (IBKR), Account U17525738

Code Scale: 10,253 lines of Python (excluding dependencies), spanning 30+ modules

Development Timeline

Phase 1: NAV Calculation Engine Core (October 2025 – February 2026)

An iterative development process spanning multiple Claude Code conversation sessions.

October 2025 — Starting from Zero

  • Fund inception on October 6, 2025. First subscription: Class B $1,000,000 + Class C $3,175,000 = $4,175,000
  • Gradually translated manual Excel calculation logic into Python using TJZ NAV working Excel as reference
  • Built core data structures: Position, MonthlyInputs, NAVResult dataclasses
  • Developed nav_engine.py (493 lines): Complete NAV waterfall calculation
  • First validation script: 14 assertions all passed
  • Established key methodology: MTD UGL change (not absolute), options UGL = received premium + current market value, management fee base excludes performance fee add-back

November 2025 — Complex Scenarios: Subscriptions + Option Expiry + Fee Add-backs

  • Second subscription $1,625,000 into Class C, shares increased to 4,745.0434
  • Handled October options expiry: ghost positions (quantity=0) to reverse prior month UGL
  • Implemented performance fee add-back mechanism
  • 26 assertions all passed, MTD returns: B -9.08%, C -9.61%

December 2025 — Steady State: Low Trading Volume

  • No stock trades, only one new TSLA option
  • First dividend income $40 and tax $12
  • Near breakeven: B -0.117%, C -0.032%
  • 20 assertions passed, validating engine stability in low-activity scenarios

January 2026 — Amicorp Reconciliation: Introducing FA Validation Track

  • BABA 165P exercise generated $59,100 RGL
  • First introduction of FA (Amicorp) official data validation
  • Developed gl_parser.py (153 lines): Parse Amicorp GL Movements Excel, mapping 17 P&L ledger subcategories to NAV engine
  • NAV/share: B $924.74, C $927.86

February 2026 — IBKR XML Auto-Parsing: From Manual Input to Automation

  • Developed ibkr_reader.py (421 lines): Parse IBKR Flex Query XML, auto-extracting positions, trades, interest, FX
  • Switched from TJZ cost basis to IBKR FIFO cost basis
  • Cross-validated IBKR fifoPnlUnrealized data against engine calculations

Phase 2: Report Generation System (March 5, 2026)

Massive development completed in a single day.

March 5, 11:53 — Initial Commit (8,148 lines)

  • Unified all developed modules into git
  • fund_admin/ directory: NAV engine + 5-month validation scripts + IBKR reader + GL parser
  • fund_report/ directory: Monthly performance report generator
  • 19-page NAV Pack PDF generator (nav_pack_generator.py, 1,591 lines)
  • Investor statement PDF generator (generate_statements.py, 567 lines)
  • Monthly chain validator (run_chain.py, 188 lines)

March 5–6 — Iterative Improvements

  • Template engine v2.1 with complete data injection
  • Excel workbook generator (generate_nav_excel.py, 1,074 lines)
  • User feedback bug fixes

Phase 3: Continuous Iteration (March–April 2026)

  • March report generation and validation script updates
  • validate_mar26.py (241 lines) added
  • Reconciliation precision narrowed to ~$0.003/share

Why This Project: Manual Process Pain Points

Previous Manual Process

Each month-end required:

  1. Data Collection (1-2 days) — Download IB Activity Statement, manually transcribe data to Excel, query Bloomberg for option prices, confirm DBS bank balances
  2. NAV Calculation (2-3 days) — Manual UGL calculations, RGL by type, P&L aggregation, Class B/C allocation, management fee, performance fee with HWM tracking
  3. Report Preparation (2-3 days) — Manually fill 19-page NAV Pack, 6 individual investor statements, monthly performance report
  4. Reconciliation (1-2 days) — Compare with Amicorp official NAV Pack, track every discrepancy

Most Painful Issues

  1. Extreme repetitiveness with high error potential
  2. Complex options handling — short option UGL, exercise cost basis, ghost position reversal
  3. Three conflicting cost bases — IBKR FIFO vs TJZ tracking vs Amicorp
  4. Fragile Excel formulas — accidental overwrites, broken SPILL formulas
  5. Endless reconciliation — days spent finding cents-level differences

NAV Calculation Engine Development

Calculation Pitfalls Solved

Management Fee Base: Fee base = NAV before mgmt fee - perf fee addback. The add-back amount is not "real" fund assets.

HWM Performance Fee: HWM tracked per share class (Class B/C), taking max HWM across all investors. Performance fee = rate x max(0, current NAV/share - HWM) x total shares. HWM at inception = 1000 (subscription price).

Options UGL: Options UGL = gross premium received + (quantity x current price x 100). IBKR's fifoPnlUnrealized validated to match this formula exactly.

Ghost Positions: Expired options retained as quantity=0 ghost positions to correctly reverse prior month UGL: mtd_ugl_change = 0 - prior_ugl.

Options RGL Cost Basis: TJZ uses gross premium (not IBKR's net-of-commission value). Commissions tracked separately.

Setup Fee Decomposition: Setup fee = FA fee $59.72/month + FA fee $74.65/month + KWM lawyer fee (HKD 76,666.67 / 34 months, converted at month-end HKD/USD rate).

DBS Cash Allocation: DBS bank cash allocated proportionally to Class B/C by opening NAV weight.

Report Generation Challenges

NAV Pack PDF (19 pages, 1,591 lines ReportLab code)

Structure spanning cover page, balance sheet, income statement, NAV history, valuation details, fee breakdowns, cash flow, currency revaluation, securities cost basis, position lists, realized/unrealized P&L, and investor statements. Every table's colors, fonts, alignment, and borders must match Amicorp's official format.

NAV Excel Workbook (1,074 lines)

Critical Challenge — openpyxl XML Corruption: openpyxl deletes xl/metadata.xml when saving .xlsx, destroying all dynamic array formulas (cells with cm="1" attribute).

Solution — Post-save XML patching: After save, open .xlsx as ZIP, restore xl/metadata.xml from template, rewrite dynamic array formula cells with cm="1" attributes and _xlfn. prefixes.

Monthly Performance Report .xlsm Injection (756 lines)

VBA Preservation: Load .xlsm with keep_vba=True, then ZIP-level patching to restore xl/externalLinks/, xl/drawings/, xl/richData/ directories. Custom _merge_content_types() ensures VBA and external link content types survive.

Amicorp Reconciliation

Precision Journey

Initial gap: ~$0.24/share — Differences in Setup Fee, FX Gain/Loss, Interest, Commissions.

After CRS Fee adjustment: Gap narrowed from ~$0.24/share to ~$0.02/share (CRS Fee $1,397.50 included in TJZ but not in FA calculations).

Final precision: ~$0.003/share — Achieved through Setup Fee decomposition, unified FX rate timing, accrual-basis interest, exact commission matching, and confirmed recurring fees (Admin $1,343.75 + AFS $225.75 + Audit $1,053.33 + Setup $422.96 = $3,045.79/month).

Dual P&L Tracks

The engine maintains two independent P&L tracking tracks:

  • TJZ Track: Gross premiums, own cost tracking, includes CRS and one-time fees
  • FA Track: Amicorp official methodology, excludes CRS, uses their FX rates

The reconciliation table in run_chain.py displays differences between tracks, with the known ~$21 gap (~$0.004/share) documented as methodology differences.

Automated Monthly Flow

Before vs After

| Step | Manual (Before) | Automated (Now) | |------|----------------|-----------------| | Data Collection | 1-2 days | ~30 minutes | | NAV Calculation | 2-3 days | ~1 second | | Report Generation | 2-3 days | ~10 seconds | | Reconciliation | 1-2 days | ~30 minutes | | Total | 6-10 days | ~1 hour |

Top Technical Achievements

  1. NAV Precision: $0.003/share — From ~$0.24/share gap, narrowed through multi-dimensional corrections. On ~$5.3M fund, the difference is under $20.

  2. 5-Month Chain Validation Passed — Oct 2025 through Feb 2026, 107 assertions all passed. Each month's closing NAV seamlessly connects to the next month's opening NAV.

  3. Excel XML Patching — Solved the industry-level pain point of openpyxl destroying dynamic array formulas through post-save ZIP patching.

  4. VBA-Preserving Template Injection — Data injection into .xlsm files while preserving VBA macros, external links, and rich data types — functionality not officially supported by openpyxl.

  5. End-to-End Automation — One command from IBKR XML to 19-page NAV Pack + 6 investor statements + NAV Excel workbook + monthly performance report.

  6. Dual-Track Reconciliation System — Maintains TJZ and FA calculation tracks with built-in reconciliation reporting and difference tracking.

Code Statistics

| Module | Lines | Function | |--------|-------|----------| | nav_pack_generator.py | 1,591 | 19-page NAV Pack PDF | | generate_nav_excel.py | 1,074 | NAV Excel workbook (with XML patching) | | generate_nav_pack.py | 970 | NAV Pack generation entry + multi-month orchestration | | template_engine.py | 756 | Excel .xlsm template injection engine | | generate_statements.py | 567 | Investor statement PDFs | | nav_engine.py | 493 | Core NAV calculation engine | | analytics_engine.py | 429 | Risk metrics and analytics | | ibkr_reader.py | 421 | IBKR Flex Query XML parser | | build_comps.py | 346 | FUTU peer comparison analysis | | daily_data.py | 276 | Daily data processing | | generate_report.py | 265 | Monthly report generation entry | | Validation scripts (x6) | ~1,220 | Oct–Mar monthly validation | | Other helper modules | ~845 | GL parser, CSV parser, FX utilities | | Total | ~10,253 | |

Validation Coverage

| Month | Assertions | Status | |-------|-----------|--------| | Oct 2025 | 14 | All passed | | Nov 2025 | 26 | All passed | | Dec 2025 | 20 | All passed | | Jan 2026 | 20 | All passed | | Feb 2026 | 10 | All passed | | Mar 2026 | ~17 | In progress | | Total | ~107 | |

Report Date: April 13, 2026