Building Real-Time Investment Dashboard with Stock + Crypto Analytics

-- schema.sql
 
-- Unified holdings table (stocks + crypto)
CREATE TABLE holdings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    asset_symbol VARCHAR(10) NOT NULL,
    quantity DECIMAL(15, 6) NOT NULL,
    asset_type ENUM('stock', 'crypto') NOT NULL,
    cost_basis DECIMAL(12, 2),
    purchase_date DATE,
    source VARCHAR(50),  -- 'robinhood', 'coinbase', 'manual', etc.
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_asset_symbol (asset_symbol),
    INDEX idx_asset_type (asset_type),
    INDEX idx_user_asset (user_id, asset_symbol)  -- Composite index
);
 
-- Price cache (reduce API calls)
CREATE TABLE price_cache (
    id INT AUTO_INCREMENT PRIMARY KEY,
    symbol VARCHAR(10) NOT NULL,
    asset_type ENUM('stock', 'crypto') NOT NULL,
    price DECIMAL(12, 6) NOT NULL,
    timestamp TIMESTAMP NOT NULL,
    source VARCHAR(50),
    
    UNIQUE KEY unique_symbol_timestamp (symbol, timestamp),
    INDEX idx_symbol (symbol),
    INDEX idx_timestamp (timestamp)
);
 
-- Transaction history
CREATE TABLE transactions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    asset_symbol VARCHAR(10) NOT NULL,
    asset_type ENUM('stock', 'crypto') NOT NULL,
    transaction_type ENUM('buy', 'sell') NOT NULL,
    quantity DECIMAL(15, 6) NOT NULL,
    price DECIMAL(12, 6) NOT NULL,
    total_value DECIMAL(12, 2) NOT NULL,
    fees DECIMAL(8, 2) DEFAULT 0,
    transaction_date TIMESTAMP NOT NULL,
    
    INDEX idx_user_id (user_id),
    INDEX idx_date (transaction_date)
);
 
-- Calculated analytics (materialized view pattern)
CREATE TABLE portfolio_analytics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE,
    total_value DECIMAL(15, 2),
    total_cost_basis DECIMAL(15, 2),
    total_return DECIMAL(15, 2),
    return_percentage DECIMAL(6, 2),
    sharpe_ratio DECIMAL(6, 4),
    value_at_risk DECIMAL(15, 2),
    max_drawdown DECIMAL(6, 2),
    beta DECIMAL(6, 4),
    alpha DECIMAL(6, 4),
    calculated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id)
);

-- Calculate Value at Risk (VaR) - 95% confidence, 1-day horizon
DELIMITER $$
 
CREATE PROCEDURE calc_var(IN p_user_id INT)
BEGIN
    DECLARE portfolio_value DECIMAL(15, 2);
    DECLARE daily_volatility DECIMAL(10, 6);
    
    -- Get current portfolio value
    SELECT SUM(h.quantity * pc.price)
    INTO portfolio_value
    FROM holdings h
    JOIN price_cache pc ON h.asset_symbol = pc.symbol
    WHERE h.user_id = p_user_id
    AND pc.timestamp = (
        SELECT MAX(timestamp) 
        FROM price_cache 
        WHERE symbol = pc.symbol
    );
    
    -- Calculate daily volatility from historical prices
    WITH daily_returns AS (
        SELECT 
            symbol,
            (price - LAG(price) OVER (PARTITION BY symbol ORDER BY timestamp)) / 
            LAG(price) OVER (PARTITION BY symbol ORDER BY timestamp) as daily_return
        FROM price_cache
        WHERE timestamp >= DATE_SUB(NOW(), INTERVAL 30 DAY)
    ),
    portfolio_returns AS (
        SELECT 
            dr.daily_return,
            h.quantity * pc.price / portfolio_value as weight
        FROM daily_returns dr
        JOIN holdings h ON dr.symbol = h.asset_symbol
        JOIN price_cache pc ON h.asset_symbol = pc.symbol
        WHERE h.user_id = p_user_id
    )
    SELECT STDDEV(daily_return * weight) INTO daily_volatility
    FROM portfolio_returns;
    
    -- VaR = Portfolio Value * 1.65 * Daily Volatility (95% confidence)
    UPDATE portfolio_analytics
    SET value_at_risk = portfolio_value * 1.65 * daily_volatility,
        calculated_at = NOW()
    WHERE user_id = p_user_id;
    
    SELECT value_at_risk FROM portfolio_analytics WHERE user_id = p_user_id;
END$$
 
-- Calculate Sharpe Ratio
CREATE PROCEDURE calc_sharpe(IN p_user_id INT)
BEGIN
    DECLARE portfolio_return DECIMAL(10, 6);
    DECLARE portfolio_std DECIMAL(10, 6);
    DECLARE risk_free_rate DECIMAL(6, 4) DEFAULT 0.04;  -- 4% annual
    DECLARE sharpe DECIMAL(6, 4);
    
    -- Calculate portfolio returns
    WITH daily_returns AS (
        SELECT 
            DATE(pc.timestamp) as date,
            SUM(h.quantity * pc.price) as portfolio_value
        FROM holdings h
        JOIN price_cache pc ON h.asset_symbol = pc.symbol
        WHERE h.user_id = p_user_id
        AND pc.timestamp >= DATE_SUB(NOW(), INTERVAL 365 DAY)
        GROUP BY DATE(pc.timestamp)
    ),
    returns AS (
        SELECT 
            (portfolio_value - LAG(portfolio_value) OVER (ORDER BY date)) / 
            LAG(portfolio_value) OVER (ORDER BY date) as daily_return
        FROM daily_returns
    )
    SELECT 
        AVG(daily_return) * 252 INTO portfolio_return,  -- Annualized
        STDDEV(daily_return) * SQRT(252) INTO portfolio_std  -- Annualized
    FROM returns;
    
    -- Sharpe = (Return - Risk Free Rate) / Std Dev
    SET sharpe = (portfolio_return - risk_free_rate) / NULLIF(portfolio_std, 0);
    
    UPDATE portfolio_analytics
    SET sharpe_ratio = sharpe,
        calculated_at = NOW()
    WHERE user_id = p_user_id;
    
    SELECT sharpe_ratio FROM portfolio_analytics WHERE user_id = p_user_id;
END$$
 
-- Calculate Maximum Drawdown
CREATE PROCEDURE calc_drawdown(IN p_user_id INT)
BEGIN
    DECLARE max_dd DECIMAL(6, 2);
    
    WITH daily_values AS (
        SELECT 
            DATE(pc.timestamp) as date,
            SUM(h.quantity * pc.price) as portfolio_value
        FROM holdings h
        JOIN price_cache pc ON h.asset_symbol = pc.symbol
        WHERE h.user_id = p_user_id
        AND pc.timestamp >= DATE_SUB(NOW(), INTERVAL 365 DAY)
        GROUP BY DATE(pc.timestamp)
    ),
    running_max AS (
        SELECT 
            date,
            portfolio_value,
            MAX(portfolio_value) OVER (ORDER BY date) as peak_value
        FROM daily_values
    ),
    drawdowns AS (
        SELECT 
            date,
            ((portfolio_value - peak_value) / peak_value) * 100 as drawdown
        FROM running_max
    )
    SELECT MIN(drawdown) INTO max_dd
    FROM drawdowns;
    
    UPDATE portfolio_analytics
    SET max_drawdown = max_dd,
        calculated_at = NOW()
    WHERE user_id = p_user_id;
    
    SELECT max_drawdown FROM portfolio_analytics WHERE user_id = p_user_id;
END$$
 
DELIMITER ;

# backend/services/price_service.py
import yfinance as yf
import requests
from typing import Dict, List
import pandas as pd
from datetime import datetime, timedelta
import mysql.connector
 
class PriceService:
    """
    Aggregate price data from multiple sources with intelligent caching
    
    Sources:
    - Yahoo Finance (stocks, ETFs)
    - CoinGecko (crypto, free tier)
    - Alpha Vantage (backup for stocks)
    
    Caching strategy:
    - Real-time prices: 1-minute cache
    - Historical prices: Permanent cache
    - Batch fetch to minimize API calls
    """
    
    def __init__(self, db_config: Dict):
        self.db = mysql.connector.connect(**db_config)
        self.coingecko_base = "https://api.coingecko.com/api/v3"
    
    def get_current_prices(
        self,
        symbols: List[str],
        asset_types: List[str]
    ) -> Dict[str, float]:
        """
        Get current prices for multiple symbols
        
        Args:
            symbols: ['AAPL', 'BTC', 'ETH']
            asset_types: ['stock', 'crypto', 'crypto']
        
        Returns:
            {'AAPL': 178.23, 'BTC': 43250.50, 'ETH': 2280.75}
        """
        prices = {}
        
        # Group by asset type for batch fetching
        stocks = [s for s, t in zip(symbols, asset_types) if t == 'stock']
        cryptos = [s for s, t in zip(symbols, asset_types) if t == 'crypto']
        
        # Fetch stocks (Yahoo Finance)
        if stocks:
            stock_prices = self._fetch_stock_prices(stocks)
            prices.update(stock_prices)
        
        # Fetch crypto (CoinGecko)
        if cryptos:
            crypto_prices = self._fetch_crypto_prices(cryptos)
            prices.update(crypto_prices)
        
        # Cache prices
        self._cache_prices(prices, asset_types)
        
        return prices
    
    def _fetch_stock_prices(self, symbols: List[str]) -> Dict[str, float]:
        """Fetch stock prices from Yahoo Finance"""
        # Check cache first (1-minute TTL)
        cached = self._get_cached_prices(symbols, 'stock', ttl_minutes=1)
        
        # Fetch missing symbols
        missing = [s for s in symbols if s not in cached]
        
        if not missing:
            return cached
        
        try:
            # Batch fetch with yfinance
            tickers = yf.Tickers(' '.join(missing))
            
            for symbol in missing:
                try:
                    ticker = tickers.tickers[symbol]
                    price = ticker.fast_info['lastPrice']
                    cached[symbol] = price
                except Exception as e:
                    print(f"Error fetching {symbol}: {e}")
                    cached[symbol] = 0.0
        
        except Exception as e:
            print(f"Batch fetch error: {e}")
        
        return cached
    
    def _fetch_crypto_prices(self, symbols: List[str]) -> Dict[str, float]:
        """Fetch crypto prices from CoinGecko"""
        # Check cache first
        cached = self._get_cached_prices(symbols, 'crypto', ttl_minutes=1)
        
        # Fetch missing symbols
        missing = [s for s in symbols if s not in cached]
        
        if not missing:
            return cached
        
        try:
            # Map symbols to CoinGecko IDs
            coin_map = {
                'BTC': 'bitcoin',
                'ETH': 'ethereum',
                'SOL': 'solana',
                'MATIC': 'matic-network',
                'AVAX': 'avalanche-2',
                # Add more mappings
            }
            
            coin_ids = [coin_map.get(s, s.lower()) for s in missing]
            
            # Batch API call
            response = requests.get(
                f"{self.coingecko_base}/simple/price",
                params={
                    'ids': ','.join(coin_ids),
                    'vs_currencies': 'usd'
                },
                timeout=5
            )
            
            data = response.json()
            
            for symbol, coin_id in zip(missing, coin_ids):
                if coin_id in data:
                    cached[symbol] = data[coin_id]['usd']
                else:
                    cached[symbol] = 0.0
        
        except Exception as e:
            print(f"CoinGecko API error: {e}")
        
        return cached
    
    def _get_cached_prices(
        self,
        symbols: List[str],
        asset_type: str,
        ttl_minutes: int = 1
    ) -> Dict[str, float]:
        """Get prices from cache if fresh enough"""
        cursor = self.db.cursor(dictionary=True)
        
        cutoff_time = datetime.now() - timedelta(minutes=ttl_minutes)
        
        placeholders = ','.join(['%s'] * len(symbols))
        
        cursor.execute(f"""
            SELECT symbol, price
            FROM price_cache
            WHERE symbol IN ({placeholders})
            AND asset_type = %s
            AND timestamp >= %s
        """, (*symbols, asset_type, cutoff_time))
        
        cached = {row['symbol']: float(row['price']) for row in cursor.fetchall()}
        cursor.close()
        
        return cached
    
    def _cache_prices(
        self,
        prices: Dict[str, float],
        asset_types: List[str]
    ):
        """Store prices in cache"""
        cursor = self.db.cursor()
        
        timestamp = datetime.now()
        
        for symbol, price in prices.items():
            asset_type = 'crypto' if symbol in ['BTC', 'ETH', 'SOL'] else 'stock'
            
            cursor.execute("""
                INSERT INTO price_cache (symbol, asset_type, price, timestamp, source)
                VALUES (%s, %s, %s, %s, %s)
                ON DUPLICATE KEY UPDATE price = VALUES(price)
            """, (symbol, asset_type, price, timestamp, 'api'))
        
        self.db.commit()
        cursor.close()
    
    def get_historical_prices(
        self,
        symbol: str,
        asset_type: str,
        start_date: str,
        end_date: str
    ) -> pd.DataFrame:
        """
        Get historical price data for backtesting/analytics
        
        Returns:
            DataFrame with columns: date, open, high, low, close, volume
        """
        # Check cache first
        cached_df = self._get_cached_historical(symbol, start_date, end_date)
        
        if cached_df is not None and len(cached_df) > 0:
            return cached_df
        
        # Fetch from API
        if asset_type == 'stock':
            df = self._fetch_stock_history(symbol, start_date, end_date)
        else:
            df = self._fetch_crypto_history(symbol, start_date, end_date)
        
        # Cache for future use
        self._cache_historical(symbol, asset_type, df)
        
        return df
    
    def _fetch_stock_history(
        self,
        symbol: str,
        start_date: str,
        end_date: str
    ) -> pd.DataFrame:
        """Fetch stock historical data from Yahoo Finance"""
        ticker = yf.Ticker(symbol)
        df = ticker.history(start=start_date, end=end_date)
        df = df.reset_index()
        df.columns = [c.lower() for c in df.columns]
        return df
    
    def _fetch_crypto_history(
        self,
        symbol: str,
        start_date: str,
        end_date: str
    ) -> pd.DataFrame:
        """Fetch crypto historical data from CoinGecko"""
        coin_map = {'BTC': 'bitcoin', 'ETH': 'ethereum', 'SOL': 'solana'}
        coin_id = coin_map.get(symbol, symbol.lower())
        
        # Convert dates to timestamps
        start_ts = int(pd.Timestamp(start_date).timestamp())
        end_ts = int(pd.Timestamp(end_date).timestamp())
        
        response = requests.get(
            f"{self.coingecko_base}/coins/{coin_id}/market_chart/range",
            params={
                'vs_currency': 'usd',
                'from': start_ts,
                'to': end_ts
            }
        )
        
        data = response.json()
        
        # Convert to DataFrame
        prices = data.get('prices', [])
        df = pd.DataFrame(prices, columns=['timestamp', 'close'])
        df['date'] = pd.to_datetime(df['timestamp'], unit='ms')
        
        return df[['date', 'close']]

# backend/services/analytics_service.py
import pandas as pd
import numpy as np
from typing import Dict, List
from scipy import stats
 
class PortfolioAnalytics:
    """
    Calculate advanced portfolio metrics
    
    Metrics:
    - Total return (absolute and percentage)
    - Sharpe ratio (risk-adjusted return)
    - Beta (correlation with market)
    - Alpha (excess return over market)
    - Value at Risk (VaR)
    - Maximum Drawdown
    - Correlation matrix
    """
    
    def __init__(self, db_config: Dict, price_service: PriceService):
        self.db = mysql.connector.connect(**db_config)
        self.price_service = price_service
    
    def calculate_all_metrics(self, user_id: int) -> Dict:
        """Calculate all portfolio metrics"""
        # Get user holdings
        holdings = self._get_holdings(user_id)
        
        if not holdings:
            return {}
        
        # Get current prices
        symbols = [h['asset_symbol'] for h in holdings]
        asset_types = [h['asset_type'] for h in holdings]
        prices = self.price_service.get_current_prices(symbols, asset_types)
        
        # Calculate basic metrics
        total_value = sum(h['quantity'] * prices.get(h['asset_symbol'], 0) 
                         for h in holdings)
        total_cost = sum(h['quantity'] * h.get('cost_basis', 0) 
                        for h in holdings)
        total_return = total_value - total_cost
        return_pct = (total_return / total_cost * 100) if total_cost > 0 else 0
        
        # Get historical data for advanced metrics
        historical_data = self._get_portfolio_history(user_id, days=365)
        
        # Calculate advanced metrics
        sharpe = self._calculate_sharpe_ratio(historical_data)
        beta, alpha = self._calculate_beta_alpha(historical_data)
        var = self._calculate_var(historical_data)
        max_dd = self._calculate_max_drawdown(historical_data)
        
        metrics = {
            'total_value': round(total_value, 2),
            'total_cost_basis': round(total_cost, 2),
            'total_return': round(total_return, 2),
            'return_percentage': round(return_pct, 2),
            'sharpe_ratio': round(sharpe, 4),
            'beta': round(beta, 4),
            'alpha': round(alpha, 4),
            'value_at_risk': round(var, 2),
            'max_drawdown': round(max_dd, 2)
        }
        
        # Save to database
        self._save_analytics(user_id, metrics)
        
        return metrics
    
    def _calculate_sharpe_ratio(
        self,
        portfolio_history: pd.DataFrame,
        risk_free_rate: float = 0.04
    ) -> float:
        """
        Calculate Sharpe ratio
        
        Sharpe = (Portfolio Return - Risk Free Rate) / Portfolio Std Dev
        """
        if len(portfolio_history) < 30:
            return 0.0
        
        # Calculate daily returns
        portfolio_history['daily_return'] = portfolio_history['value'].pct_change()
        
        # Annualized return and std
        annual_return = portfolio_history['daily_return'].mean() * 252
        annual_std = portfolio_history['daily_return'].std() * np.sqrt(252)
        
        if annual_std == 0:
            return 0.0
        
        sharpe = (annual_return - risk_free_rate) / annual_std
        
        return sharpe
    
    def _calculate_beta_alpha(
        self,
        portfolio_history: pd.DataFrame,
        benchmark: str = 'SPY'  # S&P 500 ETF
    ) -> tuple:
        """
        Calculate beta (market correlation) and alpha (excess return)
        
        Beta = Cov(portfolio, market) / Var(market)
        Alpha = Portfolio Return - (Risk Free + Beta * (Market Return - Risk Free))
        """
        if len(portfolio_history) < 30:
            return 0.0, 0.0
        
        # Get benchmark (S&P 500) historical data
        benchmark_data = self.price_service.get_historical_prices(
            benchmark,
            'stock',
            portfolio_history['date'].min().strftime('%Y-%m-%d'),
            portfolio_history['date'].max().strftime('%Y-%m-%d')
        )
        
        # Merge on date
        merged = pd.merge(
            portfolio_history,
            benchmark_data[['date', 'close']],
            on='date',
            how='inner'
        )
        
        # Calculate returns
        merged['portfolio_return'] = merged['value'].pct_change()
        merged['benchmark_return'] = merged['close'].pct_change()
        
        # Drop NaN
        merged = merged.dropna()
        
        if len(merged) < 20:
            return 0.0, 0.0
        
        # Calculate beta using linear regression
        slope, intercept, r_value, p_value, std_err = stats.linregress(
            merged['benchmark_return'],
            merged['portfolio_return']
        )
        
        beta = slope
        
        # Calculate alpha
        portfolio_return_annual = merged['portfolio_return'].mean() * 252
        benchmark_return_annual = merged['benchmark_return'].mean() * 252
        risk_free_rate = 0.04
        
        alpha = portfolio_return_annual - (risk_free_rate + beta * (benchmark_return_annual - risk_free_rate))
        
        return beta, alpha
    
    def _calculate_var(
        self,
        portfolio_history: pd.DataFrame,
        confidence: float = 0.95,
        horizon_days: int = 1
    ) -> float:
        """
        Calculate Value at Risk (VaR)
        
        VaR = Amount you could lose with X% confidence over N days
        """
        if len(portfolio_history) < 30:
            return 0.0
        
        # Calculate daily returns
        portfolio_history['daily_return'] = portfolio_history['value'].pct_change()
        
        # Get current portfolio value
        current_value = portfolio_history['value'].iloc[-1]
        
        # Calculate VaR using historical simulation
        returns = portfolio_history['daily_return'].dropna()
        
        # Find percentile
        var_percentile = np.percentile(returns, (1 - confidence) * 100)
        
        # Scale by current value and horizon
        var = current_value * var_percentile * np.sqrt(horizon_days)
        
        return abs(var)
    
    def _calculate_max_drawdown(self, portfolio_history: pd.DataFrame) -> float:
        """
        Calculate maximum drawdown (peak-to-trough decline)
        
        Max DD = (Trough Value - Peak Value) / Peak Value
        """
        if len(portfolio_history) < 2:
            return 0.0
        
        # Calculate running maximum
        running_max = portfolio_history['value'].expanding().max()
        
        # Calculate drawdown at each point
        drawdown = (portfolio_history['value'] - running_max) / running_max * 100
        
        # Return maximum (most negative)
        max_drawdown = drawdown.min()
        
        return max_drawdown
    
    def _get_portfolio_history(self, user_id: int, days: int = 365) -> pd.DataFrame:
        """Get portfolio value over time"""
        cursor = self.db.cursor(dictionary=True)
        
        cursor.execute("""
            SELECT 
                DATE(pc.timestamp) as date,
                SUM(h.quantity * pc.price) as value
            FROM holdings h
            JOIN price_cache pc ON h.asset_symbol = pc.symbol
            WHERE h.user_id = %s
            AND pc.timestamp >= DATE_SUB(NOW(), INTERVAL %s DAY)
            GROUP BY DATE(pc.timestamp)
            ORDER BY date
        """, (user_id, days))
        
        df = pd.DataFrame(cursor.fetchall())
        cursor.close()
        
        return df

# backend/routes/upload.py
from flask import Blueprint, request, jsonify
import pandas as pd
import io
 
upload_bp = Blueprint('upload', __name__)
 
@upload_bp.route('/upload/holdings', methods=['POST'])
def upload_holdings():
    """
    Upload portfolio holdings via CSV
    
    CSV format:
    symbol,quantity,asset_type,cost_basis,purchase_date
    AAPL,10,stock,150.50,2024-01-15
    BTC,0.5,crypto,42000,2024-02-01
    """
    try:
        # Check file
        if 'file' not in request.files:
            return jsonify({'error': 'No file uploaded'}), 400
        
        file = request.files['file']
        user_id = request.form.get('user_id')
        
        if not user_id:
            return jsonify({'error': 'user_id required'}), 400
        
        # Read CSV
        csv_data = file.read().decode('utf-8')
        df = pd.read_csv(io.StringIO(csv_data))
        
        # Validate columns
        required_cols = ['symbol', 'quantity', 'asset_type']
        if not all(col in df.columns for col in required_cols):
            return jsonify({'error': f'Missing required columns: {required_cols}'}), 400
        
        # Validate data
        valid_asset_types = ['stock', 'crypto']
        if not df['asset_type'].isin(valid_asset_types).all():
            return jsonify({'error': f'asset_type must be one of: {valid_asset_types}'}), 400
        
        # Insert holdings
        cursor = db.cursor()
        
        inserted = 0
        for _, row in df.iterrows():
            try:
                cursor.execute("""
                    INSERT INTO holdings (
                        user_id, asset_symbol, quantity, asset_type, 
                        cost_basis, purchase_date, source
                    )
                    VALUES (%s, %s, %s, %s, %s, %s, 'csv_upload')
                    ON DUPLICATE KEY UPDATE
                        quantity = VALUES(quantity),
                        cost_basis = VALUES(cost_basis)
                """, (
                    user_id,
                    row['symbol'],
                    row['quantity'],
                    row['asset_type'],
                    row.get('cost_basis', None),
                    row.get('purchase_date', None)
                ))
                inserted += 1
            except Exception as e:
                print(f"Error inserting row: {e}")
        
        db.commit()
        cursor.close()
        
        # Trigger analytics refresh
        analytics_service.calculate_all_metrics(int(user_id))
        
        return jsonify({
            'success': True,
            'inserted': inserted,
            'message': f'Successfully uploaded {inserted} holdings'
        })
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500
 
@upload_bp.route('/upload/trades', methods=['POST'])
def upload_trades():
    """
    Upload trade history via CSV
    
    CSV format:
    symbol,type,quantity,price,fees,date
    AAPL,buy,10,150.50,0.50,2024-01-15
    BTC,sell,0.2,43000,5.00,2024-02-20
    """
    try:
        file = request.files['file']
        user_id = request.form.get('user_id')
        
        csv_data = file.read().decode('utf-8')
        df = pd.read_csv(io.StringIO(csv_data))
        
        # Insert transactions
        cursor = db.cursor()
        
        for _, row in df.iterrows():
            asset_type = 'crypto' if row['symbol'] in ['BTC', 'ETH', 'SOL'] else 'stock'
            
            cursor.execute("""
                INSERT INTO transactions (
                    user_id, asset_symbol, asset_type, transaction_type,
                    quantity, price, total_value, fees, transaction_date
                )
                VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
            """, (
                user_id,
                row['symbol'],
                asset_type,
                row['type'],
                row['quantity'],
                row['price'],
                row['quantity'] * row['price'],
                row.get('fees', 0),
                row['date']
            ))
        
        db.commit()
        cursor.close()
        
        return jsonify({
            'success': True,
            'message': f'Uploaded {len(df)} trades'
        })
    
    except Exception as e:
        return jsonify({'error': str(e)}), 500

// frontend/Dashboard.tsx
import React, { useState, useEffect } from 'react';
import { Line, Pie, Bar } from 'react-chartjs-2';
import axios from 'axios';
import { DataGrid } from '@mui/x-data-grid';
 
interface Holding {
  id: number;
  symbol: string;
  quantity: number;
  assetType: 'stock' | 'crypto';
  costBasis: number;
  currentPrice: number;
  currentValue: number;
  return: number;
  returnPct: number;
}
 
interface Analytics {
  totalValue: number;
  totalReturn: number;
  returnPercentage: number;
  sharpeRatio: number;
  beta: number;
  alpha: number;
  valueAtRisk: number;
  maxDrawdown: number;
}
 
export const PortfolioDashboard: React.FC<{ userId: number }> = ({ userId }) => {
  const [holdings, setHoldings] = useState<Holding[]>([]);
  const [analytics, setAnalytics] = useState<Analytics | null>(null);
  const [historicalData, setHistoricalData] = useState<any[]>([]);
  const [loading, setLoading] = useState(true);
 
  useEffect(() => {
    loadDashboard();
    
    // Refresh every 60 seconds
    const interval = setInterval(loadDashboard, 60000);
    return () => clearInterval(interval);
  }, [userId]);
 
  const loadDashboard = async () => {
    try {
      const [holdingsRes, analyticsRes, historyRes] = await Promise.all([
        axios.get(`/api/holdings/${userId}`),
        axios.get(`/api/analytics/${userId}`),
        axios.get(`/api/portfolio/history/${userId}?days=365`)
      ]);
 
      setHoldings(holdingsRes.data);
      setAnalytics(analyticsRes.data);
      setHistoricalData(historyRes.data);
    } catch (error) {
      console.error('Failed to load dashboard:', error);
    } finally {
      setLoading(false);
    }
  };
 
  const handleCellEdit = async (params: any) => {
    // Update holding in real-time
    try {
      await axios.put(`/api/holdings/${params.id}`, {
        quantity: params.value
      });
      
      // Refresh analytics
      loadDashboard();
    } catch (error) {
      console.error('Failed to update holding:', error);
    }
  };
 
  if (loading || !analytics) {
    return <div>Loading portfolio...</div>;
  }
 
  // Prepare data for charts
  const allocationData = {
    labels: holdings.map(h => h.symbol),
    datasets: [{
      data: holdings.map(h => h.currentValue),
      backgroundColor: holdings.map((_, i) => 
        `hsl(${(i * 360) / holdings.length}, 70%, 60%)`
      )
    }]
  };
 
  const performanceData = {
    labels: historicalData.map(d => new Date(d.date).toLocaleDateString()),
    datasets: [{
      label: 'Portfolio Value',
      data: historicalData.map(d => d.value),
      borderColor: 'rgb(75, 192, 192)',
      backgroundColor: 'rgba(75, 192, 192, 0.2)',
      tension: 0.4
    }]
  };
 
  return (
    <div className="dashboard p-8">
      <h1 className="text-3xl font-bold mb-8">quantDesk Portfolio</h1>
 
      {/* Summary Cards */}
      <div className="grid grid-cols-4 gap-6 mb-8">
        <div className="bg-white rounded-lg shadow p-6">
          <p className="text-gray-600 text-sm mb-2">Total Value</p>
          <p className="text-3xl font-bold text-green-600">
            ${analytics.totalValue.toLocaleString()}
          </p>
        </div>
 
        <div className="bg-white rounded-lg shadow p-6">
          <p className="text-gray-600 text-sm mb-2">Total Return</p>
          <p className={`text-3xl font-bold ${analytics.totalReturn >= 0 ? 'text-green-600' : 'text-red-600'}`}>
            {analytics.totalReturn >= 0 ? '+' : ''}${analytics.totalReturn.toLocaleString()}
            <span className="text-lg ml-2">({analytics.returnPercentage.toFixed(2)}%)</span>
          </p>
        </div>
 
        <div className="bg-white rounded-lg shadow p-6">
          <p className="text-gray-600 text-sm mb-2">Sharpe Ratio</p>
          <p className="text-3xl font-bold text-blue-600">
            {analytics.sharpeRatio.toFixed(2)}
          </p>
          <p className="text-xs text-gray-500 mt-1">
            {analytics.sharpeRatio > 1 ? 'Excellent' : analytics.sharpeRatio > 0.5 ? 'Good' : 'Poor'}
          </p>
        </div>
 
        <div className="bg-white rounded-lg shadow p-6">
          <p className="text-gray-600 text-sm mb-2">Value at Risk (95%)</p>
          <p className="text-3xl font-bold text-red-600">
            ${analytics.valueAtRisk.toLocaleString()}
          </p>
          <p className="text-xs text-gray-500 mt-1">1-day horizon</p>
        </div>
      </div>
 
      {/* Charts */}
      <div className="grid grid-cols-2 gap-6 mb-8">
        {/* Allocation Pie Chart */}
        <div className="bg-white rounded-lg shadow p-6">
          <h2 className="text-xl font-semibold mb-4">Asset Allocation</h2>
          <Pie data={allocationData} />
        </div>
 
        {/* Performance Line Chart */}
        <div className="bg-white rounded-lg shadow p-6">
          <h2 className="text-xl font-semibold mb-4">Portfolio Performance</h2>
          <Line 
            data={performanceData}
            options={{
              responsive: true,
              plugins: {
                legend: { display: false }
              },
              scales: {
                y: {
                  ticks: {
                    callback: (value) => `$${value.toLocaleString()}`
                  }
                }
              }
            }}
          />
        </div>
      </div>
 
      {/* Holdings Grid */}
      <div className="bg-white rounded-lg shadow p-6">
        <h2 className="text-xl font-semibold mb-4">Holdings</h2>
        <DataGrid
          rows={holdings}
          columns={[
            { field: 'symbol', headerName: 'Symbol', width: 100 },
            { field: 'assetType', headerName: 'Type', width: 100 },
            { 
              field: 'quantity', 
              headerName: 'Quantity', 
              width: 120,
              editable: true,
              type: 'number'
            },
            { 
              field: 'currentPrice', 
              headerName: 'Price', 
              width: 120,
              valueFormatter: (params) => `$${params.value.toFixed(2)}`
            },
            { 
              field: 'currentValue', 
              headerName: 'Value', 
              width: 140,
              valueFormatter: (params) => `$${params.value.toLocaleString()}`
            },
            { 
              field: 'return', 
              headerName: 'Return', 
              width: 140,
              valueFormatter: (params) => `$${params.value.toLocaleString()}`,
              cellClassName: (params) => params.value >= 0 ? 'text-green-600' : 'text-red-600'
            },
            { 
              field: 'returnPct', 
              headerName: 'Return %', 
              width: 120,
              valueFormatter: (params) => `${params.value.toFixed(2)}%`,
              cellClassName: (params) => params.value >= 0 ? 'text-green-600' : 'text-red-600'
            }
          ]}
          pageSize={20}
          checkboxSelection
          disableSelectionOnClick
          onCellEditCommit={handleCellEdit}
          autoHeight
        />
      </div>
 
      {/* Advanced Metrics */}
      <div className="bg-white rounded-lg shadow p-6 mt-6">
        <h2 className="text-xl font-semibold mb-4">Risk Metrics</h2>
        <div className="grid grid-cols-3 gap-6">
          <div>
            <p className="text-gray-600 text-sm mb-1">Beta (vs S&P 500)</p>
            <p className="text-2xl font-bold">{analytics.beta.toFixed(3)}</p>
            <p className="text-xs text-gray-500 mt-1">
              {analytics.beta > 1 ? 'More volatile than market' : 'Less volatile than market'}
            </p>
          </div>
 
          <div>
            <p className="text-gray-600 text-sm mb-1">Alpha (Excess Return)</p>
            <p className={`text-2xl font-bold ${analytics.alpha >= 0 ? 'text-green-600' : 'text-red-600'}`}>
              {(analytics.alpha * 100).toFixed(2)}%
            </p>
            <p className="text-xs text-gray-500 mt-1">
              {analytics.alpha > 0 ? 'Outperforming market' : 'Underperforming market'}
            </p>
          </div>
 
          <div>
            <p className="text-gray-600 text-sm mb-1">Max Drawdown</p>
            <p className="text-2xl font-bold text-red-600">
              {analytics.maxDrawdown.toFixed(2)}%
            </p>
            <p className="text-xs text-gray-500 mt-1">Largest peak-to-trough decline</p>
          </div>
        </div>
      </div>
    </div>
  );
};

// Update UI immediately
setHoldings(prev => prev.map(h => 
  h.id === editedId ? { ...h, quantity: newQuantity } : h
));
 
// Recalculate in background
recalculateAnalytics(userId).then(newAnalytics => {
  setAnalytics(newAnalytics);
});

# Integrate with Plaid for multi-broker support
from plaid import Client
 
plaid_client = Client(
    client_id=os.getenv('PLAID_CLIENT_ID'),
    secret=os.getenv('PLAID_SECRET'),
    environment='production'
)
 
# Link user's brokerage account
access_token = plaid_client.exchange_public_token(public_token)
 
# Fetch holdings
holdings = plaid_client.investments.holdings.get(access_token)

# Analyze portfolio drift from target allocation
target_allocation = {'stocks': 0.6, 'crypto': 0.3, 'bonds': 0.1}
current_allocation = calculate_current_allocation(holdings)
 
# Suggest trades to rebalance
suggestions = []
for asset_type, target_pct in target_allocation.items():
    current_pct = current_allocation[asset_type]
    drift = abs(current_pct - target_pct)
    
    if drift > 0.05:  # 5% threshold
        suggestions.append({
            'action': 'buy' if current_pct < target_pct else 'sell',
            'asset_type': asset_type,
            'amount': drift * total_portfolio_value
        })

def find_tax_loss_harvest_opportunities(holdings):
    """Find holdings with losses that can offset gains"""
    opportunities = []
    
    for holding in holdings:
        unrealized_loss = holding['cost_basis'] - holding['current_value']
        
        if unrealized_loss > 100:  # $100 minimum
            opportunities.append({
                'symbol': holding['symbol'],
                'loss': unrealized_loss,
                'recommendation': f"Sell to harvest ${unrealized_loss:.2f} loss"
            })
    
    return opportunities

from scipy.optimize import minimize
import cvxpy as cp
 
def optimize_portfolio(returns, target_return=0.10):
    """
    Find optimal weights to minimize risk for target return
    
    Uses Markowitz Mean-Variance Optimization
    """
    n_assets = len(returns.columns)
    
    # Calculate expected returns and covariance
    mu = returns.mean() * 252  # Annualized
    Sigma = returns.cov() * 252
    
    # Define optimization problem
    w = cp.Variable(n_assets)
    ret = mu.T @ w
    risk = cp.quad_form(w, Sigma)
    
    # Constraints
    constraints = [
        cp.sum(w) == 1,  # Weights sum to 1
        w >= 0,  # No short selling
        ret >= target_return  # Meet target return
    ]
    
    # Minimize risk
    problem = cp.Problem(cp.Minimize(risk), constraints)
    problem.solve()
    
    return w.value