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 model_probation_testing():
if request.method == 'POST':
form_input = request.form
probation_testing_start_date = form_input['Start Date']
probation_testing_end_date = form_input['End Date']
if (not probation_testing_end_date) or (not probation_testing_start_date):
flash('Error! Incorrect Values!', 'error')
return render_template("pair_trade_probation_test.html")
if datetime.strptime(probation_testing_start_date,"%Y-%m-%d") >= datetime.strptime(probation_testing_end_date,"%Y-%m-%d")\
or datetime.strptime(probation_testing_end_date, "%Y-%m-%d") > datetime.now():
flash('Error! Incorrect Dates!', 'error')
return render_template("pair_trade_probation_test.html")
pair_trade_probation_test(probation_testing_start_date, probation_testing_end_date)
select_stmt = "SELECT symbol1, symbol2, sum(profit_loss) AS Profit, count(profit_loss) AS Total_Trades, \
sum(CASE WHEN profit_loss > 0 THEN 1 ELSE 0 END) AS Profit_Trades, \
sum(CASE WHEN profit_loss <0 THEN 1 ELSE 0 END) AS Loss_Trades FROM pair_trades \
WHERE profit_loss <> 0 \
GROUP BY symbol1, symbol2;"
result_df = database.execute_sql_statement(select_stmt)
total = result_df['Profit'].sum()
result_df['Profit'] = result_df['Profit'].map('${:,.2f}'.format)
result_df = result_df.transpose()
trade_results = [result_df[i] for i in result_df]
return render_template("pair_trade_probation_test_result.html", trade_list=trade_results, total=usd(total))
else:
return render_template("pair_trade_probation_test.html")
def build_model():
if request.method == 'POST':
select_stmt = "SELECT DISTINCT sector FROM sp500;"
result_df = database.execute_sql_statement(select_stmt)
sector_list = list(result_df['sector'])
form_input = request.form
corr_threshold = form_input['Corr Threshold']
adf_threshold = form_input['P Threshold']
pair_trading_start_date = form_input['Start Date']
pair_trading_end_date = form_input['End Date']
sector = form_input['Sector']
if not (corr_threshold and adf_threshold and pair_trading_end_date and pair_trading_start_date and sector):
flash('Error! Incorrect Values!', 'error')
return render_template("pair_trade_build_model_param.html", sector_list=sector_list)
if float(corr_threshold) >= 1 or float(corr_threshold) <= - 1:
flash('Error! Incorrect Correlation Threshold!', 'error')
return render_template("pair_trade_build_model_param.html", sector_list=sector_list)
if float(adf_threshold) >= 1 or float(adf_threshold) <= 0:
flash('Error! Incorrect P Value Threshold!', 'error')
return render_template("pair_trade_build_model_param.html", sector_list=sector_list)
if datetime.strptime(pair_trading_start_date, "%Y-%m-%d") >= datetime.strptime(pair_trading_end_date,
"%Y-%m-%d") \
or datetime.strptime(pair_trading_end_date, "%Y-%m-%d") > datetime.now():
flash('Error! Incorrect Dates!', 'error')
return render_template("pair_trade_build_model_param.html", sector_list=sector_list)
build_pair_trading_model(corr_threshold, adf_threshold, sector, pair_trading_start_date, pair_trading_end_date)
select_stmt = "SELECT * FROM stock_pairs;"
result_df = database.execute_sql_statement(select_stmt)
result_df['price_mean'] = result_df['price_mean'].map('{:.4f}'.format)
result_df['volatility'] = result_df['volatility'].map('{:.4f}'.format)
result_df = result_df.transpose()
list_of_pairs = [result_df[i] for i in result_df]
return render_template("pair_trade_build_model.html", pair_list=list_of_pairs)
else:
select_stmt = "SELECT DISTINCT sector FROM sp500;"
result_df = database.execute_sql_statement(select_stmt)
sector_list = list(result_df['sector'])
return render_template("pair_trade_build_model_param.html", sector_list=sector_list)
def model_back_testing():
pair_trade_back_test(back_testing_start_date, back_testing_end_date)
select_stmt = "SELECT symbol1, symbol2, sum(profit_loss) AS Profit, count(profit_loss) AS Total_Trades, \
sum(CASE WHEN profit_loss > 0 THEN 1 ELSE 0 END) AS Profit_Trades, \
sum(CASE WHEN profit_loss <0 THEN 1 ELSE 0 END) AS Loss_Trades FROM pair_trades \
WHERE profit_loss <> 0 \
GROUP BY symbol1, symbol2;"
result_df = database.execute_sql_statement(select_stmt)
total = result_df['Profit'].sum()
result_df['Profit'] = result_df['Profit'].map('${:,.2f}'.format)
result_df = result_df.transpose()
trade_results = [result_df[i] for i in result_df]
return render_template("pair_trade_back_test_result.html", trade_list=trade_results, total=usd(total))
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')