def validate_deals():
"""检查交易记录是否有缺失（如分红/拆分）或错误"""
deals = defaultdict(list)
for record in Deal.select().order_by(Deal.time):
deals[record.asset.zs_code].append(record)
for code, records in deals.items():
asset = records[0].asset
bonus_history = list(
asset.history.where(
AssetMarketHistory.bonus_action.is_null(False)
).where(
AssetMarketHistory.date >= records[0].time.date()
)
)
if not bonus_history:
continue
for bonus_record in bonus_history:
matched = False
for deal in records:
if deal.time.date() == bonus_record.date:
matched = True
break
if not matched:
LOGGER.warning(
"bonus is missing in deals - fund: %s(%s), "
"date: %s, action: %s, value: %s",
asset.name, asset.zs_code, bonus_record.date,
bonus_record.bonus_action, bonus_record.bonus_value
)
def update_after_check(accounts, index_codes):
accounts = accounts or all_accounts
summary_data, assets_data = get_accounts_summary(accounts)
history = get_accounts_history(accounts).to_dict('records')
history.sort(key=itemgetter('account', 'date'))
index_history = []
for index_code in index_codes:
index = Asset.get(zs_code=index_code)
for record in index.history:
index_history.append({
'account': index.name,
'date': record.date,
'price': record.close_price
})
index_history.sort(key=itemgetter('account', 'date'))
deals = []
for record in Deal.get_deals(accounts):
deals.append({
'account': record.account,
'time': record.time,
'code': record.asset.zs_code,
'name': record.asset.name,
'action': record.action,
'amount': record.amount,
'price': record.price,
'money': record.money,
'fee': record.fee,
})
deals.sort(key=itemgetter('time'), reverse=True)
valid_deals_count = 0
for item in deals:
if item['action'] == 'fix_cash':
continue
if item['code'] == 'CASH' and item['action'] == 'reinvest':
continue
valid_deals_count += 1
pagination_options = [
{'label': idx + 1, 'value': idx}
for idx in range(ceil(valid_deals_count / 100))
]
assets_pagination_options = []
return (
assets_data,
summary_data,
history,
index_history,
deals,
pagination_options,
assets_pagination_options
)
def import_deals(infile):
"""从文件中批量导入交易"""
with open(infile) as fin:
reader = csv.reader(fin, delimiter='\t')
cnt, total = 0, 0
for row in reader:
if len(row) != 10:
LOGGER.warning('column number is not 10: %s', row)
continue
asset = Asset.get_or_none(Asset.zs_code == row[3])
if asset is None:
LOGGER.warning('no asset found for code: %s', row[3])
continue
if asset.zs_code == 'CASH' and row[6] != row[8]:
LOGGER.error('cash record is not balanced: %s', row)
return
if row[5] == 'buy':
try:
diff = abs(float(row[6]) * float(row[7]) + float(row[9]) - float(row[8]))
assert diff < 0.001
except AssertionError:
LOGGER.warning("record is not balanced: %s", row)
print(row)
elif row[5] == 'sell':
try:
diff = abs(float(row[6]) * float(row[7]) - float(row[9]) - float(row[8]))
assert diff < 0.001
except AssertionError:
LOGGER.warning("record is not balanced: %s", row)
_, created = Deal.get_or_create(
account=row[0],
sub_account=row[1],
time=datetime.strptime(row[2], '%Y-%m-%d %H:%M:%S'),
asset=asset,
action=row[5],
amount=row[6],
price=row[7],
money=row[8],
fee=row[9]
)
total += 1
if created:
cnt += 1
if cnt != total:
LOGGER.warning("%d records are already in database", total - cnt)
LOGGER.info("created %d records in database", cnt)
def price2bean(outdir):
"""将价格历史输出为 beancount 格式"""
if not os.path.exists(outdir):
os.makedirs(outdir)
for deal in Deal.select(Deal.asset).distinct():
asset = deal.asset
if asset.category not in ('stock', 'fund', 'bond'):
continue
code, suffix = asset.zs_code.split('.')
name = f'{suffix}{code}'
with open(os.path.join(outdir, f'{name}.bean'), 'w') as fout:
for record in asset.history.order_by(AssetMarketHistory.date):
if suffix == 'OF':
price = record.nav
else:
price = record.close_price
print(f'{record.date} price {name} {price:0.4f} CNY', file=fout)
def update_accounts(accounts):
"""更新账户持仓和收益数据"""
if not accounts:
accounts = set([
deal.account
for deal in Deal.select(Deal.account).distinct()
])
else:
accounts = set(accounts.split(','))
for account in accounts:
update_account_assets_history(account)
for account in accounts:
created_cnt, update_cnt = 0, 0
for item in compute_account_history(account):
record = AccountHistory.get_or_none(account=account, date=item[0])
if not record:
AccountHistory.create(
account=account,
date=item[0],
amount=item[1],
money=item[2],
nav=item[3],
cash=item[4],
position=item[5],
)
created_cnt += 1
elif record.amount != item[1] or record.money != item[2]:
record.amount = item[1]
record.money = item[2]
record.nav = item[3]
record.cash = item[4]
record.position = item[5]
record.save()
update_cnt += 1
LOGGER.info(
'created %d new history and update %d record for account %s',
created_cnt, update_cnt, account
)
def to_beancount(account, outfile, asset_prefix):
"""将交易记录输出为 beancount 格式"""
search = Deal.select().where(Deal.account == account).order_by(Deal.time)
records = list(search)
if not records:
return
if asset_prefix:
account_prefix = ':'.join(['Assets', asset_prefix, f'{account}'])
else:
account_prefix = ':'.join(['Assets', f'{account}'])
with open(outfile, 'w') as fout:
for item in records:
code, suffix = None, None
if item.asset.category != 'other':
code, suffix = item.asset.zs_code.split('.')
if item.action == 'transfer_in':
text = '\n'.join([
f'{item.time.date()} * "转账"',
f' {account_prefix}:CASH {item.money:0.2f} CNY',
' Equity:Opening-Balances',
])
print(text + '\n', file=fout)
elif item.action == 'transfer_out':
text = '\n'.join([
f'{item.time.date()} * "转出"',
f' {account_prefix}:CASH -{item.money:0.2f} CNY',
' Equity:Opening-Balances',
])
print(text + '\n', file=fout)
elif item.action == 'buy':
text = '\n'.join([
f'{item.time.date()} * "买入{item.asset.name}"',
f' {account_prefix}:持仓 {item.amount} {suffix}{code} @@ {item.money:0.2f} CNY',
f' {account_prefix}:CASH -{item.money:0.2f} CNY',
])
print(text + '\n', file=fout)
elif item.action == 'sell':
text = '\n'.join([
f'{item.time.date()} * "卖出{item.asset.name}"',
f' {account_prefix}:持仓 -{item.amount} {suffix}{code} @@ {item.money:0.2f} CNY',
f' {account_prefix}:CASH {item.money:0.2f} CNY',
])
print(text + '\n', file=fout)
elif item.action in ('reinvest', 'fix_cash') and item.asset.zs_code == 'CASH':
text = '\n'.join([
f'{item.time.date()} * "现金收益"',
f' {account_prefix}:CASH {item.money:0.2f} CNY',
' Income:现金收益',
])
print(text + '\n', file=fout)
elif item.action == 'bonus':
text = '\n'.join([
f'{item.time.date()} * "{item.asset.name}分红"',
f' {account_prefix}:CASH {item.money:0.2f} CNY',
' Income:分红',
])
print(text + '\n', file=fout)
elif item.action == 'reinvest':
text = '\n'.join([
f'{item.time.date()} * "{item.asset.name}分红"',
f' {account_prefix}:CASH {item.money:0.2f} CNY',
' Income:分红',
])
print(text + '\n', file=fout)
text = '\n'.join([
f'{item.time.date()} * "买入{item.asset.name}"',
f' {account_prefix}:持仓 {item.amount} {suffix}{code} @@ {item.money:0.2f} CNY',
f' {account_prefix}:CASH -{item.money:0.2f} CNY',
])
print(text + '\n', file=fout)
elif item.action == 'spin_off':
price = item.asset.history.\
where(AssetMarketHistory.date == item.time.date()).\
first().nav
money = round(item.amount * price, 2)
search = item.asset.assets_history.where(AccountAssetsHistory.account == account)
search = search.where(AccountAssetsHistory.date < item.time.date())
search = search.order_by(AccountAssetsHistory.date.desc())
record = search.first()
text = '\n'.join([
f'{item.time.date()} * "卖出{item.asset.name}"',
f' {account_prefix}:持仓 -{record.amount} {suffix}{code} @@ {money:0.2f} CNY',
f' {account_prefix}:CASH {money:0.2f} CNY',
])
print(text + '\n', file=fout)
text = '\n'.join([
f'{item.time.date()} * "买入{item.asset.name}"',
f' {account_prefix}:持仓 {item.amount} {suffix}{code} @@ {money:0.2f} CNY',
f' {account_prefix}:CASH -{money:0.2f} CNY',
])
print(text + '\n', file=fout)
def update_prices(category, codes, start_date):
'''更新交易记录涉及到的资产的历史价格'''
token = os.environ.get('TS_TOKEN')
if not token:
LOGGER.warning('environment `TS_TOKEN` is empty!')
return -1
assets = []
if codes:
for code in codes.split(','):
asset = Asset.get_or_none(zs_code=code)
if asset is None:
LOGGER.warning("code `%s` is not found in database", code)
continue
assets.append(asset)
else:
categories = set(['index', 'stock', 'bond', 'fund'])
if category:
categories = categories & set([category])
assets = [
deal.asset for deal in Deal.select(Deal.asset).distinct()
if deal.asset.category in categories
]
if 'index' in categories:
assets.extend(list(Asset.select().where(Asset.category == 'index')))
now = datetime.now()
if start_date is None:
start_date = (now - timedelta(days=10)).date()
else:
start_date = datetime.strptime(start_date, '%Y%m%d').date()
if now.hour >= 15:
end_date = now.date()
else:
end_date = (now - timedelta(days=1)).date()
api = EastMoneyFundExporter()
client = tushare.pro_api(token)
methods = {
'stock': client.daily,
'bond': client.cb_daily,
'fund': client.fund_daily,
'index': client.index_daily
}
for asset in assets:
created_cnt = 0
if asset.category in ('stock', 'bond', 'index') or \
(asset.category == 'fund' and not asset.zs_code.endswith('OF')):
days = (end_date - start_date).days + 1
method = methods[asset.category]
for offset in range(0, days, 1000):
cur_start_date = start_date + timedelta(days=offset)
cur_end_date = min(cur_start_date + timedelta(days=1000), end_date)
data = method(
ts_code=asset.zs_code,
start_date=cur_start_date.strftime('%Y%m%d'),
end_date=cur_end_date.strftime('%Y%m%d')
)
for _, row in data.iterrows():
_, created = AssetMarketHistory.get_or_create(
date=datetime.strptime(row['trade_date'], '%Y%m%d').date(),
open_price=row['open'],
close_price=row['close'],
pre_close=row['pre_close'],
change=row['change'],
pct_change=row['pct_chg'],
vol=row['vol'],
amount=row['amount'],
high_price=row['high'],
low_price=row['low'],
asset=asset
)
created_cnt += created
elif asset.category == 'fund':
fund_data = api.get_fund_data(asset.code)
if fund_data is None:
LOGGER.warning('no data for fund: %s', asset.zs_code)
continue
history = defaultdict(dict)
for nav in fund_data['Data_netWorthTrend']:
date = str(datetime.fromtimestamp(nav['x'] / 1000).date())
history[date]['nav'] = nav['y']
if nav.get('unitMoney'):
bonus_text = nav['unitMoney']
action, value = 'unknown', None
if bonus_text.startswith('分红'):
action = 'bonus'
value = float(re.findall(r'派现金(\d\.\d+)元', bonus_text)[0])
elif bonus_text.startswith('拆分'):
action = 'spin_off'
value = float(re.findall(r'折算(\d\.\d+)份', bonus_text)[0])
else:
LOGGER.wanring("unknown bonus text: %s", bonus_text)
if action != 'unknown':
history[date]['bonus_action'] = action
history[date]['bonus_value'] = value
for auv in fund_data['Data_ACWorthTrend']:
date = str(datetime.fromtimestamp(auv[0] / 1000).date())
history[date]['auv'] = auv[1]
for date, info in history.items():
if 'nav' not in info:
LOGGER.warning("invalid history data: %s(%s)", info, date)
_, created = AssetMarketHistory.get_or_create(
date=datetime.strptime(date, '%Y-%m-%d').date(),
nav=info['nav'],
auv=info.get('auv'),
bonus_action=info.get('bonus_action'),
bonus_value=info.get('bonus_value'),
asset=asset
)
created_cnt += created
LOGGER.info('created %d history records for %s(%s)', created_cnt, asset.name, asset.zs_code)
'成本': {'type': 'numeric', 'format': Format(nully='N/A', precision=4, scheme=Scheme.fixed)},
'价格': {'type': 'numeric', 'format': Format(nully='N/A', precision=4, scheme=Scheme.fixed)},
'收益': {'type': 'numeric', 'format': Format(nully='N/A', precision=2, scheme=Scheme.fixed)},
}
ACCOUNT_PRIORITIES = {
'长期投资': 0,
'长赢定投': 1,
'U定投': 2,
'投资实证': 3,
'稳健投资': 4,
'证券账户': 6,
'蛋卷基金': 7,
}
all_accounts = [deal.account for deal in Deal.select(Deal.account).distinct()]
all_accounts.sort(key=lambda name: ACCOUNT_PRIORITIES.get(name, 1000))
layout = html.Div(
[
dcc.Store(id='assets'),
dcc.Store(id='stats'),
dcc.Store(id='accounts_history'),
dcc.Store(id='index_history'),
dcc.Store(id='deals'),
dcc.Store(id='start-date'),
dcc.Store(id='end-date'),
html.H3('投资账户概览'),
dbc.Checklist(
id='show-money',
options=[{'label': '显示金额', 'value': 'show'}],
value=[],