def market_data_us10y():
table_list = ['us10y']
database.create_table(table_list)
# update the database to today
today = datetime.today().strftime('%Y-%m-%d')
if database.check_table_empty('us10y'):
eod_market_data.populate_stock_data(['US10Y'], "us10y", start_date, today, 'INDX')
else:
# if the table is not empty, insert data from the last date in the existing table to today.
select_stmt = 'SELECT date FROM us10y ORDER BY date DESC limit 1'
last_date = database.execute_sql_statement(select_stmt)['date'][0]
begin_date = (datetime.strptime(last_date, '%Y-%m-%d') + timedelta(days=1)).strftime('%Y-%m-%d')
if begin_date <= today:
eod_market_data.populate_stock_data(['US10Y'], "us10y", begin_date, today, 'INDX')
select_stmt = """
SELECT symbol, date,
printf("%.2f", open) as open,
printf("%.2f", high) as high,
printf("%.2f", low) as low,
printf("%.2f", close) as close,
printf("%.2f", adjusted_close) as adjusted_close
FROM us10y ORDER BY date DESC;
"""
result_df = database.execute_sql_statement(select_stmt)
result_df = result_df.transpose()
list_of_us10y = [result_df[i] for i in result_df]
return render_template("md_us10y.html", us10y_list=list_of_us10y)
def ai_build_model():
# database.drop_table('best_portfolio')
# While drop the table, table name "best_portfolio" still in metadata
# herefore, everytime only clear table instead of drop it.
table_list = ['best_portfolio']
database.create_table(table_list)
database.clear_table(table_list)
best_portfolio = build_ga_model(database)
print("yield: %8.4f%%, beta: %8.4f, daily_volatility:%8.4f%%, expected_daily_return:%8.4f%%" %
((best_portfolio.portfolio_yield * 100), best_portfolio.beta, (best_portfolio.volatility * 100),
(best_portfolio.expected_daily_return * 100)))
print("trend: %8.4f, sharpe_ratio:%8.4f, score:%8.4f" %
(best_portfolio.trend, best_portfolio.sharpe_ratio, best_portfolio.score))
# Show stocks' information of best portfolio
stocks = []
for stock in best_portfolio.stocks:
print(stock) # (symbol, name, sector,weight)
stocks.append((stock[1], stock[3], stock[0], str(round(stock[2] * 100, 4))))
length = len(stocks)
# Show portfolio's score metrics
portfolio_yield = str(round(best_portfolio.portfolio_yield * 100, 4)) + '%'
beta = str(round(best_portfolio.beta, 4))
volatility = str(round(best_portfolio.volatility * 100, 4)) + '%'
daily_return = str(round(best_portfolio.expected_daily_return * 100, 4)) + '%'
trend = str(round(best_portfolio.trend, 4))
sharpe_ratio = str(round(best_portfolio.sharpe_ratio, 4))
score = str(round(best_portfolio.score, 4))
return render_template('ai_best_portfolio.html', stock_list=stocks, portfolio_yield=portfolio_yield,
beta=beta, volatility=volatility, daily_return=daily_return, trend=trend,
sharpe_ratio=sharpe_ratio, score=score, length=length)
def market_data_sp500_sectors():
table_list = ['sp500', 'sp500_sectors']
database.create_table(table_list)
# don't need to update table again, table already updated in sp500 tab
if database.check_table_empty('sp500_sectors'):
eod_market_data.populate_sp500_data('SPY', 'US')
select_stmt = """
SELECT sector as sector_name,
printf("%.4f", equity_pct) as equity_pct,
printf("%.4f", category_pct) as category_pct
FROM sp500_sectors ORDER BY sector ASC;
"""
result_df = database.execute_sql_statement(select_stmt)
result_df = result_df.transpose()
list_of_sectors = [result_df[i] for i in result_df]
return render_template("md_sp500_sectors.html", sector_list=list_of_sectors)
def market_data_sp500():
table_list = ['sp500', 'sp500_sectors']
database.create_table(table_list)
if database.check_table_empty('sp500'):
eod_market_data.populate_sp500_data('SPY', 'US')
else:
# update tables
database.clear_table(table_list)
eod_market_data.populate_sp500_data('SPY', 'US')
select_stmt = """
SELECT symbol, name as company_name, sector, industry,
printf("%.2f", weight) as weight
FROM sp500 ORDER BY symbol ASC;
"""
result_df = database.execute_sql_statement(select_stmt)
result_df = result_df.transpose()
list_of_stocks = [result_df[i] for i in result_df]
return render_template("md_sp500.html", stock_list=list_of_stocks)
def market_data_stock():
#TODO: if ticker not in database, add new data to database.
table_list = ['stocks']
database.create_table(table_list)
if database.check_table_empty('stocks'):
tickers = database.get_sp500_symbols()
eod_market_data.populate_stock_data(tickers, "stocks", start_date, end_date, 'US')
if request.method == 'POST':
ticker = "A"
if request.form.get("symbol"):
ticker = request.form.get("symbol")
date1 = start_date
if request.form.get("start_date"):
date1 = request.form.get("start_date")
date2 = end_date
if request.form.get("end_date"):
date2 = request.form.get("end_date")
select_stmt = f"""
SELECT symbol, date,
printf("%.2f", open) as open,
printf("%.2f", high) as high,
printf("%.2f", low) as low,
printf("%.2f", close) as close,
printf("%.2f", adjusted_close) as adjusted_close,
volume
FROM stocks
WHERE symbol = "{ticker}" AND strftime('%Y-%m-%d', date) BETWEEN "{date1}" AND "{date2}"
ORDER BY date;
"""
result_df = database.execute_sql_statement(select_stmt)
if result_df.empty:
flash('Data does not exist in database. Please enter a ticker in S&P500 and a date after 2010/1/1 :)')
result_df = result_df.transpose()
list_of_stock = [result_df[i] for i in result_df]
return render_template("md_stock.html", stock_list=list_of_stock)
else:
return render_template("md_get_stock.html")
def market_data_fundamentals():
table_list = ['fundamentals']
database.create_table(table_list)
if database.check_table_empty('fundamentals'):
tickers = database.get_sp500_symbols()
tickers.append('SPY')
eod_market_data.populate_fundamental_data(tickers, 'US')
select_stmt = """
SELECT symbol,
printf("%.4f", pe_ratio) as pe_ratio,
printf("%.4f", dividend_yield) as dividend_yield,
printf("%.4f", beta) as beta,
printf("%.2f", high_52weeks) as high_52weeks,
printf("%.2f", low_52weeks) as low_52weeks,
printf("%.2f", ma_50days) as ma_50days,
printf("%.2f", ma_200days) as ma_200days
FROM fundamentals ORDER BY symbol;
"""
result_df = database.execute_sql_statement(select_stmt)
result_df = result_df.transpose()
list_of_stocks = [result_df[i] for i in result_df]
return render_template("md_fundamentals.html", stock_list=list_of_stocks)
def update_market_data():
"""
This function is for updating the MatketData database.
# Note: Not used yet. Run this function to update database manually.
# TODOs:
1.automatically trigger this function once everyday, then delete the update part in
market_data_sp500(), market_data_spy(), and market_data_us10y().
2. Make the retrieval of fundamentals and stock prices faster
"""
today = datetime.today().strftime('%Y-%m-%d')
# fundamentals (use multi-threads,takes 30 seconnds)
table_list = ['fundamentals']
database.create_table(table_list)
database.clear_table(table_list)
tickers = database.get_sp500_symbols()
tickers.append('SPY')
eod_market_data.populate_fundamental_data(tickers, 'US')
# spy price data
if database.check_table_empty('spy'):
# if the table is empty, insert data from start date to today
eod_market_data.populate_stock_data(['spy'], "spy", start_date, today, 'US')
else:
# if the table is not empty, insert data from the last date in the existing table to today.
select_stmt = 'SELECT date FROM spy ORDER BY date DESC limit 1'
last_date = database.execute_sql_statement(select_stmt)['date'][0]
# define begin_date here. The rest updates will use the same begin date
begin_date = (datetime.strptime(last_date, '%Y-%m-%d') + timedelta(days=1)).strftime(
'%Y-%m-%d') # add one day after the last date in table
if begin_date <= today:
eod_market_data.populate_stock_data(['spy'], "spy", begin_date, today, 'US')
# us10y
database.create_table(['us10y'])
if database.check_table_empty('us10y'):
eod_market_data.populate_stock_data(['US10Y'], "us10y", start_date, today, 'INDX')
else:
select_stmt = 'SELECT date FROM us10y ORDER BY date DESC limit 1'
last_date = database.execute_sql_statement(select_stmt)['date'][0]
begin_date = (datetime.strptime(last_date, '%Y-%m-%d') + timedelta(days=1)).strftime('%Y-%m-%d')
if begin_date <= today:
eod_market_data.populate_stock_data(['US10Y'], "us10y", begin_date, today, 'INDX')
# stock daily data (use multi-threads,takes 22 seconnds)
# TODO: try to use batch request from IEX data to further speed up. Get IEX subscription first. https://iexcloud.io/docs/api/#batch-requests
database.create_table(['stocks'])
tickers = database.get_sp500_symbols()
if database.check_table_empty('stocks'):
eod_market_data.populate_stocks_data_multi(tickers, "stocks", start_date, today, 'US')
else:
select_stmt = 'SELECT date FROM stocks ORDER BY date DESC limit 1'
last_date_stocks = database.execute_sql_statement(select_stmt)['date'][0]
begin_date_stocks = (datetime.strptime(last_date_stocks, '%Y-%m-%d') + timedelta(days=1)).strftime(
'%Y-%m-%d') # add one day after the last date in table
if begin_date_stocks <= today:
eod_market_data.populate_stocks_data_multi(tickers, "stocks", begin_date_stocks, today, 'US')
# sp500 index & sectors
table_list = ['sp500', 'sp500_sectors']
database.create_table(table_list)
if database.check_table_empty('sp500'):
eod_market_data.populate_sp500_data('SPY', 'US')
else:
# update tables
database.clear_table(table_list)
eod_market_data.populate_sp500_data('SPY', 'US')
begin_date_stocks = (datetime.strptime(last_date_stocks, '%Y-%m-%d') + timedelta(days=1)).strftime(
'%Y-%m-%d') # add one day after the last date in table
if begin_date_stocks <= today:
eod_market_data.populate_stocks_data_multi(tickers, "stocks", begin_date_stocks, today, 'US')
# sp500 index & sectors
table_list = ['sp500', 'sp500_sectors']
database.create_table(table_list)
if database.check_table_empty('sp500'):
eod_market_data.populate_sp500_data('SPY', 'US')
else:
# update tables
database.clear_table(table_list)
eod_market_data.populate_sp500_data('SPY', 'US')
if __name__ == "__main__":
table_list = ["users", "fre_users", "portfolios", "transactions"]
database.create_table(table_list)
# update_market_data()
add_admin_user()
try:
app.run(host='0.0.0.0', port=5001, debug=True, use_reloader=False)
if client_config.client_thread.is_alive() is True:
client_config.client_thread.join()
except (KeyError, KeyboardInterrupt, SystemExit, RuntimeError, Exception):
client_config.client_socket.close()
sys.exit(0)