2 files changed, 11 insertions, 0 deletions
diff --git a/python/collateral/gs.py b/python/collateral/gs.py
index b4889677..e2988104 100644
--- a/python/collateral/gs.py
+++ b/python/collateral/gs.py
@@ -51,6 +51,10 @@ def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs):
     df = df[["folder", "NPV (USD)", "Initial Margin Required"]]
     df = df.groupby("folder", dropna=False).sum()
     df = df.sum(axis=1).to_frame(name="Amount")
+    # handle missing ids (allocate all to TCSH)
+    df = df.reset_index()
+    df.loc[df.folder.isnull(), "folder"] = "TCSH"
+    df = df.groupby("folder").sum()
     df["Currency"] = "USD"
     df = df.reset_index()
     df.columns = ["Strategy", "Amount", "Currency"]
diff --git a/python/collateral/ms.py b/python/collateral/ms.py
index e92be447..794c4e02 100644
--- a/python/collateral/ms.py
+++ b/python/collateral/ms.py
@@ -1,6 +1,7 @@
 import pandas as pd
 from . import DAILY_DIR
 from exchangelib import FileAttachment
+from serenitas.analytics.utils import get_fx
 
 paths = {
     "Serenitas": ["NYops", "Margin calls MS"],
@@ -55,6 +56,11 @@ def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs):
     df_fx = pd.read_excel(
         DAILY_DIR / fund / "MS_reports" / f"Trade_Detail_FX_{d:%Y%m%d}.xls"
     )
+    net_fx_exposure = (
+        df_fx.loc[df_fx.buy_ccy == "EUR", "amt_buy_ccy"].sum()
+        - df_fx.loc[df_fx.sell_ccy == "EUR", "amt_sell_ccy"].sum()
+    )
+    fx_ia = net_fx_exposure * 0.05 * get_fx(d, "EUR")
     df = pd.concat([df, df_fx])
     # df = df.dropna(subset=["trade_ccy"])
     df = df.merge(dawn_trades, how="left", left_on="trade_id", right_on="cpty_id")
@@ -65,6 +71,7 @@ def collateral(d, dawn_trades, *, fund="Serenitas", **kwargs):
     df["Currency"] = "USD"
     df = df.reset_index()
     df.columns = ["Strategy", "Amount", "Currency"]
+    df.loc[df.Strategy == "TCSH", "Amount"] -= fx_ia
     df = df.append(
         {
             "Strategy": "M_CSH_CASH",