Untitled
unknown
plain_text
12 days ago
25 kB
2
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, sheet_name="Sheet2", index_col=0)
df.index = pd.to_datetime(df.index)
df = df.sort_index()
# 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 'Eq2' sheet, LF98TRUU from 'FI2' sheet.
Removes weekend data.
Also computes slopes for selected macro indicators.
"""
# VIX data
vix_data = pd.read_excel(filepath, sheet_name='Eq2', index_col=0, parse_dates=True, usecols=[0, 4, 5, 6, 7, 8])
vix_data.columns = ['VIX', 'VIX3M', 'UX1', 'UX2', 'UX3']
vix_data = vix_data[vix_data.index.dayofweek < 5]
cdx_data = pd.read_excel(filepath, sheet_name='FI2', index_col=0, parse_dates=True, usecols=[0, 2])
cdx_data.columns = ['LF98TRUU']
cdx_data = cdx_data[cdx_data.index.dayofweek < 5]
combined_data = pd.concat([vix_data, cdx_data], axis=1)
combined_data = combined_data.fillna(method='ffill').fillna(method='bfill')
combined_data = combined_data.sort_index()
return combined_data
###############################################
# 2. Helper: Observation Dates (Monthly)
###############################################
def get_observation_dates(prices, start_date, end_date, rebalance_period):
dates = []
current_date = start_date
while current_date < end_date:
candidate_date = (current_date + relativedelta(months=rebalance_period)).replace(day=1)
while candidate_date not in prices.index:
candidate_date += pd.Timedelta(days=1)
if candidate_date.month != (current_date + relativedelta(months=rebalance_period)).month:
candidate_date = None
break
if candidate_date is None or candidate_date > end_date:
break
dates.append(candidate_date)
current_date = candidate_date
return dates
###############################################
# 3. Portfolio Initialization
###############################################
def initialize_portfolio(prices, date, tickers, initial_aum):
portfolio = {}
allocation = initial_aum / len(tickers)
for ticker in tickers:
price = prices.loc[date, ticker]
portfolio[ticker] = allocation / price
return portfolio
###############################################
# 4. Lookback Metric Computation
###############################################
def compute_lookback_metric(prices, current_date, ticker, lookback_period, metric_type='simple'):
prices = prices.sort_index()
mask = prices.index < current_date
prev_date = prices.index[mask][-1]
lookback_date = prev_date - relativedelta(months=lookback_period)
current_price = prices[ticker].asof(prev_date)
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):
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):
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):
mask = prices.index < current_date
prev_date = prices.index[mask][-1]
prev_prices = prices.loc[prev_date]
curr_prices = prices.loc[current_date]
portfolio_value = sum(portfolio[ticker] * prev_prices[ticker] for ticker in tickers)
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():
if target_trade < 0:
available_notional = portfolio[ticker] * curr_prices[ticker]
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(t for t in target_trades.values() if t > 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():
execution_price = curr_prices[ticker]
qty_change = trade_notional / execution_price
new_portfolio[ticker] += qty_change
return new_portfolio, actual_trades, portfolio_value
def adjust_overweight(portfolio, prices, current_date, sorted_tickers, threshold=0.70):
mask = prices.index < current_date
prev_date = prices.index[mask][-1]
prev_prices = prices.loc[prev_date]
curr_prices = prices.loc[current_date]
portfolio_value = sum(portfolio[ticker] * prev_prices[ticker] for ticker in portfolio)
weights = {ticker: (portfolio[ticker] * prev_prices[ticker]) / portfolio_value
for ticker in portfolio}
new_portfolio = portfolio.copy()
for overweight in portfolio:
if weights[overweight] > threshold:
extra_weight = weights[overweight] - threshold
extra_value = extra_weight * portfolio_value
execution_price_over = curr_prices[overweight]
qty_reduce = extra_value / execution_price_over
new_portfolio[overweight] -= qty_reduce
remaining_value = extra_value
for candidate in sorted_tickers:
if candidate == overweight:
continue
candidate_value = new_portfolio[candidate] * curr_prices[candidate]
candidate_weight = candidate_value / portfolio_value
if candidate_weight < threshold:
capacity = (threshold - candidate_weight) * portfolio_value
allocation = min(remaining_value, capacity)
qty_add = allocation / curr_prices[candidate]
new_portfolio[candidate] += qty_add
remaining_value -= allocation
if remaining_value <= 0:
break
return new_portfolio
###############################################
# 8. Risk Signal Function
###############################################
def generate_risk_signals(current_date, macro_data, prices, portfolio):
# Get the most recent macro data before current_date
available_macro_dates = macro_data.index[macro_data.index < current_date]
ref_date = available_macro_dates[-1] if len(available_macro_dates) > 0 else current_date
# Retrieve key metrics (simplified for this example)
ux1_ux2_spread = macro_data['UX1'].asof(ref_date) - macro_data['UX2'].asof(ref_date)
slope_5d = macro_data['UX1'].asof(ref_date) # placeholder
slope_10d = macro_data['UX1'].asof(ref_date) # placeholder
slope_15d = macro_data['UX1'].asof(ref_date) # placeholder
vix_mom_signal = macro_data['UX3'].asof(ref_date) # placeholder
# Determine signal based on momentum signal
vix_signal = 'risk-off' if vix_mom_signal > 0 else 'risk-on'
# Set target allocation based on signal (example logic)
if vix_mom_signal == 3:
vix_target_alloc = 0.6
elif vix_mom_signal == 1:
vix_target_alloc = 0.8
else:
vix_target_alloc = 1.0
# Get weights for SPY and HYG using previous day's portfolio
mask = prices.index < current_date
prev_date = prices.index[mask][-1]
mom_value = compute_portfolio_value(portfolio, prices, prev_date)
spy_weight = (portfolio.get('SPY US Equity', 0) * prices.loc[prev_date, 'SPY US Equity']) / mom_value if mom_value > 0 else 0
hyg_weight = (portfolio.get('HYG US Equity', 0) * prices.loc[prev_date, 'HYG US Equity']) / mom_value if mom_value > 0 else 0
note = ""
# Override logic based on SPY+HYG weights
if vix_signal == 'risk-off':
if (spy_weight + hyg_weight) < 0.40:
regime = 'risk-on'
target_alloc = 1
note = "Forced regime to risk-on & target alloc 100% due to SPY+HYG < 40%"
else:
regime = 'risk-off'
target_alloc = vix_target_alloc
else:
regime = 'risk-on'
target_alloc = 1.0
vix_params = {
'UX1_UX2_Spread': ux1_ux2_spread,
'slope_5d': slope_5d,
'slope_10d': slope_10d,
'slope_15d': slope_15d,
'vix_mom_signal': vix_mom_signal,
'vix_target_alloc': vix_target_alloc,
'spy_weight': spy_weight,
'hyg_weight': hyg_weight
}
return regime, target_alloc, vix_signal, note, vix_params
###############################################
# 9. Helper Functions for Cash
###############################################
def invest_cash_into_portfolio(portfolio, prices, current_date, cash_qty, cash_ticker):
cash_price = prices.loc[current_date, cash_ticker]
cash_available = cash_qty * cash_price
if cash_available <= 0:
return portfolio, cash_qty, f"No {cash_ticker} to reinvest."
mask = prices.index < current_date
prev_date = prices.index[mask][-1]
mom_value = compute_portfolio_value(portfolio, prices, current_date)
new_portfolio = portfolio.copy()
for ticker in portfolio:
prev_price = prices.loc[prev_date, ticker]
total_qty = (portfolio[ticker] * (mom_value + cash_available)) / mom_value if mom_value > 0 else 1/len(portfolio)
new_portfolio[ticker] = total_qty
return new_portfolio, 0.0, f"Reinvested {cash_available:,.2f} from {cash_ticker}"
def allocate_cash_from_portfolio(portfolio, prices, current_date, target_alloc, cash_qty, cash_ticker):
# Use previous day's snapshot to compute total AUM
mask = prices.index < current_date
prev_date = prices.index[mask][-1]
curr_value = compute_portfolio_value(portfolio, prices, prev_date)
cash_price_prev = prices.loc[prev_date, cash_ticker]
cash_equiv_prev = cash_qty * cash_price_prev
total_aum = curr_value + cash_equiv_prev
# Determine desired cash based on target allocation
desired_cash = (1 - target_alloc) * total_aum
cash_price = prices.loc[current_date, cash_ticker]
current_cash = cash_qty * cash_price
new_portfolio = portfolio.copy()
new_cash_qty = cash_qty
note = ""
# If excess cash exists, scale up positions to reach target allocation
if desired_cash < current_cash:
current_portfolio_value = sum(portfolio[ticker] * prices.loc[current_date, ticker] for ticker in portfolio)
desired_risk_allocation = total_aum * target_alloc
scaling_factor = desired_risk_allocation / current_portfolio_value
for ticker in portfolio:
new_portfolio[ticker] = portfolio[ticker] * scaling_factor
excess_cash = current_cash - desired_cash
new_cash_qty -= excess_cash / cash_price
note = f"Deployed {excess_cash:,.2f} from {cash_ticker} into portfolio"
# If cash is insufficient, sell some positions to raise cash
elif desired_cash > current_cash:
cash_to_raise = desired_cash - current_cash
curr_value = compute_portfolio_value(portfolio, prices, current_date)
for ticker in portfolio:
price = prices.loc[current_date, ticker]
sell_ratio = (cash_to_raise / curr_value)
qty_to_sell = portfolio[ticker] * sell_ratio
new_portfolio[ticker] -= qty_to_sell
new_cash_qty += cash_to_raise / cash_price
note = f"Raised {cash_to_raise:,.2f} into {cash_ticker}"
return new_portfolio, new_cash_qty, note
###############################################
# 10. Simulation: Strategy
###############################################
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,
cash_ticker='SHV US Equity'):
all_tickers = eq_tickers + fi_tickers + alts_tickers
momentum_tickers = [t for t in all_tickers if t != cash_ticker]
monthly_dates = get_observation_dates(prices, start_date, end_date, rebalance_period)
daily_dates = prices.index.sort_values()
daily_dates = daily_dates[(daily_dates >= start_date) & (daily_dates <= end_date)]
macro_data = macro_data.copy()
macro_data['UX1_UX2_Spread'] = macro_data['UX1'] - macro_data['UX2']
macro_data['UX1_UX2_SpreadEMA_12'] = macro_data["UX1_UX2_Spread"].ewm(span=12, adjust=False).mean()
macro_data['Slope5D'] = macro_data["UX1_UX2_SpreadEMA_12"].diff(5)
macro_data['Slope10D'] = macro_data["UX1_UX2_SpreadEMA_12"].diff(10)
macro_data['Slope15D'] = macro_data["UX1_UX2_SpreadEMA_12"].diff(15)
macro_data['LF98TRUU_EMA_12'] = macro_data["LF98TRUU"].ewm(span=12, adjust=False).mean()
macro_data['Slope5D_HY'] = macro_data["LF98TRUU_EMA_12"].diff(5)
macro_data['Slope10D_HY'] = macro_data["LF98TRUU_EMA_12"].diff(10)
macro_data['Slope15D_HY'] = macro_data["LF98TRUU_EMA_12"].diff(15)
macro_data["Signal_Momentum"] = (
(macro_data['Slope5D'] > 0).astype(int) * 2 - 1 +
(macro_data['Slope10D'] > 0).astype(int) * 2 - 1 +
(macro_data['Slope15D'] > 0).astype(int) * 2 - 1
)
macro_data["Signal_Momentum_HY"] = (
(macro_data['Slope5D_HY'] > 0).astype(int) * 2 - 1 +
(macro_data['Slope10D_HY'] > 0).astype(int) * 2 - 1 +
(macro_data['Slope15D_HY'] > 0).astype(int) * 2 - 1
)
available_dates = prices.index[prices.index >= start_date]
if len(available_dates) == 0:
raise ValueError("No trading dates found after the specified start_date")
start_date = available_dates[0]
portfolio = initialize_portfolio(prices, start_date, momentum_tickers, initial_aum)
cash_qty = 0.0
current_regime, target_alloc = 'risk-on', 1.0
previous_regime, previous_target_alloc = current_regime, target_alloc
prev_total_aum = initial_aum
results = []
for current_date in daily_dates:
# Save snapshot of yesterday's portfolio and cash
prev_portfolio = portfolio.copy()
prev_cash_qty = cash_qty
daily_note = "No adjustment"
vix_params = {}
ranks, metrics, trades = {}, {}, {}
# --- Generate regime & allocation ---
regime, target_alloc, vix_signal, signal_note, vix_params = generate_risk_signals(
current_date, macro_data, prices, portfolio)
current_regime = regime
daily_note = signal_note
# --- Monthly Rebalancing Logic ---
if current_date in monthly_dates:
sorted_tickers, ranks, metrics = rank_assets(prices, current_date, momentum_tickers, lookback_period, metric_type)
temp_portfolio, trades, pre_rebalance_value = rebalance_portfolio(
portfolio, prices, current_date, momentum_tickers, sorted_tickers, internal_rebalance_ratios, rebalance_ratio)
temp_portfolio = adjust_overweight(temp_portfolio, prices, current_date, sorted_tickers, threshold=0.70)
temp_value = compute_portfolio_value(temp_portfolio, prices, current_date)
spy_temp = temp_portfolio.get('SPY US Equity', 0) * prices.loc[current_date, 'SPY US Equity']
hyg_temp = temp_portfolio.get('HYG US Equity', 0) * prices.loc[current_date, 'HYG US Equity']
combined_weight = (spy_temp + hyg_temp) / temp_value if temp_value > 0 else 0
if (current_regime == 'risk-off') and (combined_weight < 0.40):
current_regime = 'risk-on'
target_alloc = 1
daily_note += " | Monthly: Forced risk-on due to SPY+HYG weight < 40% after simulation."
portfolio = temp_portfolio
if target_alloc != previous_target_alloc:
total_aum_temp = compute_portfolio_value(portfolio, prices, current_date) + cash_qty * prices.loc[current_date, cash_ticker]
desired_value = target_alloc * total_aum_temp
actual_value = compute_portfolio_value(portfolio, prices, current_date)
if abs(actual_value - desired_value) / total_aum_temp > 0.001:
portfolio, cash_qty, note_update = allocate_cash_from_portfolio(
portfolio, prices, current_date, target_alloc, cash_qty, cash_ticker)
daily_note += " | Monthly: " + note_update
else:
daily_note += " | Monthly: Skipped cash adjustment due to minor deviation."
else:
daily_note += " | Monthly"
# --- Daily Regime Handling: Only if NOT monthly date ---
elif (previous_regime != current_regime) or (current_regime == 'risk-off' and target_alloc != previous_target_alloc):
if previous_regime == 'risk-off' and current_regime == 'risk-on' and cash_qty > 0:
portfolio, cash_qty, note_update = invest_cash_into_portfolio(
portfolio, prices, current_date, cash_qty, cash_ticker)
daily_note += " | " + note_update
elif (previous_regime == 'risk-on' and current_regime == 'risk-off') or \
(current_regime == 'risk-off' and target_alloc != previous_target_alloc):
portfolio, cash_qty, note_update = allocate_cash_from_portfolio(
portfolio, prices, current_date, target_alloc, cash_qty, cash_ticker)
daily_note += " | " + note_update
previous_regime = current_regime
previous_target_alloc = target_alloc
# --- Log AUM and daily stats using yesterday's snapshot (prev_portfolio, prev_cash_qty) ---
cash_price = prices.loc[current_date, cash_ticker]
cash_value = prev_cash_qty * cash_price
momentum_value = sum(
prev_portfolio[ticker] * prices.loc[current_date, ticker]
for ticker in prev_portfolio
)
total_aum = momentum_value + cash_value
ret = (total_aum - prev_total_aum) / prev_total_aum if prev_total_aum > 0 else 0
prev_total_aum = total_aum
row = {
'Total AUM': total_aum,
'Momentum AUM': momentum_value,
'Cash Qty': cash_qty,
'Cash Price': cash_price,
'Cash Value': cash_value,
'Current Regime': current_regime,
'Target Alloc': target_alloc,
'VIX Target': vix_params.get('vix_target_alloc', np.nan),
'VIX Signal': vix_signal,
'Adjustment Note': daily_note,
'Cash Adjustment': 0.0,
'Return': ret,
'Event': 'Monthly Rebalance' if current_date in monthly_dates else 'Daily Check',
'Slope_5D': vix_params.get('slope_5d', np.nan),
'Slope_10D': vix_params.get('slope_10d', np.nan),
'Slope_15D': vix_params.get('slope_15d', np.nan),
'VIX_Mom_Signal': vix_params.get('vix_mom_signal', np.nan),
'Date': current_date
}
for ticker in momentum_tickers:
qty = prev_portfolio.get(ticker, 0)
price = prices.loc[current_date, ticker]
notional = qty * price
weight = notional / momentum_value if momentum_value > 0 else np.nan
row[f'qty_{ticker}'] = qty
row[f'price_{ticker}'] = price
row[f'notional_{ticker}'] = notional
row[f'weight_{ticker}'] = weight
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)
result_df = pd.DataFrame(results)
result_df.set_index('Date', inplace=True)
return result_df
###############################################
# 11. Restructure Results for Output
###############################################
def restructure_results(result_df, momentum_tickers):
base_columns = ['Total AUM', 'Momentum AUM', 'Cash Value', 'Cash Qty', 'Cash Price', 'Return',
'Current Regime', 'Target Alloc', 'VIX Target', 'VIX Signal',
'Adjustment Note', 'Event']
vix_columns = ['Slope_5D', 'Slope_10D', 'Slope_15D', 'VIX_Mom_Signal']
price_columns = [f'price_{ticker}' for ticker in momentum_tickers]
qty_columns = [f'qty_{ticker}' for ticker in momentum_tickers]
notional_columns = [f'notional_{ticker}' for ticker in momentum_tickers]
weight_columns = [f'weight_{ticker}' for ticker in momentum_tickers]
rank_columns = [f'rank_{ticker}' for ticker in momentum_tickers]
metric_columns = [f'metric_{ticker}' for ticker in momentum_tickers]
trade_columns = [f'trade_{ticker}' for ticker in momentum_tickers]
new_column_order = (
base_columns +
price_columns +
qty_columns +
notional_columns +
weight_columns +
rank_columns +
metric_columns +
trade_columns +
vix_columns
)
existing_columns = [col for col in new_column_order if col in result_df.columns]
return result_df[existing_columns]
###############################################
# 12. Main – Example Usage
###############################################
if __name__ == '__main__':
eq_tickers = ['SPY US Equity']
fi_tickers = ['TLT US Equity', 'HYG US Equity']
alts_tickers = ['GLD US Equity', 'IGSB US Equity']
initial_aum = 100e6
start_date = pd.to_datetime('2008-01-01')
end_date = pd.to_datetime('2025-02-01')
rebalance_period = 1
rebalance_ratio = 0.2
lookback_period = 6
metric_type = 'simple'
internal_rebalance_ratios = [0.8, 0.2, 0, -0.2, -0.8]
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)
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,
cash_ticker='SHV US Equity')
pd.set_option('display.float_format', lambda x: f'{x:,.2f}')
all_tickers = eq_tickers + fi_tickers + alts_tickers
momentum_tickers = [t for t in all_tickers]
result_df = restructure_results(result_df, momentum_tickers)
# For example, print a summary of the simulation:
print(result_df[['Total AUM', 'Momentum AUM', 'Cash Value']].tail())
Editor is loading...
Leave a Comment