def pf_sell_all(self, ref_date="1990-01-01"):
"""
Sell all holdings on a particular date
@params:
ref_date - Optional : date YYYY-MM-DD (Str)
"""
mdb_query = Query()
#Get link to MongoDB
#Get date for stock prices
dayBeforeDate = (pandas.Timestamp(ref_date) +
pandas.DateOffset(days=-1)).strftime('%Y-%m-%d')
#Get existing portfolios
portfolios = mdb_query.get_portfolios(ref_date)[[
"portfolioID", "inceptionDate"
]]
#Loop through portfolios
for portfolio_index, portfolio_row in portfolios.iterrows():
#Get portfolioID and inceptionDate
portfolio = portfolio_row['portfolioID']
#Get holdings table
holdings = mdb_query.get_holdings(portfolio, ref_date, "on")
#Get latest prices from dayBeforeDate
prices = mdb_query.get_chart(holdings['symbol'].tolist(),
dayBeforeDate, 'latest')
#Merge prices with holdings
holdings = pandas.merge(holdings,
prices,
how='left',
left_on=['symbol'],
right_on=['symbol'],
sort=False)
#Remove USD
holdings = holdings[holdings['symbol'] != 'USD']
#print( holdings )
#Build transaction tables which sell the stocks
transaction_tables = []
#Loop through stocks to be sold
for index, stock in holdings.iterrows():
#Transaction tables: type = sell, date = ref_date, price = close, volume = endOfDayQuantity
transaction_table = {
"portfolioID": portfolio_row.portfolioID,
"symbol": stock.symbol,
"type": "sell",
"date": ref_date,
"price": stock.close,
"volume": stock.endOfDayQuantity,
"commission": 0.0
}
transaction_tables.append(transaction_table)
insert_pf_transactions = True
if insert_pf_transactions:
#print( transaction_tables )
self.db.pf_transactions.insert_many(transaction_tables)
def calculate_top_stocks_old(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 earnings")
earnings = mdb_query.get_earnings(symbols, ref_date, "latest",
"EPSReportDate")
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")
financials = financials[
financials['reportDate'] <= earnings['fiscalEndDate'].max()]
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_chart(symbols_split, ref_date,
"latest")
prices = prices.append(prices_split, ignore_index=True, sort=False)
idx_min = idx_min + query_num
prices.reset_index(drop=True, inplace=True)
#print( prices )
#Get company data
company = mdb_query.get_company(symbols)
company = company[['symbol', 'companyName', 'industry', 'sector']]
#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(merged,
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=["netIncome", "actualEPS", "close", "shareholderEquity"])
#Calculate marketCap value
# price * netIncome / EPS = price * sharesOutstanding = mcap
# Actually not 100% accurate, should be netIncome - preferred dividend
# Doesn't perfectly match IEX value or google - probably good enough
merged["sharesOutstanding"] = merged.netIncome / merged.actualEPS
merged["marketCap"] = merged.sharesOutstanding * merged.close
#Calculate PE, ROE, and ratio
merged["peRatio"] = merged.close / merged.actualEPS
merged["returnOnEquity"] = merged.netIncome / merged.shareholderEquity
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_prices(self):
print( "Insert new charts" )
mdb_query = Query()
iex = Iex()
#Get all symbols in MongoDB
mdb_symbols_full = mdb_query.get_active_companies()
mdb_symbols_full = mdb_symbols_full[mdb_symbols_full == 'SPY']
#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
#flag = False
while idx_min < len(mdb_symbols_full.index):
#if idx_min > 1:
# break
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_charts = mdb_query.get_chart( mdb_symbols.tolist(), currDate, "latest" )
#print( mdb_charts )
#break
#Loop through symbols
for index, mdb_symbol in mdb_symbols.iteritems():
#Get matching chart in MongoDB
mdb_chart = mdb_charts[ mdb_charts['symbol'] == mdb_symbol ]
#Get first date to insert
date = mdb_chart.date.iloc[0]
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
iex_chart = pandas.DataFrame()
while date <= currDate:
#Get date in correct format for IEX query
iex_date = (pandas.Timestamp(date)).strftime('%Y%m%d')
#Get IEX chart
iex_chart_day = iex.get_chart( mdb_symbol, ref_range=iex_date )
iex_chart = iex_chart.append(iex_chart_day, ignore_index=True, sort=False)
#Increment date
date = (pandas.Timestamp(date) + pandas.DateOffset(days=1)).strftime('%Y-%m-%d')
#Get 1y of charts from IEX
#print( mdb_symbol )
#print( mdb_symbol["symbol"] )
#if mdb_symbol["symbol"] == "ZZZZZZZZZ":
# flag = True
#if not flag:
# continue
#iex_chart = iex.get_chart( mdb_symbol, ref_range='5d' )
#Get matching chart in MongoDB
#mdb_chart = mdb_charts[ mdb_charts['symbol'] == mdb_symbol ]
#Select charts more recent than MongoDB
#if not iex_chart.empty and not mdb_chart.empty:
# mask = iex_chart['date'] > mdb_chart['date'].iloc[0]
# iex_chart = iex_chart.loc[mask]
#Insert if charts exist
#print( iex_chart )
if not iex_chart.empty:
#Update progress bar
printProgressBar(idx_min+index+1, len(mdb_symbols_full.index), prefix = 'Progress:', suffix = "Inserting chart for " + mdb_symbol + " ", length = 50)
#Print write error if couldn't insert charts
try:
print( iex_chart )
self.db.iex_charts.insert_many( iex_chart.to_dict('records') )
except BulkWriteError as bwe:
print( bwe.details )
raise
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)
idx_min = idx_min + query_num