def _GetExistingHoldings(start_date,
bbgcode=None,
platform=None,
base_ccy='HKD'):
tn = setup.GetAllTransactions()
tn = tn[tn.Date < start_date]
if platform is not None:
tn = tn[tn.Platform == platform]
# only include the supported instruments
support_instruments = setup.GetListOfSupportedInstruments()
tn = tn[tn.BBGCode.isin(support_instruments)]
holdings = tn.groupby(['Platform', 'BBGCode']).agg({
'NoOfUnits':
'sum',
'CostInPlatformCcy':
'sum'
})
holdings = holdings[holdings.NoOfUnits != 0]
holdings = holdings.reset_index()
# calculate the cost and valuation in base ccy equivalent (cost in platform ccy, val in sec ccy)
historical_data = mdata.GetHistoricalData()
val = historical_data.copy()
val = val[val.Date < start_date]
for i in range(len(holdings)):
row = holdings.iloc[i]
holdings.loc[i,
'PlatformCcy'] = setup.GetPlatformCurrency(row.Platform)
holdings.loc[i, 'SecurityCcy'] = setup.GetSecurityCurrency(row.BBGCode)
# add valuation here
v = val[val.BBGCode == row.BBGCode]
if len(v) == 0:
print('WARNING: no market data - check feed/date range')
holdings.loc[i, 'Close'] = v.iloc[-1].Close
holdings.loc[
i, 'ValuationInSecCcy'] = holdings.loc[i, 'Close'] * row.NoOfUnits
# calc base ccy equivalent
# optimise FX query (if platform ccy = security ccy then use same fx rate)
same_ccy = holdings.loc[i,
'PlatformCcy'] == holdings.loc[i,
'SecurityCcy']
if same_ccy:
fxrate = calc_fx.ConvertFX(holdings.loc[i, 'SecurityCcy'],
base_ccy)
holdings.loc[i, 'CostInBaseCcy'] = fxrate * row.CostInPlatformCcy
holdings.loc[i, 'ValuationInBaseCcy'] = fxrate * holdings.loc[
i, 'ValuationInSecCcy']
else:
holdings.loc[i, 'CostInBaseCcy'] = calc_fx.ConvertTo(
base_ccy, holdings.loc[i, 'PlatformCcy'],
row.CostInPlatformCcy)
holdings.loc[i, 'ValuationInBaseCcy'] = calc_fx.ConvertTo(
base_ccy, holdings.loc[i, 'SecurityCcy'],
holdings.loc[i, 'ValuationInSecCcy'])
return holdings
def StorePortfolioSummaryHistoryOnDB():
# connect to mongodb (get IRR, store snapshot)
db = setup.ConnectToMongoDB()
# get IRR
irr = pd.DataFrame(db['PortfolioPerformance'].find())
irr.set_index('Period', inplace=True)
dic_IRR = {}
for x in irr.index:
dic_IRR[x] = irr.loc[x, 'IRR']
# get SPX IRR
spx = calc_returns.GetSPXReturns()
dic_IRR_SPX = {}
for x in spx.index:
dic_IRR_SPX[x] = spx.loc[x, 'AnnualisedReturn']
# get the portfolio summary
now = datetime.datetime.now()
ps_inc_cash = GetPortfolioSummaryFromDB(summary_type='AdjustedIncCash')
cols = [
'Platform', 'AssetClass', 'SecurityType', 'Category', 'Name',
'CurrentValueInHKD'
]
ps = ps_inc_cash[cols].copy()
ps.rename(columns={'CurrentValueInHKD': 'ValueInHKD'}, inplace=True)
ps['Date'] = now
# get the totals in other currencies (current FX rate)
total_inc_cash = ps_inc_cash.CurrentValueInHKD.sum()
total_USD = calc_fx.ConvertTo('USD', 'HKD', total_inc_cash)
total_EUR = calc_fx.ConvertTo('EUR', 'HKD', total_inc_cash)
total_GBP = calc_fx.ConvertTo('GBP', 'HKD', total_inc_cash)
total_SGD = calc_fx.ConvertTo('SGD', 'HKD', total_inc_cash)
dic_totals = {}
dic_totals['HKD'] = total_inc_cash
dic_totals['USD'] = total_USD
dic_totals['EUR'] = total_EUR
dic_totals['GBP'] = total_GBP
dic_totals['SGD'] = total_SGD
# construct the dict to store on MongoDB
dic = {}
dic['Date'] = now
dic['PortfolioSummary'] = ps.to_dict('records')
dic['Totals'] = dic_totals
dic['IRR'] = dic_IRR
dic['IRR_SPX'] = dic_IRR_SPX
# push to MongoDB
coll = db['HistoricalSnapshot']
coll.insert_one(dic)
def DisplayPnL():
# get the calculations
pnl_unrealised = calc_summary.GetPnLUnrealised()
# print PnL by platform and Currency
print('\n*** PnL by Platform and Account ***')
print(pnl_unrealised['PnLByPlatformAndAccount'])
print('\n*** %s ***' % datetime.datetime.today())
print('\n*** PnL by Platform ***')
print(pnl_unrealised['PnLByPlatform'])
# unrealised PnL in a single currency
pnl_unrealised_by_ccy = pnl_unrealised['PnLByPlatform'].groupby(
'PlatformCurrency').agg({'PnL': 'sum'})
for x in pnl_unrealised_by_ccy.index:
pnl_unrealised_by_ccy.loc[x, 'PnLInHKD'] = calc_fx.ConvertTo(
'HKD', x, pnl_unrealised_by_ccy.loc[x, 'PnL'])
pnl_unrealised_in_HKD = pnl_unrealised_by_ccy.PnLInHKD.sum()
pnl_unrealised_in_USD = calc_fx.ConvertTo('USD', 'HKD',
pnl_unrealised_in_HKD)
print('Total unrealised PnL: %s HKD | %s USD ' %
('{:,.0f}'.format(pnl_unrealised_in_HKD),
'{:,.0f}'.format(pnl_unrealised_in_USD)))
# historical realised PnL
print('\n*** Realised PnL (not inc. platform fees) ***')
pnl_obj = calc_summary.GetHistoricalRealisedPnL()
pnl = pnl_obj[1]
pnl = pnl.reset_index()
print(pnl.groupby(['Platform', 'PlatformCurrency']).sum())
# calculate and display the total realised PnL in a single currency
pnl_by_ccy = pnl.groupby(['PlatformCurrency']).sum()
for x in pnl_by_ccy.index:
pnl_by_ccy.loc[x, 'RealisedPnLInHKD'] = calc_fx.ConvertTo(
'HKD', x, pnl_by_ccy.loc[x, 'RealisedPnL'])
pnl_in_HKD = pnl_by_ccy.RealisedPnLInHKD.sum()
# output PnL by ccy to CSV file
pnl_by_ccy.to_csv(calc_summary._output_dir + '/RealisedPnL.csv')
pnl_in_USD = calc_fx.ConvertTo('USD', 'HKD', pnl_in_HKD)
print('Total realised PnL: %s HKD | %s USD ' %
('{:,.0f}'.format(pnl_in_HKD), '{:,.0f}'.format(pnl_in_USD)))
def DisplayPortfolioSummary():
# Portfolio composition report
pcr = calc_summary.GetPortfolioSummaryFromDB(summary_type='Original')
pct = pcr.groupby('Category').agg({'CurrentValueInHKD': 'sum'})
pct.reset_index(inplace=True)
total = pct.CurrentValueInHKD.sum()
total2 = calc_fx.ConvertTo('USD', 'HKD', total)
print('\nTotal investments: %s HKD | %s USD' %
('{:,.0f}'.format(total), '{:,.0f}'.format(total2)))
# calculate the equivalent in other currencies
ps_inc_cash = calc_summary.GetPortfolioSummaryFromDB(
summary_type='AdjustedIncCash')
total_inc_cash = ps_inc_cash.CurrentValueInHKD.sum()
total_USD = calc_fx.ConvertTo('USD', 'HKD', total_inc_cash)
total_EUR = calc_fx.ConvertTo('EUR', 'HKD', total_inc_cash)
total_GBP = calc_fx.ConvertTo('GBP', 'HKD', total_inc_cash)
total_SGD = calc_fx.ConvertTo('SGD', 'HKD', total_inc_cash)
print('\nTotal portfolio value including cash & gold: %s HKD' %
'{:,.0f}'.format(total_inc_cash))
print('or %s USD | %s EUR | %s GBP | %s SGD' %
('{:,.0f}'.format(total_USD), '{:,.0f}'.format(total_EUR),
'{:,.0f}'.format(total_GBP), '{:,.0f}'.format(total_SGD)))
def GetWeightedAvgCostPerUnitInSecCcy(bbgcode, platform):
#bbgcode, platform = 'XLE US', 'FSM SG'
wac_pccy = GetWeightedAvgCost(datetime.datetime.now(),
BBGCode=bbgcode,
Quantity=1,
Platform=platform)
# check if secuirty ccy is same as platform ccy
pccy = GetPlatformCurrency(platform)
sccy = GetSecurityCurrency(bbgcode)
if sccy == pccy:
wac = wac_pccy
else:
# need to convert the WAC to security ccy using latest FX rates
wac = calc_fx.ConvertTo(target_ccy=sccy,
original_ccy=pccy,
original_amount=wac_pccy)
return wac
def GetPortfolioSummaryIncCash():
now = datetime.datetime.now()
cash = setup.GetBankAndCashBalances()
#ps = GetPortfolioSummary()
#ps_adjusted = ps['Adjusted']
ps_adjusted = GetPortfolioSummaryFromDB('Adjusted')
ps_IncCash = ps_adjusted.copy()
for i in range(len(cash)):
row = cash.iloc[i]
current_value_in_HKD = calc_fx.ConvertTo('HKD', row.Currency,
row.Balance)
dic = {
'Platform': 'Cash',
'PlatformCurrency': row.Currency,
'FundHouse': None,
'BBGCode': None,
'LastNAV': None,
'LastUpdated': now,
'NoOfUnits': None,
'CostInPlatformCcy': None,
'PnL': None,
'RealisedPnL': None,
'PnLPct': None,
'CostInHKD': None,
'PnLInHKD': None,
'WAC': None,
'AssetClass': row.Category[1:],
'Name': row.AccountName,
'CurrentValue': row.Balance,
'SecurityType': row.Category[1:],
'SecurityCcy': row.Currency,
'CurrentValueInHKD': current_value_in_HKD,
'Category': row.Category
}
ps_IncCash = ps_IncCash.append(dic, ignore_index=True)
# recalculate % of total
ps_IncCash.loc[:,
'PortfolioPct'] = ps_IncCash.loc[:,
'CurrentValueInHKD'] / ps_IncCash.CurrentValueInHKD.sum(
)
return ps_IncCash
def GetPortfolioSummary():
# get a summary of transactions in the portfolio
tn = setup.GetAllTransactions()
tn.CostInPlatformCcy = tn.CostInPlatformCcy.round(2)
tn.drop('_id', axis=1, inplace=True)
sec = setup.GetSecurities()
sec.drop('_id', axis=1, inplace=True)
# enrich transactions with Security metadata
tn = pd.merge(tn, sec, how='left', left_on='BBGCode', right_on='BBGCode')
agg = {'NoOfUnits': sum, 'CostInPlatformCcy': sum, 'RealisedPnL': sum}
#summary = tn.groupby(['Platform','Name','FundHouse','AssetClass','BBGCode','BBGPriceMultiplier','Currency']).agg(agg)
summary = tn.groupby(
['Platform', 'Name', 'FundHouse', 'AssetClass', 'BBGCode',
'Currency']).agg(agg)
summary.reset_index(inplace=True)
summary.rename(columns={'Currency': 'SecurityCurrency'}, inplace=True)
# enrich with platforms
db = setup.ConnectToMongoDB()
platforms = pd.DataFrame(list(db.Platform.find()))
summary = pd.merge(summary,
platforms,
how='left',
left_on='Platform',
right_on='PlatformName')
summary.drop(['PlatformName', '_id', 'SecurityCurrency'],
axis=1,
inplace=True)
# enrich transactions with the latest price (ARKG has 2 FX rates USDHKD USDSGD that can cause duplicates)
lastnav = calc_val.GetLastNAV()
lastnav = lastnav.groupby(['BBGCode', 'LastNAV',
'SecurityCurrency']).agg({'LastUpdated': 'min'})
lastnav.reset_index(inplace=True)
### bug fixed 26 Dec 2020: left join caused duplicates
summary = summary.merge(
lastnav[['BBGCode', 'LastNAV', 'LastUpdated', 'SecurityCurrency']],
how='left',
left_on='BBGCode',
right_on='BBGCode')
# added 22 Nov 2018 (remove unused stock code)
summary = summary[summary.SecurityCurrency.notnull()]
summary.reset_index(inplace=True, drop=True)
for i in range(len(summary)):
#summary.loc[i,'FXConversionRate'] = GetFXRate(summary.loc[i,'PlatformCurrency'], summary.loc[i,'SecurityCurrency'])
summary.loc[i, 'FXConversionRate'] = calc_fx.ConvertFX(
summary.loc[i, 'SecurityCurrency'],
summary.loc[i, 'PlatformCurrency'])
#summary['CurrentValue'] = summary.NoOfUnits * summary.LastNAV * summary.FXConversionRate / summary.BBGPriceMultiplier
summary[
'CurrentValue'] = summary.NoOfUnits * summary.LastNAV * summary.FXConversionRate
summary.CurrentValue = summary.CurrentValue.round(2)
summary[
'PnL'] = summary.CurrentValue - summary.CostInPlatformCcy #- summary.RealisedPnL
summary.PnL = summary.PnL.round(2)
agg2 = {
'NoOfUnits': sum,
'CostInPlatformCcy': sum,
'CurrentValue': sum,
'PnL': sum,
'RealisedPnL': sum
}
ps = summary.groupby([
'Platform', 'PlatformCurrency', 'FundHouse', 'AssetClass', 'Name',
'BBGCode', 'LastNAV', 'LastUpdated'
]).agg(agg2)
ps.reset_index(inplace=True)
ps['PnLPct'] = ps.PnL / ps.CostInPlatformCcy
# added 2 Dec 2020
sec = sec[['BBGCode', 'AssetType', 'Currency']]
sec.rename(columns={
'AssetType': 'SecurityType',
'Currency': 'SecurityCcy'
},
inplace=True)
ps = ps.merge(sec, how='left', left_on='BBGCode', right_on='BBGCode')
# add current value in HKD
for i in range(len(ps)):
row = ps.loc[i]
# get HKD equivalent amount
ccy = row.PlatformCurrency
value_ccy = row.CurrentValue
ps.loc[i,
'CurrentValueInHKD'] = calc_fx.ConvertTo('HKD', ccy, value_ccy)
# get Category
sec_name = row.Name
ps.loc[i, 'Category'] = _GetSecurityCategory(sec_name)
# calculate Cost and PnL in HKD
ps.loc[:,
'CostInHKD'] = ps.loc[:,
'CurrentValueInHKD'] / ps.loc[:,
'CurrentValue'] * ps.loc[:,
'CostInPlatformCcy']
ps.loc[:,
'PnLInHKD'] = ps.loc[:,
'CurrentValueInHKD'] / ps.loc[:,
'CurrentValue'] * ps.loc[:,
'PnL']
# 22 Dec 2020: add SecCcy to HKD rate, add WA cost in Security Ccy
for i in [x for x in ps.index]:
row = ps.loc[i]
ps.loc[i, 'WAC'] = setup.GetWeightedAvgCostPerUnitInSecCcy(
row.BBGCode, row.Platform)
# total PnL = realised + unrealised
# (should I add or not? TO BE DECIDED)
# special treatment to breakdown Allianz Income & Growth funds
# divide by 3 separate rows and allocate different asset classes
allianz_bbgcodes = ['ALIGH2S LX', 'ALLGAME LX']
allianz_allocations = [{
'Equity': 0.33
}, {
'Credit': 0.33
}, {
'Convertibles': 0.34
}]
# generate the new rows based on allocations
dfAllianz = ps[ps.BBGCode.isin(allianz_bbgcodes)].copy()
dfAllianzNew = pd.DataFrame(columns=dfAllianz.columns)
for i in range(len(dfAllianz)):
row = dfAllianz.iloc[i]
for j in range(len(allianz_allocations)):
new_row = row.copy()
new_row['AssetClass'] = list(allianz_allocations[j].keys())[0]
new_row['NoOfUnits'] = row.NoOfUnits * list(
allianz_allocations[j].values())[0]
new_row['CostInPlatformCcy'] = row.CostInPlatformCcy * list(
allianz_allocations[j].values())[0]
new_row['CurrentValue'] = row.CurrentValue * list(
allianz_allocations[j].values())[0]
new_row['PnL'] = row.PnL * list(allianz_allocations[j].values())[0]
new_row['RealisedPnL'] = row.RealisedPnL * list(
allianz_allocations[j].values())[0]
new_row['CurrentValueInHKD'] = row.CurrentValueInHKD * list(
allianz_allocations[j].values())[0]
dfAllianzNew = dfAllianzNew.append(new_row)
# replace the original rows with the new rows
ps2 = ps[~ps.BBGCode.isin(allianz_bbgcodes)].copy()
ps2 = ps2.append(dfAllianzNew)
# can't assign Portfolio % when Allianz is broken down into separate asset classes
ps.loc[:,
'PortfolioPct'] = ps.loc[:,
'CurrentValueInHKD'] / ps.CurrentValueInHKD.sum(
)
# remove rows with 0 holdings
ps = ps[ps.NoOfUnits != 0]
ps2 = ps2[ps2.NoOfUnits != 0]
PortfolioSummary = {'Original': ps, 'Adjusted': ps2}
return PortfolioSummary