예제 #1
0
 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
예제 #2
0
 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
예제 #3
0
 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
예제 #4
0
 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