def postcleanup(self): """Function to cleanse data after split trades have been remerged. <--Still in use? """ self.df['USDQty']=self.df['Qty'] nusd=self.df[self.df['CCY']!='USD'][['USDQty','Qty','Year','CCY']].copy() #for y in range(2009,2015,1): # for c in ccy.index: # nusd['USDQty'][(nusd['Year']==y) & (nusd['CCY']==c)]=nusd['Qty']/ccy.get_value(c,str(y))#bitwise & - careful! otherwise pandas.np.all(c1,c2,axis=0) works for y in range(2009,2017,1): for c in ccy.index: nusd.loc[(nusd['Year']==y) & (nusd['CCY']==c),'USDQty']=nusd['Qty']/ccy.loc[c,str(y)]#bitwise & - careful! otherwise pandas.np.all(c1,c2,axis=0) works self.df.loc[self.df['CCY']!='USD','USDQty']=nusd['USDQty'] self.df['MKu'].fillna(0,inplace=True) self.df['AbsQty']=self.df['USDQty'].abs() self.df['SC']=self.df['SCu']*self.df['AbsQty']/10000. self.df['MK']=self.df['MKu']*self.df['AbsQty']/10000. self.df['TotalSC']=self.df['SC']+self.df['MK'] #del self.df['DateSTR'] #self.df.sort(columns='DateDT',inplace=True) self.df.sort_values(by='DateDT',inplace=True) self.df.reset_index(inplace=True) self.df=self.df.join(bonds['TICKER'], on='Bond') self.df=self.df.join(bonds['CNTRY_OF_RISK'], on='Bond') self.df=self.df.join(bonds['INDUSTRY_GROUP'], on='Bond') #self.df['INDUSTRY_GROUP'][self.df['INDUSTRY_GROUP']!='Sovereign']='Corporate' self.df.loc[self.df['INDUSTRY_GROUP']!='Sovereign','INDUSTRY_GROUP']='Corporate' self.df.rename(columns={'TICKER':'Issuer','CNTRY_OF_RISK':'Country','INDUSTRY_GROUP':'Industry'},inplace=True) self.df=self.df.join(countries.set_index('Country code',verify_integrity=True)['Region'],on='Country') self.df['Country'].fillna('na',inplace=True) self.df['Region'].fillna('na',inplace=True) self.df=self.df[self.df['Book'].apply(filterLondonBooks)] pass
def __init__(self, rebuild=False, forceLastDay=False): self.savepath = MAPATH+'ma_full.csvz' if rebuild or (not os.path.exists(self.savepath)): self.load_files_full() elif datetime.datetime.fromtimestamp(os.path.getmtime(self.savepath)).date()<datetime.datetime.today().date() or forceLastDay: self.load_files() else: self.df = pandas.read_csv(self.savepath, parse_dates=['Inquiry Timestamp'], index_col=0, compression='bz2', dtype=IMPORT_DIC, low_memory=False)#, usecols=USECOLS) self.df = self.df[['Status','Client','CLT Trader','Bid/Offer','ISIN','Inquiry Timestamp','Currency','Local Inquiry Volume','Product']] self.df = self.df[self.df['Product']=='Emerging Markets'].copy() del self.df['Product'] self.df.rename(columns = {'Local Inquiry Volume':'AbsQty', 'Currency':'CCY','Inquiry Timestamp':'Date'}, inplace = True) self.df = self.df[self.df['CCY'].isin(['USD','EUR','CHF','GBP'])] self.df = self.df.join(allisins, on = 'ISIN') ma_counterparties = counterparties[counterparties['MAName'].notnull()] ma_counterparties.set_index('MAName', inplace=True) self.df = self.df.join(ma_counterparties['Counterparty'],on='Client') self.df = self.df.join(ccy['2017'],on='CCY') #self.df['AbsUSDQty'] = self.df.apply(lambda row:row['AbsQty']/ccy.loc[row['CCY'],'2016'],axis=1) ##TOO SLOW self.df['AbsUSDQty'] = self.df['AbsQty'] / self.df['2017'] del self.df['2017'] self.df['USDQty'] = self.df['AbsUSDQty'] self.df.loc[self.df['Bid/Offer']=='Offer','USDQty'] = -self.df.loc[self.df['Bid/Offer']=='Offer','USDQty'] self.df = self.df.join(bonds[['TICKER','CNTRY_OF_RISK']],on='Bond') self.df.rename(columns={'TICKER':'Issuer','CNTRY_OF_RISK':'Country'},inplace=True) self.df = self.df.join(countries.set_index('Country code',verify_integrity=True)['Region'],on='Country') self.df['DateDT'] = self.df['Date'] #self.df['DateDT'] = pandas.to_datetime(self.df['Date'],format='%d/%m/%Y %H:%M:%S') self.df['Date'] = self.df['DateDT'].apply(lambda x:x.date()) # we filter for error trades self.df = self.df[self.df['AbsUSDQty']<100000] # filtering for likely error trades #del self.df['Client'] - WE NEED THIS SO WE CAN LOOK FOR NAN self.df['Venue'] = 'MA'
def build_positions(self): """Builds trade positions. Function is called when building trade history data in __init__ """ #Hard-coded 2016 FX rates positions = self.df.groupby(self.df['Bond'])['Qty'].sum() positions = pandas.DataFrame(positions) #positions=positions[positions['Qty']!=0]#this seems to mess up the risktree build on position refresh positions['Issuer'] = bonds['TICKER'] positions['Country'] = bonds['CNTRY_OF_RISK'] positions['CCY'] = bonds['CRNCY'] positions['Maturity'] = bonds['MATURITY'] positions['MaturityDT'] = positions['Maturity'].apply(getMaturityDate) positions = positions[positions['MaturityDT']>=datetime.datetime.today()] positions = positions.join(countries.set_index('Country code',verify_integrity=True)['Region'],on='Country') #positions['USDQty']=positions['Qty'] positions['Country'].fillna('na',inplace=True) positions['Region'].fillna('na',inplace=True) try: #This can fail if you add a new bond and then forget to update the bonduniverse. positions['USDQty'] = positions.apply(lambda row:row['Qty']/ccy.loc[row['CCY'],'2016'],axis=1) except: positions['USDQty'] = 0 # nusd=positions[positions['CCY']!='USD'][['USDQty','Qty','CCY']].copy() # #for c in ccy.index: # # nusd['USDQty'][nusd['CCY']==c]=nusd['Qty']/ccy.get_value(c,'2015') # for c in ccy.index: # nusd.loc[nusd['CCY']==c,'USDQty']=nusd['Qty']/ccy.loc[c,'2016'] # positions.loc[positions['CCY']!='USD','USDQty']=nusd['USDQty'] positions['Bond'] = positions.index#needed self.positions = positions pass
def build_positionsISINBook(self): """only launched once to get the start of day risk, which will be used for PnL """ positions = self.df.groupby(['ISIN','Book'],as_index=False)['Qty'].sum() positions = positions[(positions['Qty']>1) | (positions['Qty']<-1)]#useful here, filter zero positions and errors on amortized bonds positions = positions.join(allisins,on='ISIN') positions = positions.join(bonds['TICKER'],on='Bond') positions = positions.join(bonds['CNTRY_OF_RISK'],on='Bond') positions = positions.join(bonds['CRNCY'],on='Bond') positions = positions.join(bonds['MATURITY'],on='Bond') positions['MaturityDT']=positions['MATURITY'].apply(getMaturityDate) positions=positions[positions['MaturityDT']>=datetime.datetime.today()] positions.rename(columns={'Qty':'SOD_Pos','CNTRY_OF_RISK':'Country','TICKER':'Issuer','CRNCY':'CCY'},inplace=True) positions = positions.join(countries.set_index('Country code',verify_integrity=True)['Region'],on='Country') positions['Country'].fillna('na',inplace=True) positions['Region'].fillna('na',inplace=True) #positions['ISIN']=positions.index#needed positions=positions[['Bond','Book','CCY','ISIN','Issuer','Country', 'Region', 'SOD_Pos']] positions=positions[pandas.notnull(positions['Bond'])]#filter for bonds that are not understood positions['Key'] = positions['Book']+'-'+positions['ISIN'] positions['Series'] = '' for (i,row) in positions.iterrows(): if row['ISIN']==bonds.loc[row['Bond'],'REGS']: positions.loc[i,'Series'] = 'REGS' else: positions.loc[i,'Series'] = '144A' self.positionsByISINBook = positions pass
def build_positions_new(self): positions = self.df.groupby(['ISIN','Book'],as_index=False)['Qty'].sum() positions = positions[(positions['Qty']>1) | (positions['Qty']<-1)]#useful here, filter zero positions and errors on amortized bonds positions = positions.join(allisins,on='ISIN') positions['Series'] = '' positions = positions.loc[positions['Bond'].notnull()].copy() for (i,row) in positions.iterrows(): if row['ISIN'] == bonds.loc[row['Bond'],'REGS']: positions.loc[i,'Series'] = 'REGS' else: positions.loc[i,'Series'] = '144A' grp = positions.groupby(['Bond','Series']) positions = grp['Qty'].sum().unstack().fillna(0) positions['Issuer'] = bonds['TICKER'] positions['Country'] = bonds['CNTRY_OF_RISK'] positions['CCY'] = bonds['CRNCY'] positions['Maturity'] = bonds['MATURITY'] positions['MaturityDT'] = positions['Maturity'].apply(getMaturityDate) positions = positions[positions['MaturityDT']>=datetime.datetime.today()] positions = positions.join(countries.set_index('Country code',verify_integrity=True)['Region'],on='Country') positions['Country'].fillna('na',inplace=True) positions['Region'].fillna('na',inplace=True) try: #This can fail if you add a new bond and then forget to update the bonduniverse. positions['USDQty'] = positions.apply(lambda row:row['Qty']/ccy.loc[row['CCY'],'2017'],axis=1) except: positions['USDQty'] = 0 positions['Bond'] = positions.index#needed self.positions_new = positions pass
def __init__(self, rebuild=False): self.savepath = MAPATH+'ma_full.csvz' if rebuild: self.load_files_full() if not(os.path.exists(self.savepath)) or datetime.datetime.fromtimestamp(os.path.getmtime(self.savepath)).date()<datetime.datetime.today().date(): self.load_files() else: self.df = pandas.read_csv(self.savepath, parse_dates=['Inquiry Timestamp'], index_col=0, compression='bz2') self.df = self.df[['Status','Client','CLT Trader','Bid/Offer','ISIN','Inquiry Timestamp','Currency','Local Inquiry Volume']] self.df.rename(columns = {'Local Inquiry Volume':'AbsQty', 'Currency':'CCY','Inquiry Timestamp':'Date'}, inplace = True) self.df = self.df[self.df['CCY'].isin(['USD','EUR','CHF','GBP'])] self.df = self.df.join(allisins, on = 'ISIN') ma_counterparties = counterparties[counterparties['MAName'].notnull()] ma_counterparties.set_index('MAName', inplace=True) self.df = self.df.join(ma_counterparties['Counterparty'],on='Client') self.df = self.df.join(ccy['2016'],on='CCY') #self.df['AbsUSDQty'] = self.df.apply(lambda row:row['AbsQty']/ccy.loc[row['CCY'],'2016'],axis=1) ##TOO SLOW self.df['AbsUSDQty'] = self.df['AbsQty'] / self.df['2016'] del self.df['2016'] self.df['USDQty'] = self.df['AbsUSDQty'] self.df.loc[self.df['Bid/Offer']=='Offer','USDQty'] = -self.df.loc[self.df['Bid/Offer']=='Offer','USDQty'] self.df = self.df.join(bonds[['TICKER','CNTRY_OF_RISK']],on='Bond') self.df.rename(columns={'TICKER':'Issuer','CNTRY_OF_RISK':'Country'},inplace=True) self.df = self.df.join(countries.set_index('Country code',verify_integrity=True)['Region'],on='Country') self.df['DateDT'] = self.df['Date'] #self.df['DateDT'] = pandas.to_datetime(self.df['Date'],format='%d/%m/%Y %H:%M:%S') self.df['Date'] = self.df['DateDT'].apply(lambda x:x.date())
def __init__(self, th, parent): """ Keyword arguments: parent : parent th = trade history (defaults to empty array if not specified) """ self.th = th self.parent = parent self.EODPricesFilled = False #self.LivePricesFilled = False self.bdmReady = False self.lock = threading.Lock() self.cntrymap = countries.set_index('Country code') self.cntrymap.rename(columns={'Long name': 'LongCountry'}, inplace=True) self.riskFreeIssuers = ['T', 'DBR', 'UKT', 'OBL'] #RISK TREE self.th.positions['EODPrice'] = 0.0 self.th.positions['EODValue'] = 0.0 #self.th.positions['Risk'] = 0.0 self.displayPositions = self.th.positions[ (self.th.positions['Qty'] <= -1) | (self.th.positions['Qty'] >= 1)].copy() # del self.displayPositions['REGS'] # del self.displayPositions['144A'] # self.displayPositions = self.displayPositions.join(bonds['REGS']) self.displayPositions = self.displayPositions.join( self.cntrymap['LongCountry'], on='Country') self.displayPositions['NewTrades'] = 0.0 self.displayPositions.index.name = 'BondName' # to avoid warning in the next line self.displayGroup = self.displayPositions.groupby( ['Region', 'LongCountry', 'Issuer', 'Bond']).sum() #self.rateDisplayGroup = self.displayPositions.groupby(['CCY','Bond']).sum() #print self.rateDisplayGroup #BOOK AND PnL TREE self.th.positionsByISINBook['Qty'] = self.th.positionsByISINBook[ 'SOD_Pos'] #Qty will be current, SOD is start of day for c in [ 'EODPrice', 'EODValue', 'PriceY', 'Risk', 'USDQty', 'PriceT', 'SODPnL', 'TradePnL', 'TotalPnL', 'MK', 'PRINCIPAL_FACTOR', 'RISK_MID', 'SAVG', 'IRRisk' ]: self.th.positionsByISINBook[c] = pandas.np.nan self.th.positionsByISINBook = self.th.positionsByISINBook.join( self.cntrymap['LongCountry'], on='Country') self.th.positionsByISINBook.set_index('Key', inplace=True) self.th.positionsByISINBook['NewTrades'] = 0.0 self.displayGroupBook = self.th.positionsByISINBook.groupby( ['Book', 'LongCountry', 'Issuer', 'Bond', 'Series']).sum() self.traded_bonds = [] # IMPORTANT self.new_trades = self.th.df[self.th.df['Date'] == todayDateSTR].copy() self.EODPrices = self.th.positions['EODPrice'].copy() pub.subscribe(self.updatePrice, "BOND_PRICE_UPDATE") pub.subscribe(self.switchBDMReady, "BDM_READY") pub.subscribe(self.onUpdateTree, "POSITION_UPDATE") #pub.subscribe(self.updateBGNPrices, "BGN_PRICE_UPDATE") pass
def onUpdateTreeNew(self, firstBuild=False): pythoncom.CoInitialize() if firstBuild: self.th.positions['EODPrice']=0 self.th.positions['EODValue']=0 self.displayPositions=self.th.positions[(self.th.positions['Qty']<=-1) | (self.th.positions['Qty']>=1)].copy() self.displayPositions=self.displayPositions.join(bonds['REGS']) c=countries.set_index('Country code') self.displayPositions=self.displayPositions.join(c['Long name'],on='Country') self.displayPositions.rename(columns={'Long name':'LongCountry'},inplace=True) displayGroup=self.displayPositions.groupby(['Region','LongCountry','Issuer','Bond']).sum() self.doBuildTree(displayGroup,[]) else: wx.CallAfter(self.treeRebuild)
def __init__(self,filename): ma=pandas.read_csv(MAPATH+filename,thousands=',') self.df=ma[['Action','Size (000\'s)','Currency','Security','Identifier','Client','Client Trader','Date']].copy() self.df.rename(columns={'Size (000\'s)':'AbsQty','Currency':'CCY','Client':'MACounterparty','Identifier':'ISIN'}, inplace = True) self.df=self.df[self.df['CCY'].isin(['USD','EUR','CHF','GBP'])] self.df['AbsQty']=self.df['AbsQty']/1000. self.df['Qty']=self.df['AbsQty'] self.df.loc[self.df['Action']=='Offer','Qty']=-1*self.df['AbsQty'] self.df['Qty']=self.df['Qty'] self.df=self.df.join(allisins,on='ISIN') self.df['USDQty']=self.df.apply(lambda row:row['Qty']/ccy.loc[row['CCY'],'2016'],axis=1) self.df=self.df.join(bonds[['TICKER','CNTRY_OF_RISK']],on='Bond') self.df.rename(columns={'TICKER':'Issuer','CNTRY_OF_RISK':'Country'},inplace=True) self.df=self.df.join(countries.set_index('Country code',verify_integrity=True)['Region'],on='Country') pass
def build_positions(self): """Builds trade positions. Function is called when building trade history data in __init__ """ #Hard-coded 2017 FX rates positions = self.df.groupby(self.df['Bond'])['Qty'].sum() positions = pandas.DataFrame(positions) #positions=positions[positions['Qty']!=0]#this seems to mess up the risktree build on position refresh positions['Issuer'] = bonds['TICKER'] positions['Country'] = bonds['CNTRY_OF_RISK'] positions['CCY'] = bonds['CRNCY'] positions['Maturity'] = bonds['MATURITY'] positions['MaturityDT'] = positions['Maturity'].apply(getMaturityDate) positions = positions[positions['MaturityDT']>=datetime.datetime.today()] positions = positions.join(countries.set_index('Country code',verify_integrity=True)['Region'],on='Country') #positions['USDQty']=positions['Qty'] positions['Country'].fillna('na',inplace=True) positions['Region'].fillna('na',inplace=True) try: #This can fail if you add a new bond and then forget to update the bonduniverse. positions['USDQty'] = positions.apply(lambda row:row['Qty']/ccy.loc[row['CCY'],'2017'],axis=1) except: positions['USDQty'] = 0 positions['Bond'] = positions.index#needed self.positions = positions # positions = self.df.groupby(['ISIN','Book'],as_index=False)['Qty'].sum() positions = positions[(positions['Qty']>1) | (positions['Qty']<-1)]#useful here, filter zero positions and errors on amortized bonds positions = positions.join(allisins, on='ISIN') positions['Series'] = '' positions = positions.loc[positions['Bond'].notnull()].copy() for (i, row) in positions.iterrows(): if row['ISIN'] == bonds.loc[row['Bond'], 'REGS']: positions.loc[i,'Series'] = 'REGS' else: positions.loc[i,'Series'] = '144A' grp = positions.groupby(['Bond','Series']) positions = grp['Qty'].sum().unstack().fillna(0) if not 'REGS' in positions.columns: positions['REGS'] = 0 if not '144A' in positions.columns: positions['144A'] = 0 self.positions[['REGS','144A']] = positions[['REGS','144A']] pass
def __init__(self, rebuild=False, forceLastDay=False): self.savepath = BBGPATH+'bbg_full.csvz' if rebuild or (not os.path.exists(self.savepath)): self.load_files_full() elif datetime.datetime.fromtimestamp(os.path.getmtime(self.savepath)).date()<datetime.datetime.today().date() or forceLastDay: self.load_files() else: self.df = pandas.read_csv(self.savepath, index_col=0, compression='bz2', dtype=IMPORT_DIC_BBG)#, usecols=USECOLS) self.df = self.df[['Time','DlrSide','Qty (M)','Status','Customer', 'ISIN','Trade Dt','UserName']] self.df = self.df.copy() self.df['strDate'] = self.df['Trade Dt'] + ' ' + self.df['Time'] # Convert NY time to UK time - adding 5h which is not always correct (DST) self.df['DateDT'] = pandas.to_datetime(self.df['strDate']) + pandas.Timedelta(hours=5) self.df['Date'] = self.df['DateDT'].apply(lambda x: x.date()) # self.df['Time'] = self.df['DateDT'].dt.strftime('%X') del self.df['strDate'] del self.df['Time'] del self.df['Trade Dt'] # self.df = self.df.join(allisins, on = 'ISIN') self.df = self.df.join(bonds['CRNCY'], on = 'Bond') self.df.rename(columns = {'Qty (M)':'AbsQty', 'Customer': 'Client', 'CRNCY': 'CCY', 'DlrSide': 'Bid/Offer', 'UserName': '******'}, inplace = True) bbg_counterparties = counterparties[counterparties['BBGName'].notnull()] bbg_counterparties.set_index('BBGName', inplace=True) self.df = self.df.join(bbg_counterparties['Counterparty'], on='Client') self.df = self.df.join(ccy['2017'], on='CCY') #self.df['AbsUSDQty'] = self.df.apply(lambda row:row['AbsQty']/ccy.loc[row['CCY'],'2016'],axis=1) ##TOO SLOW self.df['AbsUSDQty'] = self.df['AbsQty'] / self.df['2017'] del self.df['2017'] self.df['USDQty'] = self.df['AbsUSDQty'] self.df.loc[self.df['Bid/Offer']=='S','USDQty'] = -self.df.loc[self.df['Bid/Offer']=='S','USDQty'] self.df = self.df.join(bonds[['TICKER','CNTRY_OF_RISK']],on='Bond') self.df.rename(columns={'TICKER':'Issuer','CNTRY_OF_RISK':'Country'},inplace=True) self.df = self.df.join(countries.set_index('Country code',verify_integrity=True)['Region'],on='Country') self.df['Venue'] = 'BBG'
def __init__(self, th, parent): """Keyword arguments: parent : parent th = trade history (defaults to empty array if not specified) """ self.th = th self.parent = parent self.EODPricesFilled = False #self.LivePricesFilled = False self.bdmReady = False self.lock = threading.Lock() self.cntrymap = countries.set_index('Country code') self.cntrymap.rename(columns={'Long name':'LongCountry'}, inplace=True) self.riskFreeIssuers = ['T','DBR','UKT'] #RISK TREE self.th.positions['EODPrice'] = 0.0 self.th.positions['EODValue'] = 0.0 self.th.positions['Risk'] = 0.0 self.displayPositions = self.th.positions[(self.th.positions['Qty']<=-1) | (self.th.positions['Qty']>=1)].copy() self.displayPositions = self.displayPositions.join(bonds['REGS']) self.displayPositions = self.displayPositions.join(self.cntrymap['LongCountry'],on='Country') self.displayGroup = self.displayPositions.groupby(['Region','LongCountry','Issuer','Bond']).sum() #BOOK AND PnL TREE self.th.positionsByISINBook['Qty'] = self.th.positionsByISINBook['SOD_Pos']#Qty will be current, SOD is start of day for c in ['EODPrice','EODValue','PriceY','Risk','USDQty','PriceT','SODPnL','TradePnL','TotalPnL','MK','PRINCIPAL_FACTOR','RISK_MID']: self.th.positionsByISINBook[c] = pandas.np.nan self.th.positionsByISINBook = self.th.positionsByISINBook.join(self.cntrymap['LongCountry'],on='Country') self.th.positionsByISINBook.set_index('Key',inplace=True) self.displayGroupBook = self.th.positionsByISINBook.groupby(['Book','LongCountry','Issuer','Bond','Series']).sum() self.traded_bonds = [] # IMPORTANT self.new_trades = self.th.df[self.th.df['Date']==todayDateSTR].copy() self.EODPrices = self.th.positions['EODPrice'].copy() pub.subscribe(self.updatePrice, "BOND_PRICE_UPDATE") pub.subscribe(self.switchBDMReady, "BDM_READY") pub.subscribe(self.onUpdateTree, "POSITION_UPDATE") pass
def treeRebuild(self): self.tree.DeleteAllItems() self.root = self.tree.AddRoot("Total") pythoncom.CoInitialize() _offsets = (3, 1, 1, 1, 1, 1, 2) yesterday = (datetime.datetime.today() - datetime.timedelta(days=_offsets[datetime.datetime.today().weekday()])).strftime('%Y-%m-%d') c=countries.set_index('Country code') traded_bonds = self.th.df[self.th.df['Date']==todayDateSTR]['Bond'].drop_duplicates().dropna().copy() new_bonds = list(set(traded_bonds)-set(self.displayPositions.index)) self.th.positions['EODPrice']=self.EODPrices self.th.positions['EODPrice'].fillna(0,inplace=True) #print new_bonds for bond in new_bonds: price = self.fc.historical_price_query(bonds.loc[bond,'REGS'], yesterday) if price==0: price = self.th.df[self.th.df['Bond']==bond].iloc[-1]['Price'] self.th.positions.loc[bond,'EODPrice'] = price self.EODPrices = self.th.positions['EODPrice'].copy() #Retrieve principal factor for traded bonds self.th.positions['PRINCIPAL_FACTOR']=self.principalFactor if len(new_bonds)>0: newisins=map(lambda x:bonds.loc[x,'REGS']+ ' Corp',new_bonds) blpts = blpapiwrapper.BLPTS(newisins, ['PRINCIPAL_FACTOR']) blpts.get() blpts.closeSession() blpts.output['REGS'] = blpts.output.index.str[:-5] blpts.output['Bond'] = blpts.output['REGS'].replace(isinsregs) blpts.output.set_index('Bond', inplace=True) self.th.positions.loc[new_bonds,'PRINCIPAL_FACTOR']=blpts.output['PRINCIPAL_FACTOR'].astype(float) self.principalFactor=self.th.positions['PRINCIPAL_FACTOR'] self.th.positions['EODValue']=self.th.positions['EODPrice']*self.th.positions['USDQty']/100.*(self.th.positions['PRINCIPAL_FACTOR']) self.displayPositions=self.th.positions.loc[list(self.displayPositions.index)+new_bonds]#SOD risk + new trades self.displayPositions=self.displayPositions.join(c['Long name'],on='Country') self.displayPositions.rename(columns={'Long name':'LongCountry'},inplace=True) displayGroup=self.displayPositions.groupby(['Region','LongCountry','Issuer','Bond']).sum() self.doBuildTree(displayGroup,traded_bonds)
def getBookPnL(self, book): """Function to get PnLitems and calculate PnL for the specified book. Keyword argument: book : 'APGSG'/'HYCRE'/'KAZAK'/'RUSSI'/'SPCOR'/'SPOT'/'SPTEE'/'TURKE'/'STLBK' """ if self.tradeHistory==[]: df = self.fc.load_book(book,self.today) else: #todaySOD=datetime.datetime.today() #todaySOD=datetime.datetime(todaySOD.year,todaySOD.month,todaySOD.day) #tdf=self.tradeHistory.df[(self.tradeHistory.df['Book']==book) & (self.tradeHistory.df['DateDT']<todaySOD)] #print tdf.shape # df = pandas.DataFrame(tdf.groupby('ISIN')['Qty'].sum()) # df = df[df['Qty']!=0] # df['Bond']=allisins # df = df.dropna() # df = df.join(bonds['MATURITY'], on='Bond') # df['MaturityDT']=df['MATURITY'].apply(getMaturityDate) # df = df[df['MaturityDT']>=datetime.datetime.today()] # df['ISIN'] = df.index # df.rename(columns={'Qty':'SOD_Pos'},inplace=True) df=self.tradeHistory.positionsByISINBook[self.tradeHistory.positionsByISINBook['Book']==book].copy() df.set_index('ISIN',drop=False,inplace=True) df['PriceY'] = 0.0 df['PriceT'] = 0.0 df = df[['Bond','ISIN','SOD_Pos','PriceY','PriceT']] df['PriceY'] = df['ISIN'].apply(lambda i:self.fc.historical_price_query(i,self.yesterday)) df['PriceT'] = df['ISIN'].apply(lambda i:self.fc.closing_price_query(i)) df = df.drop_duplicates('ISIN')#seems some spurious data in STLBK #print df newTradesB=self.newTrades[self.newTrades['Book']==book].copy() newIsins=set(newTradesB['ISIN'])-set(df['ISIN']) for i in newIsins: #print iz` if not i in allisins: continue row=[allisins[i],i,0,self.fc.historical_price_query(i,self.yesterday),self.fc.closing_price_query(i)] rowdf=pandas.DataFrame(data=[row],columns=df.columns) df=df.append(rowdf,ignore_index=True) df['dP']=df['PriceT']-df['PriceY'] #Added a try here, because program crashes if newTrades.csv is empty try: if book in self.positionDeltas.index.get_level_values('Book'): #This line can't execute if newTrades.csv is empty df=df.join(self.positionDeltas[book],on='ISIN') else: df['Qty']=0 except: df['Qty']=0 #Manually create a new column and hack it to 0 df['Qty'].fillna(0,inplace=True) #print df df.rename(columns={'Qty':'dPos'},inplace=True) if self.tradeHistory==[]: df.rename(columns={'Position':'EOD_Pos'},inplace=True) df['SOD_Pos']=df['EOD_Pos']-df['dPos'] else: df['EOD_Pos']=df['SOD_Pos']+df['dPos'] df['SOD_PnL']=df['SOD_Pos']*df['dP']/100. df['Book']=book df['Key']=df['ISIN'].apply(lambda x:book+'-'+x) df=df.set_index('ISIN', drop=False, verify_integrity=True) newTradesB=newTradesB.join(df['PriceT'],on='ISIN') newTradesB['TradePnL']=newTradesB['Qty']*(newTradesB['PriceT']-newTradesB['Price'])/100. #self.newTradesB=newTradesB isinPnL=newTradesB.groupby('ISIN')['TradePnL'].sum() df['Trade_PnL']=isinPnL df['Trade_PnL'].fillna(0,inplace=True) df['Total_PnL']=df['SOD_PnL']+df['Trade_PnL'] df=df[df['Total_PnL']!=0] df=df.join(bonds['CNTRY_OF_RISK'], on='Bond') df=df.join(bonds['CRNCY'], on='Bond') df=df.join(bonds['TICKER'], on='Bond') df=df.join(countries.set_index('Country code'), on='CNTRY_OF_RISK') df.rename(columns={'Long name':'Country','TICKER':'Issuer'},inplace=True) del df['CNTRY_OF_RISK'] df['USD_Total_PnL']=df['Total_PnL'] nusd=df[df['CRNCY']!='USD'][['USD_Total_PnL','Total_PnL','CRNCY']].copy() for c in ccy.index: i = nusd['CRNCY']==c nusd.loc[i,'USD_Total_PnL']=nusd['Total_PnL']/ccy.get_value(c,'2015') df.loc[df['CRNCY']!='USD','USD_Total_PnL']=nusd['USD_Total_PnL'] ##HACK - could be pandas bug - https://github.com/pydata/pandas/issues/6322## df['Region'].fillna('US',inplace=True) df['Country'].fillna('US',inplace=True) df['Issuer'].fillna('US',inplace=True) ## df.sort(['Region','Country','Bond'],inplace=True) df=df[['Key','Book','Bond','ISIN','CRNCY','Issuer','Region','Country','SOD_Pos','EOD_Pos','dPos','PriceY','PriceT','dP','SOD_PnL','Trade_PnL','Total_PnL','USD_Total_PnL']] return df
def getBookPnL(self, book): """Function to get PnLitems and calculate PnL for the specified book. Keyword argument: book : 'APGSG'/'HYCRE'/'KAZAK'/'RUSSI'/'SPCOR'/'SPOT'/'SPTEE'/'TURKE'/'STLBK' """ if self.tradeHistory == []: df = self.fc.load_book(book, self.today) else: #todaySOD=datetime.datetime.today() #todaySOD=datetime.datetime(todaySOD.year,todaySOD.month,todaySOD.day) #tdf=self.tradeHistory.df[(self.tradeHistory.df['Book']==book) & (self.tradeHistory.df['DateDT']<todaySOD)] #print tdf.shape # df = pandas.DataFrame(tdf.groupby('ISIN')['Qty'].sum()) # df = df[df['Qty']!=0] # df['Bond']=allisins # df = df.dropna() # df = df.join(bonds['MATURITY'], on='Bond') # df['MaturityDT']=df['MATURITY'].apply(getMaturityDate) # df = df[df['MaturityDT']>=datetime.datetime.today()] # df['ISIN'] = df.index # df.rename(columns={'Qty':'SOD_Pos'},inplace=True) df = self.tradeHistory.positionsByISINBook[ self.tradeHistory.positionsByISINBook['Book'] == book].copy() df.set_index('ISIN', drop=False, inplace=True) df['PriceY'] = 0.0 df['PriceT'] = 0.0 df = df[['Bond', 'ISIN', 'SOD_Pos', 'PriceY', 'PriceT']] df['PriceY'] = df['ISIN'].apply( lambda i: self.fc.historical_price_query(i, self.yesterday)) df['PriceT'] = df['ISIN'].apply( lambda i: self.fc.closing_price_query(i)) df = df.drop_duplicates('ISIN') #seems some spurious data in STLBK #print df newTradesB = self.newTrades[self.newTrades['Book'] == book].copy() newIsins = set(newTradesB['ISIN']) - set(df['ISIN']) for i in newIsins: #print iz` if not i in allisins: continue row = [ allisins[i], i, 0, self.fc.historical_price_query(i, self.yesterday), self.fc.closing_price_query(i) ] rowdf = pandas.DataFrame(data=[row], columns=df.columns) df = df.append(rowdf, ignore_index=True) df['dP'] = df['PriceT'] - df['PriceY'] #Added a try here, because program crashes if newTrades.csv is empty try: if book in self.positionDeltas.index.get_level_values( 'Book' ): #This line can't execute if newTrades.csv is empty df = df.join(self.positionDeltas[book], on='ISIN') else: df['Qty'] = 0 except: df['Qty'] = 0 #Manually create a new column and hack it to 0 df['Qty'].fillna(0, inplace=True) #print df df.rename(columns={'Qty': 'dPos'}, inplace=True) if self.tradeHistory == []: df.rename(columns={'Position': 'EOD_Pos'}, inplace=True) df['SOD_Pos'] = df['EOD_Pos'] - df['dPos'] else: df['EOD_Pos'] = df['SOD_Pos'] + df['dPos'] df['SOD_PnL'] = df['SOD_Pos'] * df['dP'] / 100. df['Book'] = book df['Key'] = df['ISIN'].apply(lambda x: book + '-' + x) df = df.set_index('ISIN', drop=False, verify_integrity=True) newTradesB = newTradesB.join(df['PriceT'], on='ISIN') newTradesB['TradePnL'] = newTradesB['Qty'] * ( newTradesB['PriceT'] - newTradesB['Price']) / 100. #self.newTradesB=newTradesB isinPnL = newTradesB.groupby('ISIN')['TradePnL'].sum() df['Trade_PnL'] = isinPnL df['Trade_PnL'].fillna(0, inplace=True) df['Total_PnL'] = df['SOD_PnL'] + df['Trade_PnL'] df = df[df['Total_PnL'] != 0] df = df.join(bonds['CNTRY_OF_RISK'], on='Bond') df = df.join(bonds['CRNCY'], on='Bond') df = df.join(bonds['TICKER'], on='Bond') df = df.join(countries.set_index('Country code'), on='CNTRY_OF_RISK') df.rename(columns={ 'Long name': 'Country', 'TICKER': 'Issuer' }, inplace=True) del df['CNTRY_OF_RISK'] df['USD_Total_PnL'] = df['Total_PnL'] nusd = df[df['CRNCY'] != 'USD'][[ 'USD_Total_PnL', 'Total_PnL', 'CRNCY' ]].copy() for c in ccy.index: i = nusd['CRNCY'] == c nusd.loc[i, 'USD_Total_PnL'] = nusd['Total_PnL'] / ccy.get_value( c, '2015') df.loc[df['CRNCY'] != 'USD', 'USD_Total_PnL'] = nusd['USD_Total_PnL'] ##HACK - could be pandas bug - https://github.com/pydata/pandas/issues/6322## df['Region'].fillna('US', inplace=True) df['Country'].fillna('US', inplace=True) df['Issuer'].fillna('US', inplace=True) ## df.sort(['Region', 'Country', 'Bond'], inplace=True) df = df[[ 'Key', 'Book', 'Bond', 'ISIN', 'CRNCY', 'Issuer', 'Region', 'Country', 'SOD_Pos', 'EOD_Pos', 'dPos', 'PriceY', 'PriceT', 'dP', 'SOD_PnL', 'Trade_PnL', 'Total_PnL', 'USD_Total_PnL' ]] return df