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'))
