Untitled
unknown
plain_text
a month ago
27 kB
4
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.
- For 'simple': metric = (Price_today / Price_lookback) - 1.
- For 'sma': metric = (Price_today - SMA) / SMA.
Uses asof() to get the nearest available price on or before the date.
"""
prices = prices.sort_index()
lookback_date = current_date - relativedelta(months=lookback_period)
current_price = prices[ticker].asof(current_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 {current_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_value, vix_mean, vix_std, max_alloc=1.0, min_alloc=0.6):
"""
Maps VIX-based signal to a target momentum allocation fraction using a simple rule.
If vix_value < vix_mean + vix_std, return max_alloc (fully risk-on);
if vix_value is between (mean+std) and (mean+2*std), return 0.8;
otherwise, return min_alloc (risk-off).
"""
if vix_value < vix_mean + vix_std:
return max_alloc
elif (vix_value >= vix_mean + vix_std) and (vix_value < vix_mean + 2*vix_std):
return 0.8
else:
return min_alloc
###############################################
# 9. Composite Macro Signal Function
###############################################
def compute_composite_series(macro_data, window):
"""
Computes the composite macro index for each date in the last 'window' days.
It uses min-max scaling on selected indicators over the same rolling 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 = {}
sub = macro_data.loc[:macro_data.index[-1]].tail(window)
for dt in sub.index:
window_data = macro_data.loc[:dt].tail(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()
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
comp_vals["INJCJC Index"] = 1 - comp_vals["INJCJC Index"]
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.
Returns:
target_alloc: the target allocation.
signal: a string label ("risk-on", "neutral", or "risk-off").
"""
comp_series = compute_composite_series(macro_data, rolling_window)
mu = comp_series.mean()
sigma = comp_series.std()
current_composite = comp_series.asof(current_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
###############################################
# 10. Refined FI Signal Function (8 EMA and 13 EMA)
###############################################
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.
Logic:
1. Retrieve FI_EMA_8 and FI_EMA_13 from macro_data.
2. If FI_EMA_8 < FI_EMA_13 → signal is "risk-on" (fi_max_alloc).
3. If FI_EMA_8 and FI_EMA_13 are nearly equal (difference within tolerance) → signal is "neutral"
(allocation is the average of fi_max_alloc and fi_min_alloc).
4. If FI_EMA_8 > FI_EMA_13:
- Compute the slope of FI_EMA_8 (current minus previous value).
- If the slope > slope_threshold, signal "risk-off" (fi_min_alloc).
- Otherwise, signal "neutral" (midpoint allocation).
5. Once a risk-off signal is triggered, it will persist until the slope flattens to near zero.
Returns:
target_alloc: the target allocation.
signal_label: a string label ("risk-on", "neutral", or "risk-off").
"""
fi_8 = macro_data["FI_EMA_8"].asof(current_date)
fi_13 = macro_data["FI_EMA_13"].asof(current_date)
if pd.isna(fi_8) or pd.isna(fi_13):
return fi_max_alloc, "risk-on"
# Compute slope of FI_EMA_8
available_dates = macro_data.loc[:current_date].index
if len(available_dates) < 2:
slope = 0
else:
prev_date = available_dates[-2]
fi_8_prev = macro_data["FI_EMA_8"].asof(prev_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, "neutral"
else: # fi_8 > fi_13
if slope > slope_threshold:
return fi_min_alloc, "risk-off"
else:
return (fi_max_alloc + fi_min_alloc) / 2, "neutral"
###############################################
# 11. Simulation: VIX-based 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,
macro_overlay_frequency=10):
"""
Runs the simulation with:
- Macro overlay adjustments every 'macro_overlay_frequency' days.
- Monthly momentum rebalancing.
Only logs days on which a macro overlay adjustment or monthly rebalance occurs.
Also computes and logs:
• VIX signal (and target allocation)
• Composite macro signal (and target allocation)
• FI signal (using the refined 8-day vs. 13-day EMA approach)
"""
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 = "risk-on"
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['VIX9D_EMA'] = macro_data["VIX9D"].ewm(span=5, adjust=False).mean()
macro_data["Mean"] = macro_data['VIX9D_EMA'].rolling(window=504, min_periods=1).mean()
macro_data["Std"] = macro_data['VIX9D_EMA'].rolling(window=504, min_periods=1).std()
# Precompute FI EMAs on the FI index ("LF98TRUU").
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()
portfolio = initialize_portfolio(prices, start_date, tickers, initial_aum)
CASH = 0.0
# Initially use VIX signal (risk-on by default)
current_regime = 'risk-on'
target_alloc = macro_max_alloc # using VIX signal for now
results = []
prev_total_aum = initial_aum
last_macro_date = start_date
for current_date in daily_dates:
if current_date < start_date or current_date > end_date:
continue
daily_adjustment_note = "No adjustment"
cash_adjustment = 0
# Execute macro overlay adjustment on user-defined frequency.
if (current_date - last_macro_date).days >= macro_overlay_frequency:
# --- VIX Signal ---
try:
vix_ema = macro_data['VIX9D_EMA'].asof(current_date)
vix_mean = macro_data['Mean'].asof(current_date)
vix_std = macro_data['Std'].asof(current_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)
vix_signal = "risk-on" if vix_target_alloc == macro_max_alloc else "risk-off"
# --- Composite Macro Signal ---
macro_target_alloc, macro_signal = macro_signal_allocation_std(macro_data, current_date, macro_max_alloc, macro_min_alloc, rolling_window=252)
# --- FI Signal (Refined) ---
fi_target_alloc, fi_signal = compute_fi_target_allocation(macro_data, current_date, macro_max_alloc, macro_min_alloc, slope_threshold=0.01)
# For now, we use only the VIX signal for cash adjustment.
if vix_signal != current_regime:
current_regime = vix_signal
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 # positive means cash raised.
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 # negative means cash used.
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."
last_macro_date = current_date
# On monthly rebalancing dates, run full 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_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"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
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:
# Only log days where macro overlay occurred
if (current_date - last_macro_date).days == 0:
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': 'Macro Overlay'
}
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
###############################################
# 12. 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('2012-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)
# Set the macro overlay frequency in days (e.g., 30 days for overlay events)
macro_overlay_frequency = 30
# 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,
macro_overlay_frequency=macro_overlay_frequency)
pd.set_option('display.float_format', lambda x: f'{x:,.2f}')
# Filter to show only rows for Macro Overlay and Monthly Rebalance events.
filtered_df = result_df[result_df['Event'].isin(['Macro Overlay', 'Monthly Rebalance'])]
print(filtered_df)
Editor is loading...
Leave a Comment