1 files changed, 60 insertions, 0 deletions
diff --git a/python/rmbs/marketing.py b/python/rmbs/marketing.py
new file mode 100644
index 00000000..120e00bd
--- /dev/null
+++ b/python/rmbs/marketing.py
@@ -0,0 +1,60 @@
+import pandas as pd
+import datetime
+from db import dbengine
+from yieldcurve import YC
+
+def ver_one():
+    date = (datetime.date.today() + pd.offsets.BMonthEnd(-1)).date()
+    df = pd.read_sql_query("select * from risk_positions(%s, 'Subprime') where price >0 and length(identifier) = 9"
+                        , dbengine('dawndb'), params = [date], index_col=['identifier'])
+    v1_temp = {}
+    for cusip in df.index:
+        v1_temp[cusip] = pd.read_sql_query("select * from priced_percentiles where date(timestamp) = %s and cusip = %s and model_version = 1"
+                        , dbengine('dawndb'), params = [date, cusip])
+    v1 = pd.concat(v1_temp)
+    v1 = v1.set_index('cusip')
+    v1 = v1.merge(df, left_index=True, right_index=True)
+    yc = YC(evaluation_date=date)
+    v1 = v1.replace('M_MTG_GOOD','M_MTG_LMG')
+
+    v1['return'] = v1.apply(lambda df: (df.pv/(df.price/100)) ** (1/df.moddur) - 1 + yc.zero_rate(df.moddur).rate, axis = 1)
+
+    port_ret = v1.groupby('percentile').apply(wavg, 'return', 'usd_market_value')
+    strat_ret = v1.groupby(['percentile','strategy']).apply(wavg, 'return', 'usd_market_value').unstack(-1)
+    port_ret = port_ret.rename('Portfolio')
+    strat_ret = strat_ret.join(port_ret)
+
+    return strat_ret
+
+def plot_strat():
+
+    import seaborn as sns
+
+    strat_ret = ver_one()
+    strat_ret = strat_ret.rename(columns={ \
+                'M_MTG_FP': 'First Pay',\
+                'M_MTG_IO': 'Interest Only',\
+                'M_MTG_LMG': 'Long Duration Par',\
+                'M_MTG_PR': 'Pro-Rata Pay',\
+                'M_MTG_RW': 'R&W Dependent',\
+                'M_MTG_SD': 'Stepdown',\
+                'M_MTG_THRU': 'Pass-through'
+                })
+
+    sns.set(style="darkgrid")
+    ax = strat_ret.plot(figsize = [10, 3.5])
+    ax.set_ylim([-.2,.6])
+
+    vals = ax.get_yticks()
+    ax.set_yticklabels(['{:3.0f}%'.format(x*100) for x in vals])
+    ax.set_ylabel('return')
+
+    ax.legend(loc='upper center', bbox_to_anchor=(0.5, -.05),  shadow=True, ncol=3)
+
+def wavg(group, avg_name, weight_name):
+    d = group[avg_name]
+    w = group[weight_name]
+    try:
+        return (d * w).sum() / w.sum()
+    except ZeroDivisionError:
+        return d.mean()