diff options
Diffstat (limited to 'python/process_queue.py')
| -rw-r--r-- | python/process_queue.py | 144 |
1 files changed, 85 insertions, 59 deletions
diff --git a/python/process_queue.py b/python/process_queue.py index 125a7a27..26f1b819 100644 --- a/python/process_queue.py +++ b/python/process_queue.py @@ -62,48 +62,45 @@ def build_line(obj, queue_name='bond_trades'): elif queue_name == 'cds_trades': freq = {4: 'Quaterly', 12: 'Monthly'} line = ["CreditDefaultSwapDeal", obj.dealid, obj.action, "Serenitas", - obj.dealid, obj.action ,"Serenitas", None, None , obj.folder, - obj.custodian, obj.cashaccount, obj.cp_code, None, 'Valid', - str(obj.trade_date), None, None, str(obj.effective_date), str(obj.maturity), - obj.currency, obj.notional, obj.fixed_rate, obj.payment_rolldate, obj.day_count, + None, None , obj.folder, obj.custodian, obj.cashaccount, + obj.cp_code, None, 'Valid', str(obj.trade_date), None, None, + str(obj.effective_date), str(obj.maturity), obj.currency, + obj.curr_notional, obj.fixed_rate, obj.payment_rolldate, obj.day_count, freq[obj.frequency]] - line += [None]*5 - line += [obj.protection, obj.security_id, obj.security_desc] - line += [None]*9 + line += [None]*5 + [obj.protection, obj.security_id, obj.security_desc] + line += [None]*9 +[obj.upfront, obj.upfront_settle_date] + line += [None]*3 + [line.swap_type, None, None, line.attach, line.detach] + line += [None] *5 + line.clearing_facility + line += [None] *10 + line.isda_definition return line -def bbg_process(conn, session, trade): +def get_bbg_data(conn, session, identifier, cusip=None, isin = None, settle_date = None, asset_class=None, + **kwargs): fields = ["MTG_FACTOR_SET_DT", "INT_ACC"] - fields_mtge = ["MTG_FACE_AMT", "START_ACC_DT"] - fields_corp = ["AMT_ISSUED", "PREV_CPN_DT"] + fields_dict = {'Mtge': ["MTG_FACE_AMT", "START_ACC_DT"], + 'Corp': ["AMT_ISSUED", "PREV_CPN_DT"]} with conn.cursor() as c: c.execute("SELECT identifier FROM securities WHERE identifier=%s", - (trade['identifier'],)) + (identifier,)) if not c.fetchone(): fields += ["MATURITY", "CRNCY", "NAME", "FLOATER", "FLT_SPREAD", "CPN", "CPN_FREQ", "FIRST_CPN_DT", "MTG_PAY_DELAY", "DAY_CNT_DES"] - bbg_id = (trade['cusip'] or trade['isin']) + ' Mtge' - bbg_type = 'Mtge' - data = retrieve_data(session, [bbg_id], fields + fields_mtge, - overrides={'SETTLE_DT': trade['settle_date']}) - if not data[bbg_id]: - bbg_id = (trade['cusip'] or trade['isin']) + ' Corp' - bbg_type = 'Corp' - data = retrieve_data(session, [bbg_id], fields + fields_corp, - overrides={'SETTLE_DT': trade['settle_date']}) + cusip_or_isin = (trade['cusip'] or trade['isin']) + for bbg_type in ['Mtge', 'Corp']: + bbg_id = cusip_or_isin + ' ' + bbg_type + data = retrieve_data(session, [bbg_id], fields + fields_dict[bbg_type], + overrides={'SETTLE_DT': settle_date} if settle_date else None) + if data[bbg_id]: + break + else: + logging.error('{0} not in bloomberg'.format(cusip_or_ising)) + return bbg_data = data[bbg_id] if bbg_data.get('MTG_FACTOR_SET_DT', 0) == 0: bbg_data['MTG_FACTOR_SET_DT'] = 1 bbg_data['INT_ACC'] = 0 - currentface = trade['faceamount'] * bbg_data['MTG_FACTOR_SET_DT'] - accrued_payment = bbg_data['INT_ACC'] * currentface /100. - principal_payment = currentface * trade['price'] / 100. - with conn.cursor() as c: - c.execute("UPDATE bonds SET principal_payment = %s, accrued_payment = %s " - "WHERE id = %s", (principal_payment, accrued_payment, int(trade['id']))) - conn.commit() if len(fields) > 2: #we don't have the data in the securities table sql_fields = ['identifier', 'cusip', 'isin', 'description', 'face_amount', 'maturity', 'floater', 'spread', 'coupon', 'frequency', @@ -120,17 +117,27 @@ def bbg_process(conn, session, trade): if isinstance(pay_delay, str): pay_delay = int(pay_delay.split(' ')[0]) with conn.cursor() as c: - c.execute(sqlstr, (trade['identifier'], trade['cusip'], trade['isin'], - bbg_data['NAME'], + c.execute(sqlstr, (identifier, cusip, isin, bbg_data['NAME'], bbg_data.get('MTG_FACE_AMT') or bbg_data.get('AMT_ISSUED'), bbg_data.get('MATURITY'), isfloater, bbg_data.get('FLT_SPREAD') if isfloater else None, bbg_data.get('CPN') if not isfloater else None, bbg_data.get('CPN_FREQ'), day_count, bbg_data.get('FIRST_CPN_DT'), pay_delay, - bbg_data.get('CRNCY'), bbg_type, trade['asset_class'], + bbg_data.get('CRNCY'), bbg_type, asset_class, bbg_data.get('START_ACC_DT') or bbg_data.get('PREV_CPN_DT'))) conn.commit() + return bbg_data + +def bond_trade_process(conn, session, trade): + bbg_data = get_bbg_data(conn, session, **trade) + currentface = trade['faceamount'] * bbg_data['MTG_FACTOR_SET_DT'] + accrued_payment = bbg_data['INT_ACC'] * currentface /100. + principal_payment = currentface * trade['price'] / 100. + with conn.cursor() as c: + c.execute("UPDATE bonds SET principal_payment = %s, accrued_payment = %s " + "WHERE id = %s", (principal_payment, accrued_payment, int(trade['id']))) + conn.commit() #mark it at buy price if trade.buysell: sqlstr = "INSERT INTO marks VALUES(%s, %s, %s)" @@ -143,32 +150,45 @@ def bbg_process(conn, session, trade): finally: conn.commit() -headers = {'bond_trades':['Deal Type', 'Deal ID', 'Action', 'Client', 'Reserved', 'Reserved', - 'Folder', 'Custodian', 'Cash Account', 'Counterparty', 'Comments', - 'State', 'Trade Date', 'Settlement Date', 'Reserved', 'GlopeOp Security Identifier', - 'CUSIP', 'ISIN', 'Reserved', 'Reserved', - 'Reserved', 'Security Description', 'Transaction Indicator', - 'SubTransaction Indicator', 'Accrued', 'Price', 'BlockId', 'BlockAmount', - 'Fund', 'Portfolio', 'Reserved', 'Reserved', 'ClientReference', 'ClearingMode', - 'FaceAmount', 'Pool Factor', 'FactorAsOfDate', 'Delivery'], - 'cds_trades': ['Deal Type', 'Deal ID', 'Action', 'Client', 'Reserved', 'Reserved', - 'Folder', 'Custodian', 'Cash Account', 'Counterparty', 'Comments', - 'State', 'Trade Date', 'Reserved', 'Reserved', 'EffectiveDate', 'MaturityDate', - 'Currency', 'Notional', 'FixedRate', 'PaymentRollDateConvention', 'DayCount', - 'PaymentFrequency', 'FirstCouponRate', 'FirstCouponDate', 'ResetLag', 'Liquidation', - 'LiquidationDate', 'Protection', 'UnderlyingSecurityId', - 'UnderlyingSecurityDescription', 'CreditSpreadCurve', - 'CreditEvents', 'RecoveryRate', 'Settlement', 'InitialMargin', - 'InitialMarginPercentage','InitialMarginCurrency', 'DiscountCurve', - 'ClientReference', 'UpfrontFee', 'UpfrontFeePayDate', 'RegenerateCashFlow', - 'UpfrontFeeComment', 'GiveUpBroker','SwapType', 'OnPrice', - 'OffPrice', 'AttachmentPoint', 'ExhaustionPoint', 'Fees', 'Fee Payment Dates', - 'Fee Comments', 'Credit Event Occurred', 'Calendar', - 'Clearing Facility', 'Adjusted', 'CcpTradeRef', 'BlockId', - 'BlockAmount', 'NettingId', 'AnnouncementDate', 'ExecTS', - 'DefaultProbability', 'ClientMargin', 'Factor', 'ISDADefinition']} +def cds_trade_process(serenitasdb, dawndb, session, trade): + sqlstr = 'SELECT indexfactor FROM index_version WHERE redindexcode=%s' + with serenitasdb.cursor() as c: + c.execute(sqlstr, (trade.security_id,)) + try: + factor, = c.fetchone() + except ValueError: + bbg_data = get_bbg_data(dawndb, session, trade['security_id'], isin = trade['security_id'], + asset_class='Subprime') + factor = bbg_data['MTG_FACTOR_SET_DT'] + trade['curr_notional'] = trade['notional'] * factor + return trade def generate_csv(l, queue_name='bond_trades'): + headers = {'bond_trades':['Deal Type', 'Deal ID', 'Action', 'Client', 'Reserved', 'Reserved', + 'Folder', 'Custodian', 'Cash Account', 'Counterparty', 'Comments', + 'State', 'Trade Date', 'Settlement Date', 'Reserved', 'GlopeOp Security Identifier', + 'CUSIP', 'ISIN', 'Reserved', 'Reserved', + 'Reserved', 'Security Description', 'Transaction Indicator', + 'SubTransaction Indicator', 'Accrued', 'Price', 'BlockId', 'BlockAmount', + 'Fund', 'Portfolio', 'Reserved', 'Reserved', 'ClientReference', 'ClearingMode', + 'FaceAmount', 'Pool Factor', 'FactorAsOfDate', 'Delivery'], + 'cds_trades': ['Deal Type', 'Deal ID', 'Action', 'Client', 'Reserved', 'Reserved', + 'Folder', 'Custodian', 'Cash Account', 'Counterparty', 'Comments', + 'State', 'Trade Date', 'Reserved', 'Reserved', 'EffectiveDate', 'MaturityDate', + 'Currency', 'Notional', 'FixedRate', 'PaymentRollDateConvention', 'DayCount', + 'PaymentFrequency', 'FirstCouponRate', 'FirstCouponDate', 'ResetLag', 'Liquidation', + 'LiquidationDate', 'Protection', 'UnderlyingSecurityId', + 'UnderlyingSecurityDescription', 'CreditSpreadCurve', + 'CreditEvents', 'RecoveryRate', 'Settlement', 'InitialMargin', + 'InitialMarginPercentage','InitialMarginCurrency', 'DiscountCurve', + 'ClientReference', 'UpfrontFee', 'UpfrontFeePayDate', 'RegenerateCashFlow', + 'UpfrontFeeComment', 'GiveUpBroker','SwapType', 'OnPrice', + 'OffPrice', 'AttachmentPoint', 'ExhaustionPoint', 'Fees', 'Fee Payment Dates', + 'Fee Comments', 'Credit Event Occurred', 'Calendar', + 'Clearing Facility', 'Adjusted', 'CcpTradeRef', 'BlockId', + 'BlockAmount', 'NettingId', 'AnnouncementDate', 'ExecTS', + 'DefaultProbability', 'ClientMargin', 'Factor', 'ISDADefinition']} + output = StringIO() csvwriter = csv.writer(output) csvwriter.writerow(headers[queue_name]) @@ -208,14 +228,20 @@ if __name__=="__main__": serenitasdb = dbconn('serenitasdb') dawndb = dbconn('dawndb') for queue_name in ['bond_trades', 'cds_trades']: - l = get_trades(q, queue_name) - if l: - buf = generate_csv(l, queue_name) + list_trades = get_trades(q, queue_name) + if list_trades: if queue_name == 'bond_trades': + buf = generate_csv(list_trades, queue_name) + with init_bbg_session(BBG_IP) as session: + for trade in list_trades: + bond_trade_process(dawndb, session, trade) + elif queue_name == 'cds_trades': with init_bbg_session(BBG_IP) as session: - for trade in l: - bbg_process(conn, session, trade) + l = [cds_trade_process(serenitasdb, dawndb, session, trade) for trade in list_trades] + buf = generate_csv(list_trades, queue_name) timestamp = write_buffer(buf, queue_name) if not args.no_upload and queue_name!='cds_trades': upload_file(timestamp) q.delete(queue_name) + serenitasdb.close() + dawndb.close() |