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
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,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 __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 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
