# Equity Portfolio
## Feature Description
Complete equity portfolio management including holdings tracking, trade history, XIRR calculations, and CSV import.
## User Stories
- **As a User**, I want to view my holdings so that I can see current portfolio value.
- **As a User**, I want to see my trade history so that I can track all transactions.
- **As a User**, I want to calculate XIRR so that I can measure portfolio performance.
- **As a User**, I want to import historical trades so that I can analyze long-term performance.
- **As a User**, I want to resolve import conflicts so that data integrity is maintained.
## Technical Implementation
### Holdings
**API Route**: `GET /api/stats?accountId=...`
**Features**:
- Current holdings per symbol
- Current prices (Yahoo Finance)
- Investment value vs current value
- P&L (realized + unrealized)
- Stock-level XIRR
- Position lifecycle tracking
**Calculation Method**: FIFO (First-In-First-Out)
- Holdings = Sum of buy quantities - Sum of sell quantities
- Current Value = Holdings × Current Price
- **Realized P&L**: Calculated using FIFO matching of sells to oldest buys
- Only includes P&L from completed sales
- Matches each sell to specific buy lots chronologically
- New buys after position closure don't affect past realized P&L
- **Unrealized P&L**: Based on remaining unmatched buy lots
- Current value - Cost basis of remaining lots
- **Investment**: Cost basis of current holdings only (not all-time purchases)
- **Position Cycles**: Tracks when positions close (quantity = 0) and reopen
### Trade History
**API Route**: `GET /api/tradebook?accountId=...`
**Features**:
- All trades grouped by symbol
- Holdings per symbol
- Average price (weighted by remaining lots)
- Current price
- P&L per symbol (FIFO-based)
- Realized P&L from closed positions
- Unrealized P&L from active position
- XIRR per symbol (separated by position cycle)
- Position status (active/sold)
- **Opportunity Cost** (for sold positions only)
- Shows "what if" value if shares were still held
- Displays opportunity cost vs actual sell proceeds
- Helps identify if position was sold too early or at the right time
### XIRR Calculation
**Library**: `xirr` npm package
**Location**: `equity/lib/xirr-calculator.ts`
**Types**:
1. **Portfolio XIRR**: Based on ledger entries (cash flows) and current portfolio value
2. **Stock XIRR**: Based on trades for a symbol and current price
- For **closed positions**: Uses only trades from that closed position cycle
- Sell trades already represent final cash flows (no additional cash flow needed)
- For **active positions**: Uses only trades from current open position cycle
- Current market value added as final cash flow
- This prevents mixing of different position lifecycles
**Validation**:
- Requires at least 2 cash flows
- Must have both positive (sells/current value) and negative (buys) cash flows
- Returns `null` if calculation not possible (displayed as "N/A" in UI)
- For active positions, requires current price > 0
**Usage**:
```typescript
import { calculatePortfolioXIRR, calculateStockXIRR } from '@/lib/xirr-calculator';
import { calculateFIFOPosition } from '@/lib/fifo-calculator';
// Portfolio XIRR
const portfolioXIRR = calculatePortfolioXIRR(ledgerEntries, currentValue);
// Stock XIRR with position lifecycle tracking
const fifoResult = calculateFIFOPosition(trades);
const tradesForXIRR = netQuantity === 0
? fifoResult.closedPositionTrades
: fifoResult.activePositionTrades;
const stockXIRR = calculateStockXIRR(tradesForXIRR, currentPrice, netQuantity);
// stockXIRR will be null if:
// - No trades available
// - Less than 2 cash flows
// - Only buys or only sells (no opposite cash flows)
// - Current price is 0 for active positions
```
**Debugging**:
- Console logs prefixed with `[XIRR]` show calculation details
- API routes log XIRR results with `[Stats XIRR]` and `[Tradebook XIRR]` prefixes
- Check browser console for specific reasons why XIRR shows "N/A"
### FIFO Position Tracking
**Location**: `equity/lib/fifo-calculator.ts`
**Purpose**: Properly track position lifecycles and calculate accurate P&L using FIFO matching
**Key Features**:
- Matches sells with oldest unmatched buys (FIFO principle)
- Detects when positions close (quantity reaches zero)
- Separates closed positions from new positions
- Calculates realized P&L only from matched buy-sell pairs
- Maintains separate trade lists for closed vs active positions
**Usage**:
```typescript
import { calculateFIFOPosition, calculateUnrealizedPnL } from '@/lib/fifo-calculator';
const fifoResult = calculateFIFOPosition(trades);
// Realized P&L (from closed sales only)
const realizedPnL = fifoResult.realizedPnL;
// Unrealized P&L (from active position)
const unrealizedResult = calculateUnrealizedPnL(fifoResult, currentPrice);
const unrealizedPnL = unrealizedResult.unrealizedPnL;
// Position metrics
const investment = fifoResult.activePositionCost; // Cost of current holdings
const avgPrice = fifoResult.avgBuyPriceForActivePosition; // Avg of remaining lots
```
**See**: `equity/POSITION_LIFECYCLE_FIX.md` for detailed explanation
### CSV Import
**API Routes**:
- `POST /api/import/tradebook` - Import tradebook CSV
- `POST /api/import/ledger` - Import ledger CSV
**Process**:
1. Parse CSV file
2. Validate each record
3. Check for duplicates (by trade_id for trades)
4. Create conflicts for mismatches
5. Insert new records
6. Update account sync timestamps
**Conflict Detection**:
- Duplicate trade_id with different data
- Missing required fields
- Invalid data formats
### Conflict Resolution
**API Route**: `GET /api/conflicts`, `PUT /api/conflicts/[id]`
**Resolution Options**:
- `resolved_keep_existing` - Keep database record
- `resolved_use_new` - Replace with CSV data
- `resolved_manual` - Manually resolved
- `ignored` - Ignore conflict
**UI**: `/conflicts` page for resolving conflicts
### Stock Split Tool
**API Route**: `POST /api/tools/split`
**Purpose**: Apply stock split to historical trades
**Process**:
1. Find all trades for symbol before split date
2. Adjust quantity and price based on split ratio
3. Update trades in database
## Database Tables
- `accounts` - Trading accounts
- `trades` - Trade records
- `ledger` - Cash flow entries
- `import_conflicts` - Import conflicts
## Files
### API Routes
- `equity/app/api/stats/route.ts` - Portfolio statistics (uses FIFO calculation)
- `equity/app/api/tradebook/route.ts` - Tradebook with XIRR (uses FIFO calculation)
- `equity/app/api/trades/route.ts` - Trade CRUD
- `equity/app/api/import/tradebook/route.ts` - Tradebook import
- `equity/app/api/import/ledger/route.ts` - Ledger import
- `equity/app/api/conflicts/route.ts` - Conflict management
- `equity/app/api/tools/split/route.ts` - Stock split tool
### Libraries
- `equity/lib/fifo-calculator.ts` - FIFO position lifecycle tracking and P&L calculation
- `equity/lib/xirr-calculator.ts` - XIRR calculations
- `equity/lib/yahoo-finance.ts` - Price fetching
### Documentation
- `equity/POSITION_LIFECYCLE_FIX.md` - Detailed explanation of FIFO P&L calculation
