def insert_performance(self):
print( "Insert portfolio performance tables" )
mdb_query = Query()
#Get current date
currDate = datetime.datetime.now().strftime("%Y-%m-%d")
#currDate = "2019-12-27"
#Get existing portfolios
portfolios = mdb_query.get_portfolios(currDate)[["portfolioID","inceptionDate"]]
#Loop through portfolios
for portfolio_index, portfolio_row in portfolios.iterrows():
#Get portfolioID and inceptionDate
portfolio = portfolio_row.portfolioID
inceptionDate = portfolio_row.inceptionDate
print( 'Inserting performance tables for ' + portfolio )
#Get holdings tables from inception
holdings = mdb_query.get_holdings(portfolio, inceptionDate, "after").sort_values(by="lastUpdated", ascending=False, axis="index")
#print( holdings )
#Default to calculating performance from inception
date = inceptionDate
#Get list of symbols in holdings table
symbols = holdings["symbol"].unique().tolist()
#Get existing performance table for portfolio sorted by date
performance = mdb_query.get_performance([portfolio], inceptionDate)
if not performance.empty:
performance.sort_values(by="date", ascending=False, axis="index", inplace=True)
#Get close value from last date and increment the date
perf_tables = []
prevCloseValue = 0
adjPrevCloseValue = 0
if not performance.empty:
date = performance.iloc[0]["date"]
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
adjPrevCloseValue = performance.iloc[0]["closeValue"]
prevCloseValue = performance.iloc[0]["closeValue"]
#print( date )
#Get prices for symbols in portfolio after date
prices = mdb_query.get_quotes(symbols, date, "after")
#print( prices )
#If there are no prices then can't calculate performance
if prices.empty:
print( "No prices!" )
continue
#Get any transactions after date
transactions = mdb_query.get_transactions(portfolio, date, "after")
#print( transactions )
#Loop through dates
while date <= currDate:
#print( date )
#Initialize portfolio close of day values
closeValue = 0
adjCloseValue = 0
#Get latest holding for each symbol on date
holdings_date = holdings[holdings.lastUpdated <= date]
holdings_date = holdings_date[holdings_date.groupby(['symbol'], sort=False)['lastUpdated'].transform(max) == holdings_date['lastUpdated']]
#Merge with stock prices
holdings_date = pandas.merge(holdings_date,prices[prices.date == date],how='left',left_on=["symbol"],right_on=["symbol"],sort=False)
#Remove stocks no longer held
holdings_date = holdings_date[ holdings_date['endOfDayQuantity'] != 0 ]
#print( holdings_date )
#Skip if only USD in holdings
if holdings_date[holdings_date.symbol != "USD"].empty:
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
continue
#Skip any day where there aren't prices for all stocks
if holdings_date[holdings_date.symbol != "USD"]['close'].isnull().values.any():
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
continue
#Calculate portfolio close of day value from close of day stock prices
if not holdings_date.empty:
for index, holding in holdings_date.iterrows():
if holding.symbol == "USD":
closeValue = closeValue + (holding.endOfDayQuantity)
else:
closeValue = closeValue + (holding.endOfDayQuantity * holding.close)
#Get any deposits or withdrawals
deposits = pandas.DataFrame()
withdrawals = pandas.DataFrame()
if not transactions.empty:
deposits = transactions[(transactions.date == date) & (transactions.type == "deposit")]
withdrawals = transactions[(transactions.date == date) & (transactions.type == "withdrawal")]
#print( deposits )
#print( withdrawals )
#Adjust close or previous close for withdrawals/deposits
adjPrevCloseValue = prevCloseValue
adjCloseValue = closeValue
if not deposits.empty:
for index, deposit in deposits.iterrows():
adjPrevCloseValue = adjPrevCloseValue + (deposit.volume * deposit.price)
if not withdrawals.empty:
for index, withdrawal in withdrawals.iterrows():
adjCloseValue = adjCloseValue + (withdrawal.volume * withdrawal.price)
#If portfolio has no holdings or deposits yet then continue
if adjPrevCloseValue == 0:
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
continue
#Build portfolio performance table
perf_table = { "portfolioID": portfolio,
"date": date,
"prevCloseValue": prevCloseValue,
"closeValue": closeValue,
"adjPrevCloseValue": adjPrevCloseValue,
"adjCloseValue": adjCloseValue,
"percentReturn": 100.*((adjCloseValue-adjPrevCloseValue)/adjPrevCloseValue) }
perf_tables.append( perf_table )
#Reset previous close values
prevCloseValue = closeValue
adjPrevCloseValue = closeValue
#Increment date
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
#Insert performance table
insert_pf_performance = True
#print( perf_tables )
if insert_pf_performance and len(perf_tables)>0:
#print( perf_tables )
self.db.pf_performance.insert_many( perf_tables )
def insert_holdings(self):
print( "Calculate portfolio holdings" )
mdb_query = Query()
#Get current date
currDate = datetime.datetime.now().strftime("%Y-%m-%d")
#currDate = "2019-12-30"
#Get existing portfolios
portfolios = mdb_query.get_portfolios(currDate)[["portfolioID","inceptionDate"]]
#Loop through portfolios
for portfolio_index, portfolio_row in portfolios.iterrows():
#Get portfolioID and inceptionDate
portfolio = portfolio_row['portfolioID']
inceptionDate = portfolio_row['inceptionDate']
print( 'Calculating holdings for ', portfolio )
#Default to calculating holdings from inception
date = inceptionDate
#Get current holdings table
holdings = mdb_query.get_holdings(portfolio, currDate, "on")
#print( holdings )
#If holdings exist then calculate holdings from next date
if not holdings.empty:
date = holdings['lastUpdated'].max()
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
#If no existing holdings create 0 dollar entry to create table
if holdings.empty:
holding_dict = { "portfolioID": portfolio,
"symbol": "USD",
"endOfDayQuantity": 0.0,
"lastUpdated": inceptionDate }
holdings = pandas.DataFrame.from_dict(holding_dict, orient='index').T
#Get all new transactions
transactions = mdb_query.get_transactions(portfolio, date, "after")
#Loop through dates and update holdings table
while date <= currDate:
#Insert dividends to transactions database and update transactions table
#Dividends will be ahead of time so will be ready to be added on the correct date
#Get dividends for all holdings except USD
div_holdings = holdings[ ~holdings["symbol"].isin(["USD"]) ]
dividends = mdb_query.get_dividends(div_holdings["symbol"].unique().tolist(), date, "on")
#Get rid of any non-USD dividends
if not dividends.empty:
dividends = dividends[ dividends['currency'] == 'USD' ]
if not dividends.empty:
#Get dividends with exDate = date
div_date = dividends[ dividends.exDate == date ]
#Loop through dividends
for d_index, dividend in div_date.iterrows():
#print( dividend )
#Is dividend already in transactions?
#If not insert it
#Skip dividends with bad data entries
if not dividend.amount:
print( "Skipping dividend as amount is empty!" )
continue
if dividend.paymentDate == None:
print( "Skipping dividend as paymentDate is None!" )
continue
transactions_paymentDate = transactions
if not transactions.empty:
transactions_paymentDate = transactions[ (transactions.date == dividend.paymentDate) & (transactions.symbol == dividend.symbol) & (transactions.type == 'dividend') ]
holding_quantity = holdings[holdings["symbol"] == dividend.symbol]["endOfDayQuantity"]
holding_quantity.reset_index(drop=True, inplace=True)
if transactions_paymentDate.empty and (holding_quantity != 0).any():
transaction_table = { "portfolioID": portfolio,
"symbol": dividend.symbol,
"type": "dividend",
"date": dividend.paymentDate,
"price": float(dividend.amount),
"volume": holding_quantity.iloc[0],
"commission": 0.0 }
transactions = transactions.append( pandas.DataFrame.from_dict(transaction_table, orient='index').T, ignore_index=True, sort=False )
insert_pf_transactions = True
if insert_pf_transactions:
print( "Inserting dividend: " + date )
print( transaction_table )
self.db.pf_transactions.insert_one( transaction_table )
#Now attend to transactions on date
if transactions.empty:
#Increment date
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
continue
transactions_date = transactions[transactions.date == date]
#Loop through transactions
for t_index, transaction in transactions_date.iterrows():
print( "Inserting transaction:" )
print( transaction )
#Get any existing holding for the transaction symbol
if transaction.type == "dividend":
holding = holdings[holdings.symbol == "USD"]
else:
holding = holdings[holdings.symbol == transaction.symbol]
holding.reset_index(drop=True, inplace=True)
#print( holding )
#Remove that holding from holdings table
if not holding.empty:
holdings = holdings[ ~holdings["symbol"].isin([holding['symbol'].iloc[0]]) ]
#print( holdings )
#Add any dividends to the holdings table
if transaction.type == "dividend":
holding["endOfDayQuantity"] = holding["endOfDayQuantity"] + (transaction.price * transaction.volume)
holding["lastUpdated"] = date
holdings = holdings.append( holding, ignore_index=True, sort=False )
#Add any deposits to the holdings table
if transaction.type == "deposit":
holding["endOfDayQuantity"] = holding["endOfDayQuantity"] + (transaction.price * transaction.volume)
holding["lastUpdated"] = date
holdings = holdings.append( holding, ignore_index=True, sort=False )
#Add any stocks purchased to the holdings table
if transaction.type == "buy":
holding_dict = {}
if not holding.empty:
holding_dict = { "portfolioID": transaction.portfolioID,
"symbol": transaction.symbol,
"endOfDayQuantity": holding["endOfDayQuantity"].iloc[0] + transaction.volume,
"lastUpdated": date }
else:
holding_dict = { "portfolioID": transaction.portfolioID,
"symbol": transaction.symbol,
"endOfDayQuantity": transaction.volume,
"lastUpdated": date }
#print( holding_dict )
holdings = holdings.append( pandas.DataFrame.from_dict(holding_dict, orient='index').T, ignore_index=True, sort=False )
#Adjust cash entry accordingly
cash = holdings[holdings.symbol == "USD"]
holdings = holdings[ ~holdings["symbol"].isin(["USD"]) ]
cash["endOfDayQuantity"] = cash["endOfDayQuantity"] - (transaction.price * transaction.volume)
cash["lastUpdated"] = date
holdings = holdings.append( cash, ignore_index=True, sort=False )
#print( holdings )
#Remove any stocks sold from the holdings table
if transaction.type == "sell":
if not holding.empty:
holding["endOfDayQuantity"] = holding["endOfDayQuantity"] - transaction.volume
holding["lastUpdated"] = date
holdings = holdings.append( holding, ignore_index=True, sort=False )
else:
raise Exception("Trying to sell unowned stock!")
#Adjust cash entry accordingly
cash = holdings[holdings.symbol == "USD"]
holdings = holdings[ ~holdings["symbol"].isin(["USD"]) ]
cash["endOfDayQuantity"] = cash["endOfDayQuantity"] + (transaction.price * transaction.volume)
cash["lastUpdated"] = date
holdings = holdings.append( cash, ignore_index=True, sort=False )
#print( holdings )
#Upload new holdings entries to MongoDB
holdings_date = holdings[holdings.lastUpdated == date]
if not holdings_date.empty:
print( "New holdings:" )
print( holdings_date )
insert_holdings_tx = True
if insert_holdings_tx:
print( "Inserting holdings for " + portfolio )
self.db.pf_holdings.insert_many( holdings_date.to_dict('records') )
#Increment date
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
