def insertUser(username, password, fname, lname, email, subscriber):
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
insertUsers = (
"INSERT INTO USERS"
"(username, password_salt, password_hash, fname, lname, email, subscriber) "
"VALUES (%s, %s, %s, %s, %s, %s, %s)")
salt = int(binascii.hexlify(os.urandom(3)), 16)
guid = uuid.uuid4()
# names=["username", "password", "fname", "lname", "email", "subscriber"])
user_data = [
username,
str(salt),
encrypt_string(str(salt) + password), fname, lname, email, subscriber
]
cursor.execute(insertUsers, user_data)
print("INSERT INTO USERS(variables) VALUES " + str(user_data))
conn.commit()
cursor.close()
conn.close()
def deleteAcc():
login_id = session['user_id']
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb',
autocommit=True)
cursor = conn.cursor()
# find portfolios of user to delete before removing account
portfolio_ids = session['portfolio_id']
count = 0
for x in portfolio_ids:
deletePortfolios = ("DELETE FROM portfolio WHERE portfolio_id = " +
str(x))
cursor.execute(deletePortfolios)
print("Deleting Portfolio: " + session['portfolio_name'][count])
count += 1
deleteAccount = ("DELETE FROM users WHERE login_id = " + str(login_id))
cursor.execute(deleteAccount)
print("Deleting Account: " + session['username'])
conn.commit()
cursor.close()
conn.close()
return redirect('logout2')
def seeUsersPortfoliosStocks():
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectQuery = (
"Select username, portfolio_name, stock_name "
"From Users, has_portfolio, portfolio, has_stock, SP500 "
"where Users.login_id = has_portfolio.login_id and has_portfolio.portfolio_id = "
"portfolio.portfolio_id and portfolio.portfolio_id = has_stock.portfolio_id and "
"has_stock.ticker = SP500.ticker "
"Order By username")
cursor.execute(selectQuery)
records = cursor.fetchall()
user = []
port = []
ticker = []
for row in records:
user.append(row[0])
port.append(row[1])
ticker.append(row[2])
return ([user, port, ticker]) # returns columns for a table
cursor.close()
conn.close()
def importUsers(users):
conn = sqlconnection.connect(user='******', password='', host='127.0.0.1', database='financedb')
cursor = conn.cursor()
insertUsers = ("INSERT INTO USERS"
"(username, password_salt, password_hash, fname, lname, email, subscriber) "
"VALUES (%s, %s, %s, %s, %s, %s, %s)")
for x in range(users['password'].count()):
salt = int(binascii.hexlify(os.urandom(3)),16)
print(users['password'].loc[x])
guid = uuid.uuid4()
sub = str2boolint(users['subscriber'].loc[x])
print(guid)
#names=["username", "password", "fname", "lname", "email", "subscriber"])
user_data = [users['username'].loc[x], str(salt), encrypt_string(str(salt)+users['password'].loc[x]), users['fname'].loc[x], users['lname'].loc[x],
users['email'].loc[x], sub]
cursor.execute(insertUsers, user_data)
print("INSERT INTO USERS(variables) VALUES " + str(user_data))
conn.commit()
cursor.close()
conn.close()
def delStock():
session['currentStock'] = request.form['ticker']
ticker = session['currentStock']
portid = session['currentPortid']
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectTickerQuery = ("Select ticker "
"From has_stock "
"Where portfolio_id = " + str(portid))
cursor.execute(selectTickerQuery)
tickers = []
for x in cursor:
tickers.append(x[0])
if ticker not in tickers:
flash(u'Stock is not in Portfolio -- Cannot delete', 'deleteStock')
else:
deleteStock = "DELETE FROM has_stock WHERE portfolio_id = " + str(
portid) + " and ticker = " + "'" + str(ticker) + "'"
cursor.execute(deleteStock)
print(deleteStock)
tickers.remove(ticker)
conn.commit()
cursor.close()
conn.close()
return render_template('viewPort.html', tickers=tickers)
def mostPopularStock():
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
insertHas_Stock = (
"select has_stock.ticker, stock_name, count(*) from has_stock, SP500 "
"where has_stock.ticker = SP500.ticker "
"group by has_stock.ticker "
"order by count(*) desc "
"limit 1")
cursor.execute(insertHas_Stock)
x = cursor.fetchone()
moPop = []
line1 = "SELECTING MOST POPULAR STOCK IN ALL PORTFOLIOS AND ITS FREQUENCY: "
line2 = "(" + str(x[0]) + ") " + str(x[1]) + " with a count of " + str(
x[2])
moPop.append(line1)
moPop.append(line2)
return (moPop)
conn.commit()
cursor.close()
conn.close()
def viewPort():
port = request.form['portfolio']
count = 0
for x in session['portfolio_name']:
if x == port:
break
count += 1
portid = session['portfolio_id'][count]
session['currentPortid'] = portid
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectTickerQuery = ("Select ticker "
"From has_stock "
"Where portfolio_id = " + str(portid))
cursor.execute(selectTickerQuery)
tickers = []
for x in cursor:
tickers.append(x[0])
conn.commit()
cursor.close()
conn.close()
return render_template('viewPort.html', tickers=tickers)
def updateSubscription():
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
if session['subscriber'] == 1:
updateQuery = ("UPDATE users "
"SET subscriber = 0 "
"WHERE login_id = '" + str(session['user_id']) + "'")
cursor.execute(updateQuery)
print(updateQuery)
session['subscriber'] = 0
else:
updateQuery = ("UPDATE users "
"SET subscriber = 1 "
"WHERE login_id = '" + str(session['user_id']) + "'")
cursor.execute(updateQuery)
print(updateQuery)
session['subscriber'] = 1
flash(u'Thank you for subscribing!!', 'sub')
conn.commit()
cursor.close()
conn.close()
return render_template('viewAcc.html')
def setRecTickerPort():
if (session['subscriber'] == 0):
portid = session['currentPortid']
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectTickerQuery = ("Select ticker "
"From has_stock "
"Where portfolio_id = " + str(portid))
cursor.execute(selectTickerQuery)
tickers = []
for x in cursor:
tickers.append(x[0])
conn.commit()
cursor.close()
conn.close()
flash(u'You must be a subscriber to view a trading strategy', 'subs')
return render_template('viewPort.html', tickers=tickers)
ticker = session['currentStock']
return getRecData(ticker)
def insertStockData(tickers):
for x in tickers:
conn = sqlconnection.connect(user='******', password='', host='127.0.0.1', database='financedb')
cursor = conn.cursor()
insertStockData = ("INSERT INTO " + x + " "
"(eod, open_price, high_price, low_price, close_price, adjclose, volume, ticker) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")
try:
data = pd.DataFrame(si.get_data(x, index_as_date=False))
for y in range(data['ticker'].count()):
print(y)
stock_data = [data['date'].loc[y], float(data['open'].loc[y]), float(data['high'].loc[y]), float(data['low'].loc[y]), float(data['close'].loc[y]), float(data['adjclose'].loc[y]), float(data['volume'].loc[y]), data['ticker'].loc[y]]
cursor.execute(insertStockData, stock_data)
print("INSERT INTO " + x + " (eod, open_price, high_price, low_price, close_price, adjclose, volume, ticker) VALUES " + str(stock_data))
except (KeyError, ValueError):
print("No Stock Data Available For " + x)
pass
print("Done Inserting Data For " + x)
conn.commit()
cursor.close()
conn.close()
def createStockTables(tickers):
conn = sqlconnection.connect(user='******', password='', host='127.0.0.1', database='financedb')
cursor = conn.cursor()
for x in tickers:
createTable = ("Create Table " + x + "( "
"eod date not null, "
"open_price float, "
"high_price float, "
"low_price float, "
"close_price float, "
"adjclose float, "
"volume float, "
"ticker varchar(5) not null, "
"Primary Key(ticker, eod), "
"Foreign Key(ticker) references SP500(ticker));")
try:
print("Creating table: ", x)
cursor.execute(createTable)
except sqlconnection.Error as er:
if er.errno == errorcode.ER_TABLE_EXISTS_ERROR:
print("Table already exists")
else:
print(er.msg)
else:
print("Table was successfully created")
cursor.close()
conn.close()
def createPort():
portfolio_name = request.form['name']
loginid = session['user_id']
if (portfolio_name in session['portfolio_name']):
flash(u'Portfolio name already exists -- Cannot create portfolio',
'create')
return render_template('viewAcc.html')
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb',
autocommit=True)
cursor = conn.cursor()
selectPortfolioid = ("SELECT Max(portfolio_id) + 1 " "FROM portfolio")
cursor.execute(selectPortfolioid)
for x in cursor:
portfolio_id = x[0]
insertPortfolio = ("INSERT INTO portfolio "
"(portfolio_id, portfolio_name) "
"VALUES (%s, %s)")
insertData = [portfolio_id, portfolio_name]
cursor.execute(insertPortfolio, insertData)
insertStatement = "INSERT INTO portfolio (portfolio_id, portfolio_name) VALUES " + "(" + str(
portfolio_id) + ", " + str(portfolio_name) + ")"
print(insertStatement)
inserthasPortfolio = ("INSERT INTO has_portfolio "
"(login_id, portfolio_id) "
"VALUES (%s, %s)")
insertData = [loginid, portfolio_id]
cursor.execute(inserthasPortfolio, insertData)
insertStatement = "INSERT INTO has_portfolio (login_id, portfolio_id) VALUES " + str(
insertData)
print(insertStatement)
idslist = session['portfolio_id']
namelist = session['portfolio_name']
idslist.append(portfolio_id)
namelist.append(portfolio_name)
session['portfolio_id'] = idslist
session['portfolio_name'] = namelist
conn.commit()
cursor.close()
conn.close()
return render_template('viewAcc.html')
def getStockData(ticker, sdate, edate):
style.use('ggplot')
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
if (sdate == "" and edate == ""):
selectDateQuery = ("Select * " "From " + ticker)
elif (sdate != "" and edate == ""):
selectDateQuery = ("Select * "
"From " + ticker + " "
"Where eod > " + sdate)
elif (sdate == "" and edate != ""):
selectDateQuery = ("Select * "
"From " + ticker + " "
"Where eod < " + edate)
elif (sdate != "" and edate != ""):
selectDateQuery = ("Select * "
"From " + ticker + " "
"Where eod > " + sdate + " and eod < " + edate)
cursor.execute(selectDateQuery)
data = pd.DataFrame(columns=[
'date', 'open', 'high', 'low', 'close', 'adjclose', 'volume', 'ticker'
])
count = 0
for x in cursor:
data.loc[count] = [x[0], x[1], x[2], x[3], x[4], x[5], x[6], x[7]]
count += 1
cursor.close()
conn.close()
data = data.set_index('date')
data = data[['adjclose']]
fig = plt.figure()
plt.plot(data['adjclose'])
plt.legend(loc=4)
plt.title(ticker)
plt.xlabel('Date')
plt.ylabel('Price')
fig.savefig(
'C:/Users/wrenp/PycharmProjects/FlaskPractice/static/displayFigure.png'
) # to save picture of plot
return redirect('/viewStock2.html')
def insertSP500(data):
conn = sqlconnection.connect(user='******', password='', host='127.0.0.1', database='financedb')
cursor = conn.cursor()
insertSP500 = ("INSERT INTO SP500"
"(ticker, stock_name, sector) "
"VALUES (%s, %s, %s)")
for x in range(data['ticker'].count()):
stock_data = [data['ticker'].loc[x], data['stock_name'].loc[x], data['sector'].loc[x]]
cursor.execute(insertSP500, stock_data)
print("INSERT INTO SP500(ticker, stock_name, sector) VALUES " + str(stock_data))
conn.commit()
cursor.close()
conn.close()
def importHas_Stock(has_stock):
conn = sqlconnection.connect(user='******', password='', host='127.0.0.1', database='financedb')
cursor = conn.cursor()
insertHas_Stock = ("INSERT INTO HAS_STOCK"
"(portfolio_id, ticker) "
"VALUES (%s, %s)")
for x in range(has_stock['portfolio_id'].count()):
has_stock_data = [int(has_stock['portfolio_id'].loc[x]), has_stock['ticker'].loc[x]]
cursor.execute(insertHas_Stock, has_stock_data)
print("INSERT INTO HAS_STOCK(variables) VALUES " + str(has_stock_data))
conn.commit()
cursor.close()
conn.close()
def importHas_Portfolio(has_portfolio):
conn = sqlconnection.connect(user='******', password='', host='127.0.0.1', database='financedb')
cursor = conn.cursor()
insertHas_Portfolio = ("INSERT INTO HAS_PORTFOLIO"
"(login_id , portfolio_id) "
"VALUES (%s, %s)")
for x in range(has_portfolio['login_id'].count()):
has_portfolio_data = [int(has_portfolio['login_id'].loc[x]), int(has_portfolio['portfolio_id'].loc[x])]
cursor.execute(insertHas_Portfolio, has_portfolio_data)
print("INSERT INTO HAS_PORTFOLIO(variables) VALUES " + str(has_portfolio_data))
conn.commit()
cursor.close()
conn.close()
def importPortfolio(portfolio):
conn = sqlconnection.connect(user='******', password='', host='127.0.0.1', database='financedb')
cursor = conn.cursor()
insertPortfolio = ("INSERT INTO PORTFOLIO"
"(portfolio_name) "
"VALUES (%s)")
for x in range(portfolio['portfolio_name'].count()):
portfolio_data = [portfolio['portfolio_name'].loc[x]]
cursor.execute(insertPortfolio, portfolio_data)
print("INSERT INTO PORTFOLIO(variables) VALUES " + str(portfolio_data))
conn.commit()
cursor.close()
conn.close()
def delPort():
try:
portfolio_name = request.form['name']
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb',
autocommit=True)
cursor = conn.cursor()
deletePortfolio = ("DELETE FROM portfolio WHERE portfolio_name = '" +
portfolio_name + "'")
cursor.execute(deletePortfolio)
print(deletePortfolio)
index = None
ports = session['portfolio_name']
for x in range(len(ports)):
if ports[x] == portfolio_name:
index = x
break
portfolio_id = session['portfolio_id'][index]
idslist = session['portfolio_id']
namelist = session['portfolio_name']
idslist.remove(portfolio_id)
namelist.remove(portfolio_name)
session['portfolio_id'] = idslist
session['portfolio_name'] = namelist
conn.commit()
cursor.close()
conn.close()
return render_template('viewAcc.html')
except:
flash(u'Portfolio does not exist -- Cannot delete', 'delete')
return render_template('viewAcc.html')
def getLatestDate(x):
try:
conn = sqlconnection.connect(user='******', password='', host='127.0.0.1', database='financedb')
cursor = conn.cursor()
selectDateQuery = ("Select eod "
"From " + x + " "
"Order By eod Desc Limit 1")
cursor.execute(selectDateQuery)
x = cursor.fetchone()
lastDate = x[0]
except:
lastDate = None
pass
return(lastDate)
cursor.close()
conn.close()
def getRecData(ticker):
style.use('ggplot')
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectDateQuery = ("Select * " "From " + ticker)
cursor.execute(selectDateQuery)
df = pd.DataFrame(columns=[
'date', 'open', 'high', 'low', 'close', 'adjclose', 'volume', 'ticker'
])
count = 0
for x in cursor:
df.loc[count] = [x[0], x[1], x[2], x[3], x[4], x[5], x[6], x[7]]
count += 1
cursor.close()
conn.close()
data = df.set_index('date')
# Initialize the short and long windows
short_window = 40
long_window = 100
# Initialize the `signals` DataFrame with the `signal` column
signals = pd.DataFrame(index=data.index)
signals['signal'] = 0.0
# Create short simple moving average over the short window
signals['short_mavg'] = data['adjclose'].rolling(window=short_window,
min_periods=1,
center=False).mean()
# Create long simple moving average over the long window
signals['long_mavg'] = data['adjclose'].rolling(window=long_window,
min_periods=1,
center=False).mean()
# Create signals
signals['signal'][short_window:] = np.where(
signals['short_mavg'][short_window:] >
signals['long_mavg'][short_window:], 1.0, 0.0)
# Generate trading orders
signals['positions'] = signals['signal'].diff()
# Print `signals`
# print(signals)
fig = plt.figure()
ax1 = fig.add_subplot(
111, ylabel='Price in $') # Add a subplot and label for y-axis
data['adjclose'].plot(ax=ax1, color='r', lw=2.) # Plot the closing price
signals[['short_mavg', 'long_mavg'
]].plot(ax=ax1, lw=2.) # Plot the short and long moving averages
ax1.plot(signals.loc[signals.positions == 1.0].index,
signals.short_mavg[signals.positions == 1.0],
'^',
markersize=10,
color='m') # Plot the buy signals
ax1.plot(signals.loc[signals.positions == -1.0].index,
signals.short_mavg[signals.positions == -1.0],
'v',
markersize=10,
color='k') # Plot the sell signals
plt.title('Trading Strategy for ' + ticker)
fig.savefig(
'C:/Users/wrenp/PycharmProjects/FlaskPractice/static/displayRecFigure.png'
)
return redirect('/viewRec2.html')
def getPredData(ticker):
style.use('ggplot')
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectDateQuery = ("Select * " "From " + ticker)
cursor.execute(selectDateQuery)
df = pd.DataFrame(columns=[
'date', 'open', 'high', 'low', 'close', 'adjclose', 'volume', 'ticker'
])
count = 0
for x in cursor:
df.loc[count] = [x[0], x[1], x[2], x[3], x[4], x[5], x[6], x[7]]
count += 1
cursor.close()
conn.close()
data = df[['adjclose']]
bp = int(data.shape[0] - 31)
train = data[:bp]
valid = data[bp:]
# splitting into train and validation (change valid to be length=50)
# find average percent change over the last week of train
Mdata = df[['adjclose', 'open']]
Mtrain = Mdata[bp - 7:bp]
close = np.array(Mtrain['adjclose'])
open = np.array(Mtrain['open'])
pct_C = []
for i in range(len(close)):
pct_change = (close[i] - open[i]) / open[i]
pct_C.append(pct_change)
# df['PCT_change'] = (df['adjclose'] - df['open']) / df['open'] * 100.0
avg_pct_C = sum(pct_C) / len(
pct_C) # multiplying by 10 to give multiplier more weight
multiplier = 1 + avg_pct_C
pred = []
for i in range(0, valid.shape[0]):
a = (
train['adjclose'][len(train) - valid.shape[0] + i:].sum() +
sum(pred)
) # * multiplier # multiplying by stock trend within the last week
b = a / valid.shape[0] * multiplier
pred.append(b)
# make predictions
lastTrain = train['adjclose'].iloc[-1]
firstPred = pred[:1]
dif = lastTrain - firstPred
pred = [x + dif for x in pred]
valid.insert(loc=0, column='Predictions', value=pred)
xupper = data.shape[0] + 10
xlower = data.shape[0] - 175
closeArr = np.array(data['adjclose'])
yupper = 0
for x in closeArr[xlower:]:
if (x > yupper):
yupper = x
ylower = 1000000
for x in closeArr[xlower:]:
if (x < ylower):
ylower = x
ylower = ylower - 15
fig = plt.figure()
plt.plot(train['adjclose'])
# plt.plot(valid[['adjclose', 'Predictions']]) # to print real values and predicted values
plt.plot(valid[['Predictions']])
plt.title(ticker + ' Forecast')
plt.xlim(xlower, xupper)
plt.ylim(ylower, yupper)
fig.savefig(
'C:/Users/wrenp/PycharmProjects/FlaskPractice/static/displayPredFigure.png'
) # to save picture of plot
return redirect('/viewPred2.html')
def updateStockData():
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectTickers = ("SELECT ticker " "FROM SP500")
cursor.execute(selectTickers)
tickers = []
for x in cursor:
tickers.append(x[0])
conn.commit()
cursor.close()
conn.close()
for x in tickers:
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
insertStockData = (
"INSERT INTO " + x + " "
"(eod, open_price, high_price, low_price, close_price, adjclose, volume, ticker) "
"VALUES (%s, %s, %s, %s, %s, %s, %s, %s)")
try:
# data['ticker'].count() - 2, 0, -1
data = pd.DataFrame(
si.get_data(x,
start_date=getLatestDate(x),
index_as_date=False))
for y in range(data['ticker'].count()):
print(y)
stock_data = [
data['date'].loc[y],
float(data['open'].loc[y]),
float(data['high'].loc[y]),
float(data['low'].loc[y]),
float(data['close'].loc[y]),
float(data['adjclose'].loc[y]),
float(data['volume'].loc[y]), data['ticker'].loc[y]
]
cursor.execute(insertStockData, stock_data)
print(
"INSERT INTO " + x +
" (eod, open_price, high_price, low_price, close_price, adjclose, volume, ticker) VALUES "
+ str(stock_data))
except:
# skipping the most recent date in stock_data table
pass
print("Done Inserting Data For " + x)
conn.commit()
cursor.close()
conn.close()
def login():
username = request.form['username']
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectUsers = ("SELECT username " "FROM users ")
cursor.execute(selectUsers)
users = []
for x in cursor:
users.append(x)
usernameExist = False
for x in users:
if (username == x[0]):
usernameExist = True
if usernameExist == False:
flash('Username does not exist')
return mainPage()
selectDateQuery = ("Select password_salt "
"From users "
"Where username = "******"'" + username + "'")
cursor.execute(selectDateQuery)
for x in cursor:
salt = x[0]
hash = encrypt_string(str(salt) + request.form['password'])
selectDateQuery = (
"Select login_id, password_hash, fname, subscriber, username "
"From users "
"Where username = "******"'" + username + "'")
cursor.execute(selectDateQuery)
for x in cursor:
currentUser = x[0]
pass_hash = x[1]
fname = x[2]
subscriber = x[3]
username = x[4]
selectDateQuery = (
"Select Portfolio.portfolio_id, portfolio_name "
"From has_portfolio, portfolio "
"Where has_portfolio.portfolio_id = portfolio.portfolio_id and has_portfolio.login_id = "
+ str(currentUser))
cursor.execute(selectDateQuery)
portids = []
portNames = []
for x in cursor:
portids.append(x[0])
portNames.append(x[1])
if (hash == pass_hash):
session['logged_in'] = True
session['user_id'] = currentUser
session['fname'] = fname
session['subscriber'] = subscriber
session['username'] = username
session['portfolio_id'] = portids
session['portfolio_name'] = portNames
else:
flash('Wrong Password')
return mainPage()
def percentages():
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectSectors = ("Select all count(ticker), sector "
"from SP500 "
"group by sector")
cursor.execute(selectSectors)
sectorsCounts = []
for x in cursor:
sectorsCounts.append([x[0], x[1], 0])
selectTickers = ("Select ticker " "from SP500")
cursor.execute(selectTickers)
currentTickers = []
for x in cursor:
currentTickers.append(x[0])
sumCap = 0
for x in currentTickers:
newdate = str(getLatestDate(x)).replace("-", "")
selectStocks = ("Select sector, close_price*volume "
"from " + x + ", SP500 "
"where SP500.ticker = " + x + ".ticker and eod = " +
newdate)
cursor.execute(selectStocks)
y = cursor.fetchone()
if y[0] == sectorsCounts[0][1]:
sectorsCounts[0][2] = sectorsCounts[0][2] + y[1]
elif y[0] == sectorsCounts[1][1]:
sectorsCounts[1][2] = sectorsCounts[1][2] + y[1]
elif y[0] == sectorsCounts[2][1]:
sectorsCounts[2][2] = sectorsCounts[2][2] + y[1]
elif y[0] == sectorsCounts[3][1]:
sectorsCounts[3][2] = sectorsCounts[3][2] + y[1]
elif y[0] == sectorsCounts[4][1]:
sectorsCounts[4][2] = sectorsCounts[4][2] + y[1]
elif y[0] == sectorsCounts[5][1]:
sectorsCounts[5][2] = sectorsCounts[5][2] + y[1]
elif y[0] == sectorsCounts[6][1]:
sectorsCounts[6][2] = sectorsCounts[6][2] + y[1]
elif y[0] == sectorsCounts[7][1]:
sectorsCounts[7][2] = sectorsCounts[7][2] + y[1]
elif y[0] == sectorsCounts[8][1]:
sectorsCounts[8][2] = sectorsCounts[8][2] + y[1]
elif y[0] == sectorsCounts[9][1]:
sectorsCounts[9][2] = sectorsCounts[9][2] + y[1]
elif y[0] == sectorsCounts[10][1]:
sectorsCounts[10][2] = sectorsCounts[10][2] + y[1]
sumCap = sumCap + y[1]
sectorList = []
print(sectorsCounts)
returnstr = "~Current Market Cap for S&P500: " + str(sumCap)
sectorList.append(returnstr)
for x in sectorsCounts:
sectorList.append("Sector: " + str(x[1]) +
" Companies in Sector: " + str(x[0]) +
" Sector Market Cap: " + str(round(x[2], 2)) +
" "
"Market Share Percentage: " +
str(round(((x[2] / sumCap) * 100), 1)) + "%")
return (sectorList)
cursor.close()
conn.close()
def addStock():
try:
session['currentStock'] = request.form['ticker']
ticker = session['currentStock']
portid = session['currentPortid']
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
insertStock = ("INSERT INTO has_stock "
"(portfolio_id, ticker) "
"VALUES (%s, %s)")
insertData = [int(portid), ticker]
cursor.execute(insertStock, insertData)
insertStatement = "INSERT INTO has_stock (portfolio_id, ticker) VALUES " + str(
insertData)
print(insertStatement)
selectTickerQuery = ("Select ticker "
"From has_stock "
"Where portfolio_id = " + str(portid))
cursor.execute(selectTickerQuery)
tickers = []
for x in cursor:
tickers.append(x[0])
conn.commit()
cursor.close()
conn.close()
return render_template('viewPort.html', tickers=tickers)
except:
portid = session['currentPortid']
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectTickerQuery = ("Select ticker "
"From has_stock "
"Where portfolio_id = " + str(portid))
cursor.execute(selectTickerQuery)
tickers = []
for x in cursor:
tickers.append(x[0])
conn.commit()
cursor.close()
conn.close()
flash(
u'Ticker does not exist in Database or it is already in your portfoliod',
'addStock')
return render_template('viewPort.html', tickers=tickers)
def findBiggestPercentChange():
conn = sqlconnection.connect(user='******',
password='******',
host='127.0.0.1',
database='financedb')
cursor = conn.cursor()
selectTickers = ("Select ticker " "from SP500")
cursor.execute(selectTickers)
tickerlistparse = []
for x in cursor:
tickerlistparse.append(x[0])
highlow = []
tickerlist = []
ndate = str(getLatestDate('GOOG'))
for x in tickerlistparse:
newdate = str(getLatestDate(x)).replace("-", "")
#print(newdate)
findQuery = (
"Select ((adjclose - open_price) / open_price) * 100.0 From " + x +
" Where eod = " + newdate)
#print("Saving: ", x)
cursor.execute(findQuery)
y = cursor.fetchone()
highlow.append(y)
tickerlist.append(x)
indexmax, valuemax = max(enumerate(highlow), key=operator.itemgetter(1))
indexmin, valuemin = min(enumerate(highlow), key=operator.itemgetter(1))
'''
print(valuemax)
print(indexmin)
print(valuemin)
print(tickerlist)
print(highlow)
'''
valuemax = str(valuemax).replace("(", "")
valuemax = str(valuemax).replace(")", "")
valuemax = str(valuemax).replace(",", "")
valuemin = str(valuemin).replace("(", "")
valuemin = str(valuemin).replace(")", "")
valuemin = str(valuemin).replace(",", "")
#print("Max Percentage Change: " + str(tickerlist[indexmax]) + ": " + valuemax + "%")
#print("Min Percentage Change: " + str(tickerlist[indexmin]) + ": " + valuemin + "%")
findQuery = ("Select stock_name From SP500 Where ticker = '" +
str(tickerlist[indexmax]) + "'")
cursor.execute(findQuery)
namemax = str(cursor.fetchone())
namemax = str(namemax).replace("(", "")
namemax = str(namemax).replace(")", "")
namemax = str(namemax).replace(",", "")
namemax = str(namemax).replace("'", "")
findQuery = ("Select stock_name From SP500 Where ticker = '" +
str(tickerlist[indexmin]) + "'")
cursor.execute(findQuery)
namemin = str(cursor.fetchone())
namemin = str(namemin).replace("(", "")
namemin = str(namemin).replace(")", "")
namemin = str(namemin).replace(",", "")
namemin = str(namemin).replace("'", "")
pctChList = []
line1 = "For the most current date, " + ndate + ":"
line2 = "Max Percentage Change: " + namemax + ": " + str(
round(float(valuemax), 2)) + "%"
line3 = "Min Percentage Change: " + namemin + ": " + str(
round(float(valuemin), 2)) + "%"
pctChList.append(line1)
pctChList.append(line2)
pctChList.append(line3)
return (pctChList)
cursor.close()
conn.close()
