def performInitialCalculations():
"""
Generates the *Standard Deviation* value for each stock before storing it in the *Calculations Registry* table.
"""
for symbol in nameList:
codes = pullDataRegistry(symbol, "Low", "High")
values = []
for k in codes:
values.append([float(k[0]), float(k[1])])
standardDeviation = np.std(values)
perm = permFinder(symbol)
dbcursor.execute(
"""
INSERT OR REPLACE INTO CalculationsRegistry
(
Perm_No,
StandardDeviation
)
VALUES (?,?)""",
(
int(perm),
standardDeviation,
),
)
dbconnector.commit()
def initializeCalculationsRegistry():
"""
Here we initialize the *Calculations Registry* table where we store data on each stock's overall (not daily) values.
This function does not take any input or return any values, it just initilizes a table in the SQL database
"""
dbcursor.execute("""DROP TABLE IF EXISTS CalculationsRegistry""")
dbconnector.commit()
dbcursor.execute("""
CREATE TABLE IF NOT EXISTS CalculationsRegistry(
Perm_No INTEGER PRIMARY KEY,
StandardDeviation DOUBLE,
ValueScore DOUBLE,
polyregScore DOUBLE,
MemoryScore DOUBLE,
ranForScore Double,
BestModel STRING,
PolyProfit DOUBLE,
MemProfit DOUBLE,
RanProfit DOUBLE,
YearProfit DOUBLE
);
""")
dbconnector.commit()
def updateSNPName():
"""
This function does is change the name of the SNP 500 from ^GSPC to SNP for easy of use during the program.
"""
query = """
UPDATE stockRegistry
SET Symbol = "SNP"
WHERE Symbol = "^GSPC"
"""
dbcursor.execute(query)
dbconnector.commit()
def pullStockRegistry(ticker, *args):
"""
Used to query the *Stock Registry* table for connections between stock calculations table, data registry table and other information.
Args:
ticker (str): Stock ticker value to be queried
args (list of strings): Desired values queried from *Stock Registry*.
:return:
cursor object: Returns a sql cursor object that needs to be parsed for results.
"""
parameters = []
# Build the Query list - max of ten, anything over is set to blank
for k in args:
parameters.append(f"{k}, ")
parameters = "".join(parameters)[:-2]
query = f"""SELECT {parameters}
FROM stockRegistry
WHERE Symbol = "{ticker}"
"""
data = dbcursor.execute(query)
return data
def pullFullEstimates():
"""
Used with *Historical Markings* this function pulls a full list of all estimates for all stocks in the *Data Registry* table.
:return:
data (list of dict/key pairs with ticker (str) and df (dataframe): List of dataframes containing the *Close*, *Date*, *polyPred*, *memPred*, *ranPred* values for each stock
"""
results = {}
for stonk in nameList:
query = f"""SELECT DR.Close, DR.Date, DR.polyRegPred, DR.memPred, DR.ranForPred
FROM DataRegistry DR
INNER JOIN IDRegistry ID
ON DR.Perm_No = ID.Perm_No
INNER JOIN stockRegistry SR
ON ID.Stock_ID = SR.Stock_ID
WHERE Symbol = ?
ORDER BY Date DESC LIMIT {maxHistory}
"""
data = dbcursor.execute(query, (stonk,))
preDfData = []
for k in data:
preDfData.append(k)
df = pd.DataFrame(
data=preDfData, columns=["Close", "Date", "polyPred", "memPred", "ranPred"]
)
results.update({stonk: df})
return results
def pullCalculationsRegistry(*args):
"""
Used to query the *Calculations Registry* table for overview information on all stocks.
Does not distinguish between stock tickers
Args:
args (list of strings): Desired values queried from *Calcuations Registry*.
:return:
cursor object: Returns a sql cursor object that needs to be parsed for results.
"""
parameters = []
for k in args:
parameters.append(f"CR.{k}, ")
parameters = "".join(parameters)[:-2]
query = f"""SELECT SR.Symbol, SR.Security, {parameters}
FROM CalculationsRegistry CR
INNER JOIN IDRegistry ID
ON CR.Perm_No = ID.Perm_No
INNER JOIN stockRegistry SR
ON ID.Stock_ID = SR.Stock_ID
"""
data = dbcursor.execute(query)
return data
def pullDataRegistry(ticker, *args):
"""
Very used function that takes the ticker values and args to query the *Data Registry* table
Args:
ticker (type) : The stock ticker value to be queried
*args (str) : Fields in the db that the caller wants pulled
:return:
cursor object: Returns a sql cursor object that needs to be parsed for results.
"""
parameters = []
if len(args) == 1:
if isinstance(args, tuple):
parameters = ", ".join(list(args)[0])
else:
for k in args:
parameters.append(f"{k}, ")
parameters = "".join(parameters)[:-2]
try: # if the value has dates
query = f"""SELECT {parameters}
FROM DataRegistry DR
INNER JOIN IDRegistry ID
ON DR.Perm_No = ID.Perm_No
INNER JOIN stockRegistry SR
ON ID.Stock_ID = SR.Stock_ID
WHERE Symbol = ?
ORDER BY Date DESC LIMIT {maxSQLPull}
"""
data = dbcursor.execute(query, (ticker,))
return data
except BaseException: # if the value is not based on dates
query = f"""SELECT {parameters}
FROM DataRegistry DR
INNER JOIN IDRegistry ID
ON DR.Perm_No = ID.Perm_No
INNER JOIN stockRegistry SR
ON ID.Stock_ID = SR.Stock_ID
WHERE Symbol = ?
"""
data = dbcursor.execute(query, (ticker,))
return data
def updateCalculationsRegistry(ticker, column, value):
"""
Updates the *Calcuations Registry* table based on the Args listed below.
Args:
ticker (str): Ticker value to be updated
column (str): which column needs to be updated
value (varies): the value that the ticker/date/column combo will be changed to. Can be str, float or int
"""
perm = permFinder(ticker)
query = f"""
UPDATE CalculationsRegistry
SET {column} = {value}
WHERE Perm_No = {perm}
"""
dbcursor.execute(query)
dbconnector.commit()
def nameListGen():
"""
Summary:
Generates a list used by many of the functions that contains all of the stock ticker values currently loaded into the SQL database.
:results:
nameList (list): list is stored in *paths* module and is updated to contain all current stock tickers.
"""
names = dbcursor.execute("SELECT Symbol FROM stockRegistry")
for k in names:
nameList.append(k[0])
def initializeStageTable():
"""
Here we initialize the *Stage* table that is used to load in the data before processing.
This function does not take any input or return any values, it just initilizes a table in the SQL database
"""
dbcursor.execute("""DROP TABLE IF EXISTS StageTable""")
dbconnector.commit()
dbcursor.execute("""
CREATE TABLE IF NOT EXISTS StageTable(
Data_ID INTEGER PRIMARY KEY,
Perm_No INTEGER,
Date DATE NOT NULL,
Open DOUBLE,
High DOUBLE,
Low DOUBLE,
Close DOUBLE,
Volume DOUBLE,
Dividends DOUBLE,
Stock_Splits DOUBLE,
SAR DOUBLE,
RSI DOUBLE,
CCI DOUBLE,
MACDHist DOUBLE,
BBUpperBand DOUBLE,
BBMiddleBand DOUBLE,
BBLowerBand DOUBLE,
EMA DOUBLE,
Chaikin DOUBLE,
StochK DOUBLE,
StochD DOUBLE,
WILLR DOUBLE,
memPred DOUBLE,
polyreg DOUBLE,
ranForPred DOUBLE
);
""")
dbconnector.commit()
def initializeExchangeRegistry():
"""
Here we initialize the *Exchange Registry* table that matches each stock with the exchange they are traded on.
This function does not take any input or return any values, it just initilizes a table in the SQL database
"""
dbcursor.execute("DROP TABLE IF EXISTS ExchangeRegistry")
dbconnector.commit()
dbcursor.execute("""
CREATE TABLE IF NOT EXISTS exchangeRegistry(
Exchange_ID INTEGER PRIMARY KEY,
Exchange_Name TEXT NOT NULL,
Data_Source TEXT NOT NULL);
""")
# Insert into the Exchange Registry the Information of each Exchange
dbcursor.execute("""
INSERT INTO exchangeRegistry(Exchange_Name, Data_Source)
VALUES ('S&P500', 'Yahoo Finance');
""")
dbconnector.commit()
def initializeIDRegistry():
"""
Here we initialize the *ID Registry* table that matches each stock with their perm node number and stock exhange number
This function does not take any input or return any values, it just initilizes a table in the SQL database
"""
dbcursor.execute("DROP TABLE IF EXISTS IDRegistry")
dbconnector.commit()
dbcursor.execute("""
CREATE TABLE IF NOT EXISTS IDRegistry(
Perm_No INTEGER PRIMARY KEY,
Exchange_ID INTEGER NOT NULL,
Stock_ID INTEGER NOT NULL,
FOREIGN KEY (Stock_ID)
REFERENCES stockRegistry (Stock_ID),
FOREIGN KEY (EXCHANGE_ID)
REFERENCES exchangeRegistry (Exchange_ID)
);
""")
dbconnector.commit()
def pullRankedTopStocksDataCalculationRegistry():
"""
Used with the flask code, it pulls the top five stock ticker values based on YearProfit value found in the *Calculations Registry* table.
:return:
cursor object: Returns a sql cursor object with five ticker values that needs to be parsed for results.
"""
query = """SELECT SR.Symbol, CR.YearProfit
FROM StockRegistry SR
INNER JOIN IDRegistry ID
ON ID.Stock_ID = SR.Stock_ID
INNER JOIN CalculationsRegistry CR
ON ID.Perm_No = CR.Perm_No
ORDER BY CR.ValueScore DESC
"""
data = dbcursor.execute(query)
return data
def initializeDataRegistry():
"""
Here we initialize the *Data Registry* table that stores the daily values for stocks and indicators.
This function does not take any input or return any values, it just initilizes a table in the SQL database
"""
dbcursor.execute("""DROP TABLE IF EXISTS DataRegistry""")
dbconnector.commit()
dbcursor.execute("""
CREATE TABLE IF NOT EXISTS DataRegistry (
Data_ID INTEGER PRIMARY KEY,
Perm_No INTEGER,
Date DATE NOT NULL,
Open DOUBLE,
High DOUBLE,
Low DOUBLE,
Close DOUBLE,
Volume DOUBLE,
Dividends DOUBLE,
Stock_Splits DOUBLE,
SAR DOUBLE,
RSI DOUBLE,
CCI DOUBLE,
MACDHist DOUBLE,
BBUpperBand DOUBLE,
BBMiddleBand DOUBLE,
BBLowerBand DOUBLE,
EMA DOUBLE,
Chaikin DOUBLE,
StochK DOUBLE,
StochD DOUBLE,
WILLR DOUBLE,
memPred DOUBLE,
polyregPred DOUBLE,
ranForPred DOUBLE,
FOREIGN KEY (Perm_No)
REFERENCES IDRegistry (Perm_No)
);
""")
dbcursor.execute("""
CREATE UNIQUE INDEX nix_permno_date ON DataRegistry (Perm_No, Date)
""")
dbconnector.commit()
def permFinder(ticker):
"""
Used internally for other sql queries, this function takes the *ticker* value provided and returns the *perm node* number.
Perm node is used to speed up queries but is not very user friendly, so we have this code to bridge the gap.
Args:
ticker (str): ticker value to referenced for perm node number
:return:
perm (int): perm node number that references the stock ticker to be queried.
"""
query = """
Select Perm_No
FROM IDRegistry ID
INNER JOIN stockRegistry SR
ON ID.Stock_ID = SR.Stock_ID
WHERE Symbol = ?"""
perm = dbcursor.execute(query, (ticker,)).fetchall()
perm = perm[0][0]
return perm
def initializeStockRegistry():
"""
Here we initialize the *Stock Registry* table that matches each stock with the their exhange, information and data tables.
This function does not take any input or return any values, it just initilizes a table in the SQL database
"""
df = pd.read_csv(filePaths["namesFile"])
df.reset_index()
df = df.rename(columns={"GICS Sector": "Sector"})
dbcursor.execute("DROP TABLE IF EXISTS stockRegistry")
dbconnector.commit()
dbcursor.execute("""
CREATE TABLE IF NOT EXISTS stockRegistry(
Stock_ID INTEGER PRIMARY KEY,
Symbol nvarchar(50),
Security nvarchar(50),
Sector nvarchar(50));
""")
# Populates StockRegistry table from our list of Stocks in the csv file
for row in df.itertuples():
dbcursor.execute(
"""
INSERT INTO StockRegistry
(
Symbol,
Security,
Sector
)
VALUES (?,?,?)
""",
(row.Symbol, row.Security, row.Sector),
)
dbconnector.commit()
updateSNPName()
def populateIDRegistry():
"""
This function does is generate the connections between the ID registry, exchange registry and stock registry tables.
"""
dbcursor.execute("""SELECT * FROM exchangeRegistry""")
exchangeRecords = dbcursor.fetchall()
for row in exchangeRecords:
exchangeIdTemp = row[0]
dbcursor.execute("""SELECT * FROM stockRegistry""")
stockRecords = dbcursor.fetchall()
for stock in stockRecords:
stockIdTemp = stock[0]
dbcursor.execute(
"""
INSERT INTO IDRegistry(Exchange_ID, Stock_ID)
VALUES (?,?)""",
(exchangeIdTemp, stockIdTemp),
)
dbconnector.commit()
def populateStageTable():
"""
Here we populate the *Stage* table that we used to hold the data before processing them into the correct tables.
It uses the *Name List Gen* function to get a full list of the stock IDs that are used and then uses the *Pull Historical Data* function to pull from Yahoo before loading in the data.
Values Loaded from Yahoo Finance:
- *Date* : Date for the row
- *Open* : Opening price for the stock that day
- *High* : Stocks highest value during that day of trading
- *Low* : Stocks lowest value during that day of trading
- *Close* : The closing price for the stock during that day of trading
- *Volume* : Amount of stocks traded during that day of trading
- *Dividends* : Dividends payed our during the day of trading
- *Stock Splits* : If the stock split or merged during trading hours, the value of the plit is listed here
Values Processed Here:
- *SAR* : Daily Parabolic SAR value for the stock for the given date
- *RSI* : Daily Relative Strength Index value for the stock for the given date
- *CCI* : Daily Commodity Channel Index value for the stock for the given date
- *MACDHist* : Daily Moving Average Convergence Divergence value for the stock for the given date
- *BBUpperBand* : Daily Upper Bollinger Band's value for the stock for the given date
- *BBMiddleBand* : Daily Middle Bollinger Band's value for the stock for the given date
- *BBLowerBand* : Daily Lower Bollinger Band's value for the stock for the given date
- *EMA* : Daily Exponential Moving Average value for the stock for the given date
- *Chaikin* : Daily Chaikin Oscillator value for the stock for the given date
- *StockK* : Daily Stochastics K value for the stock for the given date
- *StockD* : Daily Stochastics D value for the stock for the given date
- *WILLR* : Daily Williams Percent Range value for the stock for the given date
"""
nameListGen()
for symbol in nameList:
data = pullHistoricalData(symbol)
processedData = []
for idx, k in enumerate(data):
hold = [
str(k["Date"]),
str(k["Open"]),
str(k["High"]),
str(k["Low"]),
str(k["Close"]),
str(k["Volume"]),
str(k["Dividends"]),
str(k["Stock Splits"]),
]
processedData.append(hold)
df = pd.DataFrame(
processedData,
columns=[
"Date",
"Open",
"High",
"Low",
"Close",
"Volume",
"Dividends",
"Stock Split",
],
)
df["SAR"] = talib.SAR(df["High"],
df["Low"],
acceleration=0.02,
maximum=0.2)
df["RSI"] = talib.RSI(df["Close"], RSILookbackTime)
df["CCI"] = talib.CCI(df["High"], df["Low"], df["Close"])
_, _, df["macdhist"] = talib.MACD(df["Close"],
fastperiod=12,
slowperiod=26,
signalperiod=9)
df["upperband"], df["middleband"], df["lowerband"] = talib.BBANDS(
df["Close"], timeperiod=5, nbdevup=2, nbdevdn=2, matype=0)
df["EMA"] = talib.EMA(df["Close"], timeperiod=30)
df["Chaikin"] = talib.ADOSC(
df["High"],
df["Low"],
df["Close"],
df["Volume"],
fastperiod=3,
slowperiod=10,
)
df["stochK"], df["stochD"] = talib.STOCH(
df["High"],
df["Low"],
df["Close"],
fastk_period=14,
slowk_period=3,
slowk_matype=0,
slowd_period=3,
slowd_matype=0,
)
df["WILLR"] = talib.WILLR(df["High"],
df["Low"],
df["Close"],
timeperiod=14)
perm = permFinder(symbol)
for idx in range(len(df)):
values = (
perm,
df.at[idx, "Date"],
df.at[idx, "Open"],
df.at[idx, "High"],
df.at[idx, "Low"],
df.at[idx, "Close"],
df.at[idx, "Volume"],
df.at[idx, "Dividends"],
df.at[idx, "Stock Split"],
df.at[idx, "SAR"],
df.at[idx, "RSI"],
df.at[idx, "CCI"],
df.at[idx, "macdhist"],
df.at[idx, "upperband"],
df.at[idx, "middleband"],
df.at[idx, "lowerband"],
df.at[idx, "EMA"],
df.at[idx, "Chaikin"],
df.at[idx, "stochK"],
df.at[idx, "stochD"],
df.at[idx, "WILLR"],
)
dbcursor.execute(
"""
INSERT INTO StageTable
(
Perm_No,
Date,
Open,
High,
Low,
Close,
Volume,
Dividends,
Stock_Splits,
SAR,
RSI,
CCI,
MACDHist,
BBUpperBand,
BBMiddleBand,
BBLowerBand,
EMA,
Chaikin,
StochK,
StochD,
WILLR
)
VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)""",
values,
)
dbconnector.commit()
def loadDataRegistry():
"""
Moves data from the *Stage* table to the *Data Registry* table
"""
dbcursor.execute("""
INSERT OR REPLACE INTO DataRegistry
(
Data_ID,
Perm_No,
Date,
Open,
High,
Low,
Close,
Volume,
Dividends,
Stock_Splits,
SAR,
RSI,
CCI,
MACDHist,
BBUpperBand,
BBMiddleBand,
BBLowerBand,
EMA,
Chaikin,
StochK,
StochD,
WILLR
)
SELECT
ST.Data_ID,
ST.Perm_No,
ST.Date,
ST.Open,
ST.High,
ST.Low,
ST.Close,
ST.Volume,
ST.Dividends,
ST.Stock_Splits,
ST.SAR,
ST.RSI,
ST.CCI,
ST.MACDHist,
ST.BBUpperBand,
ST.BBMiddleBand,
ST.BBLowerBand,
ST.EMA,
ST.Chaikin,
ST.StochK,
ST.StochD,
ST.WILLR
FROM StageTable ST
LEFT JOIN DataRegistry DR
ON DR.Perm_No = ST.Perm_No
AND DR.Date = ST.Date
WHERE DR.Data_ID IS NULL
""")
dbcursor.execute("""
DELETE FROM StageTable""")
dbconnector.commit()