from flask_wtf import FlaskForm from flask_wtf.file import FileField from sqlalchemy.dialects.postgresql import ENUM from wtforms.validators import Length from wtforms_alchemy import model_form_factory from sqlalchemy_utils import EmailType, PhoneNumberType from . import db class Counterparties(db.Model): __tablename__ = 'counterparties' code = db.Column(db.String(12), primary_key=True) name = db.Column(db.String) city = db.Column(db.String) state = db.Column(db.String(2)) dtc_number = db.Column(db.Integer) sales_contact = db.Column(db.String) sales_email = db.Column(EmailType) sales_phone = db.Column(PhoneNumberType()) valuation_contact1 = db.Column(db.String) valuation_email1 = db.Column(EmailType) valuation_contact2 = db.Column(db.String) valuation_email2 = db.Column(EmailType) valuation_contact3 = db.Column(db.String) valuation_email3 = db.Column(EmailType) valuation_contact4 = db.Column(db.String) valuation_email4 = db.Column(EmailType) notes = db.Column(db.String) instructions = db.Column(db.String, info={'form_field_class': FileField}) class Accounts(db.Model): __tablename__ = 'accounts' code = db.Column(db.String(5), primary_key=True) name = db.Column(db.String) custodian = db.Column(db.String) cash_account = db.Column(db.String) counterparty = db.Column(db.String(12), db.ForeignKey('counterparties.code')) PORTFOLIO = ENUM('OPTIONS', 'IR', 'MORTGAGES', name='portfolio') BOND_STRAT = ENUM('M_STR_MAV', 'M_STR_MEZZ', 'CSO_TRANCH', 'M_CLO_BB20', 'M_CLO_AAA', 'M_CLO_BBB', 'M_MTG_IO', 'M_MTG_THRU', 'M_MTG_GOOD', 'M_MTG_B4PR', 'M_MTG_RW', 'M_MTG_FP', 'M_MTG_LMG', 'M_MTG_SD', 'M_MTG_PR', 'M_MTG_CRT_SD', name='bond_strat') CDS_STRAT = ENUM('HEDGE_CSO', 'HEDGE_CLO', 'HEDGE_MAC', 'HEDGE_MBS', 'SER_IGSNR', 'SER_IGMEZ', 'SER_IGEQY', 'SER_IGINX', 'SER_HYSNR', 'SER_HYMEZ', 'SER_HYEQY', 'SER_HYINX', 'SER_IGCURVE', 'MBSCDS', 'IGOPTDEL', 'HYOPTDEL', 'SER_ITRXCURVE', name='cds_strat') SWAPTION_STRAT = ENUM('IGPAYER', 'IGREC', 'HYPAYER', 'HYREC', 'STEEP', name='swaption_strat') FUTURE_STRAT = ENUM('M_STR_MAV', 'M_MTG_IO', 'M_STR_MEZZ', 'M_MTG_RW', 'SER_ITRXCURVE', name='future_strat') CASH_STRAT = ENUM('M_CSH_CASH', 'MBSCDSCSH', 'SER_IGCVECSH', 'SER_ITRXCVCSH', 'CSOCDSCSH', 'IGCDSCSH', 'HYCDSCSH', 'CLOCDSCSH', 'IGTCDSCSH', 'MACCDSCSH', 'M_STR_MEZZ', 'IRDEVCSH', name='cash_strat') OPTION_TYPE = ENUM('PAYER', 'RECEIVER', name='option_type') SWAPTION_TYPE = ENUM('CD_INDEX_OPTION', 'SWAPTION', name='swaption_type') SETTLEMENT_TYPE = ENUM('Delivery', 'Cash', name='settlement_type') REPO_TYPE = ENUM('REPO', 'REVERSE REPO', name='repo_type') FUTURE_TYPE = ENUM('FUTURE', 'CFD', 'SYNTHETIC-FUTURE', 'LME-FORWARD', name='future_type') CALL_NOTICE = ENUM('24H', '48H', '3D', '4D', '5D', '6D', '1W', '8D', '9D', '10D', '2W', '1M', '2M', name='call_notice') ASSET_CLASS = ENUM('CSO', 'Subprime', 'CLO', 'Tranches', 'Futures', 'Cash', 'FX', 'Cleared', name='asset_class') ACTION = ENUM('NEW', 'UPDATE', 'CANCEL', name='action') CCY = ENUM('USD', 'CAD', 'EUR', 'YEN', name='currency') BBG_TYPE = ENUM('Mtge', 'Corp', name='bbg_type') SWAP_TYPE = ENUM('CD_INDEX', 'CD_INDEX_TRANCHE', 'CD_BASKET_TRANCHE', 'ABS_CDS', name='swap_type') ISDA = ENUM('ISDA2014', 'ISDA2003Cred', name='isda') DAY_COUNT = ENUM('ACT/360', 'ACT/ACT', '30/360', 'ACT/365', name='day_count') FREQ = ENUM('Yearly', 'Half-Yearly', 'Quarterly', 'Bi-Monthly', 'Monthly', 'Weekly', 'Daily', 'Straight', name='frequency') BUS_DAY_CONVENTION = ENUM('Modified Following', 'Following', 'Modified Preceding', 'Preceding', 'Second-Day-After', 'End-of-Month', name='bus_day_convention') PROTECTION = ENUM('Buyer', 'Seller', name='protection') CAPFLOOR_TYPE = ENUM('CAPFLOOR', 'CMS_OPTION', 'STRUCTURED', 'FORWARD_PREMIUM', name='capfloor_type') CAPFLOOR = ENUM('C', 'F', name='cap_or_floor') PAYMENT_MODE = ENUM('F', 'S', name='payment_mode') PRICING_TYPE = ENUM('PlainVanilla', 'EquityIndex', name='pricing_type') BEGIN_OR_END = ENUM('B', 'E', name='begin_or_end') class BondDeal(db.Model): __tablename__ = 'bonds' id = db.Column('id', db.Integer, primary_key=True) dealid = db.Column(db.String(28)) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(BOND_STRAT, nullable=False) custodian = db.Column(db.String(12), default='BAC', nullable=False) cashaccount = db.Column(db.String(10), default='V0NSCLMAMB', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'counterparty'}, nullable=False) trade_date = db.Column(db.Date, nullable = False) settle_date = db.Column(db.Date, nullable = False) cusip = db.Column(db.String(9), info={'validators': Length(9, 9), 'filters': [lambda x: x or None,], 'trim': True}) isin = db.Column(db.String(12), info={'validators': Length(12, 12), 'filters': [lambda x: x or None,], 'trim': True}) identifier = db.Column(db.String(12), info={'filters': [lambda x: x or None,], 'trim': True}) description = db.Column(db.String(32), nullable = False, info={'trim': True}) buysell = db.Column(db.Boolean, nullable=False, info={'choices': [(0, 'sell'), (1, 'buy')], 'coerce': lambda x: bool(int(x)) \ if x is not None else x}) faceamount = db.Column(db.Float, nullable=False) price = db.Column(db.Float, nullable=False) accrued = db.Column(db.Float, nullable = False) asset_class = db.Column(ASSET_CLASS) ticket = db.Column(db.String, info={'form_field_class': FileField}) principal_payment = db.Column(db.Float) accrued_payment = db.Column(db.Float) counterparty = db.relationship(Counterparties) __table_args__= (db.CheckConstraint('cusip is not Null or isin is not Null'),) class CDSDeal(db.Model): __tablename__ = 'cds' id = db.Column('id', db.Integer, primary_key=True) dealid = db.Column(db.String(28)) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(CDS_STRAT, nullable=False) custodian = db.Column(db.String(12), default='SGFCM', nullable=False) cashaccount = db.Column(db.String(10), default='SGNSCLMASW', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'counterparty'}, nullable=False) trade_date = db.Column(db.Date, nullable=False) effective_date = db.Column(db.Date, nullable=False) maturity = db.Column(db.Date, nullable=False) currency = db.Column(CCY, nullable=False) payment_rolldate = db.Column(BUS_DAY_CONVENTION, nullable=False) notional = db.Column(db.Float, nullable=False) fixed_rate = db.Column(db.Float, nullable=False) day_count = db.Column(DAY_COUNT, nullable=False) frequency = db.Column(db.SmallInteger, default=4, nullable=False) protection = db.Column(PROTECTION, nullable=False) security_id = db.Column(db.String(12), nullable=False) security_desc = db.Column(db.String(32), nullable=False) upfront = db.Column(db.Float, nullable=False) upfront_settle_date = db.Column(db.Date, nullable=False) initial_margin_percentage = db.Column(db.Float) swap_type = db.Column(SWAP_TYPE, nullable=False) attach = db.Column(db.SmallInteger, info={'min': 0, 'max': 100}) detach = db.Column(db.SmallInteger, info={'min': 0, 'max': 100}) corr_attach = db.Column(db.Float, info={'min': 0.}) corr_detach = db.Column(db.Float, info={'min': 0.}) index_ref = db.Column(db.Float) clearing_facility = db.Column(db.String(12), default=None, info={'filters': [lambda x: x or None,]}) isda_definition = db.Column(ISDA) termination_date = db.Column(db.Date) termination_amount = db.Column(db.Float) termination_cp = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'termination_counterparty'}) counterparty = db.relationship(Counterparties, foreign_keys=[cp_code]) termination_counterparty = db.relationship(Counterparties, foreign_keys=[termination_cp]) __table_args__ = (db.CheckConstraint("swap_type!='CD_INDEX_TRANCHE' or " \ "(attach is not NULL and detach is not NULL AND " \ "clearing_facility is NULL)"),) class RepoDeal(db.Model): __tablename__ = 'repo' id = db.Column('id', db.Integer, primary_key=True) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(CDS_STRAT, nullable=False) custodian = db.Column(db.String(12), default='SGFCM', nullable=False) cashaccount = db.Column(db.String(10), default='SGNSCLMASW', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'counterparty'}, nullable=False) trade_date = db.Column(db.Date, nullable=False) settle_date = db.Column(db.Date, nullable=False) cusip = db.Column(db.String(9), info={'validators': Length(9,9), 'filters': [lambda x: x or None,], 'trim': True}) isin = db.Column(db.String(12), info={'validators': Length(12, 12), 'filters': [lambda x: x or None,], 'trim': True}) identifier = db.Column(db.String(12), info={'filters': [lambda x: x or None,], 'trim': True}) description = db.Column(db.String(32), nullable=False, info={'trim': True}) transation_indicator = db.Column(REPO_TYPE) faceamount = db.Column(db.Float, nullable=False) price = db.Column(db.Float, nullable=False) currency = db.Column(CCY, nullable=False) expiration_date = db.Column(db.Date) weighted_amount = db.Column(db.Float) haircut = db.Column(db.Float) repo_rate = db.Column(db.Float, nullable=False) call_notice = db.Column(CALL_NOTICE) daycount = db.Column(DAY_COUNT) ticket = db.Column(db.String, info={'form_field_class': FileField}) __table__args = (db.CheckConstraint("haircut is not NULL and weighted_amount is NULL) or " \ "haircut is NULL and weighted_amount is NOT NULL)"), db.CheckConstraint("cusip is NOT NULL or isin is NOT NULL")) class SwaptionDeal(db.Model): __tablename__ = 'swaptions' id = db.Column('id', db.Integer, primary_key=True) dealid = db.Column(db.String(28)) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) portfolio = db.Column(PORTFOLIO) folder = db.Column(SWAPTION_STRAT, nullable=False) custodian = db.Column(db.String(12), default='NONE', nullable=False) cashaccount = db.Column(db.String(10), default='SGNSCLMASW', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'counterparty'}, nullable=False) swap_type = db.Column(SWAPTION_TYPE, nullable=False) trade_date = db.Column(db.Date, nullable=False) settle_date = db.Column(db.Date, nullable=False) buysell = db.Column(db.Boolean, nullable=False, info={'choices': [(0, 'sell'), (1, 'buy')], 'coerce': lambda x: bool(int(x)) if x is not None else x}) notional = db.Column(db.Float, nullable=False) option_type = db.Column(OPTION_TYPE, nullable=False) strike = db.Column(db.Float, nullable=False) price = db.Column(db.Float, nullable=False) expiration_date = db.Column(db.Date, nullable=False) initial_margin_percentage = db.Column(db.Float) index_ref = db.Column(db.Float) security_id = db.Column(db.String(12), nullable=False) security_desc = db.Column(db.String(32)) fixed_rate = db.Column(db.Float) maturity = db.Column(db.Date, nullable=False) currency = db.Column(CCY, nullable=False) settlement_type = db.Column(SETTLEMENT_TYPE, nullable=False) termination_date = db.Column(db.Date) termination_amount = db.Column(db.Float) termination_cp = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'termination_counterparty'}) counterparty = db.relationship(Counterparties, foreign_keys=[cp_code]) termination_counterparty = db.relationship(Counterparties, foreign_keys=[termination_cp]) class FutureDeal(db.Model): __tablename__ = 'futures' id = db.Column('id', db.Integer, primary_key=True) dealid = db.Column(db.String(28)) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(FUTURE_STRAT, nullable=False) custodian = db.Column(db.String(12), default='INTBR', nullable=False) cashaccount = db.Column(db.String(10), default='IANSCLMAFU', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [('IBKRNY', 'Interactive Brokers')], 'label': 'counterparty'}, nullable=False) trade_date = db.Column(db.Date, nullable=False) settle_date = db.Column(db.Date, nullable=False) buysell = db.Column(db.Boolean, nullable=False, info={'choices':[(0, 'sell'), (1, 'buy')], 'coerce': lambda x: bool(int(x)) \ if x is not None else x}) bbg_ticker = db.Column(db.String(32), nullable=False) quantity = db.Column(db.Float, nullable=False) price = db.Column(db.Float, nullable=False) commission = db.Column(db.Float) swap_type = db.Column(FUTURE_TYPE, nullable=False) security_desc = db.Column(db.String(32), nullable=False) maturity = db.Column(db.Date, nullable=False) currency = db.Column(CCY, nullable=False) exchange = db.Column(db.String(3), default='CME', nullable=False) counterparty = db.relationship(Counterparties) class CashFlowDeal(db.Model): __tablename__ = 'wires' id = db.Column('id', db.Integer, primary_key=True) dealid = db.Column(db.String(28)) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(CASH_STRAT, nullable=False) code = db.Column(db.String(5), db.ForeignKey('accounts.code'), nullable=False) amount = db.Column(db.Float, nullable=False) currency = db.Column(CCY, nullable=False) trade_date = db.Column(db.Date, nullable=False) account = db.relationship(Accounts) class CapFloorDeal(db.Model): __tablename__ = 'capfloors' id = db.Column('id', db.Integer, primary_key=True) dealid = db.Column(db.String(28)) lastupdate = db.Column(db.DateTime, server_default=db.func.now(), onupdate=db.func.now()) action = db.Column(ACTION) folder = db.Column(SWAPTION_STRAT, nullable=False) custodian = db.Column(db.String(12), default='NONE', nullable=False) cashaccount = db.Column(db.String(10), default='SGNSCLMASW', nullable=False) cp_code = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'counterparty'}, nullable=False) comments = db.Column(db.String(100)) floating_rate_index = db.Column(db.String(12), nullable=False, info={'label': 'Floating Rate Index'}) floating_rate_index_desc = db.Column(db.String(32)) buysell = db.Column(db.Boolean, nullable=False, info={'choices':[(0, 'sell'), (1, 'buy')], 'coerce': lambda x: bool(int(x)) \ if x is not None else x}) cap_or_floor = db.Column(CAPFLOOR, nullable=False, info={'choices': [('C', 'Cap'), ('F', 'Floor')], 'label': 'Cap or Floor?'}) strike = db.Column(db.Float, nullable=False) value_date = db.Column(db.Date, nullable=False) expiration_date = db.Column(db.Date, nullable=False) premium_percent = db.Column(db.Float, nullable=False) pricing_type = db.Column(PRICING_TYPE, nullable=False) payment_frequency = db.Column(FREQ, nullable=False, default='Straight') fixing_frequency = db.Column(FREQ, nullable=False, default='Straight') day_count_counvention = db.Column(DAY_COUNT, default='Act/Act') bdc_convention = db.Column(BUS_DAY_CONVENTION, default='Modified') payment_mode = db.Column(PAYMENT_MODE, nullable=False, info={'choices': [('F', 'Flat'), ('S', 'Schedule')]}) payment_at_beginning_or_end = db.Column(BEGIN_OR_END, nullable=False, info={'label': 'In arrears?', 'choices': [('E', True), ('B', False)]}) initial_margin_percentage = db.Column(db.Float) initial_margin_currency = db.Column(CCY) amount = db.Column(db.Float, nullable=False, info={'label': 'notional'}) trade_date = db.Column(db.Date, nullable=False) swap_type = db.Column(CAPFLOOR_TYPE, nullable=False) reset_lag = db.Column(db.Integer, default=2) trade_confirm = db.Column(db.String, info={'form_field_class': FileField}) termination_date = db.Column(db.Date) termination_amount = db.Column(db.Float) termination_cp = db.Column(db.String(12), db.ForeignKey('counterparties.code'), info={'choices': [(None, '')], 'label': 'termination counterparty'}) cpty_id = db.Column(db.Text) counterparty = db.relationship(Counterparties, foreign_keys=[cp_code]) termination_counterparty = db.relationship(Counterparties, foreign_keys=[termination_cp]) BaseModelForm = model_form_factory(FlaskForm) class ModelForm(BaseModelForm): @classmethod def get_session(self): return db.session