def get_single_commodity(start, end, ticker, has_INV=False):
index_data = pd.DataFrame(dbFetch.get_index_all()).set_index("Dates")
market = index_data[index_data["name"] == ticker][start:end]
market["VOL/OPI"] = market["volume"] / market["opi"]
market["mid_price"] = 0.5 * (market["open"] + market["close"])
market = market[["mid_price", "opi", "volume", "r1", "r2", "VOL/OPI"]]
if has_INV:
inv_df = pd.DataFrame(
dbFetch.get_historical_inventory()).set_index("Dates")
inventory = inv_df.loc[inv_df["Product"].str.upper() ==
ticker][start:end]["INV"]
market = market.join(inventory, how="left")
return market
def gen_RB_spread(start, end):
#generate bean and meal and oil comparison
myFmt = mdates.DateFormatter('%y/%m') # Dates format
RB_Ticker = "RB"
I_Ticker = "I"
J_Ticker = "J"
#Get raw market data
index_data = pd.DataFrame(dbFetch.get_index_all()).set_index("Dates")
inv_df = pd.DataFrame(
dbFetch.get_historical_inventory()).set_index("Dates")
RB_market = index_data[index_data["name"] == RB_Ticker][start:end]
RB_market["mid_price"] = 0.5 * (RB_market["open"] + RB_market["close"])
RB_market["mid_price"] = RB_market["mid_price"] / RB_market["mid_price"][0]
RB_market = RB_market[["mid_price", "r1"]]
Inventory = inv_df.loc[inv_df["Product"].str.upper() ==
RB_Ticker][start:end]["INV"]
RB_market = RB_market.join(Inventory, how="left").fillna(0)
I_market = index_data[index_data["name"] == I_Ticker][start:end]
I_market["mid_price"] = 0.5 * (I_market["open"] + I_market["close"])
I_market["mid_price"] = I_market["mid_price"] / I_market["mid_price"][0]
I_market = I_market[["mid_price", "r1"]]
Inventory = inv_df.loc[inv_df["Product"].str.upper() ==
I_Ticker][start:end]["INV"]
I_market = I_market.join(Inventory, how="left").fillna(0)
J_market = index_data[index_data["name"] == J_Ticker][start:end]
J_market["mid_price"] = 0.5 * (J_market["open"] + J_market["close"])
J_market["mid_price"] = J_market["mid_price"] / J_market["mid_price"][0]
J_market = J_market[["mid_price", "r1"]]
Inventory = inv_df.loc[inv_df["Product"].str.upper() ==
J_Ticker][start:end]["INV"]
J_market = J_market.join(Inventory, how="left").fillna(0)
# Start plotting
#Fig1: Bean and Bean Meal spread plot
#Subplot 1,2: spread and spread distribution
fig, axes = plt.subplots(nrows=8, ncols=2, figsize=(10, 20))
RB_market["RB_I_Spread"] = RB_market["mid_price"] - I_market["mid_price"]
axes[0, 0].plot(RB_market["RB_I_Spread"], color='C0', label="RB-I")
axes[0, 0].legend()
axes[0, 0].xaxis.set_major_formatter(myFmt)
axes[0, 1].hist(RB_market["RB_I_Spread"],
bins=50,
color='C1',
label="RB_I_Spread")
axes[0, 1].axvline(RB_market["RB_I_Spread"][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[0, 1].get_ylim()
pct_rank = RB_market["RB_I_Spread"].sort_values().values.searchsorted(
RB_market["RB_I_Spread"][-1]) / len(RB_market["RB_I_Spread"])
axes[0, 1].text(
RB_market["RB_I_Spread"][-1], top * 0.9,
'Current:{:.1},\nPct:{:.1%}'.format(RB_market["RB_I_Spread"][-1],
pct_rank))
RB_market["RB_J_Spread"] = RB_market["mid_price"] - J_market["mid_price"]
axes[1, 0].plot(RB_market["RB_J_Spread"], color='C0', label="RB-J")
axes[1, 0].legend()
axes[1, 0].xaxis.set_major_formatter(myFmt)
axes[1, 1].hist(RB_market["RB_J_Spread"],
bins=50,
color='C1',
label="RB_J_Spread")
axes[1, 1].axvline(RB_market["RB_J_Spread"][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[1, 1].get_ylim()
pct_rank = RB_market["RB_J_Spread"].sort_values().values.searchsorted(
RB_market["RB_J_Spread"][-1]) / len(RB_market["RB_J_Spread"])
axes[1, 1].text(
RB_market["RB_J_Spread"][-1], top * 0.9,
'Current:{:.1},\nPct:{:.1%}'.format(RB_market["RB_J_Spread"][-1],
pct_rank))
#Subplot 3,4: single commodity price and roll yield
axes[2, 0].plot(RB_market["mid_price"], color='C0', label="RB")
ax2 = axes[2, 0].twinx()
ax2.bar(RB_market.index,
RB_market["r1"],
alpha=0.5,
width=3,
color='C4',
label="RollYield")
axes[2, 0].xaxis.set_major_formatter(myFmt)
axes[2, 0].legend()
axes[2, 0].xaxis.set_major_formatter(myFmt)
R1 = RB_market["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1]) / len(R1)
axes[2, 1].hist(R1, bins=50, color='C4', alpha=0.65)
axes[2, 1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[2, 1].get_ylim()
axes[2, 1].text(R1[-1] * 1.1, top * 0.9,
'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1], pct_rank))
axes[3, 0].plot(I_market["mid_price"], color='C0', label="IRON")
ax2 = axes[3, 0].twinx()
ax2.bar(I_market.index,
I_market["r1"],
alpha=0.5,
width=3,
color='C4',
label="RollYield")
axes[3, 0].xaxis.set_major_formatter(myFmt)
axes[3, 0].legend()
axes[3, 0].xaxis.set_major_formatter(myFmt)
R1 = I_market["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1]) / len(R1)
axes[3, 1].hist(R1, bins=50, color='C4', alpha=0.65)
axes[3, 1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[3, 1].get_ylim()
axes[3, 1].text(R1[-1] * 1.1, top * 0.9,
'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1], pct_rank))
axes[4, 0].plot(J_market["mid_price"], color='C0', label="J")
ax2 = axes[4, 0].twinx()
ax2.bar(J_market.index,
J_market["r1"],
alpha=0.5,
width=3,
color='C4',
label="RollYield")
axes[4, 0].xaxis.set_major_formatter(myFmt)
axes[4, 0].legend()
axes[4, 0].xaxis.set_major_formatter(myFmt)
R1 = J_market["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1]) / len(R1)
axes[4, 1].hist(R1, bins=50, color='C4', alpha=0.65)
axes[4, 1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[4, 1].get_ylim()
axes[4, 1].text(R1[-1] * 1.1, top * 0.9,
'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1], pct_rank))
axes[5, 0].bar(RB_market.index,
RB_market["INV"],
alpha=0.5,
width=3,
color='C4',
label="RB_INV")
axes[5, 0].legend()
axes[5, 0].xaxis.set_major_formatter(myFmt)
pct_rank = RB_market["INV"].sort_values().values.searchsorted(
RB_market["INV"][-1]) / len(RB_market["INV"])
axes[5, 1].hist(RB_market["INV"], bins=50, color='C3', alpha=0.65)
axes[5, 1].axvline(RB_market["INV"][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[5, 1].get_ylim()
axes[5, 1].text(
RB_market["INV"][-1] * 1.1, top * 0.9,
'Current:{:,},\nPct:{:.1%}'.format(RB_market["INV"][-1], pct_rank))
axes[6, 0].bar(I_market.index,
I_market["INV"],
alpha=0.5,
width=3,
color='C4',
label="I_INV")
axes[6, 0].legend()
axes[6, 0].xaxis.set_major_formatter(myFmt)
pct_rank = I_market["INV"].sort_values().values.searchsorted(
I_market["INV"][-1]) / len(I_market["INV"])
axes[6, 1].hist(I_market["INV"], bins=50, color='C3', alpha=0.65)
axes[6, 1].axvline(I_market["INV"][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[6, 1].get_ylim()
axes[6, 1].text(
I_market["INV"][-1] * 1.1, top * 0.9,
'Current:{:,},\nPct:{:.1%}'.format(I_market["INV"][-1], pct_rank))
axes[7, 0].bar(J_market.index,
J_market["INV"],
alpha=0.5,
width=3,
color='C4',
label="J_INV")
axes[7, 0].legend()
axes[7, 0].xaxis.set_major_formatter(myFmt)
pct_rank = J_market["INV"].sort_values().values.searchsorted(
J_market["INV"][-1]) / len(J_market["INV"])
axes[7, 1].hist(J_market["INV"], bins=50, color='C3', alpha=0.65)
axes[7, 1].axvline(J_market["INV"][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[7, 1].get_ylim()
axes[7, 1].text(
J_market["INV"][-1] * 1.1, top * 0.9,
'Current:{:,},\nPct:{:.1%}'.format(J_market["INV"][-1], pct_rank))
fig.suptitle('RB/I/J Spread Strat', y=0.9)
return fig
def gen_HC_spread(start,end):
#generate bean and meal and oil comparison
myFmt = mdates.DateFormatter('%y/%m') # Dates format
Ticker_1 = "HC"
Ticker_2 = "RB"
#Get raw market data
index_data = pd.DataFrame(dbFetch.get_index_all()).set_index("Dates")
inv_df = pd.DataFrame(dbFetch.get_historical_inventory()).set_index("Dates")
market_1 = index_data[index_data["name"]==Ticker_1][start:end]
market_1["mid_price"] = 0.5*(market_1["open"]+market_1["close"])
market_1["mid_price"] = market_1["mid_price"]/market_1["mid_price"][0]
market_1 = market_1[["mid_price","r1"]]
Inventory = inv_df.loc[inv_df["Product"].str.upper()==Ticker_1][start:end]["INV"]
market_1 = market_1.join( Inventory, how="left" ).fillna(0)
market_2 = index_data[index_data["name"]==Ticker_2][start:end]
market_2["mid_price"] = 0.5*(market_2["open"]+market_2["close"])
market_2["mid_price"] = market_2["mid_price"]/market_2["mid_price"][0]
market_2 = market_2[["mid_price","r1"]]
Inventory = inv_df.loc[inv_df["Product"].str.upper()==Ticker_2][start:end]["INV"]
market_2 = market_2.join(Inventory,how="left").fillna(0)
# Start plotting
#Fig1: Bean and Bean Meal spread plot
#Subplot 1,2: spread and spread distribution
spread_name = Ticker_1+"_"+Ticker_2+"_Spread"
fig, axes = plt.subplots(nrows=5, ncols=2,figsize=(10,15))
market_1[spread_name] = market_1["mid_price"]-market_2["mid_price"]
axes[0,0].plot(market_1[spread_name],color='C0', label=Ticker_1+"-"+Ticker_2)
axes[0,0].legend()
axes[0,1].hist(market_1[spread_name],bins=50,color='C1', label=spread_name)
axes[0,1].axvline(market_1[spread_name][-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[0,1].get_ylim()
pct_rank = market_1[spread_name].sort_values().values.searchsorted(market_1[spread_name][-1])/len(market_1[spread_name])
axes[0,1].text(market_1[spread_name][-1], top*0.9, 'Current:{:.1},\nPct:{:.1%}'.format(market_1[spread_name][-1],pct_rank))
#Subplot 3,4: single commodity price and roll yield
axes[1,0].plot(market_1["mid_price"],color='C0', label=Ticker_1)
ax2 = axes[1,0].twinx()
ax2.bar(market_1.index,market_1["r1"],alpha=0.5,width=3,color='C4', label="RollYield")
axes[1,0].xaxis.set_major_formatter(myFmt)
axes[1,0].legend()
R1 = market_1["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1])/len(R1)
axes[1,1].hist(R1,bins=50,color='C4',alpha=0.65)
axes[1,1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[1,1].get_ylim()
axes[1,1].text(R1[-1]*1.1, top*0.9, 'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1],pct_rank))
axes[2,0].plot(market_2["mid_price"],color='C0', label=Ticker_2)
ax2 = axes[2,0].twinx()
ax2.bar(market_2.index,market_2["r1"],alpha=0.5,width=3,color='C4', label="RollYield")
axes[2,0].xaxis.set_major_formatter(myFmt)
axes[2,0].legend()
R1 = market_2["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1])/len(R1)
axes[2,1].hist(R1,bins=50,color='C4',alpha=0.65)
axes[2,1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[2,1].get_ylim()
axes[2,1].text(R1[-1]*1.1, top*0.9, 'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1],pct_rank))
axes[3,0].bar(market_1.index,market_1["INV"],alpha=0.5,width=3,color='C4', label=Ticker_1+"_INV")
axes[3,0].legend()
pct_rank = market_1["INV"].sort_values().values.searchsorted(market_1["INV"][-1])/len(market_1["INV"])
axes[3,1].hist(market_1["INV"],bins=50,color='C3',alpha=0.65)
axes[3,1].axvline(market_1["INV"][-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[3,1].get_ylim()
axes[3,1].text(market_1["INV"][-1]*1.1, top*0.9, 'Current:{:,},\nPct:{:.1%}'.format(market_1["INV"][-1],pct_rank))
axes[4,0].bar(market_2.index,market_2["INV"],alpha=0.5,width=3,color='C4', label=Ticker_2+"_INV")
axes[4,0].legend()
pct_rank = market_2["INV"].sort_values().values.searchsorted(market_2["INV"][-1])/len(market_2["INV"])
axes[4,1].hist(market_2["INV"],bins=50,color='C3',alpha=0.65)
axes[4,1].axvline(market_2["INV"][-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[4,1].get_ylim()
axes[4,1].text(market_2["INV"][-1]*1.1, top*0.9, 'Current:{:,},\nPct:{:.1%}'.format(market_2["INV"][-1],pct_rank))
fig.suptitle(Ticker_1+"/"+Ticker_2+" Spread Strat",y=0.9)
return fig
def gen_RM_spread(start, end):
#generate bean and meal and oil comparison
myFmt = mdates.DateFormatter('%y/%m') # Dates format
RM_Ticker = "RM"
OI_Ticker = "OI"
#Get raw market data
index_data = pd.DataFrame(dbFetch.get_index_all()).set_index("Dates")
inv_df = pd.DataFrame(
dbFetch.get_historical_inventory()).set_index("Dates")
RM_market = index_data[index_data["name"] == RM_Ticker][start:end]
RM_market["mid_price"] = 0.5 * (RM_market["open"] + RM_market["close"])
RM_market["mid_price"] = RM_market["mid_price"] / RM_market["mid_price"][0]
RM_market = RM_market[["mid_price", "r1"]]
B_inventory = inv_df.loc[inv_df["Product"].str.upper() ==
RM_Ticker][start:end]["INV"]
RM_market = RM_market.join(B_inventory, how="left")
OI_market = index_data[index_data["name"] == OI_Ticker][start:end]
OI_market["mid_price"] = 0.5 * (OI_market["open"] + OI_market["close"])
OI_market["mid_price"] = OI_market["mid_price"] / OI_market["mid_price"][0]
OI_market = OI_market[["mid_price", "r1"]]
M_inventory = inv_df.loc[inv_df["Product"].str.upper() ==
OI_Ticker][start:end]["INV"]
OI_market = OI_market.join(M_inventory, how="left")
# Start plotting
#Fig1: Bean and Bean Meal spread plot
#Subplot 1,2: spread and spread distribution
fig, axes = plt.subplots(nrows=5, ncols=2, figsize=(10, 20))
RM_market[
"RM_OIL_Spread"] = RM_market["mid_price"] - OI_market["mid_price"]
axes[0, 0].plot(RM_market["RM_OIL_Spread"], color='C0', label="RM-RMOIL")
axes[0, 0].legend()
axes[0, 0].xaxis.set_major_formatter(myFmt)
axes[0, 1].hist(RM_market["RM_OIL_Spread"],
bins=50,
color='C1',
label="RM_OIL_Spread")
axes[0, 1].axvline(RM_market["RM_OIL_Spread"][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[0, 1].get_ylim()
pct_rank = RM_market["RM_OIL_Spread"].sort_values().values.searchsorted(
RM_market["RM_OIL_Spread"][-1]) / len(RM_market["RM_OIL_Spread"])
axes[0, 1].text(
RM_market["RM_OIL_Spread"][-1], top * 0.9,
'Current:{:.1},\nPct:{:.1%}'.format(RM_market["RM_OIL_Spread"][-1],
pct_rank))
#Subplot 3,4: single commodity price and roll yield
axes[1, 0].plot(RM_market["mid_price"], color='C0', label="RM")
ax2 = axes[1, 0].twinx()
ax2.bar(RM_market.index,
RM_market["r1"],
alpha=0.5,
width=3,
color='C4',
label="RollYield")
axes[1, 0].xaxis.set_major_formatter(myFmt)
axes[1, 0].legend()
axes[1, 0].xaxis.set_major_formatter(myFmt)
R1 = RM_market["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1]) / len(R1)
axes[1, 1].hist(R1, bins=50, color='C4', alpha=0.65)
axes[1, 1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[1, 1].get_ylim()
axes[1, 1].text(R1[-1] * 1.1, top * 0.9,
'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1], pct_rank))
axes[2, 0].plot(OI_market["mid_price"], color='C0', label="RM OIL")
ax2 = axes[2, 0].twinx()
ax2.bar(OI_market.index,
OI_market["r1"],
alpha=0.5,
width=3,
color='C4',
label="RollYield")
axes[2, 0].xaxis.set_major_formatter(myFmt)
axes[2, 0].legend()
axes[2, 0].xaxis.set_major_formatter(myFmt)
R1 = OI_market["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1]) / len(R1)
axes[2, 1].hist(R1, bins=50, color='C4', alpha=0.65)
axes[2, 1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[2, 1].get_ylim()
axes[2, 1].text(R1[-1] * 1.1, top * 0.9,
'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1], pct_rank))
axes[3, 0].bar(RM_market.index,
RM_market["INV"],
alpha=0.5,
width=3,
color='C4',
label="RM_INV")
axes[3, 0].legend()
axes[3, 0].xaxis.set_major_formatter(myFmt)
pct_rank = RM_market["INV"].sort_values().values.searchsorted(
RM_market["INV"][-1]) / len(RM_market["INV"])
axes[3, 1].hist(RM_market["INV"], bins=50, color='C3', alpha=0.65)
axes[3, 1].axvline(RM_market["INV"][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[3, 1].get_ylim()
axes[3, 1].text(
RM_market["INV"][-1] * 1.1, top * 0.9,
'Current:{:,},\nPct:{:.1%}'.format(RM_market["INV"][-1], pct_rank))
axes[4, 0].bar(OI_market.index,
OI_market["INV"],
alpha=0.5,
width=3,
color='C4',
label="OIL_INV")
axes[4, 0].legend()
axes[4, 0].xaxis.set_major_formatter(myFmt)
pct_rank = OI_market["INV"].sort_values().values.searchsorted(
OI_market["INV"][-1]) / len(OI_market["INV"])
axes[4, 1].hist(OI_market["INV"], bins=50, color='C3', alpha=0.65)
axes[4, 1].axvline(OI_market["INV"][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[4, 1].get_ylim()
axes[4, 1].text(
OI_market["INV"][-1] * 1.1, top * 0.9,
'Current:{:,},\nPct:{:.1%}'.format(OI_market["INV"][-1], pct_rank))
fig.suptitle('RMMeal/RMOIL Spread Strat', y=0.9)
return fig
handler = s_handler.stock_handler()
# Date format setting
pd.options.mode.chained_assignment = None # default='warn'
end = dt.date.today() - dt.timedelta(days=1)
Relative_Switch = True
commodity_start = dt.datetime(2019, 1, 1).date()
stock_start = dt.datetime(2019, 1, 1).date()
spread_start = dt.datetime(2019, 1, 1).date()
# Data Preparition
stock_data_all = handler._gen_all_ticker_df(stock_start, end)
index_data = pd.DataFrame(dbFetch.get_index_all()).set_index("Dates")
panel = index_data.loc[end]
inv_df = pd.DataFrame(dbFetch.get_historical_inventory()).set_index("Dates")
stock_data = stock.get_stock_sector_index_V2(stock_data_all, True, {}, [],
Relative_Switch)
in_df = ind_tracking.get_industry_data_V2(stock_data_all, True)
# General Plotting
S_I_plot.plot_stock_industry(in_df)
C_S_plot.plot_commodity_sector(index_data, stock_start)
D_S_plot.plot_product_figure(stock_data_all, Relative_Switch)
C_P_plot.plot_commodity_panel(panel)
S_C_plot.plot_single_commodity(index_data, inv_df, stock_data, commodity_start)
# Spread Plotting
INF_Fig = Inflation_Spread.gen_INF_spread(spread_start, end)
#Bean_Fig = Bean_Spread.gen_bean_spread(spread_start,end)
#RM_Fig = RM_Spread.gen_RM_spread(spread_start,end)
def gen_bean_spread(start,end):
#generate bean and meal and oil comparison
myFmt = mdates.DateFormatter('%y/%m') # Dates format
B_Ticker = "A"
M_Ticker = "M"
Y_Ticker = "Y"
#Get raw market data
index_data = pd.DataFrame(dbFetch.get_index_all()).set_index("Dates")
inv_df = pd.DataFrame(dbFetch.get_historical_inventory()).set_index("Dates")
B_market = index_data[index_data["name"]==B_Ticker][start:end]
B_market["mid_price"] = 0.5*(B_market["open"]+B_market["close"])
B_market["mid_price"] = B_market["mid_price"]/B_market["mid_price"][0]
B_market = B_market[["mid_price","r1"]]
B_inventory = inv_df.loc[inv_df["Product"].str.upper()==B_Ticker][start:end]["INV"]
B_market = B_market.join(B_inventory,how="left")
M_market = index_data[index_data["name"]==M_Ticker][start:end]
M_market["mid_price"] = 0.5*(M_market["open"]+M_market["close"])
M_market["mid_price"] = M_market["mid_price"]/M_market["mid_price"][0]
M_market = M_market[["mid_price","r1"]]
M_inventory = inv_df.loc[inv_df["Product"].str.upper()==M_Ticker][start:end]["INV"]
M_market = M_market.join(M_inventory,how="left")
Y_market = index_data[index_data["name"]==Y_Ticker][start:end]
Y_market["mid_price"] = 0.5*(Y_market["open"]+Y_market["close"])
Y_market["mid_price"] = Y_market["mid_price"]/Y_market["mid_price"][0]
Y_market = Y_market[["mid_price","r1"]]
Y_inventory = inv_df.loc[inv_df["Product"].str.upper()==Y_Ticker][start:end]["INV"]
Y_market = Y_market.join(Y_inventory,how="left")
# Start plotting
#Fig1: Bean and Bean Meal spread plot
#Subplot 1-6: spread and spread distribution
fig, axes = plt.subplots(nrows=9, ncols=2,figsize=(10,36))
B_market["B_M_Spread"] = B_market["mid_price"]-M_market["mid_price"]
axes[0,0].plot(B_market["B_M_Spread"],color='C0', label="Bean-Meal")
axes[0,0].legend()
axes[0,1].hist(B_market["B_M_Spread"],bins=50,color='C1', label="B_M_Spread")
axes[0,1].axvline(B_market["B_M_Spread"][-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[0,1].get_ylim()
pct_rank = B_market["B_M_Spread"].sort_values().values.searchsorted(B_market["B_M_Spread"][-1])/len(B_market["B_M_Spread"])
axes[0,1].text(B_market["B_M_Spread"][-1], top*0.9, 'Current:{:.1},\nPct:{:.1%}'.format(B_market["B_M_Spread"][-1],pct_rank))
B_market["B_Y_Spread"] = B_market["mid_price"]-Y_market["mid_price"]
axes[1,0].plot(B_market["B_Y_Spread"],color='C0', label="Bean-OIL")
axes[1,0].legend()
axes[1,1].hist(B_market["B_Y_Spread"],bins=50,color='C1', label="Spread")
axes[1,1].axvline(B_market["B_Y_Spread"][-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[1,1].get_ylim()
pct_rank = B_market["B_Y_Spread"].sort_values().values.searchsorted(B_market["B_Y_Spread"][-1])/len(B_market["B_Y_Spread"])
axes[1,1].text(B_market["B_Y_Spread"][-1], top*0.9, 'Current:{:.1},\nPct:{:.1%}'.format(B_market["B_Y_Spread"][-1],pct_rank))
M_market["M_Y_Spread"] = M_market["mid_price"]-Y_market["mid_price"]
axes[2,0].plot(M_market["M_Y_Spread"],color='C0', label="Meal-OIL")
axes[2,0].legend()
axes[2,1].hist(M_market["M_Y_Spread"],bins=50,color='C1', label="Spread")
axes[2,1].axvline(M_market["M_Y_Spread"][-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[2,1].get_ylim()
pct_rank = M_market["M_Y_Spread"].sort_values().values.searchsorted(M_market["M_Y_Spread"][-1])/len(M_market["M_Y_Spread"])
axes[2,1].text(M_market["M_Y_Spread"][-1], top*0.9, 'Current:{:.1},\nPct:{:.1%}'.format(M_market["M_Y_Spread"][-1],pct_rank))
#Subplot 3,4: single commodity price and roll yield
axes[3,0].plot(B_market["mid_price"],color='C0', label="Bean")
ax2 = axes[3,0].twinx()
ax2.bar(B_market.index,B_market["r1"],alpha=0.5,width=3,color='C4', label="RollYield")
axes[3,0].xaxis.set_major_formatter(myFmt)
axes[3,0].legend()
R1 = B_market["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1])/len(R1)
axes[3,1].hist(R1,bins=50,color='C4',alpha=0.65)
axes[3,1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[3,1].get_ylim()
axes[3,1].text(R1[-1]*1.1, top*0.9, 'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1],pct_rank))
axes[4,0].plot(M_market["mid_price"],color='C0', label="Bean Meal")
ax2 = axes[4,0].twinx()
ax2.bar(M_market.index,M_market["r1"],alpha=0.5,width=3,color='C4', label="RollYield")
axes[4,0].xaxis.set_major_formatter(myFmt)
axes[4,0].legend()
R1 = M_market["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1])/len(R1)
axes[4,1].hist(R1,bins=50,color='C4',alpha=0.65)
axes[4,1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[4,1].get_ylim()
axes[4,1].text(R1[-1]*1.1, top*0.9, 'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1],pct_rank))
axes[5,0].plot(Y_market["mid_price"],color='C0', label="Bean Oil")
ax2 = axes[5,0].twinx()
ax2.bar(Y_market.index,Y_market["r1"],alpha=0.5,width=3,color='C4', label="RollYield")
axes[5,0].xaxis.set_major_formatter(myFmt)
axes[5,0].legend()
R1 = Y_market["r1"].dropna()
pct_rank = R1.sort_values().values.searchsorted(R1[-1])/len(R1)
axes[5,1].hist(R1,bins=50,color='C4',alpha=0.65)
axes[5,1].axvline(R1[-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[5,1].get_ylim()
axes[5,1].text(R1[-1]*1.1, top*0.9, 'Current:{:.0%},\nPct:{:.1%}'.format(R1[-1],pct_rank))
axes[6,0].bar(B_market.index,B_market["INV"],alpha=0.5,width=3,color='C4', label="Bean_INV")
axes[6,0].legend()
pct_rank = B_market["INV"].sort_values().values.searchsorted(B_market["INV"][-1])/len(B_market["INV"])
axes[6,1].hist(B_market["INV"],bins=50,color='C3',alpha=0.65)
axes[6,1].axvline(B_market["INV"][-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[6,1].get_ylim()
axes[6,1].text(B_market["INV"][-1]*1.1, top*0.9, 'Current:{:,},\nPct:{:.1%}'.format(B_market["INV"][-1],pct_rank))
axes[7,0].bar(M_market.index,M_market["INV"],alpha=0.5,width=3,color='C4', label="Meal_INV")
axes[7,0].legend()
pct_rank = M_market["INV"].sort_values().values.searchsorted(M_market["INV"][-1])/len(M_market["INV"])
axes[7,1].hist(M_market["INV"],bins=50,color='C3',alpha=0.65)
axes[7,1].axvline(M_market["INV"][-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[7,1].get_ylim()
axes[7,1].text(M_market["INV"][-1]*1.1, top*0.9, 'Current:{:,},\nPct:{:.1%}'.format(M_market["INV"][-1],pct_rank))
axes[8,0].bar(Y_market.index,Y_market["INV"],alpha=0.5,width=3,color='C4', label="OIL_INV")
axes[8,0].legend()
pct_rank = Y_market["INV"].sort_values().values.searchsorted(Y_market["INV"][-1])/len(Y_market["INV"])
axes[8,1].hist(Y_market["INV"],bins=50,color='C3',alpha=0.65)
axes[8,1].axvline(Y_market["INV"][-1], color='k', linestyle='dashed', linewidth=3)
bottom, top = axes[8,1].get_ylim()
axes[8,1].text(Y_market["INV"][-1]*1.1, top*0.9, 'Current:{:,},\nPct:{:.1%}'.format(Y_market["INV"][-1],pct_rank))
fig.suptitle('Bean/BeanMeal/BeanOIL Spread Strat',y=0.9)
return fig
def gen_INF_spread(start, end):
#generate bean and meal and oil comparison
myFmt = mdates.DateFormatter('%y/%m') # Dates format
Ticker_1 = "AU"
Ticker_2 = "AG"
Ticker_3 = "T"
Ticker_4 = "CU"
#Get raw market data
index_data = pd.DataFrame(dbFetch.get_index_all()).set_index("Dates")
inv_df = pd.DataFrame(
dbFetch.get_historical_inventory()).set_index("Dates")
market_1 = index_data[index_data["name"] == Ticker_1][start:end]
market_1["mid_price"] = 0.5 * (market_1["open"] + market_1["close"])
market_1["mid_price"] = market_1["mid_price"] / market_1["mid_price"][0]
market_1 = market_1[["mid_price", "r1"]]
Inventory = inv_df.loc[inv_df["Product"].str.upper() ==
Ticker_1][start:end]["INV"]
market_1 = market_1.join(Inventory, how="left").fillna(0)
market_2 = index_data[index_data["name"] == Ticker_2][start:end]
market_2["mid_price"] = 0.5 * (market_2["open"] + market_2["close"])
market_2["mid_price"] = market_2["mid_price"] / market_2["mid_price"][0]
market_2 = market_2[["mid_price", "r1"]]
Inventory = inv_df.loc[inv_df["Product"].str.upper() ==
Ticker_2][start:end]["INV"]
market_2 = market_2.join(Inventory, how="left").fillna(0)
market_3 = index_data[index_data["name"] == Ticker_3][start:end]
market_3["T_price"] = 0.5 * (market_3["open"] + market_3["close"])
market_3["T_price"] = market_3["T_price"] / market_3["T_price"][0]
market_3 = market_3[["T_price", "r1"]]
market_1 = market_1.join(market_3[["T_price"]],
how="left").fillna(method="ffill")
market_4 = index_data[index_data["name"] == Ticker_4][start:end]
market_4["mid_price"] = 0.5 * (market_4["open"] + market_4["close"])
market_4["mid_price"] = market_4["mid_price"] / market_4["mid_price"][0]
market_4 = market_4[["mid_price", "r1"]]
Inventory = inv_df.loc[inv_df["Product"].str.upper() ==
Ticker_4][start:end]["INV"]
market_4 = market_4.join(Inventory, how="left").fillna(0)
# Start plotting
spread_name = Ticker_1 + "_" + Ticker_2 + "_Spread"
fig, axes = plt.subplots(nrows=3, ncols=2, figsize=(10, 12))
market_1[spread_name] = market_1["mid_price"] - market_2["mid_price"]
axes[0, 0].plot(market_1[spread_name],
color='C0',
label=Ticker_1 + "-" + Ticker_2)
ax2 = axes[0, 0].twinx()
ax2.plot(market_1["T_price"], color='C1', label="T_Bond")
axes[0, 0].legend()
axes[0, 0].xaxis.set_major_formatter(myFmt)
axes[0, 1].hist(market_1[spread_name],
bins=50,
color='C1',
label=spread_name)
axes[0, 1].axvline(market_1[spread_name][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[0, 1].get_ylim()
pct_rank = market_1[spread_name].sort_values().values.searchsorted(
market_1[spread_name][-1]) / len(market_1[spread_name])
axes[0, 1].text(
market_1[spread_name][-1], top * 0.9,
'Current:{:.1},\nPct:{:.1%}'.format(market_1[spread_name][-1],
pct_rank))
spread_name = Ticker_1 + "_" + Ticker_4 + "_Spread"
market_1[spread_name] = market_1["mid_price"] - market_4["mid_price"]
axes[1, 0].plot(market_1[spread_name],
color='C0',
label=Ticker_1 + "-" + Ticker_4)
ax2 = axes[1, 0].twinx()
ax2.plot(market_3["T_price"], color='C1', label="T_Bond")
axes[1, 0].legend()
axes[1, 0].xaxis.set_major_formatter(myFmt)
axes[1, 1].hist(market_1[spread_name],
bins=50,
color='C1',
label=spread_name)
axes[1, 1].axvline(market_1[spread_name][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[1, 1].get_ylim()
pct_rank = market_1[spread_name].sort_values().values.searchsorted(
market_1[spread_name][-1]) / len(market_1[spread_name])
axes[1, 1].text(
market_1[spread_name][-1], top * 0.9,
'Current:{:.1},\nPct:{:.1%}'.format(market_1[spread_name][-1],
pct_rank))
spread_name = Ticker_2 + "_" + Ticker_4 + "_Spread"
market_2[spread_name] = market_2["mid_price"] - market_4["mid_price"]
axes[2, 0].plot(market_2[spread_name],
color='C0',
label=Ticker_2 + "-" + Ticker_4)
ax2 = axes[2, 0].twinx()
ax2.plot(market_3["T_price"], color='C1', label="T_Bond")
axes[2, 0].legend()
axes[2, 0].xaxis.set_major_formatter(myFmt)
axes[2, 1].hist(market_2[spread_name],
bins=50,
color='C1',
label=spread_name)
axes[2, 1].axvline(market_2[spread_name][-1],
color='k',
linestyle='dashed',
linewidth=3)
bottom, top = axes[2, 1].get_ylim()
pct_rank = market_2[spread_name].sort_values().values.searchsorted(
market_2[spread_name][-1]) / len(market_2[spread_name])
axes[2, 1].text(
market_2[spread_name][-1], top * 0.9,
'Current:{:.1},\nPct:{:.1%}'.format(market_2[spread_name][-1],
pct_rank))
fig.suptitle("Inflation Spread Strat", y=0.9)
return fig