def GetFXRate(target_ccy, original_ccy): #target_ccy, original_ccy ='HKD','GBP' #target_ccy, original_ccy ='HKD','XAU' if target_ccy == original_ccy: fxrate = 1 else: ccypair = original_ccy + target_ccy # use exchange rate in LastNAV db = setup.ConnectToMongoDB() coll = db['FX'] df = pd.DataFrame(list(coll.find())) # check if there is data, if not try inverted if len(df[df.Ccypair == ccypair]) > 0: row = df[df.Ccypair == ccypair].iloc[0] fxrate = row.PX_LAST else: ccypair = ccypair[-3:] + ccypair[:3] # in case ccypair not cached, get live rate from Yahoo Finance API if len(df[df.Ccypair == ccypair]) == 0: fxrate = ConvertFX(original_ccy, target_ccy) else: row = df[df.Ccypair == ccypair].iloc[0] fxrate = 1 / row.PX_LAST if original_ccy == 'JPY': fxrate = 1 / fxrate return fxrate
def GetPortfolioSummaryFromDB(summary_type='Original'): db = setup.ConnectToMongoDB() coll = db['PortfolioSummary'] df = pd.DataFrame(list(coll.find({'SummaryType': summary_type}))) df.drop(columns=['_id'], inplace=True) return df
def CalcIRRAndCacheOnDB(): print('\nCalculating IRR - this may take a few mins...') # DB table db = setup.ConnectToMongoDB() coll = db['PortfolioPerformance'] # get the date ranges for computation date_ranges = util.date_ranges # compute the returns returns = {} for i in range(len(date_ranges)): returns[date_ranges[i]] = CalcIRR(period=date_ranges[i]) # prepare the record to append to DB rec = { 'Platform': None, 'BBGCode': None, 'Period': date_ranges[i], 'IRR': returns[date_ranges[i]]['IRR'], 'StartDate': returns[date_ranges[i]]['StartDate'], 'EndDate': returns[date_ranges[i]]['EndDate'], 'StartCashflow': returns[date_ranges[i]]['InitialCashflow'], 'EndCashflow': returns[date_ranges[i]]['FinalCashflow'], 'LastUpdated': datetime.datetime.now() } # remove previous record coll.delete_many({ 'Platform': None, 'BBGCode': None, 'Period': date_ranges[i] }) # append the record coll.insert_one(rec) print('(completed and cached on DB)')
def GetLastNAV(): db = setup.ConnectToMongoDB() LastNAV = db['LastNAV'] LastNAV.find() df = pd.DataFrame(list(LastNAV.find())) df.drop(columns=['_id'], inplace=True) return df
def _GetSecurityCategory(name): #name='Cash NZD' db = setup.ConnectToMongoDB() coll = db['Security'] df = pd.DataFrame(list(coll.find())) match = df[df.Name == name] category = match.iloc[0].Category return category
def GetHistoricalData(bbgcode=None, start_date=None): db = setup.ConnectToMongoDB() coll = db['HistoricalMarketData'] df = pd.DataFrame(list(coll.find())) df.drop(['_id'], axis=1, inplace=True) if bbgcode is not None: df = df[df.BBGCode == bbgcode] if start_date is not None: df = df[df.Date >= start_date] return df
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 CalcPortfolioSummaryAndCacheOnDB(): print('\nComputing portfolio summary...') # DB db = setup.ConnectToMongoDB() coll = db['PortfolioSummary'] # Get calculations ps = GetPortfolioSummary() ps_original = ps['Original'] ps_adjusted = ps['Adjusted'] ps_adjustedIncCash = GetPortfolioSummaryIncCash() # inc cash is adjusted # fill blanks ps_adjusted['PortfolioPct'] = None # add summary type ps_original['SummaryType'] = 'Original' ps_adjusted['SummaryType'] = 'Adjusted' ps_adjustedIncCash['SummaryType'] = 'AdjustedIncCash' # cache on DB coll.delete_many({'SummaryType': 'Original'}) coll.insert_many(ps_original.to_dict('records')) coll.delete_many({'SummaryType': 'Adjusted'}) coll.insert_many(ps_adjusted.to_dict('records')) coll.delete_many({'SummaryType': 'AdjustedIncCash'}) coll.insert_many(ps_adjustedIncCash.to_dict('records')) print('(updated portfolio summary on mongodb)') # output to CSV file ps_original = ps_original[[ 'Platform', 'Name', 'BBGCode', 'WAC', 'LastNAV', 'LastUpdated', 'CostInHKD', 'CurrentValueInHKD', 'PnLInHKD', 'PnLPct', 'PortfolioPct' ]].copy() ps_original.rename(columns={ 'LastNAV': 'Last NAV', 'LastUpdated': 'Last Updated', 'WAC': 'WA cost', 'CostInHKD': 'Cost (HKD)', 'CurrentValueInHKD': 'Current Value (HKD)', 'PnLInHKD': 'PnL (HKD)', 'PnLPct': 'PnL (%)', 'PortfolioPct': '% of Ptf' }, inplace=True) ps_original.to_csv(_output_dir + r'\ps_original.csv', index=False) print('(exported portfolio summary as CSV)')
def ProcessHistoricalSPX(): print('\nProcessing historical S&P 500 prices...') # collect from Yahoo Finance spx = pdr.get_data_yahoo('^GSPC', start='2015-07-01', end=datetime.datetime.today()) spx = spx[['Close']] spx.columns = ['SPX'] spx = spx.reset_index() # store on DB db = setup.ConnectToMongoDB() coll = db['SPX'] coll.delete_many({}) coll.insert_many(spx.to_dict('records')) print('(updated %s records on MongoDB)' % len(spx))
def ProcessHistoricalUSDHKD(): print('\nProcessing historical USDHKD rates...') # collect from Yahoo Finance usdhkd = pdr.get_data_yahoo('HKD=X', start='2015-07-01', end=datetime.datetime.today()) usdhkd = usdhkd[['Close']] usdhkd.columns = ['USDHKDrate'] usdhkd = usdhkd.reset_index() # store on DB db = setup.ConnectToMongoDB() coll = db['USDHKD'] coll.delete_many({}) coll.insert_many(usdhkd.to_dict('records')) print('(updated %s records on MongoDB)' % len(usdhkd))
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
def _GetPlatformDef(): db = setup.ConnectToMongoDB() coll = db['Platform'] df = pd.DataFrame(list(coll.find())) df.drop('_id', axis=1, inplace=True) return df
def GetHistoricalSPX(): db = setup.ConnectToMongoDB() coll = db['SPX'] df = pd.DataFrame(list(coll.find())) df.drop(['_id'], axis=1, inplace=True) return df
def GetIRRFromDB(): db = setup.ConnectToMongoDB() coll = db['PortfolioPerformance'] df = pd.DataFrame(list(coll.find({'Platform': None, 'BBGCode': None}))) df.drop(columns=['_id'], inplace=True) return df # # compute the Modified Dietz return # def CalcModDietzReturn(platform, bbgcode=None, period=None): # #period='3M' # #period='1W' # #platform='FSM HK' # #bbgcode=None # #bbgcode='VGT US' # #bbgcode='XLE US' # bad example, need to adjust for transferring from SG to HK # #bbgcode='ALLGAME LX' # # collect the data # #df = _GetDataset() # df = setup.GetAllTransactions() # # filter on date range for the transactions / cash flows # if period is not None: # start_date = util.GetStartDate(period) # df = df[df.Date >= np.datetime64(start_date)] # # filter the data based on selection criteria (bbgcode, or platform) # df = df[df.Platform==platform] # df.drop(['_id'], axis=1, inplace=True) # if bbgcode is not None: # df = df[df.BBGCode==bbgcode] # # Buy and Sell # cf = df[df.Type.isin(['Buy','Sell'])].copy() # div = df[df.Type=='Dividend'] # # calc dates # date_start = cf.Date.min() # date_end = np.datetime64(datetime.datetime.today().date()) # date_diff = (date_end - date_start).days # # calculate No of days for weighting # cf.loc[:,'NoOfDays'] = (datetime.datetime.today() - cf.loc[:,'Date']) / pd.Timedelta(days=1) # cf.loc[:,'NoOfDays'] = cf.loc[:,'NoOfDays'].astype(int) # cf.loc[:,'WeightedCF'] = cf.loc[:,'CostInPlatformCcy'] * cf.loc[:,'NoOfDays'] / date_diff # # pnl = current value + realised gains/losses # # realised gains/losses (sold securities + dividends) # pnl_realised = df.RealisedPnL.sum() # ps = calc_summary.GetPortfolioSummary() # ps = ps['Original'] # #ps = calc_summary.ps_original.copy() # # unrealised pnl # if bbgcode is not None: # if len(ps_active.BBGCode==bbgcode) > 0: # r = ps_active[ps_active.BBGCode==bbgcode].iloc[0] # # current value needs to include realised gains & dividends # current_value = r.CurrentValue + pnl_realised # else: # ps_active = ps[ps.CurrentValue!=0] # r = ps_active[ps_active.Platform==platform] # # current value needs to include realised gains & dividends # current_value = pnl_realised + r.CurrentValue.sum() # # withdrawals # withdrawals = cf[cf.Type=='Sell'] # # deposits # deposits = cf[cf.Type=='Buy'] # # numerator: V(1) - V(0) - sum of cash flows # if period is None: # beginning_value = 0 # else: # beginning_value = _GetValuation(np.datetime64(start_date)) # net_external_cash_flows = deposits.CostInPlatformCcy.sum() + withdrawals.CostInPlatformCcy.sum() # num = current_value - beginning_value - net_external_cash_flows # # denominator: V(0) + sum of cash flows weighted # den = beginning_value + deposits.WeightedCF.sum() + withdrawals.WeightedCF.sum() # # Modified Dietz Return (cumulative) # mdr = num/den # # calculate annualised return # annualised_return = (1 + mdr) ** (365/date_diff) - 1 # # object to return # obj = {} # obj['DateStart'] = date_start # obj['DateEnd'] = date_end # obj['CumulativeReturn'] = mdr # obj['AnnualisedReturn'] = annualised_return # return obj # #CalcModDietzReturn('FSM HK', period='3M') # #CalcModDietzReturn('FSM HK', period='1M') # BUG!!! # #CalcModDietzReturn('FSM HK', period='1W') # BUG!!!
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 GetHistoricalSnapshotFromDB(): db = setup.ConnectToMongoDB() coll = db['HistoricalSnapshot'] df = pd.DataFrame(list(coll.find())) df.drop(columns=['_id'], inplace=True) return df
def ProcessLastNAV(): # get all transactions from MongoDB tran = setup.GetAllTransactions() #tran['NoOfUnits'] = tran.NoOfUnits.astype(np.float32) tran_summary = tran.groupby(['Platform', 'BBGCode']).aggregate({'NoOfUnits': 'sum'}) tran_summary = tran_summary.reset_index(drop=False) # filter transactions tran_summary = tran_summary[tran_summary.NoOfUnits > 0.001] tran_summary = tran_summary[tran_summary.Platform.str[:4] != 'Cash'] # exclude Singapore cash fund tran_summary = tran_summary[tran_summary.BBGCode != 'CASHFND SP'] # enrich with platform and security currency dfPlatforms = pd.DataFrame() dfPlatforms['Platform'] = tran_summary.Platform.unique() dfPlatforms['PlatformCcy'] = [ setup.GetPlatformCurrency(x) for x in list(tran_summary.Platform.unique()) ] tran_summary = tran_summary.merge(dfPlatforms, how='left', left_on='Platform', right_on='Platform') secs = setup.GetSecurities() tran_summary = tran_summary.merge(secs[['BBGCode', 'Currency']], how='left', left_on='BBGCode', right_on='BBGCode') tran_summary.rename(columns={'Currency': 'SecurityCcy'}, inplace=True) # enrich with last NAV lastnav = pd.read_excel(mdata.LastNavFilePath) lastnav.rename(columns={'Ticker_BBG': 'BBGCode'}, inplace=True) tran_summary = tran_summary.merge( lastnav[['BBGCode', 'LastNAV', 'LastUpdated']], how='left', left_on='BBGCode', right_on='BBGCode') # calculate FX rate for i in range(len(tran_summary)): row = tran_summary.iloc[i] if row.PlatformCcy == row.SecurityCcy: tran_summary.loc[i, 'FXRate'] = 1 else: tran_summary.loc[i, 'FXRate'] = calc_fx.GetFXRate( row.PlatformCcy, row.SecurityCcy) # format output columns tran_summary.rename(columns={'SecurityCcy': 'SecurityCurrency'}, inplace=True) tran_summary.drop(columns=['PlatformCcy', 'NoOfUnits'], inplace=True) # save results on MongoDB db = setup.ConnectToMongoDB() LastNAV = db['LastNAV'] LastNAV.delete_many({}) LastNAV.insert_many(tran_summary[[ 'BBGCode', 'LastNAV', 'SecurityCurrency', 'FXRate', 'LastUpdated' ]].to_dict('records'))