General Fund Admin — Detailed Report
Project Origin
The original requirement was clear: build an automated system to generate the fund's monthly NAV Pack, replacing the workflow manually completed by Maples Fund Services.
The target fund is TM Capital FUND SPC — China Technology Growth Fund SP, a Segregated Portfolio Company (SPC) registered in the Cayman Islands, with TradeMaster Securities (Hong Kong) Limited as investment manager, Interactive Brokers Hong Kong as broker/custodian, Maples Fund Services (Asia) Limited as fund administrator, and Ernst & Young (Cayman) as auditor.
Fund structure: SP2-B class with 4 active Series (Series 1/3/5/6), 3,500 shares held by 4 investors, NAV approximately $2.2–2.4M.
The methodology was to first learn from historical data (15 months of Maples NAV Packs, Feb 2021 – Apr 2022), verify calculations accurately reproduce Maples' results, then produce future months' NAV Packs using new data.
Relationship to the ai-agents Project
This project was not built from scratch conceptually — TradeMaster already had experience using AI agents for fund-related work. However, the General Fund Admin requirements were fundamentally different:
Reusable experience: Understanding of fund structures (SPC, Series, Class), familiarity with IB data formats, Python + Decimal financial calculation architecture, agent team collaboration patterns.
Required redesign: Maples CSV/Excel formats were entirely new (7 CSV report types, 10-sheet Excel workbook with complex cross-references), fee calculation rules defined by a Fund Assumptions Document with unique rules (tiered admin fee, per-series management fee, performance fee vs HWM, prepaid accruals with biennial growth, org cost amortization), and pixel-perfect Excel formatting requirements.
Maples Data Format Research
Data Exploration
The input dataset included:
- 15 months of Maples NAV Packs (each containing 1 Excel workbook + 7 CSVs + corresponding PDF)
- IB Activity Statements (monthly)
- Bank Statements (Maples BONY account PDFs)
- Audited Financial Statements (2021 EY audit report)
- Contract Notes, Dealing Summaries, Investor Statements
- Fund Assumptions Document
Three parallel agents were launched simultaneously to analyze:
- NAV workbook 10-sheet Excel structure (row/column layouts, formulas, cross-sheet references)
- 7 CSV report field formats and data meanings
- Month-to-month data change patterns
Understanding the Maples Workbook
This was one of the most time-consuming parts of the project:
Allocation Sheet Formula Chains: Rows 9–35 form the NAV waterfall, but Column X (total column) uses SUMIF($C$4:$W$4,"Sub-total",...) to only sum subtotal columns (H, P, V), avoiding double-counting with individual series columns.
Fee Sheet Date-Conditional Formulas: Every fee sheet has =IF($A_n=$A$2, calculated_result, "Input") formulas where $A$2 is the valuation date. This design auto-displays the correct month's calculation but means all historical rows show "Input" when copied to a new workbook with a different $A$2.
PrepaidsAccruals Complexity: 4 sections with 10 sub-tables, each with its own annual base, period count, and 5% biennial growth formula.
Hidden Elements: 10 hidden Allocation columns (inactive SP2-A/SP2-C classes + spacer columns), 3 hidden sheets (QI-ARTs, NonBaseCurrencyFees, ShareReg), 53 hidden pre-launch template rows in PrepaidsAccruals, 9 hidden rows in FATCAfees.
Understanding this workbook consumed approximately 30% of the project's initial time.
Major Development Challenges
Challenge 1: IB and Maples See the World Differently
IB Activity Statements only know about the brokerage account (positions, cash, trades, dividends). Maples, as fund administrator, sees the entire fund including the BONY bank account, management/audit/organizational fees, and related party payables that IB doesn't track.
Solution: Layer Maples-level adjustments on top of IB data — add BONY bank balance, subtract fund-level fee accruals (audit, AML, FS prep, PPOC, CRS, D&O, government, registration, org costs, FATCA), subtract Administration Fee and Management Fee. This produces: IB income + Maples adjustments = Fund Net Income -> NAV waterfall.
Challenge 2: Fee Calculation Precision
The fund has over a dozen fee types, each with different calculation rules. Admin Fee has tiered rates but the minimum always applies (AUM too small). Management Fee is per-series. PrepaidsAccruals have 5% biennial growth. OrgCosts use 60-month amortization.
Problem: Formulaic calculations differed from Maples' actual posted amounts by $600–700/month (~0.03%), due to payment timing, rounding, and catch-up adjustments.
Solution: TB-calibrated mode — when Trial Balance CSVs are provided, use TB expense line deltas directly instead of formula estimates. Precision improved from 0.03% to 0.001%.
Challenge 3: Excel Formulas vs Hardcoded Values
openpyxl can write Excel formulas but cannot calculate them.
Solution: Hybrid mode using two versions of the prior workbook — data_only=True for historical row expense/payment/balance values, data_only=False for structural formulas (cross-sheet references, SUM formulas, date calculations). Historical rows use VALUES for D/E/F columns; structural columns use FORMULAS; current and future rows use all FORMULAS.
Challenge 4: Column X SUMIF Logic
Initially used =SUM(C:W) for totals. Numbers appeared correct (inactive series were all zeros), but the logic was wrong — Maples uses =ROUND(SUMIF($C$4:$W$4,"Sub-total",$C_row:$W_row),2), only summing columns marked "Sub-total" in Row 4.
Discovered through an independent audit agent (separate context to avoid hallucination). Fixed all Column X formulas to SUMIF.
Challenge 5: Row 37/38 TB Reconciliation
Labels said "Prior month GAV per Share" and "Prior month NAV per Share" but actually contain current month Trial Balance net assets and net liabilities. Calculated as DR balance minus CR balance of Assets and Liabilities respectively.
Challenge 6: Admin Fee is 8 bps, Not 8%
Fund Assumptions Document stated "1st level: 8.0%" — initially calculated at 8%, producing ~$19,000/month. Trial Balance showed Admin Fee Payable was always in the $3,000–$4,000 range. Confirmed actual tiered rate is 8 basis points (0.08%), with the $3,000 minimum always applying.
Challenge 7: Format is Compliance, Not Aesthetics
After getting numbers correct, the Excel output still differed vastly from Maples — wrong column widths, missing yellow highlights, no borders, unhidden inactive columns/sheets.
Solution: Agent extracted complete format specifications from Maples workbook (1,431 cells' font, fill, border, alignment, number_format), then wrote into code. Fee sheets directly copy formatting from prior workbook.
Challenge 8: Dividend Over-Accrual
IB's "Change in Dividend Accruals" includes all dividend activity (posted, reversed, adjusted), not just month-end unreceived amounts.
Solution: Two-layer filtering: (1) pay_date > valuation_date, (2) same symbol + ex_date deduplication.
Challenge 9: Year-End Audit Adjustments
A $32,276 continuity break between Dec 2021 and Jan 2022. Investigation revealed this was the normal year-end audit adjustment — EY's audited NAV ($2,798,133) differed from Maples monthly report NAV ($2,830,092) by $31,959.
Precision Improvement Journey
Stage 1: Basic Waterfall (Gap ~$3,600, 0.99%)
- Only IB income + admin fee + mgmt fee
- No fund-level accruals
- NAV consistently too high
Stage 2: Formulaic Accruals (Gap ~$600, 0.03%)
- Added audit/AML/FS prep/PPOC/CRS/D&O/govt/registration/org costs/FATCA formula calculations
- Remaining gap from formula estimates vs Maples actual postings
Stage 3: TB-Calibrated Mode (Gap ~$20, 0.0009%)
- Using prior + current Trial Balance CSV expense line monthly deltas
- Added bank charges from TB Bank Charges expense line
- 19x precision improvement
| Mode | Avg Gap | Avg Gap % | Worst Month | |------|---------|-----------|-------------| | Basic waterfall | $3,600 | 0.99% | — | | + Formulaic accruals | $630 | 0.03% | Jan 2022 ($2,721, 0.10%) | | + TB-calibrated | $20 | 0.0009% | Jul 2021 ($247, 0.008%) |
FIFO vs Average Cost Handling
IB uses FIFO while Maples uses Average Cost. After multiple buys and sells, the difference can accumulate to thousands of dollars.
Solution: Parse prior month Maples Investment Position Appraisal CSV, use Maples average cost for continuing positions (scaled by quantity ratio), and IB cost basis for new positions (where FIFO = Average Cost).
Current Capabilities
Complete NAV Pack Generation:
- Input: Prior month Maples workbook + IB Activity Statement (+ optional Trial Balance CSVs)
- Output: 16 files — 7 Maples-format CSVs, 1 ten-sheet Excel workbook (1,485 formulas, pixel-perfect formatting), 8 investor reports
Precision: TB-calibrated 0.0009% ($20 on $2.26M); Formulaic ~0.03% ($630 on $2.26M)
Validated: 15-month historical backtest passed; May/Jun 2022 cell-by-cell comparison with Maples; Jul 2022 successfully generated (first month without Maples reference)
Known Limitations
- Structural precision ceiling ~$20 (0.001%) due to IB settlement-based vs Maples accrual-based accounting
- Without current TB: fees use formulaic estimates (precision drops to 0.03%)
- P&L report USD Cash OtherIncome only captures IB fees (~$29), missing fund-level fees (~$11,900)
- Minor formatting differences from openpyxl limitations with merged cells
Future Work
- Fully autonomous mode without Maples TB dependency
- Precise PrepaidsAccruals monthly amounts
- Complete AdminFee formula logic alignment
- Automated IB Activity Statement download via API
- PDF-format Investor Statements
- Multi-fund support (currently hardcoded for China Technology Growth Fund SP)
Report Date: April 13, 2026