def StockAnalysisModel():
sql = '''
select
*
from stock.stock_multifactor_score_evaluation as s
'''
stocklist = mc.run(sql)
stocklist['income_cagr'] = stocklist['income_growth_rate'].apply(
lambda x: np.real(pow(x, 1 / years)) - 1)
stocklist['profit_cagr'] = stocklist['netprofit_growth_rate'].apply(
lambda x: np.real(pow(x, 1 / years)) - 1)
stocklist['eps_cagr'] = stocklist['eps_growth_rate'].apply(
lambda x: np.real(pow(x, 1 / years)) - 1)
cagr_pcent = 50
# income_cagr_pcent = np.percentile(stocklist['income_cagr'], cagr_pcent)
# profit_cagr_pcent = np.percentile(stocklist['profit_cagr'], cagr_pcent)
eps_cagr_pcent = np.percentile(stocklist['eps_cagr'], cagr_pcent)
# case1 = stocklist.income_cagr > income_cagr_pcent
# case2 = stocklist.profit_cagr > profit_cagr_pcent
case3 = stocklist.eps_cagr >= eps_cagr_pcent
case4 = stocklist.eps_cagr > 0
stocklist_filter = stocklist.loc[(case3 & case4)]
print(stocklist_filter.iloc[-1, :])
ef.format_excel_file(
stocklist_filter,
path=stock_path + r'stock_multifactor_analysis_model.xlsx',
sheet_name='multifactor',
)
return stocklist_filter
def PercentileRank(self, stock_list):
# 每股收益变异系数，作为筛选条件
basic_eps_cv30 = round(np.percentile(stock_list['basic_eps_cv'], 30),
2)
basic_eps_cv70 = round(np.percentile(stock_list['basic_eps_cv'], 70),
2)
# 每股收益平均值，作为筛选条件
basic_eps_avg50 = round(np.percentile(stock_list['basic_eps_avg'], 50),
2)
print('三个重要参数：')
print(basic_eps_cv30, basic_eps_cv70, basic_eps_avg50, '\n')
case1 = stock_list.basic_eps_cv >= basic_eps_cv30
case2 = stock_list.basic_eps_cv <= basic_eps_cv70
case3 = stock_list.basic_eps_avg >= basic_eps_avg50
# 变异系数不能太小，太小意味着没有增长
# 变异系数不能太大，太大意味着增长不稳定
stock_list_filter = stock_list.loc[case1 & case2 & case3]
print(f'筛选获得{len(stock_list_filter)}只股票')
ef.format_excel_file(stock_list_filter,
path=stock_path + r'stock_percentile_list.xlsx',
sheet_name='factor')
stock_list_filter.head()
return stock_list_filter
def SW_MonthlyReport():
sql = mc.get_sql_text(sql_path + r'sw_sector_index_chg/monthly.sql')
sw_sector_monthly = mc.run(sql)
sw_sector_monthly.iloc[:, 4:] = sw_sector_monthly.iloc[:, 4:].applymap(
numeric_format)
ef.format_excel_file(sw_sector_monthly,
path=sw_sector_path + 'sw_sector_monthly.xlsx',
sheet_name='sector_monthly')
sw_sector_monthly.head()
def SW_WeeklyReport():
sql = mc.get_sql_text(sql_path + r'sw_sector_index_chg/weekly_page.sql')
wk_report = mc.run(sql)
# 将文本转化为数字，注意列的位置，日期回转换为空值
# wk_report.iloc[:, 4:] = wk_report.iloc[:, 4:].applymap(numeric_format)
ef.format_excel_file(wk_report,
path=sw_sector_path + 'sw_sector_weekly.xlsx',
sheet_name='sector_weekly')
print(wk_report.head())
def RawStocklist(self, sw_indexname):
# 查询申万一级行业股票池
f_sql = mc.get_sql_text(
sql_path +
r'stock_basic_financial_report_research_weight_by_sw_indexname.sql'
)
sql = f_sql.format(sw_indexname)
stock_list = mc.run(sql)
# stock_list.fillna(0, inplace=True)
print()
print(f'一共获得{len(stock_list)}只股票')
# stock_list.dropna(how='any', inplace=True)
ef.format_excel_file(stock_list,
path=stock_path + r'stock_raw_list.xlsx',
sheet_name='stock')
pprint(stock_list.head(5))
return stock_list