def update_collection(name: str, tickers: list):
try:
conn = get_ps_conn()
cur = conn.cursor()
delete_sql = """
DELETE FROM collections
WHERE collection = %s
"""
cur.execute(delete_sql, (name, ))
conn.commit()
insert_sql = """
INSERT INTO collections
(ticker, collection, last_updated)
VALUES (%s, %s, NOW())
"""
cur.executemany(insert_sql, tuple(
(ticker, name) for ticker in tickers))
conn.commit()
except Exception as e:
logger.warning(e)
finally:
cur.close()
conn.close()
def write_ticker_collections(tickers, view_name):
try:
conn = get_ps_conn()
cur = conn.cursor()
# delete the existing collection
delete_sql = """
DELETE FROM collections
WHERE collection = %s
"""
cur.execute(delete_sql, (view_name, ))
conn.commit()
data = [(ticker, view_name) for ticker in tickers]
insert_sql = """
INSERT INTO collections
(ticker, collection, last_updated)
VALUES (%s, %s, NOW())
"""
cur.executemany(insert_sql, data)
conn.commit()
except Exception as e:
logger.warning(e)
finally:
conn.close()
def add_to_collections():
"""Add any stocks in stock_info to collections"""
conn = get_ps_conn()
cur = conn.cursor()
sql = """
INSERT INTO collections (
ticker,
collection,
last_updated
)
SELECT DISTINCT
si.ticker,
'Manually Requested' as collection,
NOW() as last_updated
FROM stock_info as si
WHERE si.ticker not in (
SELECT DISTINCT ticker FROM collections
);
"""
cur.execute(sql)
conn.commit()
conn.close()
def get_requests():
"""Get all actionable requests"""
conn = get_ps_conn()
cur = conn.cursor()
sql = """
SELECT DISTINCT
sr.ticker
FROM stock_requests as sr
LEFT JOIN stock_info as si
ON sr.ticker = si.ticker
WHERE si.ticker is NULL;
"""
cur.execute(sql)
res = cur.fetchall()
tickers = []
for row in res:
tickers.append(row[0])
cur.close()
conn.close()
return tickers
def write_to_db(tickers, collection):
failed_tickers = []
conn = get_ps_conn()
for ticker in tickers:
cur = conn.cursor()
print(ticker)
try:
stock = yfinance.Ticker(ticker)
# print(stock.info)
info = stock.info
# data = {}
# data["sector"] = info.get("sector", None)
# data["longBusinessSummary"] = info.get("longBusinessSummary", None)
# data["previousClose"] = info.get("previousClose", None)
# data["marketCap"] = info.get("marketCap", None)
# data["fiveYearAvgDividendYield?"] = info.get("fiveYearAvgDividendYield?", None)
# data["shortName"] = info.get("shortName", None)
# data["52WeekChange"] = info.get("52WeekChange", None)
# data["shortPercentOfFloat"] = info.get("shortPercentOfFloat", None)
# data["SandP52WeekChange"] = info.get("SandP52WeekChange", None)
# data["fiftyTwoWeekLow"] = info.get("fiftyTwoWeekLow", None)
# data["fiftyTwoWeekHigh"] = info.get("fiftyTwoWeekHigh", None)
# data["beta"] = info.get("beta", None)
# data["dividendRate"] = info.get("dividendRate", None)
# data["trailingPE"] = info.get("trailingPE", None)
# data["forwardPE"] = info.get("forwardPE", None)
# data["enterpriseToRevenue"] = info.get("enterpriseToRevenue", None)
# data["profitMargins"] = info.get("profitMargins", None)
# data["enterpriseToEbitda"] = info.get("enterpriseToEbitda", None)
# data["bookValue"] = info.get("bookValue", None)
# data["trailingEps"] = info.get("trailingEps", None)
# data["forwardEps"] = info.get("forwardEps", None)
# data["heldPercentInstitutions"] = info.get("heldPercentInstitutions", None)
# data["pegRatio"] = info.get("pegRatio", None)
sql = f"""
INSERT INTO public.stocks
(ticker, "date", collection, info)
VALUES(%s, NOW(), %s, %s);
"""
# currently dumping all data to the db
cur.execute(sql, (ticker, collection, json.dumps(info)))
conn.commit()
cur.close()
except Exception as e:
print(f"FAILED TO FETCH DATA: {ticker}")
print(e)
failed_tickers.append(ticker)
time.sleep(1)
conn.close()
return failed_tickers
def find_bad_updates():
ticker = ''
try:
conn = get_ps_conn()
cur = conn.cursor()
find_bad_updates_sql = """
select
sp.ticker,
case
when
sp.price > (si."fiftyTwoWeekHigh" * 1.5)
then 1
when
sp.price < (si."fiftyTwoWeekLow" * .5)
then 1
else 0
end high_price_variance
from stock_prices sp
inner join stock_info si
on sp.ticker = si.ticker
where (sp.price > (si."fiftyTwoWeekHigh" * 1.5)) or (sp.price < (si."fiftyTwoWeekLow" * .5))
"""
cur.execute(find_bad_updates_sql)
res = cur.fetchall()
for row in res:
ticker = row[0]
yticker = yfinance.Ticker(ticker)
info = yticker.get_info()
price = info.get('previousClose')
volume = info.get('volume')
print(ticker)
delete_sql = """
DELETE FROM stock_prices
WHERE ticker = %s;
"""
cur.execute(delete_sql, (ticker, ))
conn.commit()
sql = """
INSERT INTO stock_prices
(ticker, last_updated, price, volume)
VALUES(%s, NOW(), %s, %s);
"""
cur.execute(sql, (ticker, price, volume))
conn.commit()
except Exception as e:
print(e)
log_failure(ticker, 'stock_prices', 'Fix bad price', e)
finally:
conn.commit()
conn.close()
def create_view(sql):
try:
conn = get_ps_conn()
cur = conn.cursor()
cur.execute(sql)
conn.commit()
except Exception as e:
print(sql)
print(e)
finally:
conn.close()
def delete_request(ticker):
"""Delete the request from request table"""
conn = get_ps_conn()
cur = conn.cursor()
sql = """
DELETE FROM stock_requests sr
WHERE sr.ticker = %s
"""
cur.execute(sql, (ticker, ))
conn.commit()
conn.close()
def clear_bad_requests():
"""Clear bad requests"""
conn = get_ps_conn()
cur = conn.cursor()
sql = """
DELETE FROM stock_requests
WHERE ticker in (
SELECT DISTINCT
ticker
FROM stock_info
)
"""
cur.execute(sql)
conn.commit()
conn.close()
def get_price(ticker):
"""Get price for ticker for previous close"""
try:
conn = get_ps_conn()
cur = conn.cursor()
yticker = yfinance.Ticker(ticker)
price_df = yticker.history(period='1d')
price_df.reset_index(inplace=True)
price_close = float(price_df.iloc[0]['Close'])
volume = int(price_df.iloc[0]['Volume'])
sql = """
INSERT INTO stock_prices
(ticker, last_updated, price, volume)
VALUES(%s, NOW(), %s, %s);
"""
cur.execute(sql, (ticker, price_close, volume))
conn.commit()
# delete where last_updated != max last_updated
delete_sql = """
DELETE FROM stock_prices
WHERE ticker = %s
AND last_updated != (
SELECT max(last_updated)
FROM stock_prices
WHERE ticker = %s
)
"""
cur.execute(delete_sql, (ticker, ticker))
except Exception as e:
print(e)
log_failure(ticker, 'stock_prices', 'Update Prices', e)
finally:
conn.commit()
conn.close()
def write_changes(added, removed, view_name):
"""Write collection changes to DB"""
try:
conn = get_ps_conn()
cur = conn.cursor()
# write added
added_sql = f"""
INSERT INTO collection_changes
(ticker, collection, change, date)
VALUES (
%s, '{view_name}', 'Added', NOW()
)
"""
added_data = [(ticker, ) for ticker in added]
cur.executemany(added_sql, added_data)
conn.commit()
removed_sql = f"""
INSERT INTO collection_changes
(ticker, collection, change, date)
VALUES (
%s, '{view_name}', 'Removed', NOW()
)
"""
removed_data = [(ticker, ) for ticker in removed]
cur.executemany(removed_sql, removed_data)
conn.commit()
except Exception as e:
logger.warning(e)
finally:
conn.close()
def populate_tickers():
"""Populate index_tickers table"""
sql = """INSERT INTO index_tickers
(ticker, "name", active, last_updated)
VALUES (%s, %s, 1, NOW())
"""
conn = get_ps_conn()
cur = conn.cursor()
# delete all records from table
cur.execute("DELETE FROM index_tickers WHERE 1=1")
indices = [("^DJI", "Dow Jones Industrial Average"),
("^IXIC", "NASDAQ Composite"), ("^GSPC", "S&P 500"),
("^VIX", "CBOE Volatility Index")]
cur.executemany(sql, indices)
cur.close()
conn.commit()
conn.close()
def get_existing_tickers(view_name):
try:
conn = get_ps_conn()
cur = conn.cursor()
sql = f"""
SELECT
ticker
FROM
collections
WHERE
collection = '{view_name}'
"""
cur.execute(sql)
res = cur.fetchall()
tickers = {row[0] for row in res}
except Exception as e:
logger.warning(e)
tickers = set() # empty set
finally:
conn.close()
return tickers
def get_view_tickers(view_name, limit):
try:
conn = get_ps_conn()
cur = conn.cursor()
sql = f"""
SELECT
ticker
FROM
{view_name}
LIMIT {limit}
"""
cur.execute(sql)
res = cur.fetchall()
tickers = {row[0] for row in res}
except Exception as e:
logger.warning(e)
tickers = set() # empty set
finally:
conn.close()
return tickers
def get_all_tickers():
"""Get all tickers from database"""
try:
conn = get_ps_conn()
cur = conn.cursor()
sql = """
select distinct ticker from collections;
"""
cur.execute(sql)
results = cur.fetchall()
tickers = [res[0] for res in results]
except Exception as e:
print(e)
tickers = []
finally:
cur.close()
conn.close()
return tickers
def get_index_prices():
"""Get daily price and change"""
# get database conn and cursor
conn = get_ps_conn()
cur = conn.cursor()
# get the tickers
get_tickers_sql = """
SELECT ticker, name FROM index_tickers
"""
cur.execute(get_tickers_sql)
ticker_results = cur.fetchall()
print(ticker_results)
for res in ticker_results:
ticker = res[0]
name = res[1]
try:
yticker = yfinance.Ticker(ticker)
info = yticker.get_info()
# previous_close = info["regularMarketPreviousClose"]
closes = yticker.history(period="2d")
closes.reset_index(inplace=True)
previous_close = closes.iloc[0]['Close']
most_recent_close = closes.iloc[1]['Close']
dollar_change = most_recent_close - previous_close
decimal_change = (most_recent_close -
previous_close) / previous_close
delete_sql = """
DELETE FROM index_prices
WHERE ticker = %s
"""
cur.execute(delete_sql, (ticker, ))
conn.commit()
sql = """
INSERT INTO index_prices (
ticker,
name,
date,
previous_close,
close,
dollar_change,
decimal_change
) VALUES (%s, %s, NOW(), %s, %s, %s, %s)
"""
cur.execute(sql, (ticker, name, previous_close, most_recent_close,
dollar_change, decimal_change))
print(f"Successfully inserted data for {ticker}")
conn.commit()
except Exception as e:
print(e)
print(f"Processing {ticker} failed")
print("Moving to next ticker")
cur.close()
conn.close()
def get_info(ticker):
"""Get info for a ticker and write to db"""
try:
conn = get_ps_conn()
cur = conn.cursor()
yticker = yfinance.Ticker(ticker)
# get all the info
info = yticker.get_info()
# get all relevant info
shortName = info.get("shortName")
longName = info.get("longName")
sector = info.get("sector")
industry = info.get("industry")
longBusinessSummary = info.get("longBusinessSummary")
state = info.get("state")
country = info.get("country")
website = info.get("website")
logo_url = info.get("logo_url")
marketCap = info.get("marketCap")
beta = info.get("beta")
enterpriseValue = info.get("enterpriseValue")
netIncomeToCommon = info.get("netIncomeToCommon")
fiftyTwoWeekLow = info.get("fiftyTwoWeekLow")
fiftyTwoWeekHigh = info.get("fiftyTwoWeekHigh")
fiftyTwoWeekChange = info.get("52WeekChange")
fiftyDayAverage = info.get("fiftyDayAverage")
twoHundredDayAverage = info.get("twoHundredDayAverage")
dividendRate = info.get("dividendRate")
dividendYield = info.get("dividendYield")
lastDividendDate = info.get("lastDividendDate")
lastDividendValue = info.get("lastDividendValue")
floatShares = info.get("floatShares")
sharesOutstanding = info.get("sharesOutstanding")
sharesShort = info.get("sharesShort")
sharesShortPriorMonth = info.get("sharesShortPriorMonth")
shortPercentOfFloat = info.get("shortPercentOfFloat")
shortRatio = info.get("shortRatio")
trailingPE = info.get("trailingPE")
forwardPE = info.get("forwardPE")
trailingEps = info.get("trailingEps")
forwardEps = info.get("forwardEps")
bookValue = info.get("bookValue")
enterpriseToEbitda = info.get("enterpriseToEbitda")
enterpriseToRevenue = info.get("enterpriseToRevenue")
payoutRatio = info.get("payoutRatio")
priceToSalesTrailing12Months = info.get("priceToSalesTrailing12Months")
profitMargins = info.get("profitMargins")
priceToBook = info.get("priceToBook")
pegRatio = info.get("pegRatio")
earningsQuarterlyGrowth = info.get("earningsQuarterlyGrowth")
# additional dividend info
trailingAnnualDividendYield = info.get("trailingAnnualDividendYield")
trailingAnnualDividendRate = info.get("trailingAnnualDividendRate")
exDividendDate = info.get("exDividendDate")
# handle last lastDividendDate
if lastDividendDate:
timestamp = datetime.datetime.fromtimestamp(lastDividendDate)
lastDividendDate = timestamp.strftime('%Y-%m-%d')
# handle exDividendDate
if exDividendDate:
timestamp = datetime.datetime.fromtimestamp(exDividendDate)
exDividendDate = timestamp.strftime('%Y-%m-%d')
sql = """
INSERT INTO stock_info
(
last_updated,
ticker,
"shortName",
"longName",
sector,
industry,
"longBusinessSummary",
state,
country,
website,
logo_url,
"marketCap",
beta,
"enterpriseValue",
"netIncomeToCommon",
"fiftyTwoWeekLow",
"fiftyTwoWeekHigh",
"fiftyTwoWeekChange",
"fiftyDayAverage",
"twoHundredDayAverage",
"dividendRate",
"dividendYield",
"lastDividendDate",
"lastDividendValue",
"floatShares",
"sharesOutstanding",
"sharesShort",
"sharesShortPriorMonth",
"shortPercentOfFloat",
"shortRatio",
"trailingPE",
"forwardPE",
"trailingEps",
"forwardEps",
"bookValue",
"enterpriseToEbitda",
"enterpriseToRevenue",
"payoutRatio",
"priceToSalesTrailing12Months",
"profitMargins",
"priceToBook",
"pegRatio",
"earningsQuarterlyGrowth",
"trailingAnnualDividendYield",
"trailingAnnualDividendRate",
"exDividendDate"
)
VALUES(NOW(), %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);
"""
cur.execute(
sql,
(ticker, shortName, longName, sector, industry,
longBusinessSummary, state, country, website, logo_url, marketCap,
beta, enterpriseValue, netIncomeToCommon, fiftyTwoWeekLow,
fiftyTwoWeekHigh, fiftyTwoWeekChange, fiftyDayAverage,
twoHundredDayAverage, dividendRate, dividendYield,
lastDividendDate, lastDividendValue, floatShares,
sharesOutstanding, sharesShort, sharesShortPriorMonth,
shortPercentOfFloat, shortRatio, trailingPE, forwardPE,
trailingEps, forwardEps, bookValue, enterpriseToEbitda,
enterpriseToRevenue, payoutRatio, priceToSalesTrailing12Months,
profitMargins, priceToBook, pegRatio, earningsQuarterlyGrowth,
trailingAnnualDividendYield, trailingAnnualDividendRate,
exDividendDate))
conn.commit()
delete_sql = """
DELETE FROM stock_info
WHERE ticker = %s
AND last_updated != (
SELECT max(last_updated)
FROM stock_info
WHERE ticker = %s
)
"""
cur.execute(delete_sql, (ticker, ticker))
except Exception as e:
print(e)
log_failure(ticker, 'stock_info', 'Update Info', e)
finally:
conn.commit()
conn.close()