def query_upload_marketdata(self, tickers, start, end):
try:
fromdate = f"{start}"
todate = f"{end}"
if end == None:
todate = dt.datetime.now().strftime(constants.DATE_FORMAT)
if not os.path.exists('stock_dfs'):
os.makedirs('stock_dfs')
total = 0
for ticker in tickers:
total += 1
print('{}. {}'.format(total, ticker))
summery = MarketDataSummery.query.filter_by(
symbol=ticker).first()
if summery == None:
summery = MarketDataSummery(symbol=ticker)
db.session.add(summery)
db.session.commit()
summery = MarketDataSummery.query.filter_by(
symbol=ticker).first()
if summery.fromdate == None or fromdate < summery.fromdate or todate > summery.todate:
alpha_stock(ticker)
# get_yahoo_market_data(ticker, fromdate, todate)
self.upload_marketdata(summery)
except Exception:
print("Exception for initialing ")
logger.exception(f'Exceptiion for initialing test')
traceback.print_exc()
print('\n\nTotal downloaded market data symbols is {}'.format(total))
def get_yahoo_market_data(ticker, fromDate, toDate):
logger.info(f'get_market_data:{ticker} from {fromDate} to {toDate}')
startDate = dt.datetime.strptime(fromDate, constants.DATE_FORMAT)
present = dt.datetime.strptime(toDate, constants.DATE_FORMAT)
all_data = pd.DataFrame()
while startDate < present:
endDate = startDate + dt.timedelta(days=150)
if present < endDate:
endDate = present
start = startDate.strftime(constants.DATE_FORMAT)
end = endDate.strftime(constants.DATE_FORMAT)
try:
data = get_market_data(ticker, start, end)
if data.empty == False:
del data['Adj Close']
data = data[[
constants.OPEN_COLUMN, constants.HIGH_COLUMN,
constants.LOW_COLUMN, constants.CLOSE_COLUMN,
constants.VOLUME_COLUMN
]]
if all_data.empty:
all_data = data
else:
all_data = all_data.append(data)
except ValueError as error:
print(error)
logger.info(f'get_market_data {ticker}: {error}')
except KeyError as error:
print(error)
logger.info(f'get_market_data {ticker}: {error}')
except Exception:
print('Exception during getting data for ', ticker)
logger.exception(f'Exceptiion for get_market_data {ticker}')
startDate = endDate + dt.timedelta(days=1)
if all_data.empty:
log_info = f'Failed to get data for {ticker}'
print(log_info)
logger.info(log_info)
return None
else:
filename = '{}/{}.csv'.format(constants.MARKETDATA_FOLDER, ticker)
log_info = f'Save {ticker} data in {filename}'
print(log_info)
logger.info(log_info)
all_data.to_csv(filename)
return ticker
def update_marketdata_summery(self):
print('Start to create or update market data summeries')
lastTicker = None
total = 0
try:
datapath = os.path.join(constants.MARKETDATA_FOLDER, "*.csv")
names = glob.glob(datapath)
for index, name in enumerate(names, start=1):
total = index
if (index % 500) == 0:
print(f'Create or update {index} summeries')
parts = name.split('.')
ticker = self.path_leaf(parts[0])
lastTicker = ticker
# print(ticker)
summery = MarketDataSummery.query.filter_by(
symbol=ticker).first()
marketData = pd.read_csv(name)
start = marketData.head(1)[constants.DATE_COLUMN].values[0]
end = marketData.tail(1)[constants.DATE_COLUMN].values[0]
if summery == None:
summery = MarketDataSummery(symbol=ticker,
fromdate=start,
todate=end)
db.session.add(summery)
else:
summery.fromdate = start
summery.todate = end
db.session.commit()
print(f'Finished to create or update {total} summeries')
return True
except Exception:
err_msg = f'Exceptiion for creating or update {lastTicker}'
print(err_msg)
logger.exception(err_msg)
traceback.print_exc()
print("Finished to update marketdata summeries")
return False
def finalize_result(self, startdate, lasttradedate, daily_activities,
middle_results):
start_time = time.perf_counter()
test_summery_id = 0
try:
logger.info('Start to finalize test results')
# daily_activities.calculate_portfolio_volatility()
trading_days = daily_activities.get_trading_days()
(beta, alpha,
sharpe) = daily_activities.calculate_beta_aphla_sharpe(
startdate, lasttradedate, params.is_hongkong_stock())
test_summery_id = middle_results.build_and_save_test_summery(
startdate, lasttradedate, beta, alpha, sharpe, trading_days,
params.is_hongkong_stock())
daily_activities.save_activities(test_summery_id)
for ticker in middle_results.notFoundTickersInMarket:
log_info = f'No Market Data for {ticker}'
print(log_info)
logger.info(log_info)
if middle_results.totalFailuresToBuyStocks > 0:
info_msg = f'Total {middle_results.totalFailuresToBuyStocks} failures to buy stocks'
print(info_msg)
logger.info(info_msg)
except Exception:
print("Exception for initialing ")
logger.exception(f'Exceptiion for initialing test')
traceback.print_exc()
end_time = time.perf_counter()
time_taken = (end_time - start_time) / 60.0
latency = '\nTotal finalizing result time: {:6.2f}'.format(time_taken)
print(latency)
return test_summery_id
def upload_all_stocks_from_file(self, filename):
full_filename = f'{constants.USERINPUT_FOLDER}/{filename}'
if os.path.exists(full_filename):
try:
lastticker = None
marketData = pd.read_csv(full_filename)
for row_index, row in marketData.iterrows():
ticker = row[constants.SYMBOL_COLUMN]
currentDate = row[constants.DATE_COLUMN]
if ticker != lastticker:
if lastticker:
db.session.commit()
lastticker = ticker
summery = MarketDataSummery(symbol=ticker)
db.session.add(summery)
db.session.commit()
lastsummery = MarketDataSummery.query.filter_by(
symbol=ticker).first()
lastsummery.fromdate = currentDate
summery.todate = currentDate
markedata = MarketData(
date=row[constants.DATE_COLUMN],
symbol=ticker,
openprice=row[constants.OPEN_COLUMN],
highprice=row[constants.HIGH_COLUMN],
lowprice=row[constants.LOW_COLUMN],
closeprice=row[constants.CLOSE_COLUMN],
volumn=row[constants.VOLUME_COLUMN],
summery_id=summery.id)
db.session.add(markedata)
db.session.commit()
return True
except Exception:
print("Exception for upload_all_stocks_from_file ")
logger.exception(f'Exceptiion for upload_all_stocks_from_file')
traceback.print_exc()
return False
def compile_testdata(self, indicator_ids, sell_indicators, name, startdate,
enddate):
current_time = dt.datetime.now()
log_info = f'compile_testdata: {startdate} to {enddate} indcators={indicator_ids}, sell_indicators={sell_indicators}'
print(log_info, current_time)
logger.info(log_info)
input = self.get_input_after_init(indicator_ids, sell_indicators,
startdate, enddate)
if input.empty:
log_info = f'Input is empty'
print(log_info, current_time)
logger.info(log_info)
return 0
start_time = time.perf_counter()
daily_activities = DailyActivities()
currentProcessDate = None
lastSoldDate = None
totalCash = params.get_total_cash()
(originalLeverage, originalLongLeverage,
originalShortLeverage) = params.get_leverages(totalCash)
prevBuyDateStr = ''
indicators = utils.get_indicator_names(indicator_ids)
middle_results = MiddleResults(name, indicators, float(totalCash),
float(originalLeverage),
float(originalLongLeverage),
float(originalShortLeverage))
log_info = f'start to process with total {totalCash}$ ...'
logger.info(log_info)
print(log_info)
start_date = dt.datetime.strptime(startdate, constants.DATE_FORMAT)
# print("input=", input)
for row_index, row in input.iterrows():
ticker = row[constants.SYMBOL_COLUMN]
indicator_id = row[constants.INDICATOR_COLUMN]
isShortProcess = params.is_short_parameters(indicator_id)
if marketdataManager.is_marketdata_available(ticker) == False:
middle_results.add_not_found_in_market_data(ticker)
continue
buyDateStr = row[constants.DATE_COLUMN]
buyPrice = row[constants.BUYPRICE_COLUMN]
buyDate = dt.datetime.strptime(buyDateStr, constants.DATE_FORMAT)
print(f'buy: {ticker}/{buyDateStr}/{buyPrice}')
try:
if currentProcessDate == None:
currentProcessDate = buyDate
# print("The first trading day ", currentProcessDate)
lastSoldDate = currentProcessDate
prevBuyDateStr = buyDateStr
if (currentProcessDate.date() > start_date.date()):
self.processSellStocks(start_date, currentProcessDate,
middle_results,
daily_activities)
elif buyDate != currentProcessDate:
prevBuyDate = dt.datetime.strptime(prevBuyDateStr,
constants.DATE_FORMAT)
currentProcessDate = buyDate
if (currentProcessDate.date() > lastSoldDate.date()):
lastSoldDate = currentProcessDate
self.processSellStocks(prevBuyDate, currentProcessDate,
middle_results, daily_activities)
prevBuyDateStr = buyDateStr
if math.isnan(float(buyPrice)) or buyPrice == 0:
continue
if (ticker in self.stocksSaleInfo):
(num_stocks, period,
shortStock) = self.investedStocks.get(ticker)
if (num_stocks != None and num_stocks > 0):
(soldDate, moneyFromSoldStock1,
moneyForInvest) = self.stocksSaleInfo[ticker]
log_info = f'Cannot buy stock because {ticker} has been bought in {soldDate}'
print(log_info)
logger.info(log_info)
middle_results.increase_fail_buy_stock_with_error()
continue
availableInvestPerStock = params.invest_money_per_stock(
indicator_id, middle_results.actualPortfolio)
# print("availableInvestPerStock=", availableInvestPerStock)
holdlingDays = params.get_max_holding_days(indicator_id)
marketData = marketdataManager.get_market_data_from(
ticker, buyDate, holdlingDays)
if marketData.empty:
log_info = f'Cannot buy stock because No market data for {ticker} from {buyDate} in {holdlingDays}'
print(log_info)
logger.info(log_info)
middle_results.increase_fail_buy_stock_with_error()
continue
queryDate = buyDate.strftime(constants.DATE_FORMAT)
mkdata_row = marketData.loc[marketData[constants.DATE_COLUMN]
== queryDate]
if mkdata_row.empty:
log_info = f'Cannot buy stock because of No market data for {ticker} in {buyDate}'
print(log_info)
logger.info(log_info)
middle_results.increase_fail_buy_stock_with_error()
continue
if len(mkdata_row.index) > 1:
log_info = f'Has duplicated market data for {ticker} in {buyDate}'
print(log_info)
logger.info(log_info)
# middle_results.increase_fail_buy_stock_with_error()
continue
high_price = float(mkdata_row[constants.HIGH_COLUMN]) + 0.5
low_price = float(mkdata_row[constants.LOW_COLUMN]) - 0.05
if middle_results.is_invalid_input(ticker, buyDateStr,
buyPrice, high_price,
low_price):
log_info = f'Cannot buy stock because invalid price {buyPrice} compared with high {high_price} and low {low_price}'
print(log_info)
logger.info(log_info)
middle_results.increase_fail_buy_stock_with_error()
continue
minVolPercent = params.get_minimum_volumn_percent(indicator_id)
minTradeVolumn = marketdataManager.previous_min_total_trade(
ticker, buyDate, minVolPercent)
if minTradeVolumn == 0:
log_info = f'Cannot buy stock because cannot found Market Data before {buyDate} for {ticker}'
logger.info(log_info)
continue
middle_results.totalPotentialTrades += 1
(num_stocks,
totalInvest) = params.buy_stocks(indicator_id, buyPrice,
minTradeVolumn,
middle_results.portfolio)
# print(f'buy_stocks: {num_stocks}/{totalInvest}')
if num_stocks <= 0:
daily_activities.add_failure(buyDateStr, ticker,
'Small Volume')
middle_results.noTradeForVolume += 1
continue
# print('Cash: {} and investPerStock:{}'.format(availableCash, totalInvest))
leverageMoney = middle_results.get_leverage(isShortProcess)
if ((middle_results.cash <= availableInvestPerStock)
and (leverageMoney <= availableInvestPerStock)):
# print('No enough money to buy stock today {} and cash: {} and investPerStock:{}'.format(buyDateStr, availableCash, availableInvestPerStock))
daily_activities.add_failure(buyDateStr, ticker,
'Short Cash')
middle_results.noTradeForShortCash += 1
continue
totalNeedMoney = totalInvest
cashNeeded = 0
leverageNeeded = 0
if (middle_results.cash >= totalNeedMoney):
self.investedStocks[ticker] = (num_stocks, 0,
isShortProcess)
cashNeeded = totalNeedMoney
# print('availableCash:{}'.format(availableCash))
# print('num_stocks={}, totalInvest={}'.format(num_stocks, totalInvest))
elif (leverageMoney > (totalNeedMoney - middle_results.cash)):
leverageNeeded = (totalNeedMoney - middle_results.cash)
cashNeeded = middle_results.cash
self.investedStocks[ticker] = (num_stocks, 0,
isShortProcess)
# print('leverageMoney:{}'.format(leverageMoney))
# print('num_stocks={}, totalInvest={}'.format(num_stocks, totalInvest))
else:
daily_activities.add_failure(buyDateStr, ticker,
'Short Cash')
middle_results.noTradeForShortCash += 1
log_info = f'no money left for {buyDateStr}'
print(log_info)
logger.info(log_info)
continue
idstr = f'{indicator_id}'
use_sell_indicator = idstr in sell_indicators
(saleDateStr, salePrice) = utils.get_sale_price_date(
indicator_id, use_sell_indicator, ticker, buyDateStr,
buyPrice, marketData)
# print(f'{ticker} salePrice={salePrice}, saleDate={saleDateStr}')
if salePrice < 0.001:
self.investedStocks[ticker] = (0, 0, False)
continue
middle_results.remove_cash(cashNeeded)
middle_results.remove_leverage(leverageNeeded, isShortProcess)
middle_results.increase_cashused_holdings(cashNeeded)
totalSold = 0.0
if any(self.soldStocks.items()):
sold = self.soldStocks.get(saleDateStr)
if sold != None:
totalSold = float(sold)
middle_results.decide_win_or_loss(buyPrice, salePrice,
num_stocks, isShortProcess)
finalSalePrice = utils.get_final_sale_price(
isShortProcess, salePrice, buyPrice)
moneyFromSold = finalSalePrice * num_stocks
self.soldStocks[saleDateStr] = totalSold + moneyFromSold
self.stocksSaleInfo[ticker] = (saleDateStr, moneyFromSold,
totalNeedMoney)
log_info = f'{buyDateStr}: Bought {ticker} / {buyPrice} / {num_stocks} / {"%.2f" % totalNeedMoney}'
logger.info(log_info)
log_info = f'{buyDateStr}: Expect to sell {ticker} / {salePrice} / -{num_stocks} / {"%.2f" % moneyFromSold} / {saleDateStr}'
logger.info(log_info)
daily_activities.add_acitivity(
mkdata_row[constants.DATE_COLUMN], ticker, buyPrice,
num_stocks, salePrice, saleDateStr)
saleDate = dt.datetime.strptime(saleDateStr,
constants.DATE_FORMAT)
if (saleDate.date() > lastSoldDate.date()):
lastSoldDate = saleDate
except Exception:
print("Exception for processing ", ticker, buyDateStr)
logger.exception(
f'Exceptiion for processing {ticker} in {buyDateStr}')
traceback.print_exc()
break
if daily_activities.is_noactivity():
print('No results!')
logger.info('No results!')
return 0
print('Last Buy Date: {}, Last Sold Date: {}'.format(
currentProcessDate.strftime(constants.OUTPUT_DATE_FORMAT),
lastSoldDate.strftime(constants.OUTPUT_DATE_FORMAT)))
lasttradedate = lastSoldDate.strftime(constants.OUTPUT_DATE_FORMAT)
lastSoldDate = lastSoldDate + dt.timedelta(days=1)
self.processSellStocks(currentProcessDate, lastSoldDate,
middle_results, daily_activities)
log_info = f'finished to process data from {startdate} to {lasttradedate}'
logger.info(log_info)
test_summery_id = self.finalize_result(startdate, lasttradedate,
daily_activities,
middle_results)
end_time = time.perf_counter()
time_taken = (end_time - start_time) / 60.0
latency = '\nTotal time: {:6.2f}'.format(time_taken)
print(latency)
logger.info(
f'finished to save results with time {"%.2f" % time_taken}')
return test_summery_id
def get_input_after_init(self, indicator_ids, sell_indicators, startdate,
enddate):
start_time = time.perf_counter()
input = pd.DataFrame()
try:
# logfilepath = app.config.get('LOG_FILE_NAME')
# os.remove(logfilepath)
log_info = f'Load all buy parameters'
logger.info(log_info)
params.load_buyparameters(indicator_ids)
ishkstock = params.is_hongkong_stock()
log_info = f'Start to load buyindicators with ids {indicator_ids} from {startdate} to {enddate}'
print(log_info)
logger.info(log_info)
input = utils.load_buyindicators(indicator_ids, startdate, enddate)
if input.empty:
print("Cannot process testdata without input indicator!!!")
return input
current_time = dt.datetime.now()
log_info = "Finished to load buy indicators"
print(log_info, current_time)
logger.info(log_info)
log_info = f'Start to load marketdata from {startdate} to {enddate}'
print(log_info)
logger.info(log_info)
symbols = set(input[constants.SYMBOL_COLUMN])
# if marketdataManager.load_market_data(symbols, startdate, enddate) == False:
if marketdataManager.load_market_data_from_stock_dfs(
symbols, startdate, enddate) == False:
print("Cannot process testdata without marketdata!!!")
return input
marketdataManager.load_business_days(startdate, enddate, ishkstock)
log_info = "Finished to load marketdata"
current_time = dt.datetime.now()
print(log_info, current_time)
logger.info(log_info)
self.investedStocks.clear()
self.stocksSaleInfo.clear()
self.soldStocks.clear()
except Exception:
print("Exception for initialing ")
logger.exception(f'Exceptiion for initialing test')
traceback.print_exc()
end_time = time.perf_counter()
time_taken = (end_time - start_time) / 60.0
latency = '\nTotal initial test time: {:6.2f}'.format(time_taken)
print(latency)
return input