def get_records(self):
# 整理数据
df_raw = self.df_raw_record_list.copy()
# 基本上，证券名称为 " " 的记录，不是银行存取款，就是看也看不懂的。忽略吧
df = df_raw[~(df_raw['证券名称'] == ' ')]
# 货币基金
df_money_fund = df[df['证券名称'].isin(['银华日利', '紫金货币', '天天发1', 'GC007', 'Ｒ-001', 'GC001', 'Ｒ-003', '现金添富', '华宝添益', '添富快线'])]
# 非货币基金
df = df[~df.index.isin(df_money_fund.index)]
# df[df['业务名称'].isin(['开放基金赎回返款', '开放基金赎回','股息入帐'])]
df = df.reset_index(drop=True)
df['id'] = df.index + 1
df['date'] = df['发生日期']
df['time'] = '9:30:00'
df['code'] = df['证券代码']
df['name'] = df['证券名称']
df['deal_type'] = df['业务名称'].apply(lambda x: self.deal_type_calc(x))
df['nav_unit'] = df['成交价格']
df['nav_acc'] = df['成交价格']
df['volume'] = np.abs(df['成交数量'])
df['fee'] = df['佣金']
df['occur_money'] = df.apply(self.occur_money_calc, axis=1)
df['deal_money'] = df.apply(self.deal_money_calc, axis=1)
df['account'] = '华泰'
df['unique_id'] = df.apply(self.unique_id_calc, axis=1)
df['note'] = '无'
# 补充一二三级分类
df = pd.merge(df, self.cm.df_category, left_on='证券代码', right_on='基金代码', how='left')
df = df.rename(columns={'一级分类': 'category1', '二级分类': 'category2', '三级分类': 'category3', '分类ID': 'category_id'})
#
df = df[record_keys()]
self.df_results = df.copy()
pass
def get_records(self):
# 整理数据
# 799999 是创建账户，忽略
ignore_codes = ['799999']
df = self.df_raw_record_list[~(self.df_raw_record_list['证券代码'].isin(ignore_codes))]
df = df.reset_index()
df['id'] = df.index + 1
df['date'] = df['成交日期'].apply(lambda x: x[0:4]+'-'+x[4:6]+'-'+x[6:8])
df['nav_acc'] = df['成交价格']
df['account'] = '华宝'
df['unique_id'] = df['委托编号']
df['note'] = '无'
df['fee'] = df['佣金'] + df['印花税'] + df['过户费'] + df['成交费']
# '基金申购','托管转入' 都视为买入操作
df = df.replace(['基金申购','托管转入'], ['买入','买入'])
df['occur_money'] = df.apply(self.occur_money_calc, axis=1)
# 补充一二三级分类
df = pd.merge(df, self.cm.df_category, left_on='证券代码', right_on='基金代码', how='left')
df = df.rename(columns={'成交时间':'time', '证券代码':'code', '证券名称':'name',
'委托类别':'deal_type', '成交价格': 'nav_unit', '成交数量': 'volume',
'发生金额': 'deal_money', '成本总计': 'fee', '一级分类': 'category1',
'二级分类': 'category2', '三级分类': 'category3', '分类ID': 'category_id',
'备注': 'note'})
# 只要需要列
df = df[record_keys()]
self.df_results = df.copy()
def get_records(self):
# 读取逻辑过于复杂，自己补充的交易记录（分红、转换）
self.input_file_name = '{0}_addition.json'.format(self.user_name)
df_addtion_record = None
with open(self.input_file, 'r', encoding='utf-8') as f:
df_addtion_record = pd.DataFrame([(pd.Series(x))
for x in json.loads(f.read())])
# 每一条成交记录
# 所有的操作 ['盈米宝购买', '赎回', '赎回到盈米宝', '场外转托管']
df = self.df_raw_record_list.copy()
df['id'] = self.df_raw_record_list.reset_index().index + 1
# TODO 15 点逻辑
df['date'] = df['acceptTime'].apply(lambda x: str(x)[0:10])
df['time'] = df['acceptTime'].apply(lambda x: str(x).split(' ')[1])
df['code'] = df['fund'].apply(
lambda x: json.loads(str(x).replace('\'', '\"'))['fundCode'])
df['name'] = df['fund'].apply(
lambda x: json.loads(str(x).replace('\'', '\"'))['fundName'])
df['deal_type'] = df.apply(self.deal_type_calc, axis=1)
df['volume'] = df['uiShare']
df['fee'] = df['fee']
df['nav_unit'] = df['nav']
df['nav_acc'] = df['nav']
df['deal_money'] = df.apply(self.deal_money_calc, axis=1)
df['occur_money'] = df.apply(self.occur_money_calc, axis=1)
df['account'] = '{0}_且慢'.format(self.user_name)
# 补充额外记录
if len(df_addtion_record) > 0:
df = pd.concat([df, df_addtion_record], sort=False)
# 补充一二三级分类
df = pd.merge(df,
self.cm.df_category,
left_on='code',
right_on='基金代码',
how='left')
df = df.rename(
columns={
'一级分类': 'category1',
'二级分类': 'category2',
'三级分类': 'category3',
'分类ID': 'category_id'
})
df['unique_id'] = df.apply(self.unique_id_calc, axis=1)
# 用三级分类表中的名称统一各大基金 APP 中的名称
df['name'] = df['基金名称']
df['note'] = df.apply(self.note_calc, axis=1)
df = df.sort_values(['date', 'time'])
df = df.reset_index()
df['id'] = df.index + 1
df = df[record_keys()]
self.df_results = df.copy()
pass
def handle_fund_trades(self, order_ids):
"""
处理单只基金的买入卖出操作
"""
# 单只基金无法在 order 页面查看手续费，需要进一步请求
# 并发
fund_trade_url = 'https://danjuanapp.com/djapi/fund/order/{0}'
tasks = [
grequests.get(fund_trade_url.format(x), headers=self.headers)
for x in order_ids
]
response_list = grequests.map(tasks)
results = []
i = 1
for response in response_list:
order = response.json()['data']
s = dict()
s['id'] = i
# TODO 这里要判断 15 点前还是 15 点后
s['date'] = time.strftime(
'%Y-%m-%d %H:%M:%S',
time.localtime(int(order['created_at']) / 1000))[0:10]
s['time'] = '14:59:00'
s['code'] = order['fd_code']
s['name'] = order['fd_name']
s['deal_type'] = order['action_desc']
s['volume'] = order['confirm_volume']
s['deal_money'] = order['confirm_amount']
confirm_infos = order['confirm_infos']
fee = 0.0
nav_unit = 0.0
if len(confirm_infos) > 0:
infos = confirm_infos[0]
if len(infos) > 0:
for i in range(len(infos) - 1, 0, -1):
info = infos[i]
if u'手续费' in info:
# 手续费,0.04元
feeStr = info.replace('手续费,', '').replace('元', '')
fee = round(float(feeStr), 2)
if u'确认净值' in info:
navStr = info.replace('确认净值,', '').replace('元', '')
nav_unit = round(float(navStr), 4)
s['nav_unit'] = nav_unit
s['nav_acc'] = nav_unit
s['fee'] = fee
opType = s['deal_type']
if opType == '买入' or opType == '转换':
occur_money = round(s['deal_money'] + fee, 2)
elif opType == '卖出':
occur_money = round(s['deal_money'] - fee, 2)
elif opType == '分红':
occur_money = round(s['deal_money'], 2)
s['occur_money'] = occur_money
s['account'] = '{0}_蛋卷'.format(self.user_name)
s['unique_id'] = order['order_id']
# s['note'] = 螺丝钉指数基金组合_买入_orderid=2198590579952573614
s['note'] = order['fd_name'] + '_' + order[
'action_desc'] + '_orderid=' + order['order_id']
results.append(pd.Series(s))
i += 1
df_fund_trades = pd.DataFrame(results)
# 补充三级分类
df_fund_trades = pd.merge(df_fund_trades,
self.cm.df_category,
left_on='code',
right_on='基金代码',
how='left')
df_fund_trades = df_fund_trades.rename(
columns={
'一级分类': 'category1',
'二级分类': 'category2',
'三级分类': 'category3',
'分类ID': 'category_id'
})
df_fund_trades = df_fund_trades[record_keys()]
return df_fund_trades
def get_records(self):
# 产出清洗过后的数据
# ['买入', '卖出', '分红', '转换', '组合转出', '组合转入']
# df_raw.action_desc.unique()
df_raw = self.df_raw_record_list.copy()
# 货币基金转换
df_money_fund_exchange = df_raw[df_raw.action_desc.isin(['转换'])]
# 投资组合内部转换
df_plan_exchange = df_raw[df_raw.action_desc.isin(['组合转出', '组合转入'])]
# 正常买入卖出交易
df_normal = df_raw[df_raw.action_desc.isin(['买入', '卖出', '分红'])]
# 调整输出
# 买入、卖出、分红
df_temp = df_normal.copy()
# print(len(df_temp))
df_temp['id'] = df_normal.reset_index().index + 1
df_temp['date'] = df_temp['ts'].apply(lambda x: str(x)[0:10])
df_temp['time'] = '14:59:00'
df_temp['code'] = df_temp['fd_code']
df_temp['name'] = df_temp['fd_name']
df_temp['deal_type'] = df_temp['action_desc']
df_temp['volume'] = df_temp['confirm_volume']
df_temp['fee'] = df_temp['fee']
df_temp['nav_unit'] = df_temp.apply(self.nav_unit_calc, axis=1)
df_temp['nav_acc'] = df_temp['nav_unit']
df_temp['deal_money'] = df_temp['confirm_amount']
df_temp['occur_money'] = df_temp.apply(self.occur_money_calc, axis=1)
df_temp['account'] = '{0}_蛋卷'.format(self.user_name)
# # 补充一二三级分类
df_temp = pd.merge(df_temp,
self.cm.df_category,
left_on='fd_code',
right_on='基金代码',
how='left')
df_temp = df_temp.rename(
columns={
'一级分类': 'category1',
'二级分类': 'category2',
'三级分类': 'category3',
'分类ID': 'category_id'
})
df_temp['unique_id'] = df_temp['order_id']
df_temp['note'] = df_temp['plan_name'] + '_' + df_temp[
'deal_type'] + '_orderid=' + df_temp['order_id']
df_temp = df_temp[record_keys()]
# df_temp.to_excel('04_{0}_normal_record_list.xlsx'.format(self.user_name), sheet_name=f'{self.user_name}_交易记录')
# 货币基金转换
df_money_temp = df_money_fund_exchange.copy()
# print(len(df_money_temp))
if len(df_money_temp) > 0:
df_money_temp['id'] = df_money_temp.reset_index().index + 1
df_money_temp['date'] = df_money_temp['ts'].apply(
lambda x: str(x)[0:10])
df_money_temp['time'] = '14:59:00'
df_money_temp['code'] = df_money_temp['target_fd_code']
df_money_temp['name'] = df_money_temp['target_fd_name']
# 目前的基金转换，我只做过南方天天利货币B转指数基金的操作，以后将尽量避免这种繁琐的交易记录
df_money_temp['action_desc'] = '买入'
df_money_temp['deal_type'] = df_money_temp['action_desc']
df_money_temp['volume'] = np.abs(df_money_temp['confirm_volume'])
df_money_temp['fee'] = df_money_temp['fee']
df_money_temp['nav_unit'] = df_money_temp.apply(self.nav_unit_calc,
axis=1)
df_money_temp['nav_acc'] = df_money_temp['nav_unit']
df_money_temp['deal_money'] = df_money_temp['confirm_amount']
df_money_temp['occur_money'] = df_money_temp.apply(
self.occur_money_calc, axis=1)
df_money_temp['account'] = '{0}_蛋卷'.format(self.user_name)
# # 补充一二三级分类
df_money_temp = pd.merge(df_money_temp,
self.cm.df_category,
left_on='target_fd_code',
right_on='基金代码',
how='left')
df_money_temp = df_money_temp.rename(
columns={
'一级分类': 'category1',
'二级分类': 'category2',
'三级分类': 'category3',
'分类ID': 'category_id'
})
df_money_temp['unique_id'] = df_money_temp['order_id']
df_money_temp[
'note'] = df_money_temp['fd_name'] + '_转换_' + df_money_temp[
'target_fd_name'] + '_orderid=' + df_money_temp['order_id']
df_money_temp = df_money_temp[record_keys()]
# 组合内部转换
df_plan_exchange_temp = df_plan_exchange.copy()
# print(len(df_plan_exchange_temp))
df_plan_exchange_temp['id'] = df_plan_exchange_temp.reset_index(
).index + 1
df_plan_exchange_temp['date'] = df_plan_exchange_temp['ts'].apply(
lambda x: str(x)[0:10])
df_plan_exchange_temp['time'] = '14:59:00'
df_plan_exchange_temp['code'] = df_plan_exchange_temp['fd_code']
df_plan_exchange_temp['name'] = df_plan_exchange_temp['fd_name']
# 目前的基金转换，我只做过南方天天利货币B转指数基金的操作，以后将尽量避免这种繁琐的交易记录
df_plan_exchange_temp['action_desc_exchange'] = df_plan_exchange_temp[
'action_desc']
df_plan_exchange_temp['action_desc'] = df_plan_exchange_temp.apply(
self.deal_type_calc, axis=1)
df_plan_exchange_temp['deal_type'] = df_plan_exchange_temp[
'action_desc']
df_plan_exchange_temp['volume'] = np.abs(
df_plan_exchange_temp['confirm_volume'])
df_plan_exchange_temp['fee'] = df_plan_exchange_temp['fee']
df_plan_exchange_temp['nav_unit'] = df_plan_exchange_temp.apply(
self.nav_unit_calc, axis=1)
df_plan_exchange_temp['nav_acc'] = df_plan_exchange_temp['nav_unit']
df_plan_exchange_temp['deal_money'] = df_plan_exchange_temp[
'confirm_amount']
df_plan_exchange_temp['occur_money'] = df_plan_exchange_temp.apply(
self.occur_money_calc, axis=1)
df_plan_exchange_temp['account'] = '{0}_蛋卷'.format(self.user_name)
# # 补充一二三级分类
df_plan_exchange_temp = pd.merge(df_plan_exchange_temp,
self.cm.df_category,
left_on='fd_code',
right_on='基金代码',
how='left')
df_plan_exchange_temp = df_plan_exchange_temp.rename(
columns={
'一级分类': 'category1',
'二级分类': 'category2',
'三级分类': 'category3',
'分类ID': 'category_id'
})
df_plan_exchange_temp['unique_id'] = df_plan_exchange_temp['order_id']
df_plan_exchange_temp['note'] = df_plan_exchange_temp[
'plan_name'] + '_' + df_plan_exchange_temp[
'action_desc_exchange'] + '_' + df_plan_exchange_temp[
'target_fd_name'] + '_orderid=' + df_plan_exchange_temp[
'order_id']
df_plan_exchange_temp = df_plan_exchange_temp[record_keys()]
# 最终合并
all = []
if len(df_temp) > 0:
all.append(df_temp)
if len(df_money_temp) > 0:
all.append(df_money_temp)
if len(df_plan_exchange_temp) > 0:
all.append(df_plan_exchange_temp)
# 如果有基金交易，那也算上吧
if len(self.df_fund_output_list) > 0:
all.append(self.df_fund_output_list)
df_output = pd.concat(all, ignore_index=True, sort=False)
df_output = df_output.sort_values(['date', 'code'])
df_output = df_output.reset_index(drop=True)
df_output.id = df_output.index + 1
# 用三级分类表中的名称统一各大基金 APP 中的名称
df_output = pd.merge(df_output,
self.cm.df_category,
left_on='code',
right_on='基金代码',
how='left')
df_output['name'] = df_output['基金名称']
df_output = df_output[record_keys()]
self.df_results = df_output.copy()
# df_output.to_excel('04_{0}_蛋卷.xlsx'.format(self.user_name), sheet_name=f'{self.user_name}_交易记录')
pass