def getFundNavByTime(self, code, startDate, endDate):
url_holder = u'https://stock.finance.sina.com.cn/fundInfo/api/openapi.php/CaihuiFundInfoService.getNav?callback=callback&symbol={0}&datefrom={1}&dateto={2}&page=1'
datalist = []
response = requests.get(url_holder.format(code, startDate, endDate),
headers=self.headers,
verify=False)
if response.status_code == 200:
# 正则表达式
re_pattern = r"/\*.*?\*/\ncallback\((.*?)\)"
regex = re.compile(re_pattern)
result = regex.findall(response.text)
# 取 json
jsonData = json.loads(result[0])
for item in jsonData['result']['data']['data']:
datalist.append({
'date': item['fbrq'][0:10],
'navUnit': item['jjjz'],
'navAcc': item['ljjz']
})
datalist = list(reversed(datalist))
return datalist
db = fundDBHelper()
db.insertDataToTable(code,
keys=['date', 'nav_unit', 'nav_acc'],
values=[])
def getCategoryByCode(self, code):
result = list(
self.category_df[self.category_df['基金代码'] == code].values)
if len(result) > 0:
result = result[0]
return {
'category1': result[3],
'category2': result[4],
'category3': result[5],
'categoryId': result[6]
}
else:
print('[ERROR] {0} 不在资产配置列表中. 请添加'.format(code))
if sys.platform.startswith('win'):
os.startfile('https://qieman.com/funds/{0}'.format(code))
os.startfile(
'http://fund.eastmoney.com/{0}.html?spm=search'.format(
code))
os.startfile(self.xlsx_path)
# 下载基金详情，历史净值
fundInfos = fundInfoSpider().get([code])
# 下载分红/拆分信息
dividends = dividendInfoSpider().get([code])
# Database
db = fundDBHelper()
for info in fundInfos:
db.insertFundByJonsData(info)
for info in dividends:
db.insertFundDividendByJonsData(info)
exit(1)
return {}
def insertToDB(self, codes):
for code in codes:
with open(os.path.join(self.folder, u'dividend_data',
f'{code}_dividendInfo.json'),
'r',
encoding='utf-8') as f:
db = fundDBHelper()
data = json.loads(f.read())
db.insertFundDividendByJonsData(data)
def insertNewFundInfosToDB():
db = fundDBHelper()
folder = os.path.abspath(os.path.dirname(__file__))
fund_data_folder = os.path.join(folder, 'tools','fund_data')
for root, dirs, files in os.walk(fund_data_folder):
for filename in files:
filepath = os.path.join(root, filename)
with open(filepath, 'r',encoding='utf-8') as f:
# 用 dbHelper 的 json adapter 直接插入整个基金基础数据
db.insertFundByJonsData(json.loads(f.read()))
def updateDatabase():
folder = os.path.abspath(os.path.dirname(__file__))
today = datetime.now().strftime('%Y-%m-%d')
with open(os.path.join(folder, 'navUpdateTime.json'), 'r',encoding='utf-8') as f:
config = json.loads(f.read())
if today == config['date']:
# print('净值库已更新，退出')
return
# 更新数据库中的净值到今天
updater = fundNavUpdater()
updater.update()
# 拉取库存基金的历史分红信息
divide = dividendInfoSpider()
# 清理数据库基金分红拆分数据
divide.truncateDB()
# 获取最新
db = fundDBHelper()
codes = db.selectAllFundNavCodes()
divide.get(codes)
# TODO 这里的 get 同样要先监测库里该基金的最新一条分红数据
# 插入数据库
divide.insertToDB(codes)
with open(os.path.join(folder, 'navUpdateTime.json'), 'w+',encoding='utf-8') as f:
f.write(json.dumps({'date':today}, ensure_ascii=False, indent=4))
def _prepareDealRecords(self, jsonData):
db = fundDBHelper()
all_model_keys = dealRecordModelKeys()
index = 0
results = []
# 遍历每一条记录
orders = jsonData['compositionOrders']
for order in orders:
opType = order['uiOrderCodeName']
if '转托管' in opType:
# 转托管逻辑过于复杂，之后但凡有此类操作，直接读取 input 文件夹下的修正成交记录
continue
index = index + 1
# id
all_model_values = [index]
# date
unix_ts = int(int(order['acceptTime'])/1000)
dateObj = datetime.fromtimestamp(unix_ts)
date = str(dateObj)[0:10]
hour = dateObj.hour
all_model_values.append(date)
all_model_values.append(order['fund']['fundCode'])
all_model_values.append(order['fund']['fundName'])
confirm_amount = order['uiAmount']
confirm_volume = order['uiShare']
fee = order['fee']
occurMoney = round(order['uiAmount'], 2)
nav_unit = 0.0
nav_acc = 0.0
if u'分红' in opType:
all_model_values.append('分红')
occurMoney = confirm_amount
db_record = db.selectNearestDividendDateFundNav(code = all_model_values[2], date = all_model_values[1])
nav_unit = db_record[1]
nav_acc = db_record[2]
all_model_values.append(nav_unit)
all_model_values.append(nav_acc)
else:
db_record = None
if hour >= 15:
# 超过15点，净值应该按下一个交易日算
# 注意：目前发现只有且慢给回的信息是这样的。蛋卷给回的都是 00:00:00 的时间戳
db_record = db.selectFundNavAfterDate(code = all_model_values[2], date = all_model_values[1])
else:
db_record = db.selectFundNavByDate(code = all_model_values[2], date = all_model_values[1])
nav_acc = db_record[2]
# 如果是 01-01 这样节日下单，日期应该换成有效交易日，即顺延的下一天
all_model_values[1] = db_record[0]
if u'买' in opType or u'申' in opType:
all_model_values.append('买入')
confirm_amount = round(occurMoney - fee, 2)
elif u'赎' in opType:
all_model_values.append('卖出')
confirm_amount = round(occurMoney + fee, 2)
elif u'转换至' in opType:
# 把且慢稳稳的幸福的转换至都分拆放到 addition.json 里面了，这块太难搞了，以后应该也不会买且慢组合了，就不写逻辑了。
print('\n[WARNING] qiemanSpider 且慢的转换至应该是一笔交易转两笔，请自行确认是否再 addition.json 中做了人工补充。\n\n{0}\n'.format(order))
continue
else:
print('[WARNING] qiemanSpider 未知操作：{0}'.format(opType))
all_model_values.append(opType)
all_model_values.append(order['nav'])
all_model_values.append(nav_acc)
all_model_values.append(confirm_volume)
all_model_values.append(confirm_amount)
all_model_values.append(fee)
all_model_values.append(occurMoney)
all_model_values.append(self.owner + '_' + global_name + '_' + self.current_plan['name'])
categoryInfo = self.categoryManager.getCategoryByCode(all_model_values[2])
if categoryInfo != {}:
all_model_values.append(categoryInfo['category1'])
all_model_values.append(categoryInfo['category2'])
all_model_values.append(categoryInfo['category3'])
all_model_values.append(categoryInfo['categoryId'])
all_model_values.append('https://qieman.com/orders/' + jsonData['orderId'] + '子编号：' + order['orderId'])
itemDict = dict(zip(all_model_keys, all_model_values))
results.append(itemDict)
return results
def __init__(self):
self.db = fundDBHelper()
self.infoSpider = fundInfoSpider()
pass
def _handlePlanBuySell(self, tradeDetailJson):
db = fundDBHelper()
all_model_keys = dealRecordModelKeys()
index = 0
results = []
sub_order_list = tradeDetailJson['sub_order_list']
for sub_order in sub_order_list:
for order in sub_order['orders']:
opType = order['action_desc']
index = index + 1
# id
all_model_values = [index]
# date
unix_ts = int(int(order['ts']) / 1000)
all_model_values.append(
str(datetime.fromtimestamp(unix_ts))[0:10])
all_model_values.append(order['fd_code'])
all_model_values.append(order['fd_name'])
confirm_amount = order['confirm_amount']
confirm_volume = order['confirm_volume']
# 钉钉宝 90 组合有时候会纳入一些货币基金，货币基金是没有净值的，默认按 1.0000 处理
isCashFund = False
if u'货币' in order['fd_name']:
isCashFund = True
fee = order['fee']
occurMoney = 0
nav_unit = 0.0
nav_acc = 0.0
all_model_values.append(opType)
if opType == '分红':
occurMoney = confirm_amount
if not isCashFund:
db_record = db.selectNearestDividendDateFundNav(
code=all_model_values[2], date=all_model_values[1])
nav_unit = db_record[1]
nav_acc = db_record[2]
else:
nav_unit = 1.0000
nav_acc = 1.0000
all_model_values.append(nav_unit)
else:
# 净值
if not isCashFund:
db_record = db.selectFundNavByDate(
code=all_model_values[2], date=all_model_values[1])
nav_unit = db_record[1]
nav_acc = db_record[2]
else:
nav_unit = 1.0000
nav_acc = 1.0000
all_model_values.append(nav_unit)
if opType == '买入' or opType == '转换':
occurMoney = round(confirm_amount + fee, 2)
elif opType == '卖出':
occurMoney = round(confirm_amount - fee, 2)
else:
continue
all_model_values.append(nav_acc)
all_model_values.append(confirm_volume)
all_model_values.append(confirm_amount)
all_model_values.append(fee)
all_model_values.append(occurMoney)
all_model_values.append(self.owner + '_' + global_name + '_' +
order['plan_name'])
if isCashFund:
# 是货币基金综合的虚拟代码
categoryInfo = self.categoryManager.getCashFundCategory()
else:
categoryInfo = self.categoryManager.getCategoryByCode(
all_model_values[2])
if categoryInfo != {}:
all_model_values.append(categoryInfo['category1'])
all_model_values.append(categoryInfo['category2'])
all_model_values.append(categoryInfo['category3'])
all_model_values.append(categoryInfo['categoryId'])
all_model_values.append(
'https://danjuanapp.com/djmodule/trade-details?ordertype=plan&orderid='
+ order['order_id'])
itemDict = dict(zip(all_model_keys, all_model_values))
results.append(itemDict)
return results
def _handlePlanConvert(self, tradeDetailJson):
# "action_text": "成分基金转换信息",
# "action": "036",
# "action_text": "成分基金转入信息",
# "action": "037",
# "action_text": "成分基金转出信息",
# "action": "038",
db = fundDBHelper()
all_model_keys = dealRecordModelKeys()
index = 0
results = []
sub_order_list = tradeDetailJson['sub_order_list']
for sub_order in sub_order_list:
actionType = ''
actionText = sub_order['action_text']
if u'转换' in actionText:
# 目前知道的，叫转换的操作，只有货币基金买组合的情况
actionType = u'转换'
elif u'转入' in actionText:
actionType = u'买入'
elif u'转出' in actionText:
actionType = u'卖出'
else:
print('[Error] danjuanSpider 未知的转换系操作：{0}'.format(actionText))
exit(1)
orderlist = sub_order['orders']
for order in orderlist:
opType = actionType
index = index + 1
# id
all_model_values = [index]
# date
unix_ts = 0
unix_ts = int(int(order['confirm_ts']) / 1000)
all_model_values.append(
str(datetime.fromtimestamp(unix_ts))[0:10])
if opType == u'买入':
all_model_values.append(order['fd_code'])
all_model_values.append(order['fd_name'])
elif opType == u'转换':
all_model_values.append(order['target_fd_code'])
all_model_values.append(order['target_fd_name'])
elif opType == '卖出':
all_model_values.append(order['fd_code'])
all_model_values.append(order['fd_name'])
confirm_amount = order['confirm_amount']
confirm_volume = order['confirm_volume']
# 钉钉宝 90 组合有时候会纳入一些货币基金，货币基金是没有净值的，默认按 1.0000 处理
isCashFund = False
if u'货币' in all_model_values[3]:
isCashFund = True
fee = order['fee']
occurMoney = 0
nav_unit = 0.0
nav_acc = 0.0
if opType == u'转换':
opType = u'买入'
all_model_values.append(opType)
# 净值
db_record = None
if opType == u'买入':
if not isCashFund:
# 因为基金公司确认份额是 T+1 日，所以净值使用的 T 日的
db_record = db.selectFundNavBeforeDate(
code=all_model_values[2], date=all_model_values[1])
# 日期改成净值确认日
all_model_values[1] = db_record[0]
elif opType == '卖出':
if not isCashFund:
db_record = db.selectFundNavBeforeDate(
code=all_model_values[2], date=all_model_values[1])
# 日期改成净值确认日
all_model_values[1] = db_record[0]
else:
print('[Error] danjuanSpider 未知的操作：{0}'.format(opType))
exit(1)
continue
if not isCashFund:
nav_unit = db_record[1]
nav_acc = db_record[2]
else:
nav_unit = 1.0000
nav_acc = 1.0000
all_model_values.append(nav_unit)
if opType == '买入':
occurMoney = round(confirm_amount + fee, 2)
elif opType == '卖出':
occurMoney = round(confirm_amount - fee, 2)
else:
continue
all_model_values.append(nav_acc)
all_model_values.append(confirm_volume)
all_model_values.append(confirm_amount)
all_model_values.append(fee)
all_model_values.append(occurMoney)
all_model_values.append(self.owner + '_' + global_name + '_' +
tradeDetailJson['target_name'])
if isCashFund:
categoryInfo = self.categoryManager.getCashFundCategory()
else:
categoryInfo = self.categoryManager.getCategoryByCode(
all_model_values[2])
if categoryInfo != {}:
all_model_values.append(categoryInfo['category1'])
all_model_values.append(categoryInfo['category2'])
all_model_values.append(categoryInfo['category3'])
all_model_values.append(categoryInfo['categoryId'])
all_model_values.append(
'https://danjuanapp.com/djmodule/trade-details?ordertype=plan&orderid='
+ order['order_id'])
itemDict = dict(zip(all_model_keys, all_model_values))
results.append(itemDict)
return results
def _handleFundBuySell(self, tradeDetailJson):
# 单只基金无法在 order 页面查看手续费，需要进一步请求
fund_detail_url = 'https://danjuanapp.com/djapi/fund/order/{0}'
jsonData = {}
response = requests.get(fund_detail_url.format(
tradeDetailJson['order_id']),
headers=self.headers,
verify=False)
if response.status_code == 200:
jsonData = json.loads(response.text)
else:
print('[ERROR] danjuanSpider 单只基金交易记录 {0} 获取失败 code:{1} text:{2}'.
format(tradeDetailJson['order_id'], response.status_code,
response.text))
print('[ERROR] danjuanSpider 单只基金交易记录 {0} 缺失，退出'.format(
tradeDetailJson['order_id']))
exit(1)
results = []
if len(jsonData.keys()) == 0 or u'data' not in jsonData.keys():
return []
else:
order = jsonData['data']
db = fundDBHelper()
all_model_keys = dealRecordModelKeys()
index = 1
results = []
opType = order['action_desc']
# id
all_model_values = [index]
# date
unix_ts = int(int(order['confirm_date']) / 1000)
all_model_values.append(str(datetime.fromtimestamp(unix_ts))[0:10])
all_model_values.append(order['fd_code'])
all_model_values.append(order['fd_name'])
confirm_amount = order['confirm_amount']
confirm_volume = order['confirm_volume']
confirm_infos = order['confirm_infos']
fee = 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)
break
occurMoney = 0
nav_unit = 0.0
nav_acc = 0.0
all_model_values.append(opType)
if opType == '分红':
occurMoney = confirm_amount
db_record = db.selectNearestDividendDateFundNav(
code=all_model_values[2], date=all_model_values[1])
nav_unit = db_record[1]
nav_acc = db_record[2]
all_model_values.append(nav_unit)
else:
# 净值
db_record = db.selectFundNavBeforeDate(
code=all_model_values[2], date=all_model_values[1])
nav_unit = db_record[1]
nav_acc = db_record[2]
all_model_values[1] = db_record[0]
all_model_values.append(nav_unit)
if opType == '买入' or opType == '转换':
occurMoney = round(confirm_amount + fee, 2)
elif opType == '卖出':
occurMoney = round(confirm_amount - fee, 2)
else:
print('单只基金买入错误：{0}'.format(opType))
all_model_values.append(nav_acc)
all_model_values.append(confirm_volume)
all_model_values.append(confirm_amount)
all_model_values.append(fee)
all_model_values.append(occurMoney)
all_model_values.append(self.owner + '_' + global_name + '_' +
order['order_type_name'])
categoryInfo = self.categoryManager.getCategoryByCode(
all_model_values[2])
if categoryInfo != {}:
all_model_values.append(categoryInfo['category1'])
all_model_values.append(categoryInfo['category2'])
all_model_values.append(categoryInfo['category3'])
all_model_values.append(categoryInfo['categoryId'])
all_model_values.append(
'https://danjuanapp.com/djmodule/trade-details?ordertype=fund&orderid='
+ order['order_id'])
itemDict = dict(zip(all_model_keys, all_model_values))
results.append(itemDict)
return results
def truncateDB(self):
db = fundDBHelper()
db.truncateDividendSplitDB()