def insert_quotes(self):
print( "Insert new quotes" )
mdb_query = Query()
iex = Iex()
#Get all symbols in MongoDB
mdb_symbols_full = mdb_query.get_active_companies()
#Get current date
currDate = datetime.datetime.now().strftime("%Y-%m-%d")
#Initial call to print 0% progress
printProgressBar(0, len(mdb_symbols_full.index), prefix = 'Progress:', suffix = '', length = 50)
idx_min = 0
query_num = 100
while idx_min < len(mdb_symbols_full.index):
idx_max = idx_min + query_num
if idx_max > len(mdb_symbols_full.index):
idx_max = len(mdb_symbols_full.index)
mdb_symbols = mdb_symbols_full.iloc[ idx_min:idx_max ]
mdb_symbols.reset_index(drop=True, inplace=True)
#Get latest price in MongoDB for each symbol up to 50 days ago
mdb_quotes = mdb_query.get_quotes( mdb_symbols.tolist(), currDate, "latest" )
#Loop through symbols
iex_quotes = pandas.DataFrame()
for index, mdb_symbol in mdb_symbols.iteritems():
#Get matching quote in MongoDB
if not mdb_quotes.empty:
mdb_quote = mdb_quotes[ mdb_quotes['symbol'] == mdb_symbol ]
else:
mdb_quote = mdb_quotes
#continue if already up to date
if not mdb_quote.empty and (mdb_quote['date'].iloc[0] == currDate):
#Update progress bar
printProgressBar(idx_min+index+1, len(mdb_symbols_full.index), prefix = 'Progress:', suffix = "No new data for " + mdb_symbol + " ", length = 50)
continue
#Get quote from IEX
iex_quote = iex.get_quote( mdb_symbol )
#Select quotes more recent than MongoDB
if not iex_quote.empty and not mdb_quote.empty:
mask = iex_quote['date'] > mdb_quote['date'].iloc[0]
iex_quote = iex_quote.loc[mask]
#Insert if quotes exist
if not iex_quote.empty:
#Update progress bar
printProgressBar(idx_min+index+1, len(mdb_symbols_full.index), prefix = 'Progress:', suffix = "Inserting quote for " + mdb_symbol + " ", length = 50)
#Append quote
iex_quotes = iex_quotes.append(iex_quote, ignore_index=True, sort=False)
else:
#Update progress bar
printProgressBar(idx_min+index+1, len(mdb_symbols_full.index), prefix = 'Progress:', suffix = "No new data for " + mdb_symbol + " ", length = 50)
#Bulk insert quotes
if not iex_quotes.empty:
self.db.iex_quotes.insert_many( iex_quotes.to_dict('records') )
idx_min = idx_min + query_num
def calculate_top_stocks(self, ref_date):
"""
Calculate ranked list of stocks
@params:
ref_date - Required : date YYYY-MM-DD (Str)
"""
mdb_query = Query()
#Get ranked stock list for given date
symbols = mdb_query.get_active_companies().tolist()
print("Query balance sheets")
balancesheets = mdb_query.get_balancesheets(symbols, ref_date,
"latest")
#earnings = earnings[["EPSReportDate","actualEPS","fiscalEndDate","fiscalPeriod","symbol"]]
#print( earnings )
#Get financials within 6 months
#print( "Query financials" )
sixMonthsBeforeDate = (
pandas.Timestamp(ref_date) +
pandas.DateOffset(months=-6)).strftime('%Y-%m-%d')
#financials = mdb_query.get_financials(symbols, sixMonthsBeforeDate, "after")
balancesheets = balancesheets[
balancesheets['reportDate'] >= sixMonthsBeforeDate]
#financials = financials[["symbol","reportDate","netIncome","shareholderEquity"]]
#print( financials )
#Get prices for inception date
print("Query prices")
idx_min = 0
query_num = 100
prices = pandas.DataFrame()
while idx_min < len(symbols):
idx_max = idx_min + query_num
if idx_max > len(symbols):
idx_max = len(symbols)
symbols_split = symbols[idx_min:idx_max]
prices_split = mdb_query.get_quotes(symbols_split, ref_date,
"latest")
prices = prices.append(prices_split, ignore_index=True, sort=False)
idx_min = idx_min + query_num
#Get prices within 7 days
fiveDaysBeforeDate = (pandas.Timestamp(ref_date) +
pandas.DateOffset(days=-7)).strftime('%Y-%m-%d')
prices = prices[prices['date'] >= fiveDaysBeforeDate]
prices.reset_index(drop=True, inplace=True)
#print( prices )
#Get company data
company = mdb_query.get_company(symbols)
company = company[['symbol', 'companyName']]
#Merge dataframes together
print("Merge dataframes")
#merged = pandas.merge(earnings,financials,how='inner',left_on=["symbol","fiscalEndDate"],right_on=["symbol","reportDate"],sort=False)
merged = pandas.merge(balancesheets,
prices,
how='inner',
on="symbol",
sort=False)
merged = pandas.merge(merged,
company,
how='inner',
on='symbol',
sort=False)
#Remove any rows with missing values
merged = merged.dropna(
axis=0,
subset=['shareholderEquity', 'close', 'marketCap', 'peRatio'])
#Calculate ROE
#close / peRatio = EPS
#marketCap / close = sharesOutstanding
#sharesOutstanding * EPS = netIncome
#netIncome / shareholderEquity = returnOnEquity
merged = merged[merged.peRatio != 0]
merged["EPS"] = merged.close / merged.peRatio
merged = merged[merged.close != 0]
merged["sharesOutstanding"] = merged.marketCap / merged.close
merged["netIncome"] = merged.sharesOutstanding * merged.EPS
merged = merged[merged.shareholderEquity != 0]
merged["returnOnEquity"] = merged.netIncome / merged.shareholderEquity
merged = merged[merged.returnOnEquity != 0]
merged["peROERatio"] = merged.peRatio / merged.returnOnEquity
#Count number of stocks above mcap value
# A useful indicator of how universe compares to S&P500
print("Universe before cuts...")
print("mcap > 50M: " +
str(merged[merged["marketCap"] > 50000000].count()["marketCap"]))
print(
"mcap > 100M: " +
str(merged[merged["marketCap"] > 100000000].count()["marketCap"]))
print(
"mcap > 500M: " +
str(merged[merged["marketCap"] > 500000000].count()["marketCap"]))
print(
"mcap > 1B: " +
str(merged[merged["marketCap"] > 1000000000].count()["marketCap"]))
print(
"mcap > 5B: " +
str(merged[merged["marketCap"] > 5000000000].count()["marketCap"]))
print("mcap > 10B: " + str(merged[
merged["marketCap"] > 10000000000].count()["marketCap"]))
print("mcap > 50B: " + str(merged[
merged["marketCap"] > 50000000000].count()["marketCap"]))
print("mcap > 100B: " + str(merged[
merged["marketCap"] > 100000000000].count()["marketCap"]))
#Rank stocks
#Cut negative PE and ROE
merged = merged[(merged.peRatio > 0) & (merged.returnOnEquity > 0)]
#Remove invalid stock symbols, and different voting options
# Do the different voting options affect marketCap?
#forbidden = [ "#", ".", "-" ]
#merged = merged[ merged.apply( lambda x: not any( s in x['symbol'] for s in forbidden ), axis=1 ) ]
#Remove American Depositary Shares
#ads_str = 'American Depositary Shares'
#merged = merged[ merged.apply( lambda x: ads_str not in x['companyName'], axis=1 ) ]
#Remove industries that do not compare well
# e.g. Companies that have investments as assets
#forbidden_industry = ['Brokers & Exchanges','REITs','Asset Management','Banks']
#merged = merged[ ~merged.industry.isin( forbidden_industry ) ]
#Count number of stocks after cuts
print("Universe after cuts...")
print("mcap > 50M: " +
str(merged[merged["marketCap"] > 50000000].count()["marketCap"]))
print(
"mcap > 100M: " +
str(merged[merged["marketCap"] > 100000000].count()["marketCap"]))
print(
"mcap > 500M: " +
str(merged[merged["marketCap"] > 500000000].count()["marketCap"]))
print(
"mcap > 1B: " +
str(merged[merged["marketCap"] > 1000000000].count()["marketCap"]))
print(
"mcap > 5B: " +
str(merged[merged["marketCap"] > 5000000000].count()["marketCap"]))
print("mcap > 10B: " + str(merged[
merged["marketCap"] > 10000000000].count()["marketCap"]))
print("mcap > 50B: " + str(merged[
merged["marketCap"] > 50000000000].count()["marketCap"]))
print("mcap > 100B: " + str(merged[
merged["marketCap"] > 100000000000].count()["marketCap"]))
#Order by peROERatio
merged = merged.sort_values(by="peROERatio",
ascending=True,
axis="index")
return merged
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 )