# History Reconstruction - Design Document
## Problem Statement
Portfolio tracking applications like Wealthfolio assume users start tracking from day one with a complete transaction history. However, in reality:
- Users often want to start tracking an **existing portfolio** with years of history
- Manually entering **thousands of historical transactions** is impractical and error-prone
- Users have their **current positions** but not detailed transaction records
- Broker statements may exist but are in various formats (PDF, CSV, Excel)
- Some historical data may be lost or incomplete
**Core Issue**: How do we bootstrap portfolio tracking for existing portfolios without requiring complete historical transaction data?
---
## Use Cases
### 1. New User Starting with Existing Portfolio
**Scenario**: Alice has been investing for 10 years. She has 50 different holdings across 3 accounts but never tracked them in software. She wants to start using Wealthfolio today.
**Current State**:
- Knows current positions (symbol, quantity, account)
- Has approximate cost basis (from broker statements or memory)
- Does NOT have 10 years of transaction history readily available
**Desired Outcome**:
- Start tracking portfolio immediately
- Accurate current holdings reflected in Wealthfolio
- Reasonable cost basis for performance tracking going forward
- Skip entering thousands of historical buy/sell/dividend transactions
### 2. Portfolio Migration
**Scenario**: Bob used another portfolio tracker but wants to switch to Wealthfolio. The old system doesn't export in a compatible format.
**Current State**:
- CSV export with: symbol, shares, average cost, current value
- 100+ positions to migrate
- No detailed transaction history available
**Desired Outcome**:
- Bulk import all positions at once
- Preserve cost basis information
- Begin tracking from migration date forward
### 3. Reconciliation After Gap
**Scenario**: Carol tracked her portfolio but stopped updating for 2 years. She made many trades during that time through her broker.
**Current State**:
- Wealthfolio data is outdated
- Actual broker positions differ significantly
- Doesn't remember all trades made during the gap
**Desired Outcome**:
- Reconcile Wealthfolio with current broker positions
- Identify and fix discrepancies
- Generate adjustment transactions to correct holdings
---
## Proposed Solutions
### Solution 1: Opening Position Generator (MVP)
**Description**: Create synthetic "opening balance" transactions that establish current holdings as of a specific date.
**Implementation**:
```
Tool: generate_opening_positions
Inputs:
- effective_date: Date to use for opening positions (default: today)
- use_current_holdings: Boolean to auto-detect from holdings_snapshots
- manual_positions: Optional array of {symbol, quantity, cost_basis, account}
Process:
1. Query current holdings from holdings_snapshots (most recent snapshot)
2. For each holding:
- Create an activity with activity_type = "OPENING_BALANCE"
- Set activity_date = effective_date
- Set quantity = current position quantity
- Set unit_price = average cost (from holdings or user input)
- Set account_id, asset_id from current data
3. Insert all activities into activities table
4. Trigger Wealthfolio recalculation
Output:
- Number of positions created
- List of symbols and quantities
- Total cost basis established
- Summary by account
```
**Pros**:
- Simple to implement
- Works with existing Wealthfolio schema
- No external dependencies
- User gets tracking started immediately
**Cons**:
- No historical performance data before opening date
- Cost basis must be known or estimated
- Doesn't handle historical dividends/splits
**Priority**: **Critical** - This is the minimum viable solution
---
### Solution 2: Portfolio Snapshot Import
**Description**: Bulk import positions from CSV/Excel file with validation and preview.
**Implementation**:
```
Tool: import_portfolio_snapshot
Inputs:
- file_path or file_content: CSV/Excel data
- account_mapping: Map import account names to Wealthfolio accounts
- effective_date: Date for the snapshot
- dry_run: Preview without making changes
CSV Format:
symbol,quantity,average_cost,currency,account,asset_name
AAPL,100,150.50,USD,Trading Account,Apple Inc.
MSFT,50,300.00,USD,Trading Account,Microsoft Corp.
DCAM.PA,7770,4.75,EUR,PEA,Archos SA
Process:
1. Parse CSV/Excel file
2. Validate format and data:
- Check symbols exist or can be looked up
- Validate quantities are positive numbers
- Check currencies are valid
- Validate account names
3. Match or create assets in assets table
4. Generate preview report:
- Positions to be created
- New assets to be added
- Total value by account
- Warnings/errors
5. If not dry_run: Create opening balance activities
6. Return import summary
Output:
- Positions imported: count
- New assets created: list
- Errors/warnings: list
- Total value imported by currency
```
**Pros**:
- User-friendly for bulk imports
- Can import from broker exports
- Validation before commit
- Handles asset creation automatically
**Cons**:
- Requires CSV/Excel file preparation
- User must know cost basis
- Still no historical transactions
**Priority**: **High** - Very useful for onboarding
---
### Solution 3: Position Reconciliation Tool
**Description**: Compare current broker positions against Wealthfolio data and generate corrective transactions.
**Implementation**:
```
Tool: reconcile_positions
Inputs:
- account_id: Account to reconcile
- broker_positions: Array of {symbol, quantity, value}
- reconciliation_date: Date to reconcile as of
Process:
1. Get current Wealthfolio positions for account
2. Compare with broker positions:
- Missing in Wealthfolio: Need to add
- Extra in Wealthfolio: Need to remove
- Quantity mismatch: Need to adjust
3. Generate reconciliation report
4. Optionally create adjustment activities to fix
Output:
- Discrepancies found: list with details
- Suggested adjustments: activities to create
- Value differences: by position
- Action plan: what will be changed
```
**Pros**:
- Keeps data in sync with reality
- Catches data entry errors
- Useful for periodic validation
**Cons**:
- Requires manual broker position entry (unless integrated)
- May be complex for large portfolios
- Doesn't explain why discrepancies occurred
**Priority**: **High** - Important for data accuracy
---
### Solution 4: History Reconstruction from Statements (Future)
**Description**: Parse broker statements (PDF/CSV) to extract and import historical transactions.
**Implementation**:
- PDF parsing for major brokers
- CSV import with column mapping
- Transaction matching and deduplication
- Corporate action handling (splits, mergers)
- Pattern recognition for common formats
**Priority**: **Medium** - Nice to have, but complex
---
## Technical Considerations
### Database Modifications Required
**NONE** - All solutions work with existing Wealthfolio schema by:
- Creating activities with appropriate activity_types
- Using existing accounts and assets tables
- Leveraging Wealthfolio's built-in recalculation
### Activity Types to Use
- `OPENING_BALANCE` - For opening positions (custom type, may need to verify Wealthfolio supports this)
- `BUY` - Alternative if OPENING_BALANCE not supported
- `ADJUSTMENT` - For reconciliation corrections
### Data Validation Rules
1. **Symbol Validation**: Check if asset exists in assets table or can be looked up
2. **Quantity Validation**: Must be positive, reasonable precision
3. **Cost Basis Validation**: Must be positive, in correct currency
4. **Date Validation**: Can't be in the future, should be reasonable (not 1900)
5. **Account Validation**: Must reference existing account_id
### Error Handling
- Validation errors: Report all issues, don't partially import
- Asset not found: Option to create or skip
- Duplicate detection: Warn if opening positions already exist
- Rollback: If import fails, no data should be modified
---
## User Experience Flow
### Happy Path: New User
1. User asks Claude: "I want to start tracking my portfolio. I have my current positions from my broker."
2. Claude asks: "Do you have this information in a CSV file, or should we enter positions one by one?"
3. User provides CSV or asks to be guided
4. Claude uses `import_portfolio_snapshot` with dry_run=true
5. Claude shows preview: "I'll import 50 positions worth €250,000. Does this look correct?"
6. User confirms
7. Claude imports positions
8. Claude confirms: "Portfolio imported! You can now track performance from today forward."
### Alternative Path: Manual Entry
1. User asks: "Add opening balance for my current holdings"
2. Claude asks: "Would you like me to use your current positions from Wealthfolio, or enter manually?"
3. User: "Use current positions"
4. Claude uses `generate_opening_positions` with current holdings
5. Claude confirms: "Created opening positions for 30 holdings as of today. Total value: €180,000"
---
## Implementation Phases
### Phase 1 (MVP) - Q1 2025
- [x] Read-only query tools (completed)
- [ ] `generate_opening_positions` tool
- [ ] Basic validation and error handling
- [ ] Documentation and user guide
### Phase 2 - Q2 2025
- [ ] `import_portfolio_snapshot` tool with CSV support
- [ ] Asset auto-creation
- [ ] Dry-run preview mode
- [ ] Enhanced validation
### Phase 3 - Q2/Q3 2025
- [ ] `reconcile_positions` tool
- [ ] Discrepancy detection and reporting
- [ ] Adjustment transaction generation
- [ ] Periodic reconciliation workflows
### Phase 4 - Q3/Q4 2025 (Future)
- [ ] Broker statement parsing
- [ ] Historical transaction import
- [ ] Corporate action handling
- [ ] Multi-format support
---
## Open Questions
1. **Does Wealthfolio support OPENING_BALANCE activity type?**
- Need to verify in source code or documentation
- Alternative: Use BUY type with appropriate notes
2. **Should we write to the database or generate import files?**
- Direct write: Faster, requires careful validation
- Generate files: Safer, user imports via Wealthfolio UI
- **Decision**: Start with direct write, add file generation later
3. **How to handle cost basis when unknown?**
- Option 1: Require user input
- Option 2: Use current market price as cost (shows no gain/loss)
- Option 3: Leave as zero and let user update later
- **Decision**: Use current market price as default, allow override
4. **What if holdings_snapshots is empty?**
- Wealthfolio may not have current positions yet
- Would need manual position entry
- Or import from CSV only
5. **Security concerns with write operations?**
- Current MCP server is read-only
- Adding write capabilities changes security model
- Need proper validation and safeguards
- Consider requiring explicit user confirmation
---
## Success Metrics
- **Time to First Track**: Reduce from hours (manual entry) to minutes (import)
- **Accuracy**: 99%+ of imported positions match broker statements
- **User Satisfaction**: Users can start tracking existing portfolios without frustration
- **Adoption**: 50%+ of new users use opening position tools
---
## Risks and Mitigations
| Risk | Impact | Mitigation |
|------|--------|------------|
| Data corruption | High | Extensive validation, dry-run mode, backups |
| Incorrect cost basis | Medium | Clear warnings, ability to edit later |
| Wealthfolio compatibility | High | Test with current Wealthfolio version, verify schema |
| User error (wrong data) | Medium | Preview mode, confirmation prompts |
| Security (write access) | High | Validate all inputs, limit operations, audit logging |
---
## Next Steps
1. **Investigate Wealthfolio schema** for supported activity types
2. **Create proof-of-concept** for generate_opening_positions
3. **Test with real portfolio data** (use test database)
4. **Get user feedback** on proposed UX flow
5. **Document write operation security model**
6. **Implement MVP** with proper safeguards
---
## Appendix: Example CSV Format
```csv
symbol,quantity,average_cost,currency,account,asset_name
AAPL,100,150.50,USD,Brokerage,Apple Inc.
MSFT,50,300.00,USD,Brokerage,Microsoft Corporation
GOOGL,25,2500.00,USD,Brokerage,Alphabet Inc.
DCAM.PA,7770,4.753,EUR,PEA,Archos SA
```
## Appendix: Database Schema Impact
```sql
-- Example opening balance activity
INSERT INTO activities (
id,
account_id,
asset_id,
activity_type,
activity_date,
quantity,
unit_price,
currency,
fee,
is_draft,
comment
) VALUES (
'opening-balance-1',
'account-123',
'AAPL',
'OPENING_BALANCE', -- or 'BUY' if not supported
'2025-01-27',
100,
150.50,
'USD',
0,
false,
'Opening balance - imported from current holdings'
);
```
