Untitled
unknown
plain_text
a month ago
26 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
import pandas as pd
import numpy as np
from statsmodels.tools.tools import add_constant
from statsmodels.discrete.discrete_model import Logit
###############################################
# 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()
# 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, 4, 5])
vix_data.columns = ['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", "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(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 = {}
mask = prices.index < date
prev_date = prices.index[mask][-1]
allocation = initial_aum / len(tickers)
for ticker in tickers:
price = prices.loc[prev_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. VIX-based Allocation Function
###############################################
###############################################
# 10. Helper Functions for Cash (using a cash ticker)
###############################################
def invest_cash_into_portfolio(portfolio, prices, current_date, cash_qty, cash_ticker):
"""
When switching from risk-off to risk-on, sell the cash instrument (e.g. SHV)
and reinvest its proceeds into the portfolio using previous-day weights.
"""
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):
"""
Adjusts portfolio positions based on target allocation for cash.
When deploying cash, scales up existing positions proportionally to reach target allocation.
"""
# Get previous date and calculate total portfolio value
mask = prices.index < current_date
prev_date = prices.index[mask][-1]
curr_value = compute_portfolio_value(portfolio, prices, prev_date)
# Calculate cash position value
cash_price = prices.loc[prev_date, cash_ticker]
cash_equiv = cash_qty * cash_price
total_aum = curr_value + cash_equiv
# Determine desired cash position
desired_cash = (1 - target_alloc) * total_aum
cash_price = prices.loc[current_date, cash_ticker]
cash_equiv = cash_qty * cash_price
current_cash = cash_equiv
new_portfolio = portfolio.copy()
new_cash_qty = cash_qty
note = ""
# If we need more cash, sell proportionally from other positions
if 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]
ticker_value = portfolio[ticker] * price
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}"
# If we have excess cash, scale up positions to reach target allocation
elif desired_cash < current_cash:
# Calculate current portfolio value at today's prices
current_portfolio_value = sum(
portfolio[ticker] * prices.loc[current_date, ticker]
for ticker in portfolio
)
# Calculate desired total risk allocation
desired_risk_allocation = total_aum * target_alloc
# Calculate scaling factor to reach target allocation
scaling_factor = desired_risk_allocation / current_portfolio_value
# Scale up all positions proportionally
for ticker in portfolio:
new_portfolio[ticker] = portfolio[ticker] * scaling_factor
# Adjust cash position
excess_cash = current_cash - desired_cash
new_cash_qty -= excess_cash / cash_price
note = f"Deployed {excess_cash:,.2f} from {cash_ticker} into portfolio"
return new_portfolio, new_cash_qty, note
###############################################
# 11. Simulation: Refined Strategy with Cash & Regime Logic
###############################################
def simulate_strategy(prices, macro_data, regression_results, 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',
macro_max_alloc=1.0, macro_min_alloc=0.6):
"""
This simulation:
- Uses a designated cash_ticker (e.g. "SHV US Equity") for the cash component.
- Excludes the cash_ticker from momentum ranking/trading.
- When target allocation is below 100% (risk-off), excess portfolio value is sold into the cash_ticker.
- When switching back to risk-on, the cash position is reinvested.
- The cash position is left untouched unless the allocation % changes.
"""
# Define tickers for momentum (exclude cash_ticker)
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)]
# Initialize portfolio (momentum securities only) and cash (in cash_ticker)
portfolio = initialize_portfolio(prices, start_date, momentum_tickers, initial_aum)
cash_qty = 0.0 # cash position is held as cash_ticker units
current_regime = 'risk-on'
target_alloc = 1.0
previous_regime = current_regime
previous_target_alloc = target_alloc
prev_total_aum = initial_aum
results = []
for current_date in daily_dates:
daily_note = "No adjustment"
cash_adjustment = 0.0
# --- Determine signals using regression results ---
if current_date in regression_results.index:
vix_signal = regression_results.loc[current_date, 'Signal']
pred = regression_results.loc[current_date, 'Predicted_Prob_Negative']
# Convert numeric signal to string and determine target allocation
vix_signal = 'risk-off' if vix_signal == 1 else 'risk-on'
# Determine target allocation based on predicted probability
if pred > 0.8:
vix_target_alloc = 0.6
elif pred > 0.6:
vix_target_alloc = 0.8
else:
vix_target_alloc = 1.0
else:
# If date not in regression results, use previous values
vix_signal = previous_regime
vix_target_alloc = previous_target_alloc
daily_note += " | Date not found in regression results, using previous signal"
# --- Determine portfolio value and SPY/HYG weights ---
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
# --- Determine daily regime based on signals and weights ---
# if (vix_signal == 'risk-off'):
# if (spy_weight + hyg_weight) < 0.40:
# current_regime = 'risk-on'
# target_alloc = 1.0
# daily_note = "Forced regime to risk-on & target alloc 100% due to SPY+HYG < 40%"
# else:
# current_regime = vix_signal
# target_alloc = vix_target_alloc
# else:
# current_regime = vix_signal
# target_alloc = vix_target_alloc
# --- Cash rebalancing logic ---
if (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
# --- Monthly Rebalancing ---
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.0
daily_note += " | Monthly: Forced risk-on due to SPY+HYG weight < 40% after simulation."
total_aum = compute_portfolio_value(portfolio, prices, current_date) + cash_qty * prices.loc[current_date, cash_ticker]
simulated_value = temp_value
new_portfolio = {}
for ticker in temp_portfolio:
price = prices.loc[current_date, ticker]
simulated_weight = (temp_portfolio[ticker] * price) / simulated_value if simulated_value > 0 else 1/len(temp_portfolio)
new_qty = (total_aum * simulated_weight) / price
new_portfolio[ticker] = new_qty
portfolio = new_portfolio
cash_qty = 0
else:
portfolio = temp_portfolio
curr_value = compute_portfolio_value(portfolio, prices, current_date)
total_aum = curr_value + cash_qty * prices.loc[current_date, cash_ticker]
desired_value = target_alloc * total_aum
if curr_value > desired_value:
portfolio, cash_qty, note_update = allocate_cash_from_portfolio(portfolio, prices, current_date, target_alloc, cash_qty, cash_ticker)
daily_note += " | Monthly: " + note_update
elif curr_value < desired_value and cash_qty > 0:
portfolio, cash_qty, note_update = allocate_cash_from_portfolio(portfolio, prices, current_date, target_alloc, cash_qty, cash_ticker)
daily_note += " | Monthly: " + note_update
# --- Update daily AUM calculation ---
current_mom_value = compute_portfolio_value(portfolio, prices, current_date)
cash_price = prices.loc[current_date, cash_ticker]
cash_value = cash_qty * cash_price
total_aum = current_mom_value + cash_value
ret = (total_aum - prev_total_aum) / prev_total_aum if prev_total_aum > 0 else 0
prev_total_aum = total_aum
# --- Log daily results ---
row = {
'Date': current_date,
'Momentum AUM': current_mom_value,
'Cash Qty': cash_qty,
'Cash Price': cash_price,
'Cash Value': cash_value,
'Total AUM': total_aum,
'Current Regime': current_regime,
'Target Alloc': target_alloc,
'VIX Target': vix_target_alloc,
'VIX Signal': vix_signal,
'Predicted_Prob': pred if current_date in regression_results.index else np.nan,
'Adjustment Note': daily_note,
'Cash Adjustment': cash_adjustment,
'Return': ret,
'Event': 'Monthly Rebalance' if current_date in monthly_dates else 'Daily Check'
}
# Group ticker-specific data
for ticker in momentum_tickers:
price = prices.loc[current_date, ticker]
qty = portfolio[ticker]
notional = qty * price
row[f'price_{ticker}'] = 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}'] = ranks.get(ticker, np.nan) if current_date in monthly_dates else np.nan
row[f'metric_{ticker}'] = metrics.get(ticker, np.nan) if current_date in monthly_dates else np.nan
row[f'trade_{ticker}'] = trades.get(ticker, 0) if current_date in monthly_dates else 0
results.append(row)
# Create DataFrame
result_df = pd.DataFrame(results)
result_df.set_index('Date', inplace=True)
# Define column groups
base_cols = ['Momentum AUM', 'Cash Qty', 'Cash Price', 'Cash Value', 'Total AUM',
'Current Regime', 'Target Alloc', 'VIX Target', 'VIX Signal', 'Predicted_Prob',
'Adjustment Note', 'Cash Adjustment', 'Return', 'Event']
price_cols = [col for col in result_df.columns if col.startswith('price_')]
qty_cols = [col for col in result_df.columns if col.startswith('qty_')]
notional_cols = [col for col in result_df.columns if col.startswith('notional_')]
weight_cols = [col for col in result_df.columns if col.startswith('weight_')]
rank_cols = [col for col in result_df.columns if col.startswith('rank_')]
metric_cols = [col for col in result_df.columns if col.startswith('metric_')]
trade_cols = [col for col in result_df.columns if col.startswith('trade_')]
# Reorder columns
result_df = result_df[base_cols + price_cols + qty_cols + notional_cols +
weight_cols + rank_cols + metric_cols + trade_cols]
return result_df
if __name__ == '__main__':
# Define asset tickers
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]
# File paths
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"
# Load data
prices = load_price_data(price_filepath)
macro_data = load_macro_data(macro_filepath)
# Load and prepare regression results
regression_results = pd.read_excel("logit_vix_signals_L504_T60.xlsx")
regression_results.index = pd.to_datetime(regression_results.index)
# Run simulation
result_df = simulate_strategy(prices, macro_data, regression_results,
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',
macro_max_alloc=1.0, macro_min_alloc=0.6)
pd.set_option('display.float_format', lambda x: f'{x:,.2f}')
print(result_df[['Total AUM', 'Momentum AUM', 'Cash Price', 'Predicted_Prob', 'VIX Signal']].tail())Editor is loading...
Leave a Comment