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