def real_time_data_bowl(file_full_name):
result_data = filetools.read_workbook(file_full_name, "", REAL_TIME_DATA_SHEETS)
asset_detail_data = filetools.data_clean_in_one_sheet(result_data, REAL_TIME_DATA_SHEET_NAME, row_start_num=1,
field_row_num=0, axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
asset_detail_data = asset_detail_data[asset_detail_data[BASE_COLUMN_ASSET_NAME] != ""]
return asset_detail_data
def calc_all_dv10(file_full_name, dv10_file_full_name, key_years=KEY_YEARS):
# 读取所有sheet页的数据
dv10 = pd.DataFrame(columns=KEY_YEARS)
cash_flow_data = filetools.read_workbook(file_full_name)
for sheet_name in cash_flow_data:
if (len(cash_flow_data.get(sheet_name)) > 5) and (len(cash_flow_data.get(sheet_name)[0]) > 9) and\
(cash_flow_data.get(sheet_name)[3][7] == '最终估值') and \
(cash_flow_data.get(sheet_name)[3][8] != '#VALUE!') and \
(cash_flow_data.get(sheet_name)[3][8] != '#N/A'):
sheet_data = pd.DataFrame(cash_flow_data.get(sheet_name)[CASH_FLOW_START_ROW:])
# sheet_data = sheet_data.dropna(axis=0, how='any', thresh=DROP_NA_THRESH)
# sheet_data = sheet_data.dropna(axis=1, how='any', thresh=DROP_NA_THRESH)
sheet_data = sheet_data.loc[:, [2, 4, 7, 8]].rename(
columns={2: CASH_FLOW_STR, 4: TIME_LIMIT_STR, 7: DISCOUNT_STR, 8: DISCOUNT_CASH_FLOW_STR})
sheet_data = sheet_data[sheet_data[TIME_LIMIT_STR] > 0]
dv10 = dv10.append(calc_dv10_from_cash_flow(sheet_name, sheet_data, key_years))
else:
continue
# 遍历并筛选有效的sheet页进行计算，每个页的有效数据从第七行开始
# 读取现金流期限、金额、折现率数据
# 按照KEY YEARS进行DV10计算，每个KEY YEAR节点上下浮动时，只影响相邻节点，且为线性关系
# 将结果保存至新的pd，column为KEY_YEARS，row id 为品种名称
# 将dv10的pd保存至dv10sheet，插入至最前列
# print(dv10)
dv10.fillna(value=0.0)
dv10.to_excel(dv10_file_full_name, 'dv10')
def get_tb_mi_matrix(
file_path=r'\\10.145.39.42\北京总部文件资料\资产管理部\投资收益月度核对\TB-MI-MATRIX.xlsx'):
org_data = ft.read_workbook(file_path, "", TB_MI_MATRIX_SHEET_NAME_LIST)
matrix_df = ft.data_clean_in_one_sheet(org_data,
TB_MI_MATRIX_SHEET_NAME,
axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
return matrix_df
def read_trade_data():
# get original trade date
original_trade_data = filetools.read_workbook(org_file_name, org_file_path, org_data_sheets)
# deal with the data
trade_data_df = pd.DataFrame(original_trade_data.get(org_data_sheet_name)[1:],
columns=original_trade_data.get(org_data_sheet_name)[0])
trade_data_df = trade_data_df.dropna(axis=0, how='any', thresh=5)
trade_data_df = trade_data_df.dropna(axis=1, how='any', thresh=5)
trade_data_df = trade_data_df.fillna(value="")
return trade_data_df
def get_basic_info(file_name=BASIC_DATA_FILE_NAME,
file_path=BASIC_DATA_FILE_PATH,
sheet_name=BASIC_DATA_SHEET_NAME):
file_path = file_path + file_name
basic_info = ft.read_workbook(file_path, "", BASIC_DATA_SHEETS)
basic_info_df = ft.data_clean_in_one_sheet(basic_info,
sheet_name,
row_start_num=2,
field_row_num=1,
axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
return basic_info_df
def get_tb_info(
file_path=r'\\10.145.39.42\北京总部文件资料\资产管理部\投资收益月度核对\TB文件\TB1710-XUN.xlsx',
sheet_name=TB_INFO_SHEET_NAME):
org_data = ft.read_workbook(file_path, "", TB_INFO_SHEET_NAME_LIST)
tb_info_df = ft.data_clean_in_one_sheet(org_data,
sheet_name,
axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
if tb_info_df.columns.values.tolist().count(TB_ACCOUNT) > 1:
tb_info_df.columns = [
'科目DEL', TB_ACCOUNT_NAME, '科目说明', TB_COMPANY, TB_ACCOUNT, '渠道',
'部门', '险种', '往来', TB_DESC, '备用', TB_START_AMOUNT,
TB_DURATION_AMOUNT, TB_BALANCE
]
return tb_info_df
def org_data_bowl(rp_date):
file_full_name = guess_file_full_name(rp_date)
print("copy data from：%s" % file_full_name)
trade_data = filetools.read_workbook(file_full_name, "", BASE_DATA_SHEETS)
# 基金持仓处理
fund_asset_data = filetools.data_clean_in_one_sheet(trade_data, FUND_ASSET_SHEET_NAME, row_start_num=2,
field_row_num=1, axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
fund_asset_data_after_clean = fund_asset_data_clean(fund_asset_data)
# 股票持仓处理
stock_asset_data = filetools.data_clean_in_one_sheet(trade_data, STOCK_ASSET_SHEET_NAME, row_start_num=2,
field_row_num=1, axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
stock_asset_data_after_clean = stock_asset_data_clean(stock_asset_data)
# 债券持仓处理
bond_asset_data = filetools.data_clean_in_one_sheet(trade_data, BOND_ASSET_SHEET_NAME, row_start_num=2,
field_row_num=1, axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
bond_asset_data_after_clean = bond_asset_data_clean(bond_asset_data)
# 存款持仓处理
deposit_asset_data = filetools.data_clean_in_one_sheet(trade_data, DEPOSIT_ASSET_SHEET_NAME, row_start_num=2,
field_row_num=1, axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
deposit_asset_data_after_clean = deposit_asset_data_clean(deposit_asset_data)
# 另类持仓处理
non_standard_asset_data = filetools.data_clean_in_one_sheet(trade_data, NON_STANDARD_ASSET_SHEET_NAME,
row_start_num=2,
field_row_num=1, axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
non_standard_asset_data_after_clean = non_standard_asset_data_clean(non_standard_asset_data)
# 货币市场持仓处理
money_market_asset_data = filetools.data_clean_in_one_sheet(trade_data, MONEY_MARKET_ASSET_SHEET_NAME,
row_start_num=2,
field_row_num=1, axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
money_market_asset_data_after_clean = money_market_asset_data_clean(money_market_asset_data)
# 逆回购资产获取，TODO 由于货币市场中未包含回购，故从全资产明细中获取
all_asset_data = filetools.data_clean_in_one_sheet(trade_data, ALL_ASSET_DETAIL_SHEET_NAME,
row_start_num=2,
field_row_num=1, axis_one_drop_na_flag=False,
axis_zero_drop_na_flag=False)
reverse_repo_from_all_asset_after_clean = show_me_the_money(all_asset_data, REVERSE_REPO_FLAG_IN_ALL_ASSET)
# 投连持仓处理 TODO 剔除投连资产
# tou_lian_asset_data = filetools.data_clean_in_one_sheet(trade_data, TOU_LIAN_ASSET_SHEET_NAME,
# row_start_num=2,
# field_row_num=1, axis_one_drop_na_flag=False,
# axis_zero_drop_na_flag=False)
# tou_lian_asset_data_after_clean = tou_lian_asset_data_clean(tou_lian_asset_data)
all_clean_asset_data = pd.concat([fund_asset_data_after_clean, stock_asset_data_after_clean,
bond_asset_data_after_clean, deposit_asset_data_after_clean,
non_standard_asset_data_after_clean, money_market_asset_data_after_clean,
reverse_repo_from_all_asset_after_clean])
return all_clean_asset_data