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 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
Example #3
0
 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())
Example #4
0
 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
Example #5
0
 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
Example #6
0
    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