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