diff options
| -rw-r--r-- | python/collateral_calc.py | 31 |
1 files changed, 25 insertions, 6 deletions
diff --git a/python/collateral_calc.py b/python/collateral_calc.py index c45bda3b..4ac0159b 100644 --- a/python/collateral_calc.py +++ b/python/collateral_calc.py @@ -12,7 +12,10 @@ from paramiko import Transport, SFTPClient from download_emails import GmailMessage DAILY_DIR = Path(os.environ["DAILY_DIR"]) - +logging.basicConfig(filename=os.path.join(os.getenv("LOG_DIR"), + 'collateral_calc.log'), + level=logging.WARNING, + format='%(asctime)s %(message)s') def get_sftp_client(): transport = Transport(('prmssp.amer.sgcib.com', 22)) @@ -22,7 +25,8 @@ def get_sftp_client(): def download_sftp_files(d=None, report_types=["OTC_CASH_ACTIVITY", "OTC_POSITIONS", - "OTC_MARGIN", "OTC_MARGIN_EX_DEF"], + "OTC_MARGIN", "OTC_MARGIN_EX_DEF", + "OTC_STATEMENT"], retry_count=0): if retry_count > 20: return @@ -30,14 +34,24 @@ def download_sftp_files(d=None, sftp = get_sftp_client() if d is None: for f in sftp.listdir('OTC'): - if f.endswith("csv"): - for report_type in report_types: + if f.endswith("OTC_STATEMENT.xls"): + print(f) + sftp.get(f"OTC/{f}", localpath=DATA_DIR / f) + else: + for report_type in report_types[:-1]: if f.endswith(f"{report_type}.csv"): + print(f) sftp.get(f"OTC/{f}", localpath=DATA_DIR / f) + else: + continue + else: file_list = sftp.listdir('OTC') for report_type in report_types: - f = f"{d:%Y%m%d}_{report_type}.csv" + if report_type == "OTC_STATEMENT": + f = f"{d:%Y%m%d}_{report_type}.xls" + else: + f = f"{d:%Y%m%d}_{report_type}.csv" if f not in file_list: logging.info("File not here yet, trying again in 500s...") logging.info(f"Try count: {retry_count}") @@ -112,6 +126,8 @@ def sg_collateral(d): usecols=["Ticket Reference", "Reference Entity", "Mtm Value"]) 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"]) df_position = df_position.set_index("Ticket Reference") # expired_trades # df_position = df_position.append( @@ -157,7 +173,10 @@ def sg_collateral(d): 'SER_IGCURVE': 'SER_IGCVECSH', 'HYOPTDEL': 'HYCDSCSH', 'IGOPTDEL': 'IGCDSCSH'}) - return df + 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]) def ms_collateral(d): |