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()