<jupyter_start><jupyter_text>Load and Transform Input Data<jupyter_code># asset price data
df_price = pd.read_excel('AssetData.xlsx', sheet_name='Sheet1')
df_price.head()
df_price.tail()<jupyter_output><empty_output><jupyter_text>The provided asset price file contains daily asset price data of 7 unnamed assets, starting from 10/09/2006 through 11/07/2018. Data types are already the expected data types. No conversion needed.<jupyter_code># convert to time series
df_price = df_price.rename(columns={"Dates" : "Date"}).set_index("Date").fillna(method="ffill")<jupyter_output><empty_output><jupyter_text>Note the original price data are sorted in descending order. Let's rearrange it so that the prices are sorted in ascending order by date.<jupyter_code>df_price = df_price.sort_index()<jupyter_output><empty_output><jupyter_text>Next, we load and transform the risk free rates.<jupyter_code># use 3-month treasury bill yield as the proxy of risk free rate
# source: https://fred.stlouisfed.org/series/DGS3MO
df_rf = pd.read_csv('DGS3MO.csv')
df_rf.dtypes
# convert date strings to python date
df_rf['DATE'] = pd.to_datetime(df_rf['DATE'])

# convert rates from string format to numeric, also scale it from percentage to decimal
df_rf['DGS3MO'] = pd.to_numeric(df_rf['DGS3MO'], errors='coerce') / 100

# convert the data frame to a pandas time series, and fill NaN's with the last observation
df_rf = df_rf.rename(columns={"DGS3MO" : "Rf", "DATE" : "Date"}).set_index('Date').fillna(method="ffill")<jupyter_output><empty_output><jupyter_text>Data Analysis<jupyter_code># data statistics
df_price.describe()
df_price.plot()
plt.show()
# since the asset index has different scale, we use subplots to look each index separately.
plt.figure()
f, axes = plt.subplots(7, 1)
axes[0].plot(df_price.index, df_price.Asset1)
axes[0].set_ylabel('Asset1')

axes[1].plot(df_price.index, df_price.Asset2)
axes[1].set_ylabel('Asset2')

axes[2].plot(df_price.index, df_price.Asset3)
axes[2].set_ylabel('Asset3')

axes[3].plot(df_price.index, df_price.Asset4)
axes[3].set_ylabel('Asset4')

axes[4].plot(df_price.index, df_price.Asset5)
axes[4].set_ylabel('Asset5')

axes[5].plot(df_price.index, df_price.Asset6)
axes[5].set_ylabel('Asset6')

axes[6].plot(df_price.index, df_price.Asset7)
axes[6].set_ylabel('Asset7')
plt.show()<jupyter_output><empty_output><jupyter_text>* All assets index except asset 7 has a dip in 2009 great recession period, asset 7 might be some bond index* Asset 4 has decreasing prices over past 10 years, seems some commody price index  Calculate returns<jupyter_code># calculate daily asset returns
df_dret = df_price.pct_change()
df_dret.head()
# TODO drop partial months at the head and tail of the dataset
# monthly returns
df_mret = df_dret.groupby([df_dret.index.year, df_dret.index.month]).apply(lambda x: (x + 1).prod() - 1)
df_mvol = df_dret.groupby([df_dret.index.year, df_dret.index.month]).std()
df_mvol.head()
df_mret.head()
df_mret.tail()
# plot asset cumulative returns
CumulativeReturns = ((1+df_dret).cumprod()-1)
CumulativeReturns.plot()
plt.show()<jupyter_output><empty_output><jupyter_text>Calculate pairwise correlations of all assets.<jupyter_code>correlations = df_mret.corr()
#correlations. Asset1 and Asset2 has high correlation
# plot correlation matrix
names = df_mret.columns
fig = plt.figure()
ax = fig.add_subplot(111)
cax = ax.matshow(correlations, vmin=-1, vmax=1)
fig.colorbar(cax)
ticks = np.arange(0,7,1)
ax.set_xticks(ticks)
ax.set_yticks(ticks)
ax.set_xticklabels(names)
ax.set_yticklabels(names)
plt.show()<jupyter_output><empty_output><jupyter_text>Scatterplots<jupyter_code>from pandas.plotting import scatter_matrix
scatter_matrix(df_mret)
plt.show()
# return statistics
df_mret.describe()<jupyter_output><empty_output><jupyter_text>Portfolio Optimization Assumptions* Historical returns are used as the proxy for expected returns of assets* Historical covariances are used as the proxy for expected covariances between assets<jupyter_code>#calculate annulized assets return
R_exp = df_mret.mean() * 12
R_vol=df_mvol.mean() * 12
# assets risk adjusted return
R_sharpe=R_exp/(R_vol*R_vol)
CovMat_exp = np.cov(df_mret, rowvar=False)
def port_return(w, R):
    return np.sum(w * R)
n_assets = df_mret.columns.size
w = np.array([1./n_assets] * n_assets)
# mean risk free rate
rf_mean=df_rf.groupby([df_rf.index.year,df_rf.index.month]).mean().mean()
print(rf_mean)
# simple equal weighting allocation
port_return(w, R_exp)
def port_vol(w, Cov):
    return np.sqrt(np.dot(w.T, np.dot(Cov, w)))
port_vol(w, CovMat_exp)
def min_fun(w, R, Cov):
    return -1 * (port_return(w, R)-rf_mean) / port_vol(w, Cov)
from scipy.optimize import minimize

def port_optimize(R, Cov, min_wt = 0.05, max_wt = 0.2, req_ret = 0.05):
    """Maximize the sharpe ratio to find the optimal weights"""
    n_assets = R.size
    
    cons = ({'type': 'eq', 'fun': lambda x: np.sum(x) - 1}, 
            {'type': 'ineq', 'fun': lambda x: port_return(x, R) - req_ret})
    
    bnds = tuple((min_wt, max_wt) for x in range(n_assets))
    
    opt = minimize(min_fun,
                   x0=np.array([1./n_assets] * n_assets),
                   args=(R, Cov),
                   method='SLSQP',
                   bounds=bnds,
                   constraints=cons)

    opt_weights = opt['x']

    return  opt_weights
#Scenario 1 Assume all assets needs to be picked
opt_weight = port_optimize(R_exp, CovMat_exp, min_wt = 0.05, max_wt=1.,req_ret=0)
print(np.round(opt_weight, decimals=2))
np.sum(opt_weight)
port_return(opt_weight, R_exp)
port_vol(opt_weight, CovMat_exp)
#Scenario2 Assume the minimum annualized return requirement is 6%
opt_weight = port_optimize(R_exp, CovMat_exp, min_wt = 0.05, max_wt=1.,req_ret = 0.06)
print(np.round(opt_weight, decimals=2))
print(port_return(opt_weight,R_exp))
print(port_vol(opt_weight,CovMat_exp))
# backtesting three strategies. Assume we use the asset allocation weighting from the beginning of the performance window,
# and rebalance once every month. We can compare which strategy produced the best cumulative return
#strategy1
opt_weight = port_optimize(R_exp, CovMat_exp, min_wt = 0.05, max_wt=1.,req_ret=0)
print(np.round(opt_weight,decimals=2))
df_port_mret = pd.DataFrame(index=df_mret.index)
df_port_mret['strat1'] = np.dot(df_mret, opt_weight)
#strategy2
opt_weight = port_optimize(R_exp, CovMat_exp, min_wt = 0.05, max_wt=1.,req_ret=0.06)
print(np.round(opt_weight,decimals=2))
df_port_mret['strat2'] = np.dot(df_mret, opt_weight)
#strategy3 equal weighted
opt_weight=np.array([1/7]*7)
print(np.round(opt_weight, decimals=2))
df_port_mret['strat3'] = np.dot(df_mret, opt_weight)
# Strategy 4: Risk Parity
df_rolling_mvol = df_mret.rolling(window=12).std() * 12
# Cheat a little. Use the first available month of standard deviations to backfill earlier months with NAs
filler = df_rolling_mvol.iloc[11:12].values.flatten()
for i in range(11):
    df_rolling_mvol.iloc[i] = filler
inv_vol = (1 / df_rolling_mvol)
row_sum = inv_vol.sum(axis=1).values
df_wts = inv_vol.divide(row_sum, axis=0)
df_wts.plot()
df_port_mret['strat4'] = (df_mret * df_wts).sum(axis=1)
((df_port_mret + 1).cumprod() - 1).plot()
(df_port_mret.mean() * 12) / (df_port_mret.std() * 12)<jupyter_output><empty_output><jupyter_text>* Stratrgy 2 has largest cumulative,because we have set up the minimal return requirement, but express higher variance compared to strategy 1. * Stragegy 1 has less volatility in return with minder cumulative return compared to strategy2. * Simple equal weighting Allocation has the worst risk adjusted return.<jupyter_code># calculate max drawdown curves
cum_max = df_mret.iloc[:,7:10].max()
cum_min = min(df_mret.iloc[:,7:10])
print(cum_max,cum_min)<jupyter_output>mCumulativeReturns1 CumulativeReturns2
{'fraction_non_alphanumeric': 0.12384259259259259, 'fraction_numerical': 0.022119341563786008, 'mean_word_length': 5.821929824561403, 'pattern_counts': {'":': 0, '<': 40, '<?xml version=': 0, '>': 40, 'https://': 1, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '36750', 'license': 'MIT', 'n_tokens_mistral': 2981, 'n_tokens_neox': 2822, 'n_words': 671, 'path': 'asset_allocation.ipynb', 'repo_name': 'ruiyiz/asset_allocation', 'size': '7776'}