def insert_financials(self): print( "Insert new financials" ) mdb_query = Query() iex = Iex() #Get all symbols in MongoDB mdb_symbols = mdb_query.get_active_companies() #Get current date currDate = datetime.datetime.now().strftime("%Y-%m-%d") #Get latest financials in MongoDB for each symbol mdb_financials = mdb_query.get_financials( mdb_symbols.tolist(), currDate, "latest" ) #Initial call to print 0% progress printProgressBar(0, len(mdb_symbols.index), prefix = 'Progress:', suffix = '', length = 50) #Loop through symbols for index, mdb_symbol in mdb_symbols.iteritems(): #Get financials from IEX iex_financials = iex.get_financials( mdb_symbol ) #Get matching financial in MongoDB mdb_financial = mdb_financials[ mdb_financials['symbol'] == mdb_symbol ] #Select financials more recent than MongoDB if not mdb_financial.empty and not iex_financials.empty: mask = iex_financials['reportDate'] > mdb_financial['reportDate'].iloc[0] iex_financials = iex_financials.loc[mask] #Insert if financials exist if not iex_financials.empty: #Update progress bar printProgressBar(index+1, len(mdb_symbols.index), prefix = 'Progress:', suffix = "Inserting financials for " + mdb_symbol + " ", length = 50) self.db.iex_financials.insert_many( iex_financials.to_dict('records') ) else: #Update progress bar printProgressBar(index+1, len(mdb_symbols.index), prefix = 'Progress:', suffix = "No new data for " + mdb_symbol + " ", length = 50)
def insert_stats(self): print( "Insert new stats" ) 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 == 'A'] #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 = True 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_stats = mdb_query.get_stats( mdb_symbols.tolist(), currDate, "latest" ) #Loop through symbols for index, mdb_symbol in mdb_symbols.iteritems(): #Get stat from IEX #print( mdb_symbol ) #print( mdb_symbol["symbol"] ) #if mdb_symbol == "BAH": # flag = False #if flag: # continue iex_stat = iex.get_stats( mdb_symbol ) #Get matching stat in MongoDB if not mdb_stats.empty: mdb_stat = mdb_stats[ mdb_stats['symbol'] == mdb_symbol ] else: mdb_stat = mdb_stats #Select stats more recent than MongoDB if not iex_stat.empty and not mdb_stat.empty: mask = iex_stat['date'] > mdb_stat['date'].iloc[0] iex_stat = iex_stat.loc[mask] #Insert if stats exist #print( iex_stat ) if not iex_stat.empty: #Update progress bar printProgressBar(idx_min+index+1, len(mdb_symbols_full.index), prefix = 'Progress:', suffix = "Inserting stat for " + mdb_symbol + " ", length = 50) self.db.iex_stats.insert_many( iex_stat.to_dict('records') ) 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
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 insert_balancesheets(self): print( "Insert new balance sheets" ) mdb_query = Query() iex = Iex() #Get all symbols in MongoDB mdb_symbols = mdb_query.get_active_companies() #Get current date currDate = datetime.datetime.now().strftime("%Y-%m-%d") threeMonthsAgo = (pandas.Timestamp(currDate) + pandas.DateOffset(days=-120)).strftime('%Y-%m-%d') #Get latest balancesheets in MongoDB for each symbol mdb_balancesheets = mdb_query.get_balancesheets( mdb_symbols.tolist(), currDate, "latest" ) #Initial call to print 0% progress printProgressBar(0, len(mdb_symbols.index), prefix = 'Progress:', suffix = '', length = 50) #flag = False #Loop through symbols for index, mdb_symbol in mdb_symbols.iteritems(): #if mdb_symbol == 'YETI': # flag = True #if not flag: # continue #Get matching balancesheet in MongoDB if not mdb_balancesheets.empty: mdb_balancesheet = mdb_balancesheets[ mdb_balancesheets['symbol'] == mdb_symbol ] else: mdb_balancesheet = mdb_balancesheets #Skip is less than 3 months since most recent if not mdb_balancesheet.empty: mask = mdb_balancesheet['reportDate'] > threeMonthsAgo mdb_recent_balancesheet = mdb_balancesheet.loc[mask] if not mdb_recent_balancesheet.empty: #Update progress bar printProgressBar(index+1, len(mdb_symbols.index), prefix = 'Progress:', suffix = "Data too recent for " + mdb_symbol + " ", length = 50) continue #Get balancesheets from IEX iex_balancesheets = iex.get_balancesheets( mdb_symbol ) #Select balancesheets more recent than MongoDB if not mdb_balancesheet.empty and not iex_balancesheets.empty: mdb_balancesheet = mdb_balancesheet.sort_values(by='reportDate', ascending=False, axis='index') mask = iex_balancesheets['reportDate'] > mdb_balancesheet['reportDate'].iloc[0] iex_balancesheets = iex_balancesheets.loc[mask] #Insert if balancesheets exist if not iex_balancesheets.empty: #Update progress bar printProgressBar(index+1, len(mdb_symbols.index), prefix = 'Progress:', suffix = "Inserting balancesheets for " + mdb_symbol + " ", length = 50) self.db.iex_balancesheets.insert_many( iex_balancesheets.to_dict('records') ) else: #Update progress bar printProgressBar(index+1, len(mdb_symbols.index), prefix = 'Progress:', suffix = "No new data for " + mdb_symbol + " ", length = 50)
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