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