Untitled
unknown
plain_text
a month ago
28 kB
5
Indexable
import pandas as pd import numpy as np from datetime import datetime from dateutil.relativedelta import relativedelta from scipy.stats import zscore ############################################### # 1. Data Loading (Filtering Out Weekends) ############################################### def load_price_data(filepath): """ Load historical prices from an Excel file. Assumes that the first column is dates and the remaining columns are tickers. Removes weekend data. """ df = pd.read_excel(filepath, index_col=0) df.index = pd.to_datetime(df.index) df = df.sort_index() # ensure sorted index for asof() # Filter out weekends (Saturday=5, Sunday=6) df = df[df.index.dayofweek < 5] return df def load_macro_data(filepath): """ Load macro indicators from an Excel file. Loads VIX and VIX3M from 'Eq' sheet, LF98TRUU Index from 'FI' sheet, and other macro indicators from 'Macro' sheet. Removes weekend data. Also computes slopes for selected macro indicators. """ # VIX data vix_data = pd.read_excel(filepath, sheet_name='Eq', index_col=0, parse_dates=True, usecols=[0, 3, 4, 5]) vix_data.columns = ['VIX9D', 'VIX', 'VIX3M'] vix_data = vix_data[vix_data.index.dayofweek < 5] # FI data cdx_data = pd.read_excel(filepath, sheet_name='FI', index_col=0, parse_dates=True, usecols=[0, 2], skiprows=1) cdx_data.columns = ['LF98TRUU'] cdx_data = cdx_data[cdx_data.index.dayofweek < 5] # Macro data (assumed to include columns "CESIUSD Index", "INJCJC Index", ".HG/GC G Index", and "Consumer Confidence") macro_data = pd.read_excel(filepath, sheet_name='Macro', index_col=0, parse_dates=True, usecols=range(8), skiprows=1) macro_data = macro_data[macro_data.index.dayofweek < 5] # Compute slopes for selected macro indicators. macro_data["Surprise Index Slope"] = macro_data["CESIUSD Index"].diff() macro_data["Jobless Claims Slope"] = macro_data["INJCJC Index"].diff() macro_data["Copper Gold Slope"] = macro_data['.HG/GC G Index'].diff() # Assume "Consumer Confidence" column already exists. combined_data = pd.concat([vix_data, cdx_data, macro_data], axis=1) combined_data = combined_data.fillna(method='ffill').fillna(method='bfill') combined_data = combined_data.sort_index() # ensure sorted index return combined_data ############################################### # 2. Helper: Observation Dates (Monthly) ############################################### def get_observation_dates(start_date, end_date, rebalance_period): """ Returns a list of observation dates from start_date to end_date with a step equal to rebalance_period (in months). """ dates = [] current = start_date while current <= end_date: dates.append(current) current += relativedelta(months=rebalance_period) return dates ############################################### # 3. Portfolio Initialization ############################################### def initialize_portfolio(prices, date, tickers, initial_aum): """ On the start date, invest equal notional amounts in each asset. Uses asof() to retrieve the price on or before the date. Returns a dictionary mapping ticker -> quantity. """ prices = prices.sort_index() portfolio = {} allocation = initial_aum / len(tickers) for ticker in tickers: price = prices[ticker].asof(date) if pd.isna(price): raise ValueError(f"No price available for {ticker} as of {date}") portfolio[ticker] = allocation / price return portfolio ############################################### # 4. Lookback Metric Computation ############################################### def compute_lookback_metric(prices, current_date, ticker, lookback_period, metric_type='simple'): """ Computes the lookback metric for one ticker using previous day's data. """ prices = prices.sort_index() # Get previous trading day's date prev_date = prices.index[prices.index < current_date][-1] lookback_date = prev_date - relativedelta(months=lookback_period) current_price = prices[ticker].asof(prev_date) # Use previous day's price lookback_price = prices[ticker].asof(lookback_date) if pd.isna(current_price) or pd.isna(lookback_price): raise ValueError(f"Missing price data for {ticker} on {prev_date} or {lookback_date}.") if metric_type == 'simple': metric = (current_price / lookback_price) - 1 elif metric_type == 'sma': window = prices[ticker].loc[lookback_date:current_date] if window.empty: raise ValueError(f"No price data for {ticker} between {lookback_date} and {current_date}.") sma = window.mean() metric = (current_price - sma) / sma else: raise ValueError("Invalid metric type. Choose 'simple' or 'sma'.") return metric ############################################### # 5. Ranking Assets by Momentum ############################################### def rank_assets(prices, current_date, tickers, lookback_period, metric_type): """ For a given observation date, compute the lookback metric for each asset, then sort in descending order so that the highest momentum gets rank 1. Returns: sorted_tickers: list of tickers sorted best-first, ranks: dictionary mapping ticker -> rank (1 is best), metrics: dictionary mapping ticker -> computed metric value. """ metrics = {} for ticker in tickers: metric = compute_lookback_metric(prices, current_date, ticker, lookback_period, metric_type) metrics[ticker] = metric sorted_tickers = sorted(metrics, key=metrics.get, reverse=True) ranks = {ticker: rank+1 for rank, ticker in enumerate(sorted_tickers)} return sorted_tickers, ranks, metrics ############################################### # 6. Compute Current Portfolio Value ############################################### def compute_portfolio_value(portfolio, prices, current_date): """ Returns the portfolio AUM as of current_date. """ value = 0 for ticker, quantity in portfolio.items(): price = prices.loc[current_date, ticker] value += quantity * price return value ############################################### # 7. Rebalance the Momentum Portfolio ############################################### def rebalance_portfolio(portfolio, prices, current_date, tickers, sorted_tickers, internal_rebalance_ratios, rebalance_ratio): """ Rebalances the momentum portfolio (excluding CASH adjustments). Returns updated portfolio, trades executed, and portfolio value before cash adjustment. """ portfolio_value = compute_portfolio_value(portfolio, prices, current_date) rebalance_amount = portfolio_value * rebalance_ratio target_trades = {ticker: rebalance_amount * internal_rebalance_ratios[i] for i, ticker in enumerate(sorted_tickers)} total_sold = 0 actual_trades = {} for ticker, target_trade in target_trades.items(): price = prices.loc[current_date, ticker] if target_trade < 0: available_notional = portfolio[ticker] * price sell_target = abs(target_trade) actual_sell = min(available_notional, sell_target) actual_trades[ticker] = -actual_sell total_sold += actual_sell else: actual_trades[ticker] = 0 total_buy_target = sum(trade for trade in target_trades.values() if trade > 0) if total_buy_target > 0: for ticker, target_trade in target_trades.items(): if target_trade > 0: proportion = target_trade / total_buy_target buy_amount = total_sold * proportion actual_trades[ticker] = buy_amount new_portfolio = portfolio.copy() for ticker, trade_notional in actual_trades.items(): price = prices.loc[current_date, ticker] qty_change = trade_notional / price new_portfolio[ticker] += qty_change return new_portfolio, actual_trades, portfolio_value ############################################### # 8. VIX-based Allocation Function ############################################### def momentum_allocation(vix_9d, vix_mean, vix_std, max_alloc=1.0, min_alloc=0.6): """ Maps composite score to a target momentum allocation fraction using a piecewise linear approach. - If vix_9d < vix_mean + vix_std, return max_alloc (fully risk on). - If vix_9d >= vix_mean + 2*vix_std, return min_alloc (fully risk off). - Otherwise, return 0.8 as an intermediate allocation. """ if vix_9d < vix_mean + vix_std: return max_alloc elif (vix_9d >= vix_mean + vix_std) and (vix_9d < vix_mean + 2*vix_std): return 0.8 else: return min_alloc ############################################### # 9. Composite Macro and FI Signal Functions ############################################### def compute_composite_series(macro_data, window): """ Computes the composite macro index for each date in the last 'window' days. It uses the same logic as compute_macro_composite (which uses min-max scaling on selected indicators over the same rolling window), but here we do it for every date in the window. Assumes that macro_data contains the required columns: "CESIUSD Index", "INJCJC Index", ".HG/GC G Index" Returns: A pandas Series indexed by date containing the composite index. """ composites = {} # Use the last 'window' dates from macro_data. sub = macro_data.loc[:macro_data.index[-1]].tail(window) for dt in sub.index: # For each date dt, we use the sub-data up to dt window_data = macro_data.loc[:dt].tail(window) # For each indicator, compute min and max over the window. comp_vals = {} for col in ["CESIUSD Index", "INJCJC Index", ".HG/GC G Index"]: if col not in window_data.columns: comp_vals[col] = 0.5 else: min_val = window_data[col].min() max_val = window_data[col].max() # Avoid division by zero. if max_val - min_val != 0: comp_vals[col] = (window_data[col].iloc[-1] - min_val) / (max_val - min_val) else: comp_vals[col] = 0.5 # For Jobless Claims, lower is better, so invert. comp_vals["INJCJC Index"] = 1 - comp_vals["INJCJC Index"] # Composite is the equal weight average. composite = np.mean([comp_vals["CESIUSD Index"], comp_vals["INJCJC Index"], comp_vals[".HG/GC G Index"]]) composites[dt] = composite return pd.Series(composites) def macro_signal_allocation_std(macro_data, current_date, macro_max_alloc, macro_min_alloc, rolling_window=252): """ Computes the composite macro index over a rolling window and then assigns a target allocation based on how far the current composite is above the rolling mean. The computation uses data only up to the previous trading day to avoid lookahead bias. Returns: target_alloc: the target allocation. signal: a string label ("risk-on" or "risk-off"). """ # Determine the last available trading day before current_date. available_dates = macro_data.index[macro_data.index < current_date] if len(available_dates) == 0: return macro_max_alloc, "risk-on" ref_date = available_dates[-1] # Compute composite series for the rolling window. comp_series = compute_composite_series(macro_data, rolling_window) mu = comp_series.mean() sigma = comp_series.std() # Use ref_date to get the composite value. current_composite = comp_series.asof(ref_date) if pd.isna(current_composite): return macro_max_alloc, "risk-on" if current_composite < mu + sigma: target_alloc = macro_max_alloc signal = "risk-on" elif current_composite < mu + 2*sigma: target_alloc = (macro_max_alloc + macro_min_alloc) / 2 signal = "risk-off" else: target_alloc = macro_min_alloc signal = "risk-off" return target_alloc, signal def compute_fi_target_allocation(macro_data, current_date, fi_max_alloc, fi_min_alloc, slope_threshold=0.01, tol=0.001): """ Computes the FI target allocation based on a refined logic using only the 8-day and 13-day EMAs. The computation uses data only up to the previous trading day. Returns: target_alloc: the target allocation. signal_label: a string label ("risk-on", "neutral", or "risk-off"). """ # Determine the reference date as the previous trading day. available_dates = macro_data.index[macro_data.index < current_date] if len(available_dates) == 0: return fi_max_alloc, "risk-on" ref_date = available_dates[-1] fi_8 = macro_data["FI_EMA_8"].asof(ref_date) fi_13 = macro_data["FI_EMA_13"].asof(ref_date) if pd.isna(fi_8) or pd.isna(fi_13): return fi_max_alloc, "risk-on" # Compute slope of FI_EMA_8 using ref_date. available_ref_dates = macro_data.loc[:ref_date].index if len(available_ref_dates) < 2: slope = 0 else: prev_ref_date = available_ref_dates[-2] fi_8_prev = macro_data["FI_EMA_8"].asof(prev_ref_date) slope = fi_8 - fi_8_prev if fi_8 < fi_13: return fi_max_alloc, "risk-on" elif abs(fi_8 - fi_13) < tol: return (fi_max_alloc + fi_min_alloc) / 2, "risk off2" else: # fi_8 > fi_13 if slope > slope_threshold: return fi_min_alloc, "risk-off" else: return fi_max_alloc, "no signal" ############################################### # 10. Simulation: VIX-based (current) Strategy with Signal Logging ############################################### def simulate_strategy(prices, macro_data, eq_tickers, fi_tickers, alts_tickers, initial_aum, start_date, end_date, rebalance_period, rebalance_ratio, lookback_period, metric_type, internal_rebalance_ratios, macro_max_alloc=1.0, macro_min_alloc=0.6): """ Runs the simulation with: - Daily macro overlay adjustments - Monthly momentum rebalancing based on total returns. This version computes and logs the following signals daily: • VIX signal (and corresponding target allocation) • Composite macro signal (and target allocation) • FI signal (and target allocation) """ vix_target_alloc = macro_max_alloc # Default to max allocation vix_signal = "risk-on" # Default to risk-on macro_target_alloc = macro_max_alloc macro_signal = "risk-on" fi_target_alloc = macro_max_alloc fi_signal = "no signal" tickers = eq_tickers + fi_tickers + alts_tickers monthly_dates = get_observation_dates(start_date, end_date, rebalance_period) daily_dates = prices.index.sort_values() # Prepare macro data: compute VIX EMA fields macro_data = macro_data.copy() macro_data['VIX 1M 3M_Spread'] = macro_data['VIX'] - macro_data['VIX3M'] macro_data['VIX1M_EMA'] = macro_data["VIX 1M 3M_Spread"].ewm(span=5, adjust=False).mean() macro_data["Mean"] = macro_data['VIX1M_EMA'].rolling(window=504).mean() macro_data["Std"] = macro_data['VIX1M_EMA'].rolling(window=504).std() # Precompute FI EMAs macro_data["FI_EMA_8"] = macro_data["LF98TRUU"].ewm(span=8, adjust=False).mean() macro_data["FI_EMA_13"] = macro_data["LF98TRUU"].ewm(span=13, adjust=False).mean() macro_data["FI_EMA_21"] = macro_data["LF98TRUU"].ewm(span=21, adjust=False).mean() portfolio = initialize_portfolio(prices, start_date, tickers, initial_aum) CASH = 0.0 current_regime = 'risk-on' target_alloc = macro_max_alloc previous_regime = current_regime results = [] prev_total_aum = initial_aum # Filter daily_dates to ensure they're within range daily_dates = daily_dates[(daily_dates >= start_date) & (daily_dates <= end_date)] for current_date in daily_dates: daily_adjustment_note = "No adjustment" cash_adjustment = 0 # Determine reference date (previous trading day) available_macro_dates = macro_data.index[macro_data.index < current_date] if len(available_macro_dates) == 0: ref_date = current_date else: ref_date = available_macro_dates[-1] # Daily macro signal computation and adjustment using ref_date try: vix_ema = macro_data['VIX1M_EMA'].asof(ref_date) vix_mean = macro_data['Mean'].asof(ref_date) vix_std = macro_data['Std'].asof(ref_date) except Exception as e: raise ValueError(f"Error retrieving VIX data for {current_date}: {e}") vix_target_alloc = momentum_allocation(vix_ema, vix_mean, vix_std, max_alloc=macro_max_alloc, min_alloc=macro_min_alloc) if vix_ema >= (vix_mean + vix_std): vix_signal = 'risk-off' elif vix_ema <= (vix_mean - vix_std): vix_signal = 'risk-on' else: vix_signal = 'no-signal' # Compute Composite Macro Signal using data up to ref_date macro_target_alloc, macro_signal = macro_signal_allocation_std( macro_data, current_date, macro_max_alloc, macro_min_alloc, rolling_window=100) # Compute FI Signal using data up to ref_date fi_target_alloc, fi_signal = compute_fi_target_allocation( macro_data, current_date, macro_max_alloc, macro_min_alloc, slope_threshold=0.01, tol=0.001) current_regime = vix_signal # Use VIX signal for adjustment if current_regime != previous_regime: target_alloc = vix_target_alloc mom_value = compute_portfolio_value(portfolio, prices, current_date) total_investment = mom_value + CASH desired_mom_value = target_alloc * total_investment if mom_value > desired_mom_value: excess = mom_value - desired_mom_value cash_adjustment = excess for ticker in portfolio: price = prices.loc[current_date, ticker] ticker_value = portfolio[ticker] * price sell_amount = (ticker_value / mom_value) * excess qty_to_sell = sell_amount / price portfolio[ticker] -= qty_to_sell CASH += excess daily_adjustment_note = f"Adj: Sold excess {excess:.2f} to CASH." elif mom_value < desired_mom_value and CASH > 0: shortage = desired_mom_value - mom_value available = min(shortage, CASH) cash_adjustment = -available for ticker in portfolio: price = prices.loc[current_date, ticker] ticker_value = portfolio[ticker] * price target_weight = ticker_value / mom_value if mom_value > 0 else 1/len(portfolio) invest_amount = target_weight * available qty_to_buy = invest_amount / price portfolio[ticker] += qty_to_buy CASH -= available daily_adjustment_note = f"Adj: Bought using {available:.2f} CASH." # Monthly momentum rebalancing if current_date in monthly_dates: sorted_tickers, ranks, metrics = rank_assets( prices, current_date, tickers, lookback_period, metric_type) portfolio, trades, pre_rebalance_value = rebalance_portfolio( portfolio, prices, current_date, tickers, sorted_tickers, internal_rebalance_ratios, rebalance_ratio) mom_value = compute_portfolio_value(portfolio, prices, current_date) total_aum = mom_value + CASH desired_mom_value = target_alloc * total_aum if mom_value > desired_mom_value: excess = mom_value - desired_mom_value cash_adjustment = excess for ticker in portfolio: price = prices.loc[current_date, ticker] ticker_value = portfolio[ticker] * price sell_amount = (ticker_value / mom_value) * excess qty_to_sell = sell_amount / price portfolio[ticker] -= qty_to_sell CASH += excess daily_adjustment_note = f"Monthly: Sold excess {excess:.2f} to CASH." elif mom_value < desired_mom_value and CASH > 0: shortage = desired_mom_value - mom_value available = min(shortage, CASH) cash_adjustment = -available for ticker in portfolio: price = prices.loc[current_date, ticker] ticker_value = portfolio[ticker] * price target_weight = ticker_value / mom_value if mom_value > 0 else 1/len(portfolio) invest_amount = target_weight * available qty_to_buy = invest_amount / price portfolio[ticker] += qty_to_buy CASH -= available daily_adjustment_note = f"Monthly: Bought using {available:.2f} CASH." else: daily_adjustment_note = "Monthly: No cash adjustment needed." mom_value = compute_portfolio_value(portfolio, prices, current_date) total_aum = mom_value + CASH ret = (total_aum - prev_total_aum) / prev_total_aum prev_total_aum = total_aum # Log monthly rebalancing event row = { 'Date': current_date, 'Momentum AUM': mom_value, 'CASH': CASH, 'Total AUM': total_aum, 'Current Regime (VIX)': current_regime, 'VIX Target': vix_target_alloc, 'VIX Signal': vix_signal, 'Macro Target': macro_target_alloc, 'Macro Signal': macro_signal, 'FI Target': fi_target_alloc, 'FI Signal': fi_signal, 'Target Momentum Alloc': target_alloc, 'Adjustment Note': daily_adjustment_note, 'Cash Adjustment': cash_adjustment, 'Return': ret, 'Event': 'Monthly Rebalance' } for ticker in tickers: price = prices.loc[current_date, ticker] qty = portfolio[ticker] notional = qty * price row[f'qty_{ticker}'] = qty row[f'notional_{ticker}'] = notional row[f'weight_{ticker}'] = (notional / mom_value) if mom_value > 0 else np.nan row[f'rank_{ticker}'] = ranks.get(ticker, np.nan) row[f'metric_{ticker}'] = metrics.get(ticker, np.nan) row[f'trade_{ticker}'] = trades.get(ticker, 0) results.append(row) else: # Log daily signal checks current_mom_value = compute_portfolio_value(portfolio, prices, current_date) total_aum = current_mom_value + CASH row = { 'Date': current_date, 'Momentum AUM': current_mom_value, 'CASH': CASH, 'Total AUM': total_aum, 'Current Regime (VIX)': current_regime, 'VIX Target': vix_target_alloc, 'VIX Signal': vix_signal, 'Macro Target': macro_target_alloc, 'Macro Signal': macro_signal, 'FI Target': fi_target_alloc, 'FI Signal': fi_signal, 'Target Momentum Alloc': target_alloc, 'Adjustment Note': daily_adjustment_note, 'Cash Adjustment': cash_adjustment, 'Return': np.nan, 'Event': 'Daily Check' } for ticker in tickers: price = prices.loc[current_date, ticker] qty = portfolio[ticker] notional = qty * price row[f'qty_{ticker}'] = qty row[f'notional_{ticker}'] = notional row[f'weight_{ticker}'] = (notional / current_mom_value) if current_mom_value > 0 else np.nan row[f'rank_{ticker}'] = np.nan row[f'metric_{ticker}'] = np.nan row[f'trade_{ticker}'] = 0 results.append(row) result_df = pd.DataFrame(results) result_df.set_index('Date', inplace=True) return result_df ############################################### # 11. Main – Example Usage ############################################### if __name__ == '__main__': # Define asset tickers. eq_tickers = ['SPY US Equity'] fi_tickers = ['TLT US Equity', 'HYG US Equity'] alts_tickers = ['GLD US Equity', 'SHV US Equity', 'VNQ US Equity'] initial_aum = 100e6 # 100 million start_date = pd.to_datetime('2008-01-01') end_date = pd.to_datetime('2025-02-01') # Monthly rebalancing period. rebalance_period = 1 # monthly rebalance_ratio = 0.2 # 20% of current momentum AUM rebalanced each period lookback_period = 6 # 6-month lookback period for momentum metric_type = 'simple' internal_rebalance_ratios = [0.7, 0.3, 0, 0, -0.3, -0.7] # File paths (adjust these to your environment). price_filepath = r"\\asiapac.nom\data\MUM\IWM\India_IWM_IPAS\Reet\Momentum Strategy\Codes\Historic Prices.xlsx" macro_filepath = r"\\asiapac.nom\data\MUM\IWM\India_IWM_IPAS\Reet\Momentum Strategy\Momentum Strategy Overlay Data.xlsx" prices = load_price_data(price_filepath) macro_data = load_macro_data(macro_filepath) # Run simulation. result_df = simulate_strategy(prices, macro_data, eq_tickers, fi_tickers, alts_tickers, initial_aum, start_date, end_date, rebalance_period, rebalance_ratio, lookback_period, metric_type, internal_rebalance_ratios, macro_max_alloc=1.0, macro_min_alloc=0.6) pd.set_option('display.float_format', lambda x: f'{x:,.2f}') print(result_df)
Editor is loading...
Leave a Comment