diff options
| -rw-r--r-- | python/collateral_calc.py | 17 | ||||
| -rw-r--r-- | sql/dawn.sql | 9 |
2 files changed, 19 insertions, 7 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index 4ac0159b..759f884b 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -127,7 +127,7 @@ def sg_collateral(d): df_activity = (df_activity.loc[df_activity['Record Type'] == "VM"]. set_index("Ticket Reference")) df_margin = pd.read_csv(DAILY_DIR / "SG_reports" / f"{d:%Y%m%d}_OTC_MARGIN_EX_DEF.csv", - usecols=["Currency", "Total Excess Deficit"]) + usecols=["Currency", "SG IMR"]) df_position = df_position.set_index("Ticket Reference") # expired_trades # df_position = df_position.append( @@ -157,7 +157,7 @@ def sg_collateral(d): del positions['security_desc'] positions = positions.set_index(['index_type', 'series', 'tenor']) df = positions.join(df) - d = {} + def f(g): g.Collateral = g.Collateral * g.notional / g.notional.sum() return g @@ -173,10 +173,12 @@ def sg_collateral(d): 'SER_IGCURVE': 'SER_IGCVECSH', 'HYOPTDEL': 'HYCDSCSH', 'IGOPTDEL': 'IGCDSCSH'}) - df_margin['Strategy'] = 'M_CASH_CASH' - df_margin = df_margin.rename(columns={'Total Excess Deficit': 'Amount'}) - df_margin.Amount *= -1. - return pd.concat([df, df_margin]) + df_margin['account'] = 'SGNSCLMASW' + df_margin = df_margin.rename(columns={'SG IMR': 'amount', + 'Currency': 'currency'}) + df_margin['date'] = d + df_margin.to_sql("fcm_im", dbengine("dawndb"), if_exists='append', index=False) + return df def ms_collateral(d): @@ -201,7 +203,7 @@ def ms_collateral(d): df.columns = col_names return pd.concat([df, pd.DataFrame. - from_records([('M_CASH_CASH', -collat - df.Amount.sum(), "USD")], + from_records([('M_CSH_CASH', -collat - df.Amount.sum(), "USD")], columns=col_names)]) @@ -275,5 +277,6 @@ if __name__ == "__main__": except FileNotFoundError as e: logging.info(e) df_gs = gs_collateral(d - BDay()) + breakpoint() account = get_account('ghorel@lmcg.com') send_email(account, df_ms, df_sg, df_gs) diff --git a/sql/dawn.sql b/sql/dawn.sql index 5bc02bdc..cdd66334 100644 --- a/sql/dawn.sql +++ b/sql/dawn.sql @@ -1427,3 +1427,12 @@ CREATE TABLE tranche_marks( corr_detach float, index_duration float, PRIMARY KEY(date, id)) + + +CREATE TABLE fcm_im( + date date NOT NULL, + account text NOT NULL, -- REFERENCES accounts(cash_account) + currency currency NOT NULL, + amount float NOT NULL, + PRIMARY KEY(date, account, currency) +) |