def save_all_data(o_path, s_name, op_file_type, p_dict, f_dict, u_tzz_col,
tzz_len):
temp_df = pd.DataFrame.from_dict(p_dict)
s_df = pd.DataFrame(temp_df.values.T,
index=temp_df.columns,
columns=u_tzz_col)
op_date = time.strftime('%Y%m%d', time.localtime(time.time()))
file_name = s_name + "_" + op_date + op_file_type
save_path = os.path.join(o_path, file_name)
try:
writer = pd.ExcelWriter(save_path)
if os.path.exists(writer.path):
book = openpyxl.load_workbook(writer.path)
writer.book = book
# print(book.sheetnames)
# if sheet_n in writer.book.sheetnames:
# writer.remove(writer[sheet_n])
s_df.to_excel(writer)
writer.save()
writer.close()
print(print_info(), end=" ")
print("Save to the path: {}".format(save_path))
except:
print(print_info("E"), end=" ")
print("Can not save to the path: {}".format(save_path))
return False
font_judge = set_font(f_dict, tzz_len, save_path)
if not font_judge:
return False
return True
def get_base_data(r_path, data_n, sheet_n, col_list, f_type="xlsx", h_no=0):
# 获取同行报价数据，输出代码，询价日期，发行价，高剔价格等信息
file_list = os.listdir(r_path)
data_list = [
file for file in file_list if data_n in file and "~$" not in file
and f_type in file.split(".")[-1]
]
print(print_info(), end=" ")
print("Data List: {}".format(data_list))
if len(data_list) > 0:
data_path = data_list[-1]
try:
base_data = pd.read_excel(os.path.join(r_path, data_path),
sheet_name=sheet_n,
header=h_no)
base_data = base_data[col_list]
base_data.set_index("证券名称", inplace=True)
print(print_info(), end=" ")
print("Base Data Get!")
print(base_data)
except:
print(print_new_info("E", "R"), end=" ")
print("Can not get the base data!")
return False
else:
print(print_info(), end=" ")
print("No column data found!")
return False
return base_data
def set_font(f_dict, tzz_len, excel_name):
# try:
book = openpyxl.load_workbook(excel_name)
sheet = book["Sheet1"]
f_keys = list(f_dict.keys())
f_keys_len = len(f_keys)
for idx in range(1000):
sheet.cell(1, idx + 1).fill = PatternFill(fill_type='solid',
fgColor="F4A460")
for idx, stock in zip(range(f_keys_len), f_keys):
# 对于每一支股票逐行染色
f_stock = f_dict[stock]
for jdx, tzz_item in zip(range(tzz_len), f_stock):
cell = sheet.cell(idx + 2, jdx + 2)
if tzz_item == state_dict["低"]:
cell.font = Font(color="008000")
cell.fill = PatternFill(fill_type='solid', fgColor="00FA9A")
elif tzz_item == state_dict["高"]:
cell.font = Font(color="DC143C")
cell.fill = PatternFill(fill_type='solid', fgColor="FFC0CB")
elif tzz_item == state_dict["无"]:
cell.font = Font(color="696969")
cell.fill = PatternFill(fill_type='solid', fgColor="C0C0C0")
book.save(excel_name)
book.close()
print(print_info(), end=" ")
print("Font set!")
# except:
# print(print_info("E"), end=" ")
# print("Can not set the font")
# return False
return True
def get_race_bar_data():
root_path = os.path.abspath(".")
data_dir = os.path.join(root_path, "raw_data")
output_dir = os.path.join(root_path, "output")
save_name = "注册制同行报价汇总表"
# file_name = ["科创板", "创业板"]
file_name = ["创业板"]
file_type = ".xlsx"
data_name = "同行报价"
sheet_name = "基础数据"
my_comp_name = "上海迎水投资管理有限公司"
df_col = ["股票名称", "询价日", "投资者名称", "申购价格", "备注"]
df_zero = pd.DataFrame(columns=df_col)
for file in file_name:
file_path = find_file_path(data_dir, file, file_type)
if not file_path:
return False
raw_df = get_ns_info_data(file_path, sheet_name)
if type(raw_df) is bool:
return raw_df
if file == file_name[-1]:
raw_df = raw_df[cyb_col]
elif file == file_name[0]:
raw_df = raw_df[kcb_col]
raw_df.columns = df_col
df_zero = pd.concat([df_zero, raw_df])
df_zero.sort_values(by=["询价日", "股票名称"],
inplace=True,
ascending=[False, True])
print(df_zero.head())
tzz_mc, sg_jg = df_col[2], df_col[3]
# 获取全部的股票列表
stock_list = df_zero[df_col[0]].tolist()
union_stock_col = list(set(stock_list))
union_stock_col.sort(key=stock_list.index)
print(print_info(), end=" ")
print("Get the stock list: \n{}".format(union_stock_col))
# 获取无重复全部的投资者
all_tzz_col = df_zero[tzz_mc].tolist()
union_tzz_col = list(set(all_tzz_col))
tzz_col_len = len(union_tzz_col)
print(print_info(), end=" ")
print("Get the tzz list: \n{}".format(union_tzz_col))
race_member_list_copy = race_member_list.copy()
print(race_member_list_copy)
for race_item in race_member_list:
print(race_item + "基金")
count = 0
attribute = 0
for tzz_item in union_tzz_col:
if set(race_item + "基金").issubset(set(tzz_item)):
count += 1
attribute = 1
# elif set(race_item).issubset(set(tzz_item)):
# attribute = 2
# count += 1
print(race_item, count, attribute)
# race_member_list_copy.remove(race_item)
print(race_member_list_copy)
df_dict = dict()
price_dict = dict()
font_dict = dict()
# for stock in union_stock_col:
# print(print_info(), end=" ")
# print("Operator the stock: {}".format(stock))
# df_dict[stock] = df_zero[df_zero[df_col[0]] == stock]
# df_group = get_df_group(df_dict[stock], tzz_mc, sg_jg)
# if type(df_group) is bool:
# return df_group
#
# price_line = list()
# note_line = list()
# for tzz_item in union_tzz_col:
# if tzz_item in df_group.index:
# price_line.append(df_group[df_col[3]][tzz_item])
# note_line.append(get_note(df_dict[stock], tzz_mc, tzz_item, tzz_item, state_dict)[0])
# else:
# price_line.append("")
# note_line.append("")
#
# price_dict[stock] = price_line
# font_dict[stock] = note_line
#
# # 对于单支股票，输出一行报价记录，以及一个用于标记颜色的单条记录
# print(print_info(), end=" ")
# print("Get the price dict:\n{}".format(price_dict))
# print(print_info(), end=" ")
# print("Get the font dict:\n{}".format(font_dict))
#
# TF = save_all_data(output_dir, save_name, file_type, price_dict, font_dict, union_tzz_col, tzz_col_len)
return TF
cell = sheet.cell(idx + 2, jdx + 2)
if tzz_item == state_dict["低"]:
cell.font = Font(color="008000")
cell.fill = PatternFill(fill_type='solid', fgColor="00FA9A")
elif tzz_item == state_dict["高"]:
cell.font = Font(color="DC143C")
cell.fill = PatternFill(fill_type='solid', fgColor="FFC0CB")
elif tzz_item == state_dict["无"]:
cell.font = Font(color="696969")
cell.fill = PatternFill(fill_type='solid', fgColor="C0C0C0")
book.save(excel_name)
book.close()
print(print_info(), end=" ")
print("Font set!")
# except:
# print(print_info("E"), end=" ")
# print("Can not set the font")
# return False
return True
if __name__ == '__main__':
TF = get_race_bar_data()
if TF:
print(print_info("S"), end=" ")
print("Success!")
else:
print(print_info("E"))
print("Error!")
def set_color(c_dict, excel_name, b_len, sheet_name="Sheet1"):
left, right, top, bottom = [Side(style="thin", color="000000")] * 4
try:
book = load_workbook(excel_name)
sheet = book[sheet_name]
c_len = len(list(c_dict.keys()))
# 控制第一列颜色
for col_idx in range(b_len):
cell = sheet.cell(col_idx + 1, 1)
cell.font = Font(color="102B6A", bold=True)
cell.fill = PatternFill(fill_type='solid', fgColor="7BBFEA")
cell.border = Border(left=left,
right=right,
top=top,
bottom=bottom)
cell.alignment = Alignment(horizontal="left",
vertical="center",
wrap_text=True)
for s_idx, s_item in zip(range(1, c_len + 1), c_dict.keys()):
# 循环每只股票
# 设置列宽
sheet.column_dimensions[get_column_letter(s_idx + 1)].width = 30
# 控制表头
for col_idx in range(b_len):
cell = sheet.cell(col_idx + 1, s_idx + 1)
if col_idx == 0:
cell.font = Font(color="102B6A", bold=True)
else:
cell.font = Font(color="102B6A")
cell.fill = PatternFill(fill_type='solid', fgColor="7BBFEA")
cell.border = Border(left=left,
right=right,
top=top,
bottom=bottom)
cell.alignment = Alignment(horizontal="left",
vertical="center",
wrap_text=True)
# 控制具体颜色
r_len = len(c_dict[s_item])
for r_idx, r_item in zip(range(1, r_len + 1), c_dict[s_item]):
# 循环每行, 根据表头行数修改
cell = sheet.cell(r_idx + b_len, s_idx + 1)
cell.font = Font(color=color_base[r_item][0])
cell.fill = PatternFill(fill_type='solid',
fgColor=color_base[r_item][1])
cell.border = Border(left=left,
right=right,
top=top,
bottom=bottom)
cell.alignment = Alignment(horizontal="left",
vertical="center",
wrap_text=True)
book.save(excel_name)
book.close()
print(print_info(), end=" ")
print("Font set!")
except:
print(print_new_info("E", "R"), end=" ")
print("Can not set the font")
return False
return True
def op_history():
root_path = os.path.abspath(".")
data_dir = os.path.join(root_path, "raw_data")
output_dir = os.path.join(root_path, "output")
save_name = "注册制同行报价汇总表"
file_name = ["科创板", "创业板"]
# file_name = ["创业板"]
# file_name = input("请输入新股中文名称：") or "新致软件"
file_type = ".xlsx"
data_name = "同行报价"
sheet_name = "基础数据"
my_comp_name = "上海迎水投资管理有限公司"
df_col = ["股票名称", "询价日", "投资者名称", "申购价格", "备注"]
# col_list = get_col_list(root_path, data_name, "全部", file_type)
# if not col_list:
# return False
df_zero = pd.DataFrame(columns=df_col)
for file in file_name:
file_path = find_file_path(data_dir, file, file_type)
if not file_path:
return False
raw_df = get_ns_info_data(file_path, sheet_name)
if type(raw_df) is bool:
return raw_df
if file == file_name[-1]:
raw_df = raw_df[cyb_col]
elif file == file_name[0]:
raw_df = raw_df[kcb_col]
raw_df.columns = df_col
df_zero = pd.concat([df_zero, raw_df])
df_zero.sort_values(by=["询价日", "股票名称"],
inplace=True,
ascending=[False, True])
print(df_zero.head())
tzz_mc, sg_jg = df_col[2], df_col[3]
# 获取全部的股票列表
stock_list = df_zero[df_col[0]].tolist()
union_stock_col = list(set(stock_list))
union_stock_col.sort(key=stock_list.index)
print(print_info(), end=" ")
print("Get the stock list: \n{}".format(union_stock_col))
# 获取无重复全部的投资者
all_tzz_col = df_zero[tzz_mc].tolist()
union_tzz_col = list(set(all_tzz_col))
tzz_col_len = len(union_tzz_col)
print(print_info(), end=" ")
print("Get the tzz list: \n{}".format(union_tzz_col))
df_dict = dict()
price_dict = dict()
font_dict = dict()
for stock in union_stock_col:
print(print_info(), end=" ")
print("Operator the stock: {}".format(stock))
df_dict[stock] = df_zero[df_zero[df_col[0]] == stock]
df_group = get_df_group(df_dict[stock], tzz_mc, sg_jg)
if type(df_group) is bool:
return df_group
price_line = list()
note_line = list()
for tzz_item in union_tzz_col:
if tzz_item in df_group.index:
price_line.append(df_group[df_col[3]][tzz_item])
note_line.append(
get_note(df_dict[stock], tzz_mc, tzz_item, tzz_item,
state_dict)[0])
else:
price_line.append("")
note_line.append("")
price_dict[stock] = price_line
font_dict[stock] = note_line
# 对于单支股票，输出一行报价记录，以及一个用于标记颜色的单条记录
print(print_info(), end=" ")
print("Get the price dict:\n{}".format(price_dict))
print(print_info(), end=" ")
print("Get the font dict:\n{}".format(font_dict))
TF = save_all_data(output_dir, save_name, file_type, price_dict, font_dict,
union_tzz_col, tzz_col_len)
return TF
def output_all_df(f_path, tzz_mc, raw_df, df_group, tzz_list, col_list,
col_temp, ipo_n, ipo_c):
df_output = pd.DataFrame(columns=col_temp)
desc_list = list()
for item in col_list:
output_item = dict()
output_item[col_temp[0]] = item
output_item[col_temp[1]] = ""
output_item[col_temp[3]] = ""
item = item.rstrip(string.digits)
item_set = set("".join(item))
idx, p = 0, 0
for tzz_item in tzz_list:
tzz_item_set = set("".join(tzz_item))
if item_set.issubset(tzz_item_set):
idx += 1
p = df_group.loc[tzz_item][0]
output_item[col_temp[1]] = tzz_item
output_item[col_temp[3]], desc_item = get_note(
raw_df, tzz_mc, item, tzz_item, state_dict)
if idx > 1:
idx = 0
for tzz_item in tzz_list:
if item in tzz_item:
idx += 1
p = df_group.loc[tzz_item][0]
output_item[col_temp[1]] = tzz_item
output_item[col_temp[3]], desc_item = get_note(
raw_df, tzz_mc, item, tzz_item, state_dict)
if p == 0:
if item == "证券名称":
# zq_name = os.path.split(f_path)[-1].split(".")[0]
# if len(zq_name) <= 4:
# output_item[col_temp[2]] = zq_name
# else:
# output_item[col_temp[2]] = zq_name[:4]
output_item[col_temp[2]] = ipo_n
elif item == "证券代码":
output_item[col_temp[2]] = ipo_c
elif item == "询价日期":
if ipo_c != "":
data = w.wsd(ipo_c, "sec_name,ipo_inq_enddate", "ED-1TD",
datetime.now().strftime("%Y-%m-%d"))
output_item[col_temp[2]] = data.Data[-1][0].strftime(
"%Y-%m-%d")
elif item == "我司报价":
output_item[col_temp[1]] = "上海迎水投资管理有限公司"
for tzz_item in tzz_list:
if "迎水" in tzz_item:
output_item[col_temp[2]] = df_group.loc[tzz_item][0]
else:
output_item[col_temp[2]] = ""
df_output = df_output.append(output_item, ignore_index=True)
else:
if desc_item != "":
desc_list.append(desc_item)
output_item[col_temp[2]] = p
df_output = df_output.append(output_item, ignore_index=True)
desc_note = "；".join(desc_list)
print(print_info(), end=" ")
print("Note: {}".format(desc_note))
df_output.set_index(col_temp[0], inplace=True)
# 删除全称列
df_output.drop(columns=col_temp[1], inplace=True)
df_note = pd.DataFrame(df_output.values.T,
index=df_output.columns,
columns=df_output.index)
# 查看我司的备注
if "上海迎水投资管理有限公司" in df_note.columns:
df_note["我司报价"]["备注"] = df_note["上海迎水投资管理有限公司"]["备注"]
print(print_info(), end=" ")
print("我司备注：{}".format(df_note["我司报价"]["备注"]))
# 删除我司全称列
df_note.drop(labels="上海迎水投资管理有限公司", inplace=True, axis=1)
else:
df_note["我司报价"]["备注"] = ""
return df_note