diff options
Diffstat (limited to 'python/analytics')
| -rw-r--r-- | python/analytics/basket_index.py | 6 | ||||
| -rw-r--r-- | python/analytics/curve_trades.py | 21 | ||||
| -rw-r--r-- | python/analytics/db.py | 3 | ||||
| -rw-r--r-- | python/analytics/index.py | 9 | ||||
| -rw-r--r-- | python/analytics/index_data.py | 35 | ||||
| -rw-r--r-- | python/analytics/option.py | 9 | ||||
| -rw-r--r-- | python/analytics/tranche_basket.py | 43 |
7 files changed, 59 insertions, 67 deletions
diff --git a/python/analytics/basket_index.py b/python/analytics/basket_index.py index 2505513b..4a5ce710 100644 --- a/python/analytics/basket_index.py +++ b/python/analytics/basket_index.py @@ -1,5 +1,5 @@ from .index_data import get_index_quotes, get_singlenames_curves -from .db import _engine +from .db import serenitas_engine from .utils import tenor_t from functools import partial from pyisda.credit_index import CreditIndex @@ -42,14 +42,14 @@ class BasketIndex(CreditIndex): "issue_date "\ "FROM index_maturity " \ "WHERE index=%s AND series=%s", - _engine, + serenitas_engine, index_col='tenor', params=(index_type, series), parse_dates=['maturity', 'issue_date']) if self.index_desc.empty: raise ValueError(f"Index {index_type} {series} doesn't exist") self._index_version = tuple(tuple(r.values()) for r in - _engine.execute( + serenitas_engine.execute( "SELECT lastdate," " indexfactor/100 AS factor," " cumulativeloss/100 AS cum_loss," diff --git a/python/analytics/curve_trades.py b/python/analytics/curve_trades.py index 555194ba..73be4ab5 100644 --- a/python/analytics/curve_trades.py +++ b/python/analytics/curve_trades.py @@ -1,5 +1,5 @@ from analytics.index_data import get_index_quotes, index_returns -from db import dbengine +from db import serenitas_engine, dawn_engine from analytics import CreditIndex, Portfolio from analytics.utils import roll_date from dateutil.relativedelta import relativedelta @@ -16,12 +16,9 @@ import statsmodels.formula.api as smf import numpy as np import matplotlib.pyplot as plt -serenitasdb = dbengine('serenitasdb') -dawndb = dbengine('dawndb') - def on_the_run(index): - r = serenitasdb.execute("SELECT max(series) FROM index_version WHERE index=%s", - (index,)) + r = serenitas_engine.execute("SELECT max(series) FROM index_version WHERE index=%s", + (index,)) series, = r.fetchone() return series @@ -151,9 +148,9 @@ def forward_loss(index='IG'): "close_spread*duration / 100 AS indexel " \ "FROM index_quotes WHERE index=%s AND date >= %s " \ "ORDER BY date DESC, series ASC, duration ASC", - serenitasdb, parse_dates=['date'], params=[index, start_date]) + serenitase_engine, parse_dates=['date'], params=[index, start_date]) df1 = pd.read_sql_query("SELECT index, series, tenor, maturity FROM index_maturity", - serenitasdb, parse_dates=['maturity']) + serenitas_engine, parse_dates=['maturity']) df = df.merge(df1, on=['index','series','tenor']) df = df.set_index(['date','index', 'maturity']).dropna() @@ -277,7 +274,7 @@ def curve_pos(value_date, index_type='IG'): "JOIN index_desc " \ "ON security_id=redindexcode AND " \ "index_desc.maturity=list_cds_positions.maturity" - df = pd.read_sql_query(sql_string, dawndb, + df = pd.read_sql_query(sql_string, dawn_engine, params=[value_date, f'SER_{index_type}CURVE']) portf = Portfolio([CreditIndex(row.index, row.series, row.tenor, @@ -301,11 +298,11 @@ def curve_shape(value_date, index='IG', percentile=.95, spread=None): if spread is None: sql_string = "SELECT closespread FROM index_quotes where index = %s " \ "and series = %s and tenor = %s and date = %s" - spread_df = pd.read_sql_query(sql_string, serenitasdb, + spread_df = pd.read_sql_query(sql_string, serenitas_engine, params=[index, series, '5yr', value_date]) spread = spread_df.iloc[0][0] sql_string = "SELECT tenor, maturity FROM index_maturity where index = %s and series = %s" - lookup_table = pd.read_sql_query(sql_string, serenitasdb, parse_dates=['maturity'], + lookup_table = pd.read_sql_query(sql_string, serenitas_engine, parse_dates=['maturity'], params=[index, series]) df = curve_shape[steepness == steepness.quantile(percentile, 'nearest')] @@ -350,7 +347,7 @@ def pos_pnl_abs(portf, value_date, index='IG', rolling=6, years=3): df = df.groupby(level=['date', 'tenor']).nth(-1)['close_spread'].unstack(-1) sql_string = "SELECT tenor, maturity FROM index_maturity where index = %s and series = %s" - lookup_table = pd.read_sql_query(sql_string, serenitasdb, parse_dates=['maturity'], + lookup_table = pd.read_sql_query(sql_string, serenitas_engine, parse_dates=['maturity'], params=[index, series]) lookup_table['year_frac'] = (lookup_table.maturity - pd.to_datetime(value_date)).dt.days/365 diff --git a/python/analytics/db.py b/python/analytics/db.py index c8b9f385..4abfd813 100644 --- a/python/analytics/db.py +++ b/python/analytics/db.py @@ -1,2 +1 @@ -from db import dbengine, dbconn, DataError -_engine = dbengine('serenitasdb') +from db import serenitas_engine, dawn_engine, DataError diff --git a/python/analytics/index.py b/python/analytics/index.py index 56758693..da0063bc 100644 --- a/python/analytics/index.py +++ b/python/analytics/index.py @@ -4,7 +4,7 @@ import datetime import pandas as pd from .credit_default_swap import CreditDefaultSwap -from .db import _engine, dbengine, DataError +from .db import serenitas_engine, dawn_engine, DataError from bbg_helpers import BBG_IP, retrieve_data, init_bbg_session from pandas.tseries.offsets import BDay from pyisda.curve import SpreadCurve @@ -58,7 +58,7 @@ class CreditIndex(CreditDefaultSwap): raise ValueError("Not enough information to load the index.") try: df = pd.read_sql_query(sql_str, - _engine, + serenitas_engine, parse_dates=['lastdate', 'issue_date'], params=params) maturity = df.maturity[0] @@ -96,8 +96,7 @@ class CreditIndex(CreditDefaultSwap): @classmethod def from_tradeid(cls, trade_id): - engine = dbengine('dawndb') - r = engine.execute(""" + r = dawn_engine.execute(""" SELECT * FROM cds LEFT JOIN index_desc ON security_id = redindexcode AND cds.maturity = index_desc.maturity @@ -143,7 +142,7 @@ class CreditIndex(CreditDefaultSwap): ref_data = retrieve_data(session, [security], field) self.ref = ref_data[security][field] else: - run = _engine.execute("""SELECT * FROM index_quotes + run = serenitas_engine.execute("""SELECT * FROM index_quotes WHERE index=%s AND series=%s AND tenor=%s AND date=%s""", (self.index_type, self.series, self.tenor, self.value_date)) rec = run.fetchone() diff --git a/python/analytics/index_data.py b/python/analytics/index_data.py index 3a588e07..0fd06ff0 100644 --- a/python/analytics/index_data.py +++ b/python/analytics/index_data.py @@ -1,4 +1,4 @@ -from .db import _engine, dbconn +from .db import serenitas_engine from dates import bond_cal import numpy as np @@ -32,18 +32,20 @@ def insert_quotes(): _engine, parse_dates=['date'], index_col=['date']) df = df.loc[dates] for tup in df.itertuples(): - result = _engine.execute("SELECT indexfactor, cumulativeloss FROM index_version " - "WHERE index = 'HY' AND series=%s AND version in (%s, %s)" - "ORDER BY version", - (tup.series, tup.version, tup.version+1)) + result = serenitas_engine.execute( + "SELECT indexfactor, cumulativeloss FROM index_version " + "WHERE index = 'HY' AND series=%s AND version in (%s, %s)" + "ORDER BY version", + (tup.series, tup.version, tup.version+1)) factor1, cumloss1 = result.fetchone() factor2, cumloss2 = result.fetchone() recovery = 1-(cumloss2-cumloss1) - version2_price = (factor1 * tup.closeprice - 100*recovery)/factor2 + version2_price = (factor1 * tup.closeprice - 100 * recovery)/factor2 print(version2_price) - _engine.execute("INSERT INTO index_quotes(date, index, series, version, tenor, closeprice)" - "VALUES(%s, %s, %s, %s, %s, %s)", - (tup.Index, 'HY', tup.series, tup.version+1, tup.tenor, version2_price)) + serenitas_engine.execute( + "INSERT INTO index_quotes(date, index, series, version, tenor, closeprice)" + "VALUES(%s, %s, %s, %s, %s, %s)", + (tup.Index, 'HY', tup.series, tup.version+1, tup.tenor, version2_price)) def get_index_quotes(index=None, series=None, tenor=None, from_date=None, @@ -77,7 +79,7 @@ def get_index_quotes(index=None, series=None, tenor=None, from_date=None, return {k: tuple(v) if isinstance(v, list) else v for k, v in args.items() if v is not None} - df = pd.read_sql_query(sql_str, _engine, parse_dates=['date'], + df = pd.read_sql_query(sql_str, serenitas_engine, parse_dates=['date'], index_col=['date', 'index', 'series', 'version'], params=make_params(args)) df.tenor = df.tenor.astype(tenor_t) @@ -128,7 +130,7 @@ def index_returns(df=None, index=None, series=None, tenor=None, from_date=None, coupon_data = pd.read_sql_query("SELECT index, series, tenor, coupon * 1e-4 AS coupon, " "maturity FROM " "index_maturity WHERE coupon is NOT NULL", - _engine, + serenitas_engine, index_col=['index', 'series', 'tenor']) df = df.reset_index('date').join(coupon_data).reset_index('tenor') # for some reason pandas doesn't keep the categories, so we have to @@ -145,11 +147,9 @@ def index_returns(df=None, index=None, series=None, tenor=None, from_date=None, def get_singlenames_quotes(indexname, date, tenors): - conn = dbconn('serenitasdb') - with conn.cursor() as c: - c.execute("SELECT * FROM curve_quotes2(%s, %s, %s)", - vars=(indexname, date, list(tenors))) - return list(c) + r = serenitas_engine.execute("SELECT * FROM curve_quotes2(%s, %s, %s)", + (indexname, date, list(tenors))) + return list(r) def build_curve(r, tenors, currency="USD"): @@ -203,7 +203,8 @@ def get_singlenames_curves(index_type, series, trade_date, def get_tranche_quotes(index_type, series, tenor, date=datetime.date.today()): - conn = dbconn('serenitasdb') + conn = serenitas_engine.raw_connection() with conn.cursor() as c: c.callproc("get_tranche_quotes", (index_type, series, tenor, date)) return pd.DataFrame.from_records(dict(d) for d in c) + conn.close() diff --git a/python/analytics/option.py b/python/analytics/option.py index f75e0a93..c423f15c 100644 --- a/python/analytics/option.py +++ b/python/analytics/option.py @@ -5,13 +5,11 @@ import numpy as np import pandas as pd import analytics -from db import dbengine - from .black import black, Nx from .sabr import sabr from .utils import GHquad, build_table from .index import g, ForwardIndex, CreditIndex -from .db import _engine +from .db import serenitas_engine, dawn_engine from .utils import memoize from pandas.tseries.offsets import BDay @@ -79,8 +77,7 @@ class BlackSwaption(ForwardIndex): @classmethod def from_tradeid(cls, trade_id, index=None): - engine = dbengine('dawndb') - r = engine.execute("SELECT * from swaptions WHERE id=%s", (trade_id,)) + r = dawn_engine.execute("SELECT * from swaptions WHERE id=%s", (trade_id,)) rec = r.fetchone() if rec is None: return ValueError("trade_id doesn't exist") @@ -493,7 +490,7 @@ class QuoteSurface(): "WHERE quotedate::date = %s AND index= %s AND series = %s " "AND quote_source != 'SG' " "ORDER BY quotedate, strike", - _engine, + serenitas_engine, parse_dates=['quotedate', 'expiry'], params=(value_date, index_type.upper(), series)) self._quote_is_price = index_type == "HY" diff --git a/python/analytics/tranche_basket.py b/python/analytics/tranche_basket.py index 0a4ae8ae..64fc03dd 100644 --- a/python/analytics/tranche_basket.py +++ b/python/analytics/tranche_basket.py @@ -6,7 +6,7 @@ from .tranche_functions import ( from .index_data import get_tranche_quotes from .utils import memoize, build_table from collections import namedtuple -from db import dbconn +from .db import dawn_engine, serenitas_engine from copy import deepcopy from lru import LRU from pyisda.date import cds_accrued @@ -80,13 +80,11 @@ class DualCorrTranche(): @classmethod def from_tradeid(cls, trade_id): - with dbconn('dawndb') as conn: - with conn.cursor() as c: - c.execute("SELECT * FROM cds LEFT JOIN index_desc " - "ON security_id = redindexcode AND " - "cds.maturity = index_desc.maturity " - "WHERE id=%s", (trade_id,)) - rec = c.fetchone() + r = dawn_engine.execute("SELECT * FROM cds LEFT JOIN index_desc " + "ON security_id = redindexcode AND " + "cds.maturity = index_desc.maturity " + "WHERE id=%s", (trade_id,)) + rec = r.fetchone() instance = cls(rec['index'], rec['series'], rec['tenor'], attach=rec['attach'], detach=rec['detach'], @@ -265,20 +263,21 @@ class DualCorrTranche(): "WHERE a.index=%s AND a.series=%s AND a.tenor=%s " "AND quotedate::date=%s " "AND (a.detach = %s OR a.attach = %s) ORDER BY a.attach") - with dbconn('serenitasdb') as conn: - with conn.cursor() as c: - c.execute(sql_string, (self.index_type, self.series, - self.tenor, self.value_date, - self.attach, self.attach)) - if self.attach == 0: - self._tranche_id, self.rho[1] = next(c) - elif self.detach == 100: - self._tranche_id, self.rho[0] = next(c) - else: - self.rho = [] - for tranche_id, corr in c: - self.rho.append(corr) - self._tranche_id = tranche_id + conn = serenitas_engine.raw_connection() + with conn.cursor() as c: + c.execute(sql_string, (self.index_type, self.series, + self.tenor, self.value_date, + self.attach, self.attach)) + if self.attach == 0: + self._tranche_id, self.rho[1] = next(c) + elif self.detach == 100: + self._tranche_id, self.rho[0] = next(c) + else: + self.rho = [] + for tranche_id, corr in c: + self.rho.append(corr) + self._tranche_id = tranche_id + conn.close() @property def tranche_factor(self): |