diff options
Diffstat (limited to 'python')
| -rw-r--r-- | python/mark_swaptions.py | 214 |
1 files changed, 121 insertions, 93 deletions
diff --git a/python/mark_swaptions.py b/python/mark_swaptions.py index dd3094e9..827eb005 100644 --- a/python/mark_swaptions.py +++ b/python/mark_swaptions.py @@ -6,110 +6,138 @@ import pandas as pd from pandas.tseries.offsets import BDay from analytics import Index, Swaption, Portfolio, BlackSwaption -from db import dbengine -from sqlalchemy import exc +from db import dbconn, DataError logging.basicConfig(filename=os.path.join(os.getenv("LOG_DIR"), 'swaption_marks.log'), level=logging.INFO, format='%(asctime)s %(message)s') -def get_trades(date, engine): - return pd.read_sql_query( - "SELECT dealid, buysell, swaption_type, notional, strike, " \ - "expiration_date, index, series FROM swaptions " \ - "JOIN index_version ON (swaptions.security_id = index_version.redindexcode) " \ - "WHERE termination_date is NULL AND expiration_date > %s AND trade_date <= %s", - engine, 'dealid', params=(date, date), parse_dates=['expiration_date']) -def get_swaption_portfolio(date, engine): - tradeids = engine.execute("SELECT id FROM swaptions WHERE termination_date is NULL " - "AND expiration_date > %s AND trade_date <= %s", - (date, date)) - portf = Portfolio([BlackSwaption.from_tradeid(r[0]) for r in tradeids]) +def get_swaption_portfolio(date, conn): + with conn.cursor() as c: + c.execute("SELECT id, dealid FROM swaptions WHERE termination_date is NULL " + "AND expiration_date > %s AND trade_date <= %s", (date, date)) + try: + trade_ids, deal_ids = zip(*c) + except ValueError: + return {} + portf = Portfolio([BlackSwaption.from_tradeid(t) for t in trade_ids], + trade_ids=deal_ids) portf.value_date = date - portf.mark(interp_method='bivariate_linear') + try: + portf.mark(interp_method='bivariate_linear') + except ValueError as e: + logging.info(e) + return {} return portf -def get_swaptionvol(index, series, strike, expiry, date, engine): - r = engine.execute( - "SELECT ref, vol_payer AS payer, vol_receiver AS receiver, " \ - "vol_payer_black AS payer_black, vol_receiver_black AS receiver_black " \ - "FROM swaption_calib " \ - "LEFT JOIN swaption_quotes USING (quote_id) " \ - "LEFT JOIN swaption_ref_quotes USING (ref_id) "\ - "WHERE index = %s " \ - "AND series = %s AND strike=%s AND expiry = %s AND quotedate::date=%s " \ - "ORDER BY quotedate desc", - (index, series, strike, expiry, date)) - try: - result = r.fetchone() - if result is None: - logging.error("No data for {}{} {}K {} expiry on date {}". - format(index, series, strike, expiry, date)) - return result - except exc.DataError as e: - logging.error(e) - except e: - logging.error(e) -def mark_trades(date, engine): - df = get_trades(date, engine) - result = [] - for trade in df.itertuples(): - try: - r = engine.execute("SELECT closespread, closeprice FROM index_quotes " \ - "WHERE index=%s AND series=%s AND tenor='5yr' AND date=%s", - (trade.index, trade.series, date)) - closespread, closeprice = r.fetchone() - except exc.DataError as e: - logging.eror(e) - except TypeError: - logging.error("no quote for {}{} {} on date {}".format( - trade.index, trade.series, '5yr', date)) - underlying = Index.from_name(trade.index, trade.series, '5yr', date, - trade.notional) - quote = get_swaptionvol(trade.index, trade.series, trade.strike, trade.expiration_date.date(), - date, serenitas_engine) - if quote is None: - continue - underlying.ref = quote.ref - swaption = Swaption(underlying, trade.expiration_date.date(), - trade.strike, trade.swaption_type.lower()) - swaption.sigma = getattr(quote, swaption.option_type) - if swaption.sigma is None: - swaption.sigma = quote.receiver if swaption.option_type == "payer" else quote.payer - direction = 1. if trade.buysell else -1. - market_value = swaption.pv * trade.notional * direction - if trade.index in ["HY", "XO"]: - market_value += (quote.ref - closeprice) / 100 * swaption.delta * trade.notional * direction - else: - market_value += swaption.DV01 * (closespread - quote.ref) * trade.notional * direction - #compute the greeks at index mark - swaption.index.spread = closespread - swaption._update() +def insert_swaption_portfolio(portf, conn): + sql_str = "INSERT INTO swaption_marks VALUES(%s, %s, %s, %s, %s, %s, %s)" + with conn.cursor() as c: + for id, trade in portf.items(): + to_insert = (id, trade.value_date, trade.pv, trade.delta, trade.gamma, trade.vega, trade.theta) + try: + c.execute(sql_str, to_insert) + except DataError as e: + logging.error(e) + finally: + logging.info("succesfully marked trade id: %s", id) + conn.commit() - try: - to_insert = (trade.Index, date, market_value, swaption.delta, swaption.gamma, - swaption.vega) - engine.execute("INSERT INTO swaption_marks VALUES(%s, %s, %s, %s, %s, %s)", - to_insert) - except exc.DataError as e: - logging.error(e) - finally: - logging.info("succesfully marked trade id: %s", trade.Index) - return result +# def get_trades(date, engine): +# return pd.read_sql_query( +# "SELECT dealid, buysell, swaption_type, notional, strike, " \ +# "expiration_date, index, series FROM swaptions " \ +# "JOIN index_version ON (swaptions.security_id = index_version.redindexcode) " \ +# "WHERE termination_date is NULL AND expiration_date > %s AND trade_date <= %s", +# engine, 'dealid', params=(date, date), parse_dates=['expiration_date']) -if __name__ == "__main__": - parser = argparse.ArgumentParser() - parser.add_argument('workdate', nargs='?', - type = lambda s: pd.datetime.strptime(s, "%Y-%m-%d").date()), - dawn_engine = dbengine('dawndb') - serenitas_engine = dbengine('serenitasdb') - args = parser.parse_args() - if args.workdate is None: - workdate = (pd.Timestamp.today()-BDay()).date() - else: - workdate = args.workdate - r = mark_trades(workdate, dawn_engine) +# def get_swaptionvol(index, series, strike, expiry, date, engine): +# r = engine.execute( +# "SELECT ref, vol_payer AS payer, vol_receiver AS receiver, " \ +# "vol_payer_black AS payer_black, vol_receiver_black AS receiver_black " \ +# "FROM swaption_calib " \ +# "LEFT JOIN swaption_quotes USING (quote_id) " \ +# "LEFT JOIN swaption_ref_quotes USING (ref_id) "\ +# "WHERE index = %s " \ +# "AND series = %s AND strike=%s AND expiry = %s AND quotedate::date=%s " \ +# "ORDER BY quotedate desc", +# (index, series, strike, expiry, date)) +# try: +# result = r.fetchone() +# if result is None: +# logging.error("No data for {}{} {}K {} expiry on date {}". +# format(index, series, strike, expiry, date)) +# return result +# except exc.DataError as e: +# logging.error(e) +# except e: +# logging.error(e) + +# def mark_trades(date, engine): +# df = get_trades(date, engine) +# result = [] +# for trade in df.itertuples(): +# try: +# r = engine.execute("SELECT closespread, closeprice FROM index_quotes " \ +# "WHERE index=%s AND series=%s AND tenor='5yr' AND date=%s", +# (trade.index, trade.series, date)) +# closespread, closeprice = r.fetchone() +# except exc.DataError as e: +# logging.eror(e) +# except TypeError: +# logging.error("no quote for {}{} {} on date {}".format( +# trade.index, trade.series, '5yr', date)) +# underlying = Index.from_name(trade.index, trade.series, '5yr', date, +# trade.notional) +# quote = get_swaptionvol(trade.index, trade.series, trade.strike, trade.expiration_date.date(), +# date, serenitas_engine) +# if quote is None: +# continue +# underlying.ref = quote.ref +# swaption = Swaption(underlying, trade.expiration_date.date(), +# trade.strike, trade.swaption_type.lower()) +# swaption.sigma = getattr(quote, swaption.option_type) +# if swaption.sigma is None: +# swaption.sigma = quote.receiver if swaption.option_type == "payer" else quote.payer +# direction = 1. if trade.buysell else -1. +# market_value = swaption.pv * trade.notional * direction +# if trade.index in ["HY", "XO"]: +# market_value += (quote.ref - closeprice) / 100 * swaption.delta * trade.notional * direction +# else: +# market_value += swaption.DV01 * (closespread - quote.ref) * trade.notional * direction +# #compute the greeks at index mark +# swaption.index.spread = closespread +# swaption._update() + +# try: +# to_insert = (trade.Index, date, market_value, swaption.delta, swaption.gamma, +# swaption.vega) +# engine.execute("INSERT INTO swaption_marks VALUES(%s, %s, %s, %s, %s, %s)", +# to_insert) +# except exc.DataError as e: +# logging.error(e) +# finally: +# logging.info("succesfully marked trade id: %s", trade.Index) +# return result + + +if __name__ == "__main__": + # parser = argparse.ArgumentParser() + # parser.add_argument('workdate', nargs='?', + # type = lambda s: pd.datetime.strptime(s, "%Y-%m-%d").date()), + # args = parser.parse_args() + # if args.workdate is None: + # workdate = (pd.Timestamp.today()-BDay()).date() + # else: + # workdate = args.workdate + import pandas as pd + dr = pd.bdate_range("2016-08-19", "2018-06-14") + with dbconn('dawndb') as conn: + for date in dr: + print(date) + portf = get_swaption_portfolio(date, conn) + insert_swaption_portfolio(portf, conn) |