diff options
Diffstat (limited to 'python/option_trades.py')
| -rw-r--r-- | python/option_trades.py | 82 |
1 files changed, 60 insertions, 22 deletions
diff --git a/python/option_trades.py b/python/option_trades.py index 15b16efa..1510f583 100644 --- a/python/option_trades.py +++ b/python/option_trades.py @@ -150,14 +150,46 @@ def get_index_spread(index, series, date, conn): conn.commit() return spread -def get_option_pnl(strike, expiry, index, series, engine): - start_date = BDay().rollback(expiry - pd.DateOffset(months=1)) - df = pd.read_sql_query("SELECT quotedate, (pay_bid+pay_offer)/2 AS pay_mid, " \ - "(rec_bid+rec_offer)/2 AS rec_mid FROM swaption_quotes " \ - "WHERE strike=%s and expiry=%s and index=%s and series=%s" \ - "and quotedate>=%s", engine, - params=(strike, expiry, index, series, start_date), - index_col='quotedate', parse_dates=['quotedate']) +def get_index_ref(index, series, date, expiry, conn): + with conn.cursor() as c: + c.execute("SELECT ref, fwdspread from swaption_ref_quotes " \ + "WHERE index=%s and series=%s and quotedate::date=%s "\ + "AND expiry=%s ORDER BY quotedate desc", + (index, series, date, expiry)) + try: + ref, fwdspread = c.fetchone() + except TypeError: + ref, fwdspread = None, None + conn.commit() + return ref, fwdspread + +def get_strike(index, series, date, expiry, conn): + with conn.cursor() as c: + c.execute("SELECT strike from index_quotes " \ + "WHERE index=%s and series=%s and quotedate::date=%s "\ + "AND expiry=%s ORDER BY quotedate desc", + (index, series, date, expiry)) + try: + stri + except TypeError: + ref, fwdspread = None, None + conn.commit() + return ref, fwdspread + +def get_option_pnl(strike, expiry, index, series, start_date, engine): + for s in [strike, strike+2.5]: + df = pd.read_sql_query("SELECT quotedate, (pay_bid+pay_offer)/2 AS pay_mid, " \ + "(rec_bid+rec_offer)/2 AS rec_mid FROM swaption_quotes " \ + "WHERE strike=%s and expiry=%s and index=%s and series=%s" \ + "and quotedate>=%s", engine, + params=(s, expiry, index, series, start_date), + index_col='quotedate', parse_dates=['quotedate']) + if not df.empty and df.index[0] == start_date: + strike = s + break + else: + raise ValueError("Something wrong") + df = df.groupby(df.index.normalize()).last() if expiry < datetime.date.today(): spread = get_index_spread(index, series, expiry, engine.raw_connection()) @@ -173,30 +205,36 @@ def get_option_pnl(strike, expiry, index, series, engine): df = df.append(pd.DataFrame([[pay_mid, rec_mid]], columns=['pay_mid', 'rec_mid'], index=[pd.Timestamp(expiry)])) - return df - + return df, strike -def sell_vol_strategy(index="IG"): +def sell_vol_strategy(index="IG", months=3): engine = dbengine('serenitasdb') conn = engine.raw_connection() - d = pd.Series() with conn.cursor() as c: c.execute("SELECT DISTINCT ON (expiry) series, expiry FROM " \ "swaption_quotes GROUP BY series, expiry ORDER BY expiry, series desc") + d = {} for series, expiry in c: - start_date = BDay().rollback(expiry - pd.DateOffset(months=1)).date() + start_date = BDay().rollback(expiry - pd.DateOffset(months=months)).date() + if start_date == datetime.date(2016, 1, 15): + start_date = datetime.date(2016, 1, 14) + elif start_date == datetime.date(2014, 7, 18): + start_date = datetime.date(2014, 7, 17) + elif start_date == datetime.date(2014, 11, 17): + start_date = datetime.date(2014, 11, 14) + elif start_date == datetime.date(2015, 3, 13): + start_date = datetime.date(2015, 3, 12) if start_date > datetime.date.today(): break - index_spread = get_index_spread(index, series, start_date, conn) - if index_spread is None: - series -=1 - index_spread = get_index_spread(index, series, start_date, conn) - strike = round(index_spread/2.5) * 2.5 ##round to the closest higher 2.5 increment - df = get_option_pnl(strike, expiry, index, series, engine) - if d.empty: - d = -df.sum(1).diff().dropna() + for s in [series, series - 1]: + ref, fwdspread = get_index_ref(index, s, start_date, expiry, conn) + if fwdspread is not None: + break else: - d = d.add(-df.sum(1).diff().dropna(),fill_value=0) + continue + strike = round(fwdspread/2.5) * 2.5 + pnl, strike = get_option_pnl(strike, expiry, index, s, start_date, engine) + d[(s, strike, expiry)] = pnl conn.commit() return d |