def CalcPortfolioHistoricalCost(platform=None,
start_date=None,
base_ccy='HKD'):
if start_date is None:
tn = setup.GetAllTransactions()
supported_instruments = setup.GetListOfSupportedInstruments()
tn = tn[tn.BBGCode.isin(supported_instruments)]
start_date = tn.Date.min()
#platform='FSM HK'
tn_cost = setup.GetTransactionsETFs()
tn_cost = tn_cost[tn_cost.Date > start_date]
# need to add balance brought forward
bf = _GetExistingHoldings(start_date)
if platform is not None:
tn_cost = tn_cost[tn_cost.Platform == platform]
bf = bf[bf.Platform == platform]
for i in range(len(bf)):
row = bf.iloc[i]
dic = {
'Platform': row.Platform,
'Date': start_date,
'Type': 'Buy',
'BBGCode': row.BBGCode,
'CostInPlatformCcy': row.CostInPlatformCcy,
'NoOfUnits': row.NoOfUnits
}
tn_cost = tn_cost.append(dic, ignore_index=True)
tn_cost.sort_values(['Date', 'BBGCode'], inplace=True)
# convert all values into HKD before aggregating (need to convert platform ccy to HKD)
platforms = list(tn_cost.Platform.unique())
platform_ccys = [setup.GetPlatformCurrency(x) for x in platforms]
platform_ccy_mapping = {
platforms[i]: platform_ccys[i]
for i in range(len(platforms))
}
tn_cost['PlatformCcy'] = tn_cost.Platform.map(platform_ccy_mapping)
ccys = tn_cost.PlatformCcy.unique()
fx_rate = []
for i in range(len(ccys)):
ccy = ccys[i]
if ccy == base_ccy:
rate = 1
else:
rate = calc_fx.ConvertFX(ccy, base_ccy)
fx_rate.append(rate)
ToBaseCcyRate = {ccys[i]: fx_rate[i] for i in range(len(ccys))}
tn_cost['ToHKDrate'] = tn_cost.PlatformCcy.map(ToBaseCcyRate)
tn_cost['CostInBaseCcy'] = tn_cost.ToHKDrate * tn_cost.CostInPlatformCcy
agg = tn_cost.groupby(['Date']).agg({'CostInBaseCcy': 'sum'})
agg = agg.reset_index()
agg['AccumCostHKD'] = agg.CostInBaseCcy.cumsum()
agg.drop(['CostInBaseCcy'], axis=1, inplace=True)
return agg
def PlotPerformanceOfHoldings(period='3M'):
# get the start date
start_date = util.GetStartDate(period)
# get the list of instruments
ps = calc_summary.GetPortfolioSummaryFromDB()
ps = ps[ps.NoOfUnits > 0]
ps = ps[ps.BBGCode.isin(setup.GetListOfSupportedInstruments())]
#top10tickers = list(ps.sort_values('CurrentValueInHKD', ascending=False).head(10).BBGCode)
top10tickers = list(
ps.groupby(['BBGCode']).agg({
'CurrentValueInHKD': 'sum'
}).sort_values('CurrentValueInHKD', ascending=False).head(10).index)
# get the historical market data
hp = mdata.GetHistoricalData()
hp = hp[hp.BBGCode.isin(top10tickers)]
hp = hp[hp.Date >= start_date]
# plot the chart
title = 'Top Holdings Performance (%s)' % period
title = title + ' - %s' % datetime.datetime.strftime(
datetime.datetime.today(), '%Y-%m-%d %H:%M:%S')
fig, ax = plt.subplots()
ax.set_ylabel('Price Index')
ax.yaxis.set_major_formatter(mpl.ticker.StrMethodFormatter('{x:,.0f}'))
# plot the cost
for i in reversed(range(len(top10tickers))):
tmp = hp[hp.BBGCode == top10tickers[i]].copy()
base = tmp.Close.iloc[0]
tmp.loc[:, 'AdjustedIndex'] = tmp.loc[:, 'Close'] / base * 100
label = tmp.BBGCode.iloc[0]
x = tmp.Date
y = tmp.AdjustedIndex
colour = list(mcolors.TABLEAU_COLORS.keys())[i]
ax.plot(x, y, linestyle='-', label=label, color=colour, alpha=0.75)
# add legend and other formatting
handles, labels = ax.get_legend_handles_labels()
ax.legend(handles[::-1],
labels[::-1],
title='Bloomberg ticker',
frameon=True,
loc='best',
ncol=1,
bbox_to_anchor=(1, 1))
plt.xticks(rotation=45, ha='right')
ax.set_title(title)
ax.axhline(y=100, xmin=0, xmax=1, color='black', lw=0.5)
# plot major gridlines on y-axis
for ymaj in ax.yaxis.get_majorticklocs():
ax.axhline(y=ymaj, ls=':', lw=0.25, color='gray')
# save output as PNG
output_filename = 'TopHoldingsPerformance.png'
output_fullpath = '%s/%s' % (_output_dir, output_filename)
fig.savefig(output_fullpath, format='png', dpi=300, bbox_inches='tight')
plt.show()
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 CalcPortfolioHistoricalValuation(platform=None,
bbgcode=None,
start_date=None):
# only applies to instruments supported by Yahoo Finance
supported_instruments = setup.GetListOfSupportedInstruments()
tn = setup.GetAllTransactions()
tn_in_scope = tn[tn.BBGCode.isin(supported_instruments)]
instruments_in_scope = supported_instruments
# if platform is specified, check which instruments were actually on the platform
if platform is not None:
tn_in_scope = tn_in_scope[tn_in_scope.Platform == platform]
instruments_in_scope = list(tn_in_scope.BBGCode.unique())
# if bbgcode is specified, then restrict to just the instrument
if bbgcode is not None:
if bbgcode in instruments_in_scope:
instruments_in_scope = [bbgcode]
# if start date is not defined, start from earliest transaction in scope
if start_date is None:
start_date = tn_in_scope.Date.min()
df = pd.DataFrame()
# loop through the list
for i in range(len(instruments_in_scope)):
bbgcode = instruments_in_scope[i]
tmp = _CalcValuation(bbgcode=bbgcode,
platform=platform,
start_date=start_date)
# remove redundant rows
tmp = tmp[~((tmp.NoOfUnits == 0) & (tmp.Holdings == 0))]
tmp['BBGCode'] = bbgcode
df = df.append(tmp, ignore_index=False)
# on each unique date, take the last row of unique security to avoid duplicated valuation
df.sort_values(['Date', 'BBGCode'], inplace=True)
df = df.drop_duplicates(subset=['Date', 'BBGCode'], keep='last')
# group the data by date
agg = df.groupby(['Date']).agg({'ValuationHKD': 'sum'})
agg = agg.reset_index()
return agg
def GetStartDate(period=None):
#period='1m'
if period is None:
tn = setup.GetAllTransactions()
supported_instruments = setup.GetListOfSupportedInstruments()
tn = tn[tn.BBGCode.isin(supported_instruments)]
start_date = tn.Date.min() # since inception
else:
period = period.upper()
# supported periods: YTD 1W 1M 3M 6M 1Y 3Y 5Y 10Y; calculate up to end of yesterday (up to start of today)
today = datetime.datetime.today().date()
if period == 'YTD':
start_date = datetime.datetime(today.year, 1, 1)
elif period == '1W':
start_date = today + datetime.timedelta(days=-7)
elif period == '1M':
start_date = today + dateutil.relativedelta.relativedelta(
months=-1)
elif period == '3M':
start_date = today + dateutil.relativedelta.relativedelta(
months=-3)
elif period == '6M':
start_date = today + dateutil.relativedelta.relativedelta(
months=-6)
elif period == '1Y' or period == '12M':
start_date = today + dateutil.relativedelta.relativedelta(years=-1)
elif period == '3Y':
start_date = today + dateutil.relativedelta.relativedelta(years=-3)
elif period == '5Y':
start_date = today + dateutil.relativedelta.relativedelta(years=-5)
elif period == '10Y':
start_date = today + dateutil.relativedelta.relativedelta(
years=-10)
start_date = datetime.datetime.combine(start_date,
datetime.datetime.min.time())
return start_date
def GetDates(period=None):
'''
Accepted values for period:
None (start from first ever transaction, end yesterday)
YTD
1W
1M
3M
6M
1Y
3Y
5Y
10Y
20xx
'''
# since inception
if period is None:
tn = setup.GetAllTransactions()
supported_instruments = setup.GetListOfSupportedInstruments()
tn = tn[tn.BBGCode.isin(supported_instruments)]
start_date = tn.Date.min()
end_date = datetime.datetime.now().date() - datetime.timedelta(days=1)
# period is a year (2019, 2020, 2021)
elif len(period) == 4 and period.isnumeric():
start_date = datetime.datetime(int(period), 1, 1)
if int(period) == datetime.datetime.today().year:
end_date = end_date = datetime.datetime.now().date(
) - datetime.timedelta(days=1)
else:
end_date = datetime.datetime(int(period), 12, 31)
else:
period = period.upper()
# supported periods: YTD 1W 1M 3M 6M 1Y 3Y 5Y 10Y; calculate up to end of yesterday (up to start of today)
today = datetime.datetime.today().date()
if period == 'YTD':
start_date = datetime.datetime(today.year, 1, 1)
elif period == '1W':
start_date = today + datetime.timedelta(days=-7)
elif period == '1M':
start_date = today + dateutil.relativedelta.relativedelta(
months=-1)
elif period == '3M':
start_date = today + dateutil.relativedelta.relativedelta(
months=-3)
elif period == '6M':
start_date = today + dateutil.relativedelta.relativedelta(
months=-6)
elif period == '1Y' or period == '12M':
start_date = today + dateutil.relativedelta.relativedelta(years=-1)
elif period == '3Y':
start_date = today + dateutil.relativedelta.relativedelta(years=-3)
elif period == '5Y':
start_date = today + dateutil.relativedelta.relativedelta(years=-5)
elif period == '10Y':
start_date = today + dateutil.relativedelta.relativedelta(
years=-10)
end_date = datetime.datetime.now().date() - datetime.timedelta(days=1)
start_date = datetime.datetime.combine(start_date,
datetime.datetime.min.time())
end_date = datetime.datetime.combine(end_date,
datetime.datetime.min.time())
return start_date, end_date
def ProcessHistoricalMarketData(bbgcode=None, platform=None, start_date=None):
print('\nProcessing historical market data...')
tn = setup.GetAllTransactions()
# filter by bbgcode and platform
if bbgcode is not None:
tn = tn[tn.BBGCode == bbgcode]
if platform is not None:
tn = tn[tn.Platform == platform]
if start_date is None:
supported_instruments = setup.GetListOfSupportedInstruments()
tn = tn[tn.BBGCode.isin(supported_instruments)]
start_date = tn.Date.min()
#list_of_etfs = GetListOfETFs()
list_of_supported_instruments = setup.GetListOfSupportedInstruments()
if bbgcode is not None:
list_of_supported_instruments = [bbgcode]
# populate list of ETFs and date ranges
df = pd.DataFrame(columns=['BBGCode', 'YFTicker', 'DateFrom', 'DateTo'])
for i in range(len(list_of_supported_instruments)):
bbgcode = list_of_supported_instruments[i]
yf_ticker = setup.GetYahooFinanceTicker(bbgcode)
dates = setup.GetETFDataDateRanges(bbgcode)
date_from = dates['DateFrom']
date_to = dates[
'DateTo'] # this results in incorrect values for securites no longer held
if date_from < start_date.date():
date_from = start_date.date()
df = df.append(
{
'BBGCode': bbgcode,
'YFTicker': yf_ticker,
'DateFrom': date_from,
'DateTo': date_to
},
ignore_index=True)
# loop through the list and collect the data from Yahoo
data = pd.DataFrame()
for i in range(len(df)):
row = df.iloc[i]
tmp = pdr.get_data_yahoo(row.YFTicker,
start=row.DateFrom,
end=row.DateTo)
tmp = tmp.reset_index()
tmp['BBGCode'] = row.BBGCode
data = data.append(tmp, ignore_index=False)
# added 15 Dec 2020: Yahoo Finance null rows?
data = data[~data.Close.isnull()]
data.drop_duplicates(['BBGCode', 'Date'], inplace=True)
# NEED TO DEAL WITH HK/US HOLIDAYS MISMATCH - this process is also adding incorrect values for securities no longer held
tmp = data.pivot('Date', 'BBGCode', values='Close')
tmp = tmp.fillna(method='ffill')
tmp = tmp.reset_index()
tmp2 = pd.melt(tmp,
id_vars=['Date'],
value_vars=list(data.BBGCode.unique()),
value_name='Close')
tmp2.dropna(inplace=True)
#tmp2.to_csv('HistoricalPrices.csv', index=False)
# save to mongodb
db = setup.ConnectToMongoDB()
coll = db['HistoricalMarketData']
# clear all previous transactions
coll.delete_many({})
# insert rows into the db
coll.insert_many(tmp2.to_dict('records'))
#return tmp2
print('(updated %s records on MongoDB)' % len(tmp2))
def PlotLeadersAndLaggers(period=None, top_n=5):
# get market data
hp = mdata.GetHistoricalData()
# filter by existing holdings
ps = calc_summary.GetPortfolioSummaryFromDB('Original')
ps = ps[ps.NoOfUnits > 0]
ps = ps[ps.BBGCode.isin(setup.GetListOfSupportedInstruments())]
grouped = ps.groupby(['BBGCode', 'Name']).agg({'CurrentValueInHKD': 'sum'})
tickers = list(ps.BBGCode.unique())
# create a table to store the league table
df = pd.DataFrame(tickers, columns=['BBGCode'])
# calculate percentage change based on period
def _CalculatePctChg(bbgcode, period=None):
md = hp[hp.BBGCode == bbgcode].copy()
if period is None:
md = md.tail(2)
else:
start_date = util.GetStartDate(period)
md = md[~(md.Date < start_date)]
pct_chg = md.Close.iloc[-1] / md.Close.iloc[0] - 1
return pct_chg
# apply the calculation
for i in range(len(df)):
df.loc[i, 'PctChg'] = _CalculatePctChg(df.BBGCode.iloc[i], period)
# 16 Dec 2020: NEED TO TAKE INTO ACCOUNT INTRADAY TRANSACTIONS!!!
# get starting balance, add transactions, get final valuation
# get existing holdings and calculate amount change
df = df.merge(grouped, how='left', on='BBGCode')
df['AmountChgInHKD'] = df.CurrentValueInHKD - (df.CurrentValueInHKD /
(1 + df.PctChg))
df = df[df.PctChg != 0]
leaders = df.sort_values(['AmountChgInHKD'], ascending=False).head(top_n)
leaders = leaders[leaders.PctChg > 0]
leaders.reset_index(inplace=True)
laggers = df.sort_values(['AmountChgInHKD'], ascending=True).head(top_n)
laggers = laggers[laggers.PctChg < 0]
laggers.reset_index(inplace=True)
# added 15 Dec 2020: fill the gaps
nrows_to_add_leaders = top_n - len(leaders)
nrows_to_add_laggers = top_n - len(laggers)
dic = {'BBGCode': '', 'AmountChgInHKD': 0, 'PctChg': 0}
# add blank rows to Gainers
if nrows_to_add_leaders > 0:
for i in range(nrows_to_add_leaders):
leaders = leaders.append(dic, ignore_index=True)
# add blank rows to Losers
if nrows_to_add_laggers > 0:
for i in range(nrows_to_add_laggers):
laggers = laggers.append(dic, ignore_index=True)
# plot the charts
fig, (ax1, ax2) = plt.subplots(nrows=1, ncols=2, figsize=(10, 4))
plt.subplots_adjust(wspace=0.4)
plt.rcdefaults()
# Top 5 gainers
labels1 = leaders.BBGCode
sizes1 = leaders.AmountChgInHKD
y_pos1 = np.arange(len(labels1))
ax1.barh(y_pos1, sizes1, color='tab:green', alpha=0.75)
# plot major gridlines
for xmaj in ax1.xaxis.get_majorticklocs():
ax1.axvline(x=xmaj, ls=':', lw=0.25, color='gray')
ax1.set_yticks(y_pos1)
ax1.set_yticklabels(labels1)
vals1 = ax1.get_xticks()
ax1.set_xticklabels(['{:,.0f}'.format(x) for x in vals1])
ax1.set_xlabel('Gains (HKD)')
title1 = 'Top %s gainers (+%s HKD)' % (top_n, '{:,.0f}'.format(
sum(sizes1)))
# add labels
for i in range(len(sizes1[sizes1 != 0])):
ax1.text(x=sizes1[i],
y=y_pos1[i],
s=str('+' + '{:,.2%}'.format(leaders.PctChg.iloc[i])),
color='tab:green')
ax1.set(title=title1)
ax1.invert_yaxis()
# Top 5 losers
labels2 = laggers.BBGCode
sizes2 = laggers.AmountChgInHKD * -1
y_pos2 = np.arange(len(labels2))
ax2.barh(y_pos2, sizes2, color='tab:red', alpha=0.75)
# plot major gridlines
for xmaj in ax2.xaxis.get_majorticklocs():
ax2.axvline(x=xmaj, ls=':', lw=0.25, color='gray')
ax2.set_yticks(y_pos2)
ax2.set_yticklabels(labels2)
vals2 = ax2.get_xticks()
ax2.set_xticklabels(['{:,.0f}'.format(x) for x in vals2])
ax2.set_xlabel('Losses (HKD)')
title2 = 'Top %s losers (%s HKD)' % (top_n, '{:,.0f}'.format(
sum(sizes2) * -1))
# add labels
for i in range(len(sizes2[sizes2 != 0])):
ax2.text(x=sizes2[i],
y=y_pos2[i],
s=str('{:,.2%}'.format(laggers.PctChg.iloc[i])),
color='tab:red')
ax2.set(title=title2)
ax2.invert_yaxis()
#plt.gca().invert_yaxis()
if period is None:
dr = 'overnight'
else:
dr = period
title = 'Gainers and Losers (%s) - %s' % (dr,
datetime.datetime.strftime(
datetime.datetime.today(),
'%Y-%m-%d %H:%M:%S'))
plt.suptitle(title, fontsize=12)
# save output as PNG
output_filename = 'GainersAndLosers.png'
output_fullpath = '%s/%s' % (_output_dir, output_filename)
fig.savefig(output_fullpath, format='png', dpi=150, bbox_inches='tight')
plt.show()
def DisplayReturnPct():
# IRR
date_ranges = util.date_ranges
# get IRR from cache (DB)
returns_irr = calc_returns.GetIRRFromDB()
returns_irr = returns_irr[(returns_irr.Platform.isnull())
& (returns_irr.BBGCode.isnull())]
#returns_irr = returns_irr[['Period','IRR']]
#returns_irr.set_index('Period', inplace=True)
dic = {}
for i in range(len(returns_irr)):
dic[returns_irr.loc[i, 'Period']] = returns_irr.loc[i, 'IRR']
returns_irr = dic
# caclulate supported instruments value as % of total
supported_instruments = setup.GetListOfSupportedInstruments()
ps = calc_summary.GetPortfolioSummaryFromDB()
ps_supported_instruments = ps[ps.BBGCode.isin(supported_instruments)]
# get SPX returns as benchmark
spx = calc_returns.GetSPXReturns()
print(
'\nPerformance of Yahoo Finance supported instruments (%s of total):' %
'{:,.2%}'.format(ps_supported_instruments.CurrentValueInHKD.sum() /
ps.CurrentValueInHKD.sum()))
# get IRR vs SPX returns
perf = spx[['AnnualisedReturn']].copy()
perf.rename(columns={'AnnualisedReturn': 'SPX'}, inplace=True)
perf['MyPtf'] = pd.Series(returns_irr)
perf['Outperformance'] = perf.MyPtf - perf.SPX
perf = perf[['MyPtf', 'SPX', 'Outperformance']]
perf.rename(columns={
'MyPtf': 'My Portfolio',
'SPX': 'SPX Returns'
},
inplace=True)
pd.options.display.float_format = '{:,.2%}'.format
print(perf)
pd.options.display.float_format = '{:,.2f}'.format
# output performance data to CSV file
perf.to_csv(calc_summary._output_dir + '/Performance.csv')
# print ('\nPerformance of Yahoo Finance supported instruments (%s of total):' % '{:,.2%}'.format(ps_supported_instruments.CurrentValueInHKD.sum()/ps.CurrentValueInHKD.sum()))
# for i in range(len(returns_irr)):
# print ('> %s: \t\t' % list(returns_irr.keys())[i] + '{:,.2%}'.format(list(returns_irr.values())[i]))
print('Total value of supported instruments: %s HKD' %
('{:,.0f}'.format(ps_supported_instruments.CurrentValueInHKD.sum())))
print('')
# plot the returns on a bar chart
# prepare the data
date_ranges = np.array(date_ranges)
values = np.array(list(returns_irr.values()))
has_negative_values = False
if len(spx[spx.AnnualisedReturn < 0]) > 0:
has_negative_values = True
if np.sum(values < 0):
has_negative_values = True
# compare porfolio returns vs SPX (YTD)
YTD_spx_diff = returns_irr['YTD'] - spx.loc['YTD', 'AnnualisedReturn']
if YTD_spx_diff >= 0:
comp_label = 'Outperformed'
annotate_colour = 'tab:green'
else:
comp_label = 'Underperformed'
annotate_colour = 'tab:red'
comp_full_text = '%s\nSPX\nby %s' % (comp_label,
'{:.2%}'.format(YTD_spx_diff))
#comp_full_text = '%s SPX by %s bps' % (comp_label, int((YTD_spx_diff*10000)))
# compare porfolio returns vs SPX (5Y)
spx_diff_5Y = returns_irr['5Y'] - spx.loc['5Y', 'AnnualisedReturn']
if spx_diff_5Y >= 0:
comp_label2 = 'Outperformed'
annotate_colour2 = 'tab:green'
else:
comp_label2 = 'Underperformed'
annotate_colour2 = 'tab:red'
comp_full_text2 = '%s\nSPX\nby %s' % (comp_label2,
'{:.2%}'.format(spx_diff_5Y))
# plot the chart
fig, ax = plt.subplots()
return_positive = values > 0
return_negative = values < 0
# plot the date ranges with empty values first (to set the order)
ax.bar(date_ranges, [0] * len(date_ranges))
# then plot postive first, and then negative
ax.bar(date_ranges[return_positive],
values[return_positive],
color='tab:green',
alpha=0.75)
ax.bar(date_ranges[return_negative],
values[return_negative],
color='tab:red',
alpha=0.75)
# add SPX as benchmark
ax.plot(date_ranges,
list(spx.AnnualisedReturn),
color='tab:blue',
marker='_',
markeredgewidth=2,
markersize=20,
label='S&P500 Index',
lw=0)
# add annotate text (YTD)
#y_ytd = returns_irr['YTD'] if returns_irr['YTD'] > spx.loc['YTD','AnnualisedReturn'] else spx.loc['YTD','AnnualisedReturn']
ax.annotate(
comp_full_text,
xy=(list(date_ranges).index('YTD'), returns_irr['YTD']),
#xy=(list(date_ranges).index('YTD'), y_ytd),
#xytext=(-25, 50),
xytext=(0, 25),
textcoords='offset points',
color=annotate_colour,
#weight='bold',
fontsize=7,
ha='center',
arrowprops=dict(arrowstyle='-|>', color=annotate_colour))
# add annotate text (5Y)
ax.annotate(comp_full_text2,
xy=(list(date_ranges).index('5Y'), returns_irr['5Y']),
xytext=(0, 25),
textcoords='offset points',
color=annotate_colour2,
fontsize=7,
ha='center',
arrowprops=dict(arrowstyle='-|>', color=annotate_colour2))
# finalise chart
ax.set_ylabel('Performance % (>1Y annualised)')
ax.set_xlabel('Date Range')
#ax.set_ylabel('Annualised Return % for date range above 1Y')
title = 'Portfolio Performance - %s' % (datetime.datetime.strftime(
datetime.datetime.today(), '%Y-%m-%d %H:%M:%S'))
ax.set_title(title)
if has_negative_values:
ax.axhline(y=0, ls='-', lw=0.25, color='black')
# plot major gridlines on y-axis
for ymaj in ax.yaxis.get_majorticklocs():
ax.axhline(y=ymaj, ls=':', lw=0.25, color='gray')
# this is bugged when there is negative value, needs to be run at last
vals = ax.get_yticks()
ax.set_yticklabels(['{:,.0%}'.format(x) for x in vals])
# save output as PNG
output_filename = 'PortfolioPerformance.png'
output_fullpath = '%s/%s' % (_output_dir, output_filename)
#ax.legend(loc='upper right', bbox_to_anchor=(1,-0.1))
ax.legend(loc='upper left')
fig.savefig(output_fullpath, format='png', dpi=150, bbox_inches='tight')
plt.show()
def _CalcValuation(bbgcode, platform=None, start_date=None):
# assumes bbgcode can only be on 1 platform (exception VWO XLE)
#bbgcode='XLE US'
#platform='FSM HK'
#bbgcode='SCHSEAI SP'
tn = setup.GetAllTransactions()
# filter by platform and bbgcode
if platform is not None:
tn = tn[tn.Platform == platform]
tn = tn[tn.BBGCode == bbgcode]
if start_date is None:
#tn = setup.GetAllTransactions()
supported_instruments = setup.GetListOfSupportedInstruments()
tn = tn[tn.BBGCode.isin(supported_instruments)]
#if bbgcode is not None:
tn = tn[tn.BBGCode == bbgcode]
start_date = tn.Date.min()
hd = mdata.GetHistoricalData(bbgcode=bbgcode)
hd = hd[['Date', 'Close']]
hd_prev = hd[hd.Date < start_date].copy()
hd_prev = hd_prev.tail(1)
# filter by selected date range
hd = hd[hd.Date >= start_date]
# filter by date until its no longer held
if tn.NoOfUnits.sum() == 0:
hd = hd[hd.Date <= tn.Date.max()]
# add back last valuation before beginning of date range
hd = hd.append(hd_prev)
hd = hd.sort_values(['Date'], ascending=True)
tn = tn[['Date', 'NoOfUnits']]
tn = tn[tn.Date >= start_date]
# CAREFUL: if the transaction date is a holiday where there is no market data, the holdings will be missed
# add balance brought forward
bf = _GetExistingHoldings(start_date, platform=platform)
bf = bf[bf.BBGCode == bbgcode]
df = pd.merge(hd, tn, how='left', on='Date')
# if there is balance b/f, then add it
if len(bf) > 0:
df.loc[0, 'NoOfUnits'] = bf.iloc[0].NoOfUnits
df.NoOfUnits.fillna(0, inplace=True)
df['Holdings'] = df.NoOfUnits.cumsum()
# security currency
sec_ccy = setup.GetSecurityCurrency(bbgcode)
ToUSD = calc_fx.GetFXRate('USD', sec_ccy)
df['Valuation'] = df.Holdings * df.Close
df['ValuationUSD'] = df.Valuation * ToUSD
# filter out unused rows
# load historical USDHKD exchange rates from cache
usdhkd = mdata.GetHistoricalUSDHKD()
df = df.merge(usdhkd, how='left', on='Date')
df['USDHKDrate'] = df.USDHKDrate.fillna(method='ffill')
df['ValuationHKD'] = df.ValuationUSD * df.USDHKDrate
return df
def CalcIRR(platform=None, bbgcode=None, period=None):
#platform = 'FSM HK'
#bbgcode = 'ARKK US'
#period = None #since inception
#period = 'YTD'
#period = '1Y'
#period = '3M'
#period='2020'
#platform,bbgcode,period=None,None,None
df = setup.GetAllTransactions()
list_of_supported_securities = setup.GetListOfSupportedInstruments()
# filter the data based on selection criteria (bbgcode, platform)
df.drop(['_id'], axis=1, inplace=True)
df = df[df.BBGCode.isin(list_of_supported_securities)]
if platform is not None:
df = df[df.Platform == platform]
if bbgcode is not None:
df = df[df.BBGCode == bbgcode]
# get the start date for cashflows (the sum of anything before needs to be added as a single cashflow)
#date_range_start = util.GetStartDate(period)
date_range_start, date_range_end = util.GetDates(period)
# apply the start date from applicable transactions
earliest_transaction_date = df.Date.min()
date_range_start_dt = earliest_transaction_date
PerformCalc = True
# determine if there is previous data (i.e. whether to add cost brought forward as cashflow)
if period is None:
# if period is not defined (i.e. since inception), take the earliest transaction date
hasPrevData = False
date_range_start_dt = earliest_transaction_date
else:
# if period is defined (e.g. 3Y), check whether there are transactions before 3Y
hasPrevData = (len(df[df.Date < date_range_start_dt]) > 0)
date_range_start_dt = datetime.datetime.combine(
date_range_start, datetime.datetime.min.time())
df = df[df.Date >= date_range_start_dt]
if earliest_transaction_date > date_range_start:
# if the first transaction is after the beginning of specified period, no need to calc IRR
irr = np.nan
PerformCalc = False
dic = {
'StartDate': date_range_start_dt,
'InitialCashflow': None,
'FinalCashflow': None,
'IRR': irr
}
if PerformCalc:
# process cashflows
# cashflow needs to start after the start date, because the ptf val would have included transactions inc. on the same day
cf = df[df.Date > date_range_start_dt].copy()
#cf = df[df.Date >= date_range_start_dt].copy()
cf.loc[cf.Type == 'Buy',
'Cashflow'] = cf.loc[cf.Type == 'Buy', 'CostInPlatformCcy'] * -1
# realised PnL needs to be taken into account to the cashflow calculation too!
cf.loc[cf.Type == 'Sell',
'Cashflow'] = cf.loc[cf.Type == 'Sell',
'CostInPlatformCcy'] * -1 + cf.loc[
cf.Type == 'Sell', 'RealisedPnL']
#+ cf.loc[cf.Type=='Sell', 'RealisedPnL'] * -1
cf.loc[cf.Type == 'Dividend',
'Cashflow'] = cf.loc[cf.Type == 'Dividend', 'RealisedPnL']
# get platform and currency
platforms = list(cf.Platform.unique())
currencies = [setup.GetPlatformCurrency(x) for x in platforms]
platform_ccy = {
platforms[x]: currencies[x]
for x in range(len(platforms))
}
cf['PlatformCcy'] = cf.Platform.map(platform_ccy)
cf = cf[['Date', 'Type', 'BBGCode', 'PlatformCcy', 'Cashflow']]
# calculate HKD equivalent
SGDHKD = calc_fx.GetFXRate('HKD', 'SGD')
ToHKD = {'HKD': 1, 'SGD': SGDHKD}
cf['CashflowInHKD'] = cf.PlatformCcy.map(ToHKD) * cf.Cashflow
# need to add initial and final cashflows (valuation at beginning, valuation at end)
# get valuations (beginning, ending)
if bbgcode is None:
val = CalcPortfolioHistoricalValuation(
platform=platform,
bbgcode=bbgcode,
start_date=date_range_start_dt)
val.rename(columns={'ValuationHKD': 'Cashflow'}, inplace=True)
else:
val = _CalcValuation(bbgcode=bbgcode,
start_date=date_range_start_dt)
val.rename(columns={'ValuationHKD': 'Cashflow'}, inplace=True)
val = val[['Date', 'Cashflow']]
# valuation as of start date
if period is not None:
val_start = (val[(val.Date <= np.datetime64(date_range_start_dt))
& (val.index == val[val.Date <= np.datetime64(
date_range_start_dt)].index.max())]).copy()
val_start.loc[:, 'Cashflow'] = val_start.loc[:, 'Cashflow'] * -1
val_start.rename(columns={'Cashflow': 'CashflowInHKD'},
inplace=True)
cf = cf.append(val_start)
else:
val_start = pd.DataFrame(data={
'Date': date_range_start_dt,
'CashflowInHKD': 0
},
columns=['Date', 'CashflowInHKD'],
index=[0])
# latest valuation
val_end = val[val.index == val.index.max()].copy()
val_end.rename(columns={'Cashflow': 'CashflowInHKD'}, inplace=True)
# add latest valuation as final cashflow (only if there are still holdings)
#if (cf.Date.iloc[-1] != val.Date.iloc[0]) and val.Cashflow.iloc[0]!=0:
if val.Cashflow.iloc[-1] != 0:
cf = cf.append(val_end, ignore_index=True)
cf = cf.sort_values(['Date'])
cf = cf.reset_index(drop=True)
# annualised return
annualised_irr = _xirr(values=cf.CashflowInHKD.to_list(),
dates=cf.Date.to_list())
# convert back to period if period is < a year
no_of_days = (pd.to_datetime(cf.iloc[-1].Date) -
pd.to_datetime(cf.iloc[0].Date)).days
if no_of_days < 365:
irr = (1 + annualised_irr)**(no_of_days / 365) - 1
else:
irr = annualised_irr
# return the calc results
dic = {
'StartDate': date_range_start_dt,
'EndDate': cf.tail(1).Date.iloc[0],
'InitialCashflow': val_start.CashflowInHKD.iloc[0],
'FinalCashflow': val_end.CashflowInHKD.iloc[0],
'IRR': irr
}
return dic
