def update_tracker_prices_and_tweets_and_news(): LOGGER.info('WE IN THE SCHEDULER BOYS') if util.is_market_holiday(util.get_current_date(), session): return connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("SELECT ticker FROM Trackers") except Exception as e: return {'error': str(e)} tickers = [record[0] for record in cursor] cursor.close() connection.close() print('in scheduler got all tickers') for ticker in tickers: fundamentals = {} connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("CALL remove_old_daily_price_data(%s);", (ticker, )) except Exception as e: print({'error': e}) connection.rollback() connection.commit() try: cursor.execute("CALL remove_old_minute_price_data(%s);", (ticker, )) except Exception as e: print({'error': e}) connection.rollback() try: cursor.execute("SELECT * FROM Fundamentals WHERE ticker = %s", (ticker, )) except Exception as e: return {'error': str(e)} fundamentals = cursor.fetchone() connection.commit() cursor.close() connection.close() util.add_daily_closing_price(ticker, session) util.add_daily_minute_price(ticker, session) update_news(ticker) update_tweets(ticker, fundamentals[1]) print('in scheduler successfully updated all data possible') return { 'success': 'SUCESSFULLY UPDATED ALL PRICE AND TWEET DATA FOR ALL TRACKED TICKERS' }
def get_trackers(): connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute( "SELECT ticker, open, close, percent_change, notes, timestamp FROM Trackers" ) except Exception as e: return {'error': str(e)} tracked_raw_data = [record for record in cursor] if not tracked_raw_data or len(tracked_raw_data) == 0: return {'error': 'No stocks are currently being tracked'} tracked_stocks = [] for stock in tracked_raw_data: temp = { 'ticker': stock[0], 'open': stock[1], 'close': stock[2], 'percentage_change': stock[3], 'notes': stock[4], 'timestamp': stock[5] } tracked_stocks.append(temp) cursor.close() connection.close() return {'tracked': tracked_stocks}
def add_custom_query(name, query): postfix = util.user_query_to_postfix(query) if not postfix: return {'error': 'invalid syntax'} connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute( ''' INSERT INTO Queries (name, query, postfix) VALUES (%s, %s, %s) ON CONFLICT (query) DO NOTHING ''', [name, query, postfix]) except Exception as e: connection.rollback() return {'error': str(e)} connection.commit() cursor.close() connection.close() return {'name': name, 'query': query, 'postfix': postfix}
def on_message(ws, message): message = json.loads(message)[0] #print(message) if message['ev'] == 'AM': process_price_data(message) pass elif message['ev'] == 'status': if message['status'] != 'auth_success': return LOGGER_SOCKET.info('status message: ' + message['message']) connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute('SELECT ticker FROM Trackers') except Exception as e: LOGGER_SOCKET.error('error :' + str(e)) all_tickers = ['AM.' + record[0] for record in cursor] listen_message = { 'action': 'subscribe', 'params': ','.join(all_tickers) } ws.send(json.dumps(listen_message)) else: LOGGER_SOCKET.info('unexpected message: ' + message)
def get_all_tickers(): connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("SELECT ticker FROM Fundamentals") except Exception as e: return {'error': str(e)} tickers = [record[0] for record in cursor] connection.commit() cursor.close() connection.close() return {'tickers': tickers}
def add_tracker(ticker): ticker = ticker.upper() fundamentals = {} connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("CALL add_tracker(%s);", (ticker, )) except Exception as e: return {'error': str(e)} if not util.add_daily_price_data(ticker, session, connection, cursor) or not util.add_minute_price_data( ticker, session, connection, cursor): cursor.close() connection.close() return {'error': 'COULD NOT ADD PRICE DATA FOR' + ' ' + ticker} try: cursor.execute("SELECT * FROM Fundamentals WHERE ticker = %s", (ticker, )) except Exception as e: return {'error': str(e)} fundamentals = cursor.fetchone() util.add_tweets(ticker, fundamentals[1], TWEEPY_API, session, connection, cursor) util.add_news_articles(ticker, session, connection, cursor) col_ref = mongo_db['Live_Stock_Prices'] daily_prices = util.get_past_week_prices_mongo(ticker, session) if daily_prices: connection.commit() cursor.close() connection.close() new_tracker = { 'ticker': ticker, 'name': fundamentals[1], 'industry': fundamentals[2], 'sector': fundamentals[3], 'market_cap': fundamentals[4], 'description': fundamentals[5], 'daily_prices': daily_prices, 'minute_prices': [], 'prev_ema': [-1] * 391, 'ema_volume': [-1] * 391, 'minute_volume': [-1] * 391 } col_ref.insert_one(new_tracker) else: cursor.close() connection.close() return {'error': 'Failed to add {}!'.format(ticker)} return {'success': 'Successfully added {}!'.format(ticker)}
def delete_custom_query(query): connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute('DELETE FROM Queries WHERE query = %s', [query]) except Exception as e: connection.rollback() return {'error': str(e)} connection.commit() cursor.close() connection.close() return {'success': query}
def update_news(ticker): ticker = ticker.upper() connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("CALL remove_news(%s);", (ticker, )) except Exception as e: return {'error': str(e)} util.add_news_articles(ticker, session, connection, cursor) connection.commit() cursor.close() connection.close() return {'success': 'Successfully updated news for {}!'.format(ticker)}
def remove_all_trackers(): connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("CALL remove_all_trackers();") except Exception as e: return {'error': str(e)} connection.commit() cursor.close() connection.close() col_ref = mongo_db['Live_Stock_Prices'] col_ref.delete_many({'ticker': {'$ne': 'SPY'}, 'usecase': {'$ne': 'af2'}}) return {'success': 'Successfully removed all trackers!'}
def remove_tracker(ticker): ticker = ticker.upper() connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("CALL remove_tracker(%s);", (ticker, )) except Exception as e: return {'error': str(e)} connection.commit() cursor.close() connection.close() col_ref = mongo_db['Live_Stock_Prices'] col_ref.delete_one({'ticker': ticker}) return {'success': 'Successfully removed {}!'.format(ticker)}
def get_correlations(timeframe): connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("SELECT * FROM get_correlation(%s);", (timeframe, )) except Exception as e: return {'error': str(e)} correlations = [{ 'sector': record[0], 'correlation': record[1] } for record in cursor] cursor.close() connection.close() return {'success': correlations}
def get_market_cap_grade_percent_change(timeframe): connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("SELECT * FROM get_percent_change(%s);", (timeframe, )) except Exception as e: return {'error': str(e)} market_cap_percent_change = [{ 'grade': record[0], 'mean_percent_change': record[1], 'std_dev_percent_change': record[2] } for record in cursor] cursor.close() connection.close() return {'success': market_cap_percent_change}
def get_all_custom_queries(): connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute('SELECT * FROM Queries') except Exception as e: return {'error': str(e)} queries = [record for record in cursor] ret = list() for query in queries: temp = {'name': query[0], 'query': query[1], 'postfix': query[2]} ret.append(temp) cursor.close() connection.close() return {'success': ret}
def update_notes(ticker, notes): connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute( ''' UPDATE Trackers SET notes = %s WHERE ticker = %s ''', (notes, ticker)) except Exception as e: return {'error': str(e)} connection.commit() cursor.close() connection.close() return {'success': 'Successfully updated note for {}!'.format(ticker)}
def daily_mongo_updates(): print('WE IN THE MONGO SCHEDULER') if util.is_market_holiday(util.get_current_date(), session): return connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute("SELECT ticker FROM Trackers") except Exception as e: return {'error': str(e)} tickers = [record[0] for record in cursor] cursor.close() connection.close() col_ref = mongo_db['Live_Stock_Prices'] # delete daily_prices older than a week and reset every minute_prices array and reset emas delete_before_timestamp = datetime.datetime.strptime( util.get_date_n_days_ago(7), '%Y-%m-%d') for tracker in col_ref.find(): if 'usecase' not in tracker: col_ref.update_one({'ticker': tracker['ticker']}, { '$pull': { 'daily_prices': { 'timestamp': { '$lt': delete_before_timestamp } } }, '$set': { 'minute_prices': [], 'minute_volume': [-1] * 391, 'prev_ema': tracker['ema_volume'] } }) else: col_ref.update_one({'usecase': 'af2'}, {'$set': { 'trackers_sent_today': [] }}) print('in scheduler MONGO YEET') for ticker in tickers: url = '{}/v2/aggs/ticker/{}/prev?apiKey={}'.format( POLYGON_BASE_URL, ticker, POLYGON_API_KEY) resp = util.polygon_get_request_multithreaded(url, session) if not resp or len(resp['results']) == 0: continue resp = resp['results'][0] timestamp = datetime.datetime.strptime( util.epoch_to_timestamp_format(resp['t']), '%Y-%m-%d %H:%M:%S') new_doc = { 'volume': resp['v'], 'open': resp['o'], 'close': resp['c'], 'high': resp['h'], 'low': resp['l'], 'timestamp': timestamp } col_ref.update({'ticker': ticker}, {'$push': { 'daily_prices': new_doc }}) print('UPDATED MONGO BOISSS') return {'success': 'SUCESSFULLY UPDATED ALL PRICE DATA FOR MONGO'}
def execute_all_custom_queries(): connection = util.connect_to_postgres() cursor = connection.cursor() try: cursor.execute('SELECT * FROM Queries') except Exception as e: return {'error': str(e)} queries = [{ 'name': query[0], 'query': query[1], 'postfix': query[2] } for query in cursor] try: cursor.execute('SELECT * FROM Trackers') except Exception as e: return {'error': str(e)} tickers = [record[0] for record in cursor] ret = list() for query in queries: ret.append({'query': query, 'tickers': list()}) for ticker in tickers: try: cursor.execute( ''' SELECT * FROM Daily_Prices WHERE ticker = %s ORDER BY timestamp DESC ''', [ticker]) except Exception as e: LOGGER.info(str({'query': query, 'ticker': ticker, 'error': e})) continue daily_prices = [record for record in cursor] try: cursor.execute( ''' SELECT * FROM Minute_Prices WHERE ticker = %s ORDER BY timestamp DESC ''', [ticker]) except Exception as e: LOGGER.info(str({'query': query, 'ticker': ticker, 'error': e})) continue minute_prices = [record for record in cursor] for i, query in enumerate(queries): result = util.evaluate_user_query(query['postfix'], daily_prices, minute_prices) if result == 1: ret[i]['tickers'].append(ticker) elif result == -1: LOGGER.info( str({ 'query': query, 'ticker': ticker, 'error': 'user query evaluation error' })) cursor.close() connection.close() return {'success': ret}
def get_ticker_data(ticker): connection = util.connect_to_postgres() cursor = connection.cursor() ticker = ticker.upper() add_tracker(ticker) try: cursor.execute( ''' SELECT * FROM Fundamentals WHERE ticker = %s ''', [ticker]) except Exception as e: return {'error': str(e)} fundamentals = cursor.fetchone() try: cursor.execute( ''' SELECT * FROM Daily_Prices WHERE ticker = %s ORDER BY timestamp DESC ''', [ticker]) except Exception as e: return {'error': str(e)} daily_prices = [record for record in cursor] try: cursor.execute( ''' SELECT * FROM Minute_Prices WHERE ticker = %s ORDER BY timestamp DESC ''', [ticker]) except Exception as e: return {'error': str(e)} minute_prices = [record for record in cursor] try: cursor.execute( ''' SELECT * FROM News WHERE ticker = %s ORDER BY timestamp DESC LIMIT 20 ''', [ticker]) except Exception as e: return {'error': str(e)} news = [list(record) for record in cursor] for article in news: temp_title = article[2].encode('latin1').decode( 'unicode-escape').encode('latin1').decode('utf-8') temp_desc = article[4].encode('latin1').decode( 'unicode-escape').encode('latin1').decode('utf-8') article[2] = temp_title[2:len(temp_title) - 1] article[4] = temp_desc[2:len(temp_desc) - 1] try: cursor.execute( ''' SELECT * FROM Tweets WHERE ticker = %s ORDER BY timestamp DESC LIMIT 50 ''', [ticker]) except Exception as e: return {'error': str(e)} tweets = [list(record) for record in cursor] for tweet in tweets: temp = tweet[1].encode('latin1').decode('unicode-escape').encode( 'latin1').decode('utf-8') tweet[1] = temp[2:len(temp) - 1] if not fundamentals: return {'error': 'No fundamental data found'} if not daily_prices: return {'error': 'No daily_prices data found'} if not minute_prices: return {'error': 'No minute_prices data found'} ticker_data = { 'ticker': ticker, 'prices': { '1d': list(), '5d': list(), '1m': list(), '3m': list(), '6m': list(), '1y': list() }, 'fundamentals': { 'ticker': fundamentals[0], 'company_name': fundamentals[1], 'industry': fundamentals[2], 'sector': fundamentals[3], 'market_cap': fundamentals[4], 'description': fundamentals[5] }, 'news': list(), 'tweets': list() } for article in news: temp = { 'timestamp': article[0], 'ticker': article[1], 'title': article[2], 'url': article[3], 'summary': article[4] } ticker_data['news'].append(temp) for tweet in tweets: temp = { 'url': tweet[0], 'tweet': tweet[1], 'ticker': tweet[2], 'timestamp': tweet[3], } ticker_data['tweets'].append(temp) # datetime5d = util.get_date_n_days_ago_datetime(7) datetime1m = util.get_date_n_days_ago_datetime(31) datetime3m = util.get_date_n_days_ago_datetime(62) datetime6m = util.get_date_n_days_ago_datetime(183) for day in daily_prices: temp = { 'timestamp': day[1], 'open': day[2], 'close': day[3], 'high': day[4], 'low': day[5] } if len(ticker_data['prices']['5d']) < 5: ticker_data['prices']['5d'].append(temp) if day[1] >= datetime1m: ticker_data['prices']['1m'].append(temp) if day[1] >= datetime3m: ticker_data['prices']['3m'].append(temp) if day[1] >= datetime6m: ticker_data['prices']['6m'].append(temp) ticker_data['prices']['1y'].append(temp) for minute in minute_prices: temp = { 'timestamp': minute[1], 'open': minute[2], 'close': minute[3], 'high': minute[4], 'low': minute[5] } ticker_data['prices']['1d'].append(temp) for k, v in ticker_data['prices'].items(): ticker_data['prices'][k] = v[::-1] cursor.close() connection.close() return ticker_data