def generate_excel_history(self, end_date):
""" 大股东增减持历史信息 2017年至今 """
# 一段时间内增减持额时间序列
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
date_series = Date().get_trade_date_series("20150101", end_date, "M")
result = pd.DataFrame([])
ed_date = end_date
for i in range(len(date_series) - 1):
bg_date = Date().get_trade_date_offset(ed_date, -20)
print("Major Holder Deal %s %s" % (bg_date, ed_date))
result_add = self.major_holder_deal_period_sum(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
filename = os.path.join(sub_path, '重要股东二级市场交易历史.xlsx')
excel = WriteExcel(filename)
sheet_name = "读写EXCEL测试"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["增减持金额(亿)", "沪深300"],
chart_name="历史每月大股东增减持总和",
insert_pos="O2",
cat_beg="D2",
cat_end="D40",
val_beg_list=["E2", "G2"],
val_end_list=["E40", "G40"])
excel.close()
def generate_excel(self, end_date):
""" ETF净申购 输出到Excel """
beg_date = Date().get_trade_date_offset(end_date, -120)
beg_1m_date = Date().get_trade_date_offset(end_date, -20)
# 一段时间内增减持额时间序列
date_series = Date().get_trade_date_series(beg_date, end_date, "W")
result = pd.DataFrame([])
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
ed_date = end_date
for i in range(len(date_series) - 1):
bg_date = Date().get_trade_date_offset(ed_date, -5)
print("ETF Fund %s %s" % (bg_date, ed_date))
result_add = self.get_etf_data_period_sum(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 不同类型ETF基金流入流出
fund_type = self.get_etf_data_period_type(beg_1m_date, end_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, 'ETF流入.xlsx')
excel = WriteExcel(filename)
sheet_name = "ETF流入"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["净申购金额", '沪深300'],
chart_name="每周ETF净申购金额（亿元）",
insert_pos="F8",
cat_beg="B2",
cat_end="B25",
val_beg_list=["D2", "F2"],
val_end_list=["D25", "F25"])
num_format_pd = pd.DataFrame([],
columns=fund_type.columns,
index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(fund_type,
worksheet,
begin_row_number=0,
begin_col_number=15,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["净申购占比", '净申购金额'],
chart_name="规模前10类ETF基金最近1月净流入",
insert_pos="F24",
cat_beg="P2",
cat_end="P11",
val_beg_list=["S2", "R2"],
val_end_list=["S11", "R11"])
excel.close()
def generate_excel(self, end_date):
""" 陆股通信息 输出到Excel """
beg_date = Date().get_trade_date_offset(end_date, -60)
beg_1m_date = Date().get_trade_date_offset(end_date, -20)
beg_1m_date = "20190201"
# 一段时间内增减持额时间序列
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
date_series = Date().get_trade_date_series(beg_date, end_date, "W")
result = pd.DataFrame([])
ed_date = end_date
for i in range(len(date_series)-1):
bg_date = Date().get_trade_date_offset(ed_date, -5)
print("Hk Inflow Period %s %s" % (bg_date, ed_date))
result_add = self.hk_inflow_period(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 最近一个月平均持股金额最大、最小的几个股票
stock = self.hk_inflow_period_stock(beg_1m_date, end_date)
# 最近一个月平均持股金额行业排序
industry = self.hk_inflow_period_industry(beg_1m_date, end_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '融资融券情况春节后.xlsx')
print(filename)
excel = WriteExcel(filename)
sheet_name = "融资融券情况"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([], columns=result.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(result, worksheet, begin_row_number=0, begin_col_number=1,
num_format_pd=num_format_pd, color="orange", fillna=True)
excel.chart_columns_plot(worksheet, sheet_name=sheet_name,
series_name=["净流入金额", '沪深300'],
chart_name="最近3个月每周融资余额净买入金额(亿元)",
insert_pos="I15", cat_beg="B2", cat_end="B13",
val_beg_list=["F2", "H2"], val_end_list=["F13", "H13"])
num_format_pd = pd.DataFrame([], columns=stock.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(stock, worksheet, begin_row_number=0, begin_col_number=8,
num_format_pd=num_format_pd, color="orange", fillna=True)
num_format_pd = pd.DataFrame([], columns=industry.columns, index=['format'])
num_format_pd.loc['format', :] = '0.00'
num_format_pd.loc['format', "净流入占比"] = '0.00%'
excel.write_pandas(industry, worksheet, begin_row_number=0, begin_col_number=15,
num_format_pd=num_format_pd, color="orange", fillna=True)
excel.chart_columns_plot(worksheet, sheet_name=sheet_name,
series_name=["净流入占比", '净流入金额'],
chart_name="行业最近1月融资净买入金额",
insert_pos="I32", cat_beg="P2", cat_end="P30",
val_beg_list=["U2", "S2"], val_end_list=["U30", "S30"])
excel.close()
def generate_excel(self, end_date):
""" 大股东增减持信息 输出到EXcel"""
beg_date = Date().get_trade_date_offset(end_date, -60)
beg_1m_date = Date().get_trade_date_offset(end_date, -20)
# 一段时间内增减持额时间序列
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
date_series = Date().get_trade_date_series(beg_date, end_date, "W")
result = pd.DataFrame([])
ed_date = end_date
for i in range(len(date_series) - 1):
bg_date = Date().get_trade_date_offset(ed_date, -5)
print("Major Holder Deal %s %s" % (bg_date, ed_date))
result_add = self.major_holder_deal_period_sum(bg_date, ed_date)
result_add.loc[ed_date, '沪深300'] = index_data.loc[ed_date, "CLOSE"]
result = pd.concat([result, result_add], axis=0)
ed_date = bg_date
result = result.sort_index()
# 最近一个月增减持额最大的几个股票
stock = self.major_holder_deal_period_stock(beg_1m_date, end_date)
# 最近一个月增减持额行业排序
industry = self.major_holder_deal_period_industry(
beg_1m_date, end_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '重要股东二级市场交易.xlsx')
excel = WriteExcel(filename)
sheet_name = "读写EXCEL测试"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["增减持金额(亿)", "沪深300"],
chart_name="最近3个月每周大股东增减持总和",
insert_pos="O2",
cat_beg="D2",
cat_end="D14",
val_beg_list=["E2", "G2"],
val_end_list=["E14", "G14"])
num_format_pd = pd.DataFrame([],
columns=stock.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel.write_pandas(stock,
worksheet,
begin_row_number=0,
begin_col_number=8,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
num_format_pd = pd.DataFrame([],
columns=industry.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
excel.write_pandas(industry,
worksheet,
begin_row_number=0,
begin_col_number=11,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["行业分布"],
chart_name="行业最近1月增减持金额",
insert_pos="O16",
cat_beg="L2",
cat_end="L30",
val_beg_list=["M2"],
val_end_list=["M30"])
excel.close()
def generate_excel(self, end_date, quarter_date, quarter_last_date):
""" 陆股通信息 输出到Excel """
beg_date = Date().get_trade_date_offset(end_date, -400)
quarter_date_series = Date().get_normal_date_series(
beg_date, end_date, "Q")
# 最近4个季度重仓持股金额
from quant.stock.index import Index
index_data = Index().get_index_factor(index_code="000300.SH")
result = pd.DataFrame()
for i in range(len(quarter_date_series) - 1):
quarter_last_date_temp = quarter_date_series[i]
quarter_date_temp = quarter_date_series[i + 1]
add = self.fund_holder_quarter_sum(quarter_date_temp,
quarter_last_date_temp)
quarter_trade_date = Date().get_trade_date_offset(
quarter_date_temp, 0)
add.loc[quarter_date_temp,
'沪深300'] = index_data.loc[quarter_trade_date, "CLOSE"]
result = pd.concat([result, add], axis=0)
# 最近一个月平均持股金额最大、最小的几个股票
stock = self.hk_inflow_period_stock(quarter_date, quarter_last_date)
# 最近一个月平均持股金额行业排序
industry = self.fund_holder_quarter_industry(quarter_date,
quarter_last_date)
# 数据存贮位置
sub_path = os.path.join(self.data_path, end_date)
if not os.path.exists(sub_path):
os.makedirs(sub_path)
# 信息写入文件
filename = os.path.join(sub_path, '基金重仓季报持股.xlsx')
excel = WriteExcel(filename)
sheet_name = "基金重仓季报持股"
worksheet = excel.add_worksheet(sheet_name)
num_format_pd = pd.DataFrame([],
columns=result.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', "净流入占比"] = '0.00%'
excel.write_pandas(result,
worksheet,
begin_row_number=0,
begin_col_number=1,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["季度流入金额", '沪深300'],
chart_name="基金季报重仓持股净流入金额(亿元)",
insert_pos="B10",
cat_beg="B2",
cat_end="B6",
val_beg_list=["E2", "G2"],
val_end_list=["E6", "G6"])
num_format_pd = pd.DataFrame([],
columns=stock.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', "净流入占比"] = '0.00%'
excel.write_pandas(stock,
worksheet,
begin_row_number=0,
begin_col_number=8,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
num_format_pd = pd.DataFrame([],
columns=industry.columns,
index=['format'])
num_format_pd.ix['format', :] = '0.00'
num_format_pd.ix['format', "净流入占比"] = '0.00%'
excel.write_pandas(industry,
worksheet,
begin_row_number=0,
begin_col_number=15,
num_format_pd=num_format_pd,
color="orange",
fillna=True)
excel.chart_columns_plot(worksheet,
sheet_name=sheet_name,
series_name=["季度流入金额", "季度流入占比"],
chart_name="基金季报重仓行业净流入金额(亿元)",
insert_pos="B26",
cat_beg="P2",
cat_end="P30",
val_beg_list=["S2", "T2"],
val_end_list=["S30", "T30"])
excel.close()