def open_repos(self):
"""
Function is used to open repos from Front. Function is called by FlowTradingGUI.py > onOpenRepos
"""
selectString="t.trdnbr, u.insid, u.isin, i.ref_value*t.quantity, l.fixed_rate"
fromString="trade t, instrument i, portfolio p, instrument u, leg l"
whereString="t.prfnbr=p.prfnbr and i.insaddr=t.insaddr and i.instype='Repo/Reverse' and t.status='BO Confirmed' and i.exp_day > YESTERDAY and p.prfid in "+str(tuple(LDNFLOWBOOKS))+" and i.und_insaddr = u.insaddr and l.insaddr = i.insaddr"
res=self.sql_query('select ' + selectString + ' from ' + fromString + ' where '+ whereString)
output=pandas.DataFrame(pandas.np.array(res),columns=['trdnbr','insid','ISIN','Quantity','Rate'])
output['trdnbr']=output['trdnbr'].astype(int)
output[['Quantity','Rate']]=output[['Quantity','Rate']].astype(float)
openRepos=output.copy()
del openRepos['insid']
isinsregs=pandas.Series(bonds.index,index=bonds['REGS'])
isins144a=pandas.Series(bonds.index,index=bonds['144A'])
isinsregs.name='BondREGS'
isins144a.name='Bond144A'
isinsregs=isinsregs.drop(isinsregs.index.get_duplicates())
isins144a=isins144a.drop(isins144a.index.get_duplicates())
openRepos=openRepos.join(allisins,on='ISIN')
openRepos=openRepos.join(isinsregs,on='ISIN')
openRepos=openRepos.join(isins144a,on='ISIN')
openRepos['Series']=''
for i in openRepos.index:
if pandas.isnull(openRepos.loc[i,'BondREGS']):
openRepos.set_value(i,'Series','144A')
else:
openRepos.set_value(i,'Series','REGS')
openRepos=openRepos[['trdnbr','Bond','Series','ISIN','Quantity','Rate']]
openRepos.sort_values(by='Bond',inplace=True)
return openRepos
def getNewTrades(self):
"""Pulls any new trades for that day.
"""
#We will assume newtrades.csv is already created.
savepath = 'newtrades.csv'
#argstring = self.front_username+' '+self.front_password+' '+self.todayDT.strftime('%Y-%m-%d') + ' ' + savepath
#opstr='python '+MYPATH+'FlowTr~1\\FO_toolkit.py new_trades '+argstring
#subprocess.call(opstr)
newTrades=pandas.read_csv(TEMPPATH+savepath)
newTrades.rename(columns={'trdnbr':'FrontID','insid':'FrontName','isin':'ISIN','trade_price':'Price','quantity':'Qty','trade_time':'DateSTR','portfolio':'Book',
'trade_curr':'CCY','Sales Credit':'SCu','Sales Credit MarkUp':'MKu','Counterparty':'FrontCounterparty','Salesperson':'Sales'},inplace=True)
newTrades['ISIN'].fillna('na',inplace=True)
newTrades=newTrades[newTrades['ISIN']!='na']
#Clean bond names
isinsregs=pandas.Series(bonds.index,index=bonds['REGS'])
isins144a=pandas.Series(bonds.index,index=bonds['144A'])
isinsregs.name='BondREGS'
isins144a.name='Bond144A'
isinsregs=isinsregs.drop(isinsregs.index.get_duplicates())
isins144a=isins144a.drop(isins144a.index.get_duplicates())
newTrades=newTrades.join(allisins,on='ISIN')
newTrades=newTrades.join(isinsregs,on='ISIN')
newTrades=newTrades.join(isins144a,on='ISIN')
newTrades['Series']=''
for i in newTrades.index:
if pandas.isnull(newTrades.loc[i,'BondREGS']):
newTrades.set_value(i,'Series','144A')
else:
newTrades.set_value(i,'Series','REGS')
newTrades=newTrades[['FrontID','Bond','Series','ISIN','Qty','Price','Book']]
newTrades.sort_values(by='Bond',inplace=True)
self.positionDeltas=newTrades.groupby(['Book','ISIN'])['Qty'].sum()
self.newTrades=newTrades
pass
def getNewTrades(self):
"""Pulls any new trades for that day.
"""
#We will assume newtrades.csv is already created.
savepath = 'newtrades.csv'
#argstring = self.front_username+' '+self.front_password+' '+self.todayDT.strftime('%Y-%m-%d') + ' ' + savepath
#opstr='python '+MYPATH+'FlowTr~1\\FO_toolkit.py new_trades '+argstring
#subprocess.call(opstr)
newTrades = pandas.read_csv(TEMPPATH + savepath)
newTrades.rename(columns={
'trdnbr': 'FrontID',
'insid': 'FrontName',
'isin': 'ISIN',
'trade_price': 'Price',
'quantity': 'Qty',
'trade_time': 'DateSTR',
'portfolio': 'Book',
'trade_curr': 'CCY',
'Sales Credit': 'SCu',
'Sales Credit MarkUp': 'MKu',
'Counterparty': 'FrontCounterparty',
'Salesperson': 'Sales'
},
inplace=True)
newTrades['ISIN'].fillna('na', inplace=True)
newTrades = newTrades[newTrades['ISIN'] != 'na']
#Clean bond names
isinsregs = pandas.Series(bonds.index, index=bonds['REGS'])
isins144a = pandas.Series(bonds.index, index=bonds['144A'])
isinsregs.name = 'BondREGS'
isins144a.name = 'Bond144A'
isinsregs = isinsregs.drop(isinsregs.index.get_duplicates())
isins144a = isins144a.drop(isins144a.index.get_duplicates())
newTrades = newTrades.join(allisins, on='ISIN')
newTrades = newTrades.join(isinsregs, on='ISIN')
newTrades = newTrades.join(isins144a, on='ISIN')
newTrades['Series'] = ''
for i in newTrades.index:
if pandas.isnull(newTrades.loc[i, 'BondREGS']):
newTrades.set_value(i, 'Series', '144A')
else:
newTrades.set_value(i, 'Series', 'REGS')
newTrades = newTrades[[
'FrontID', 'Bond', 'Series', 'ISIN', 'Qty', 'Price', 'Book'
]]
newTrades.sort_values(by='Bond', inplace=True)
self.positionDeltas = newTrades.groupby(['Book', 'ISIN'])['Qty'].sum()
self.newTrades = newTrades
pass
def open_repos(self):
"""
Function is used to open repos from Front. Function is called by FlowTradingGUI.py > onOpenRepos
"""
selectString = "t.trdnbr, u.insid, u.isin, i.ref_value*t.quantity, l.fixed_rate"
fromString = "trade t, instrument i, portfolio p, instrument u, leg l"
whereString = "t.prfnbr=p.prfnbr and i.insaddr=t.insaddr and i.instype='Repo/Reverse' and t.status='BO Confirmed' and i.exp_day > YESTERDAY and p.prfid in " + str(
tuple(LDNFLOWBOOKS)
) + " and i.und_insaddr = u.insaddr and l.insaddr = i.insaddr"
res = self.sql_query('select ' + selectString + ' from ' + fromString +
' where ' + whereString)
output = pandas.DataFrame(
pandas.np.array(res),
columns=['trdnbr', 'insid', 'ISIN', 'Quantity', 'Rate'])
output['trdnbr'] = output['trdnbr'].astype(int)
output[['Quantity', 'Rate']] = output[['Quantity',
'Rate']].astype(float)
openRepos = output.copy()
del openRepos['insid']
isinsregs = pandas.Series(bonds.index, index=bonds['REGS'])
isins144a = pandas.Series(bonds.index, index=bonds['144A'])
isinsregs.name = 'BondREGS'
isins144a.name = 'Bond144A'
isinsregs = isinsregs.drop(isinsregs.index.get_duplicates())
isins144a = isins144a.drop(isins144a.index.get_duplicates())
openRepos = openRepos.join(allisins, on='ISIN')
openRepos = openRepos.join(isinsregs, on='ISIN')
openRepos = openRepos.join(isins144a, on='ISIN')
openRepos['Series'] = ''
for i in openRepos.index:
if pandas.isnull(openRepos.loc[i, 'BondREGS']):
openRepos.set_value(i, 'Series', '144A')
else:
openRepos.set_value(i, 'Series', 'REGS')
openRepos = openRepos[[
'trdnbr', 'Bond', 'Series', 'ISIN', 'Quantity', 'Rate'
]]
openRepos.sort_values(by='Bond', inplace=True)
return openRepos