diff options
Diffstat (limited to 'python/dtcc_sdr.py')
| -rw-r--r-- | python/dtcc_sdr.py | 44 |
1 files changed, 44 insertions, 0 deletions
diff --git a/python/dtcc_sdr.py b/python/dtcc_sdr.py index 5722e669..e5f05bf7 100644 --- a/python/dtcc_sdr.py +++ b/python/dtcc_sdr.py @@ -3,6 +3,7 @@ import io import pandas as pd import requests import zipfile +from utils.db import serenitas_engine, dawn_engine from pathlib import Path from typing import Dict, Any @@ -129,6 +130,45 @@ def process_tranche_data(df): df.sort_values("EXECUTION_TIMESTAMP", inplace=True) return df +def map_tranche(df): + idx_ver = pd.read_sql_query( + "select index, series, redindexcode, maturity, tenor " + "from index_desc", + serenitas_engine, + parse_dates='maturity', + index_col=["redindexcode", "maturity"] + ) + idx_ver = idx_ver.loc[idx_ver.index.dropna()] + markit = pd.read_sql_query( + "select * from markit_tranche_quotes a " + "left join (select series, index, basketid " + "as id from index_version) b on a.basketid=b.id", + serenitas_engine, + parse_dates='quotedate') + df = df.reset_index().set_index(['UNDERLYING_ASSET_1', 'END_DATE']) + df.rename_axis(index={'UNDERLYING_ASSET_1': 'redindexcode', + 'END_DATE': 'maturity'}, inplace=True) + df = df.merge(idx_ver, left_index=True, right_index=True, how='left') + df['EXECUTION_TIMESTAMP'] = df.EXECUTION_TIMESTAMP.dt.normalize() + #Normalize the price format + df.PRICE_NOTATION = (df.PRICE_NOTATION / 1e2).where(df.PRICE_NOTATION_TYPE == "Percentage", df.PRICE_NOTATION) + df['price'] = (df.PRICE_NOTATION / 1e2).where(df['index'].isin(['IG', 'EU', 'XO']), 1-df.PRICE_NOTATION/1e2) + #Allow for bigger bid/offers for equity tranche + markit.upfront_bid = (markit.upfront_mid-.01).where(markit.attach == 0, markit.upfront_bid) + markit.upfront_ask = (markit.upfront_mid+.01).where(markit.attach == 0, markit.upfront_ask) + df = df.merge(markit, + left_on=['index', 'series', 'tenor', 'EXECUTION_TIMESTAMP'], + right_on=['index', 'series', 'tenor', 'quotedate'], + how='outer') + df['identified'] = df.apply(lambda df: + (df.price > df.upfront_bid) & (df.price < df.upfront_ask) + if df.upfront_bid > 0 + else (df.price < df.upfront_bid) & (df.price > df.upfront_ask), + axis=1) + df = df[df.identified] + df = df[~df.duplicated(['DISSEMINATION_ID'], keep=False)] + return df.sort_values('EXECUTION_TIMESTAMP') + def insert_correction(conn: connection, dissemination_id: int, **kwargs) -> None: with conn.cursor() as c: @@ -158,3 +198,7 @@ if __name__ == "__main__": dr = pd.bdate_range("2018-01-01", "2019-02-11") for d in dr: download_cumulative_credit(d) + df = load_data() + df = process_tranche_data(df) + df = map_tranche(df) + |