def init_database():
print('start init database...')
print('init stock brief...')
StockBriefTable.clear_brief_table()
StockBriefTable.init_stock_brief_from_xl('./data/A_stock_list.xlsx')
print('\nusing spiders to get stock cash flow table...')
CashFlowRecdTable.clear_cash_flow_recd_table()
os.system("scrapy runspider Spiders/StockSpiders/CashFlowSpider.py --nolog")
print('\nusing spiders to get stock profit table...')
ProfitRecdTable.clear_profit_recd_table()
os.system("scrapy runspider Spiders/StockSpiders/StockProfitSpider.py --nolog")
print('\ninit finished')
def analyzer_day_cost_profit():
scode_list = StockBriefTable.get_stock_id_list()
analyzer = KLineAnalyzer()
code_cnt = len(scode_list)
win_cnt = 0
total_cnt = 0
growth = 1.0
take_days = 0
KLineBuyRecdTable.clear_table()
for index in range(0, code_cnt):
code_id = scode_list[index]
buy_record_list = analyzer.analyze_profit(code_id, 0)
has_holding = False
for buy_record in buy_record_list:
if buy_record.sell_date == 'None':
has_holding = True
continue
rate = (buy_record.sell_price - buy_record.buy_price) / buy_record.buy_price
growth = growth * (1 + rate)
total_cnt += 1
take_days += buy_record.days
if rate > 0.0:
win_cnt += 1
if has_holding:
KLineBuyRecdTable.insert_buy_recd_list(buy_record_list)
if len(buy_record_list) > 0 and total_cnt > 0:
print("[" + str(index) + "] WinRate: " + str(win_cnt * 1.0 / total_cnt) + " Growth: " + str(
pow(growth, 1 / take_days)) + " days: " + str(take_days / total_cnt))
def __init__(self, market_id='0', *args, **kwargs):
super(KLineSpider, self).__init__(*args, **kwargs)
self.str_market_id = market_id
if market_id == '0':
switch_zh_database()
elif market_id == '105':
switch_nysdaq_database()
elif market_id == '106':
switch_nyse_database()
elif market_id == '107':
switch_amex_database()
self.scode_list = StockBriefTable.get_stock_id_list()
def get_top_net_present_value_stock(top_cnt=10, season=4):
stock_id_list = StockBriefTable.get_stock_id_list()
average_list = []
stock_cnt = len(stock_id_list)
for i in range(0, stock_cnt):
stock_id = stock_id_list[i]
StockAnalyzer.print_progress(str(i) + "/" + str(stock_cnt))
average_item = StockAnalyzer.get_net_present_value_average(
stock_id, season)
average_list.append(average_item)
average_list.sort(key=StockAnalyzer.take_second, reverse=True)
print('\n')
for i in range(0, top_cnt):
tuple_item = average_list[i]
code = tuple_item[0]
average_growth = tuple_item[1]
print('code: ' + code + ', average growth: ' + str(average_growth))
def get_top_profit_rate_stock(top_cnt=10):
stock_id_list = StockBriefTable.get_stock_id_list()
stock_id_cnt = len(stock_id_list)
stock_2_average_growth_list = []
for i in range(0, stock_id_cnt):
StockAnalyzer.print_progress(str(i) + '/' + str(stock_id_cnt))
stock_id = stock_id_list[i]
tuple_item = StockAnalyzer.get_stock_average_profit_growth(
stock_id)
stock_2_average_growth_list.append(tuple_item)
stock_2_average_growth_list.sort(key=StockAnalyzer.take_second,
reverse=True)
for i in range(0, top_cnt):
tuple_item = stock_2_average_growth_list[i]
code = tuple_item[0]
average_growth = tuple_item[1]
print('code: ' + code + ', average growth: ' + str(average_growth))
def init_us_database():
print('start init database...')
print('init nysdaq stock brief...')
switch_nysdaq_database()
gt.save_tickers(NYSE=False, NASDAQ=True, AMEX=False, filename="./data/nysdaq_stock_list.csv")
StockBriefTable.init_stock_brief_from_cvs("./data/nysdaq_stock_list.csv")
print('init NYSE stock brief...')
switch_nyse_database()
gt.save_tickers(NYSE=True, NASDAQ=False, AMEX=False, filename="./data/nyse_stock_list.csv")
StockBriefTable.init_stock_brief_from_cvs("./data/nyse_stock_list.csv")
print('init AMEX stock brief...')
switch_amex_database()
gt.save_tickers(NYSE=False, NASDAQ=False, AMEX=True, filename="./data/amex_stock_list.csv")
StockBriefTable.init_stock_brief_from_cvs("./data/amex_stock_list.csv")
print('\ninit finished')
def __init__(self, *args, **kwargs):
super(RzRqSpider, self).__init__(*args, **kwargs)
self.scode_list = StockBriefTable.get_stock_id_list()
class CashFlowSpider(scrapy.Spider):
name = 'CashFlowSpider'
start_urls = ['http://www.cninfo.com.cn/data/project/commonInterface']
cur_scode_indx = 0
scode_list = StockBriefTable.get_stock_id_list()
# 现金流表 sysapi1076
sysapi_cash_flow_code = 'sysapi1076'
# 一季度: rtype = 1
# 半年: rtype = 2
# 三季度: rtype = 3
# 年度: rtype = 4
cur_rtype = 1
index_str_2_type = {
'经营活动产生的现金流量净额': 0,
'投资活动产生的现金流量净额': 1,
'筹资活动产生的现金流量净额': 2,
}
def get_cur_scode(self):
return self.scode_list[self.cur_scode_indx]
def start_requests(self):
yield self.request_cash_flow(self.get_cur_scode(), 1)
def request_cash_flow(self, code, rtype):
return scrapy.FormRequest(
url='http://www.cninfo.com.cn/data/project/commonInterface',
headers={
'Accept': 'application/json, text/javascript, */*; q=0.01',
'Accept-Language':
'en-US,en;q=0.9,zh-CN;q=0.8,zh;q=0.7,de;q=0.6',
'Content-Type':
'application/x-www-form-urlencoded; charset=UTF-8'
},
formdata={
'mergerMark': self.sysapi_cash_flow_code,
'paramStr':
'scode=' + code + ';rtype=' + str(rtype) + ';sign=1'
},
callback=self.parse_cash_flow)
def parse_cash_flow(self, response):
data = json.loads(response.body)
data_len = len(data)
season = self.cur_rtype
cash_flow_recd_list = []
for i in range(0, data_len):
cur_profit_data = data[i]
for k in cur_profit_data:
if k != 'index':
index_name = cur_profit_data['index']
cash_flow_recd = CashFlowRecd()
cash_flow_recd.codeId = self.get_cur_scode()
cash_flow_recd.season = season
cash_flow_recd.recd_type_id = self.index_str_2_type[
index_name]
cash_flow_recd.year = int(k)
cash_flow_recd.value = cur_profit_data[k]
cash_flow_recd_list.append(cash_flow_recd)
CashFlowRecdTable.insert_cash_flow_recd_list(cash_flow_recd_list)
if self.cur_rtype != 4:
self.cur_rtype += 1
else:
self.cur_scode_indx += 1
self.cur_rtype = 1
if self.cur_scode_indx < (len(self.scode_list) - 1):
self.print_progress()
yield from [
self.request_cash_flow(self.get_cur_scode(), self.cur_rtype)
]
def print_progress(self):
str_progress = "{0}/{1}: {2}".format(str(self.cur_scode_indx),
str(len(self.scode_list)),
str(self.cur_rtype))
print("\b" * (len(str_progress) * 2), end="")
print(str_progress, end="")
class KLineAnalyzer:
scode_list = StockBriefTable.get_stock_id_list()
cur_scode_indx = 0
macd_peroid = 52
def get_cur_scode(self):
return self.scode_list[self.cur_scode_indx]
def analyze_one(self, stock_id, table_id=0):
register_matplotlib_converters()
k_line_list = KLineTable.select_k_line_list(stock_id, table_id)
date_arr = []
average_price_arr = []
average_cost_arr = []
ma_20_arr = []
ma_200_arr = []
close_arr = []
k_line_cnt = len(k_line_list)
for indx in range(0, k_line_cnt):
k_line = k_line_list[indx]
ma_20 = self.get_ma_close(indx, k_line_list, 20)
ma_200 = self.get_ma_close(indx, k_line_list, 200)
cur_date = datetime(k_line.year, k_line.month, k_line.day)
date_arr.append(cur_date)
average_cost_arr.append(k_line.cost)
average_price_arr.append(k_line.price)
ma_20_arr.append(ma_20)
ma_200_arr.append(ma_200)
close_arr.append(k_line.close)
cost_line_data_fame = DataFrame({
'DatetimeIndex': date_arr,
'average_price': average_price_arr,
'average_cost': average_cost_arr,
'close': close_arr,
'ma_20': ma_20_arr,
'ma_200': ma_200_arr
})
plt.plot('DatetimeIndex', 'close', data=cost_line_data_fame)
# plt.plot('DatetimeIndex', 'average_cost', data=cost_line_data_fame)
plt.plot('DatetimeIndex', 'ma_20', data=cost_line_data_fame)
# plt.plot('DatetimeIndex', 'ma_200', data=cost_line_data_fame)
plt.legend()
plt.show()
def is_cross(self, indx, k_line_list):
if indx <= 0 or indx >= len(k_line_list):
return False
else:
cur_k_line = k_line_list[indx]
pre_k_line = k_line_list[indx - 1]
return cur_k_line.diff > cur_k_line.dea and pre_k_line.dea > pre_k_line.diff and cur_k_line.diff > pre_k_line.diff
def is_sell_cross(self, indx, k_line_list):
if indx <= 0 or indx >= len(k_line_list):
return True
else:
cur_k_line = k_line_list[indx]
return cur_k_line.diff < cur_k_line.dea
def is_in_up_trend(self, date, k_line_list):
for idx in range(0, len(k_line_list)):
k_line = k_line_list[idx]
start_date = k_line.get_date()
end_date = start_date + timedelta(weeks=4)
if start_date <= date <= end_date:
return k_line.takeover > 180
return False
def is_match(self, indx, k_line_list):
peroid = 20
if indx <= peroid:
return False
cur_k_line = k_line_list[indx]
if self.is_cross(indx, k_line_list) and cur_k_line.diff >= 0.5:
return True
else:
return False
def is_sell_match(self, indx, k_line_list):
return self.is_sell_cross(indx, k_line_list) and False
def get_macd(self, df_close_data, fastperiod=12, slowperiod=26):
diff, dea, bar = talib.MACD(df_close_data,
fastperiod=fastperiod,
slowperiod=slowperiod,
signalperiod=9)
return diff, dea, bar
def get_ma_cost(self, indx, k_line_list, ma):
sum = 0.0
if indx + 1 < ma:
return 0
else:
for i in range(0, ma):
k_line = k_line_list[indx - i]
sum += k_line.cost
return sum / ma
def get_ma_close(self, indx, k_line_list, ma):
sum = 0.0
if indx + 1 < ma:
return 0
else:
for i in range(0, ma):
k_line = k_line_list[indx - i]
sum += k_line.close
return sum / ma
def is_stock_profit(self, profit_recd_list, date):
cur_date = date - timedelta(days=180)
cur_season = 1
has_recd = False
is_profit = True
if cur_date.month <= 3:
cur_season = 1
elif cur_date.month <= 6:
cur_season = 2
elif cur_date.month <= 9:
cur_season = 3
else:
cur_season = 4
for recd in profit_recd_list:
if recd.year < cur_date.year or (recd.year == cur_date.year
and recd.season <= cur_season):
has_recd = True
if recd.profit <= 0:
is_profit = False
return is_profit and has_recd
def add_macd_to_k_line_list(self, k_line_list):
close_arr = []
for cur_idx in range(0, len(k_line_list)):
close_arr.append(k_line_list[cur_idx].close)
df_close = DataFrame({'close': close_arr})
diff, dea, bar = self.get_macd(df_close['close'])
bar_sum = 0
for cur_idx in range(0, len(k_line_list)):
k_line_list[cur_idx].diff = diff[cur_idx]
k_line_list[cur_idx].dea = dea[cur_idx]
k_line_list[cur_idx].bar = bar[cur_idx]
if not math.isnan(bar[cur_idx]):
bar_sum += 1 * k_line_list[cur_idx].bar
k_line_list[cur_idx].bar_sum = bar_sum
def analyze_profit(self, code_id, table_id=0, display_log=True):
k_line_list = KLineTable.select_k_line_list(code_id, table_id)
k_line_list.sort(key=get_k_line_date)
buy_recd_list = []
growth = 0
prev_k_line = None
is_during_check = False
start_check_k_line = None
win_exp_rate = 2 / 100
lose_exp_rate = -1 / 100
if len(k_line_list) == 0:
return buy_recd_list
self.add_macd_to_k_line_list(k_line_list)
for cur_idx in range(0, len(k_line_list)):
k_line = k_line_list[cur_idx]
if prev_k_line is None:
is_during_check = False
pass
elif is_during_check:
if start_check_k_line is None:
start_check_k_line = k_line
keep_trace_day = 1
continue
elif start_check_k_line.open != 0:
total_raise_rate = (k_line.high - start_check_k_line.open
) / start_check_k_line.open
total_des_rate = (k_line.low - start_check_k_line.open
) / start_check_k_line.open
total_over_rate = (k_line.close - start_check_k_line.open
) / start_check_k_line.open
if win_exp_rate >= total_raise_rate \
and total_des_rate >= lose_exp_rate \
and not self.is_sell_match(cur_idx, k_line_list):
pass
else:
is_during_check = False
output_rate = 0.0
if total_raise_rate > win_exp_rate:
growth = growth * (1.0 + win_exp_rate)
output_rate = win_exp_rate
elif self.is_sell_match(cur_idx, k_line_list):
growth = growth * (1.0 + total_over_rate)
output_rate = total_over_rate
else:
growth = growth * (1.0 + lose_exp_rate)
output_rate = lose_exp_rate
cur_buy_recd = KLineBuyRecd()
cur_buy_recd.code_id = k_line.codeId
cur_buy_recd.buy_date = start_check_k_line.get_date_str(
)
cur_buy_recd.sell_date = k_line.get_date_str()
cur_buy_recd.buy_price = start_check_k_line.open
cur_buy_recd.sell_price = cur_buy_recd.buy_price * (
1 + output_rate)
cur_buy_recd.days = (
k_line.get_date() -
start_check_k_line.get_date()).days
buy_recd_list.append(cur_buy_recd)
if display_log:
print(code_id + " " +
start_check_k_line.get_date_str() + "~" +
k_line.get_date_str() + ": " +
str(output_rate) + " day: " +
str(cur_buy_recd.days))
else:
is_during_check = False
elif self.is_match(cur_idx, k_line_list):
is_during_check = True
total_raise_rate = 0.0
total_des_rate = 0.0
start_check_k_line = None
prev_k_line = k_line
if is_during_check and start_check_k_line is not None:
print("has checking stock " + k_line.codeId)
cur_buy_recd = KLineBuyRecd()
cur_buy_recd.code_id = k_line.codeId
cur_buy_recd.buy_date = start_check_k_line.get_date_str()
cur_buy_recd.buy_price = start_check_k_line.open
cur_buy_recd.set_is_holding()
buy_recd_list.append(cur_buy_recd)
return buy_recd_list
def print_progress(self):
str_progress = "{0}/{1}".format(str(self.cur_scode_indx),
str(len(self.scode_list)))
print("\b" * (len(str_progress) * 2), end="")
print(str_progress, end="")