1 files changed, 51 insertions, 0 deletions
diff --git a/python/pnl_explain.py b/python/pnl_explain.py
index f32941b0..c768052d 100644
--- a/python/pnl_explain.py
+++ b/python/pnl_explain.py
@@ -152,6 +152,57 @@ def get_tranche_pv(
     return df
 
 
+def get_tranche_pv2(
+    start_date: datetime.date,
+    end_date: datetime.date,
+    fund: str,
+    conn: connection,
+    **kwargs
+):
+    df = pd.read_sql_query("SELECT date, tranche_id AS id, clean_nav, accrued, folder "
+                           "FROM tranche_risk "
+                           "JOIN cds ON tranche_id=id "
+                           "WHERE date BETWEEN %s and %s AND fund=%s",
+                           conn,
+                           params=(start_date, end_date, fund),
+                           parse_dates=("date", "maturity"),
+                           index_col=["date", "id"])
+    df = df.sort_index()
+    strategies = df.folder
+    df = df[["clean_nav", "accrued"]]
+    df -= df.groupby(level="id")[["clean_nav", "accrued"]].shift(fill_value=0.)
+    df = df.drop(start_date)
+    with conn.cursor() as c:
+        c.execute(
+            "SELECT termination_date AS date, cds.id, "
+            "termination_fee *(CASE WHEN currency='USD' "
+            "THEN 1. ELSE eurusd END) AS amount "
+            "FROM terminations "
+            "JOIN cds USING (dealid) "
+            "LEFT JOIN fx ON termination_date=date "
+            "WHERE termination_date BETWEEN %s AND %s "
+            "AND fund=%s",
+            (start_date, end_date, fund),
+        )
+        df_terminations = pd.DataFrame.from_records(c, columns=[desc.name for desc in c.description])
+    with conn.cursor() as c:
+        c.execute(
+            "SELECT trade_date AS date, id, "
+            "upfront * (CASE WHEN currency='USD' "
+            "THEN 1. ELSE eurusd END) AS amount "
+            "FROM cds LEFT JOIN fx ON date=trade_date "
+            "WHERE trade_date BETWEEN %s AND %s "
+            "AND swap_type='CD_INDEX_TRANCHE' AND fund=%s",
+            (start_date, end_date, fund),
+        )
+        df_upfronts = pd.DataFrame.from_records(c, columns=[desc.name for desc in c.description])
+    for df_temp in (df_terminations, df_upfronts):
+        df_temp.date = pd.to_datetime(df_temp.date)
+        df_temp.set_index(["date", "id"], inplace=True)
+    daily = pd.concat([df_terminations, df_upfronts], axis=1).sum(axis=1)
+    daily.name = 'daily'
+    return pd.concat([df, daily], axis=1).join(strategies).sort_index()
+
 def get_pv(**kwargs):
     if kwargs.pop("pnl_type") == "swaption":
         return get_swaption_pv(**kwargs)