def update_position_price(excelpath):
wb = openpyxl.load_workbook(excelpath, data_only=False)
# 更新汇率
config_sheet = wb.get_sheet_by_name("参数")
config_sheet.cell(row=1, column=2).value = priceCrawler.get_hk_rate()
# 保护数据透视表格式
pivot_sheet = wb.get_sheet_by_name("总计")
pivot = pivot_sheet._pivots[0] # 任何一个都可以共享同一个缓存
pivot.cache.refreshOnLoad = True # 刷新加载
pivot_sheet2 = wb.get_sheet_by_name("股票总计")
pivot2 = pivot_sheet2._pivots[0] # 任何一个都可以共享同一个缓存
pivot2.cache.refreshOnLoad = True # 刷新加载
# 更新股价
data_sheet = wb.get_sheet_by_name("实时持仓")
headers = get_headers(data_sheet)
code_col = headers.index('code') + 1
exchange_col = headers.index('交易板块') + 1
price_col = headers.index('股价') + 1
row_num = data_sheet.max_row
for i in range(2, row_num + 1):
nav = latest_price(
data_sheet.cell(i, code_col).value,
data_sheet.cell(i, exchange_col).value)
data_sheet.cell(i, price_col).value = nav
wb.save(excelpath)
def update_hkrate(excelpath):
wb = openpyxl.load_workbook(excelpath, data_only=False)
sheet1 = wb.get_sheet_by_name("参数") # 通过索引获取表格，一个文件里可能有多个sheet
sheet1.cell(row=1, column=2).value = priceCrawler.get_hk_rate()
# 保护数据透视表格式
pivot_sheet = wb.get_sheet_by_name("总计")
pivot = pivot_sheet._pivots[0] # 任何一个都可以共享同一个缓存
pivot.cache.refreshOnLoad = True # 刷新加载
wb.save(excelpath)
def update_stock_price(excelpath):
wb = openpyxl.load_workbook(excelpath, data_only=False)
# 更新汇率
config_sheet = wb.get_sheet_by_name("参数")
config_sheet.cell(row=1, column=2).value = priceCrawler.get_hk_rate()
# 更新股价
data_sheet = wb.get_sheet_by_name("当前持仓")
headers = get_headers(data_sheet)
code_col = headers.index('股票代码') + 1
price_col = headers.index('现价') + 1
inc_col = [
headers.index('一日涨幅') + 1,
headers.index('五日涨幅') + 1,
headers.index('近一月') + 1,
headers.index('近半年') + 1,
headers.index('近一年') + 1
]
row_num = data_sheet.max_row
for i in range(2, row_num + 1):
nav = latest_price(data_sheet.cell(i, code_col).value, "上海")
data_sheet.cell(i, price_col).value = nav
inc_list = priceCrawler.get_sina_increase(
data_sheet.cell(i, code_col).value)
if inc_list[0] != -200:
for j in range(len(inc_col)):
if inc_list[j] == -100:
data_sheet.cell(i, inc_col[j]).value = "--"
else:
data_sheet.cell(i, inc_col[j]).value = inc_list[j]
# 更新债价
data_sheet2 = wb.get_sheet_by_name("垃圾转债")
headers2 = get_headers(data_sheet2)
code_col = headers2.index('股票代码') + 1
price_col = headers2.index('现价') + 1
rate_col = headers2.index('到期年化') + 1
duedate_col = headers2.index('到期日期') + 1
row_num = data_sheet2.max_row
for i in range(2, row_num + 1):
nav = latest_price(str(data_sheet2.cell(i, code_col).value), "可转债")
data_sheet2.cell(i, price_col).value = nav
mytas = []
mytas.append((date_now, -nav))
mytas.append((data_sheet2.cell(i, duedate_col).value, 100))
data_sheet2.cell(i, rate_col).value = xirr(mytas)
wb.save(excelpath)
def stock():
code2stock_summary, tas = excelReader.check_stock_data()
date_now = ""
data = {
'code': [],
'stock': [],
'position': [],
'nav': [],
'profit': [],
'rate%': [],
'irr_rate%': []
}
for stock_code in code2stock_summary.keys():
nav, date_now = priceCrawler.get_sina_price(stock_code)
if stock_code[:2] == "hk":
nav = nav * priceCrawler.get_hk_rate()
code2stock_summary[stock_code].rate(nav)
tas.append(
(stock_code, date_now, code2stock_summary[stock_code].value))
data['code'].append(stock_code)
data['stock'].append(code2stock_summary[stock_code].name)
data['position'].append(code2stock_summary[stock_code].position)
data['nav'].append(nav)
data['profit'].append(round(code2stock_summary[stock_code].profit, 2))
data['rate%'].append(round(code2stock_summary[stock_code].rate, 2))
data['irr_rate%'].append(round(stock_irr(stock_code, tas), 2))
pd.set_option('display.unicode.ambiguous_as_wide', True)
pd.set_option('display.unicode.east_asian_width', True)
pd.set_option('display.width', None)
df = pd.DataFrame(data,
columns=[
'code', 'stock', 'position', 'nav', 'profit',
'rate%', 'irr_rate%'
])
df = df.sort_values(by='irr_rate%', ascending=False)
#显示所有列
pd.set_option('display.max_columns', None)
#显示所有行
pd.set_option('display.max_rows', None)
print(df)
df.to_csv("stock.csv", index=False)
print(date_now.date(), "irr_rate_all", round(stock_irr("all", tas), 2))
return
def update_peterlynch_price(excelpath):
wb = openpyxl.load_workbook(excelpath, data_only=False)
# 更新汇率
config_sheet = wb.get_sheet_by_name("参数")
config_sheet.cell(row=1, column=2).value = priceCrawler.get_hk_rate()
# 保护数据透视表格式
pivot_sheet = wb.get_sheet_by_name("总计")
pivot = pivot_sheet._pivots[0] # 任何一个都可以共享同一个缓存
pivot.cache.refreshOnLoad = True # 刷新加载
# 更新股价
data_sheet = wb.get_sheet_by_name("整体估值")
headers = get_headers(data_sheet)
code_col = headers.index('代码') + 1
price_col = headers.index('股价') + 1
category_col = headers.index('行业分类') + 1
row_num = data_sheet.max_row
for i in range(2, row_num + 1):
#print(data_sheet.cell(i, code_col).value)
if data_sheet.cell(i, code_col).value is None:
pass
elif data_sheet.cell(i, category_col).value == "宽基":
nav = latest_price(data_sheet.cell(i, code_col).value, "宽基")
data_sheet.cell(i, price_col).value = nav
else:
nav = latest_price(data_sheet.cell(i, code_col).value, "上海")
data_sheet.cell(i, price_col).value = nav
# 更新报表好看一列
data_sheet2 = wb.get_sheet_by_name("报表好看")
headers2 = get_headers(data_sheet2)
code_col = headers2.index('代码') + 1
price_col = headers2.index('现价') + 1
row_num = data_sheet2.max_row
for i in range(2, row_num + 1):
nav = latest_price(str(data_sheet2.cell(i, code_col).value), "上海")
data_sheet2.cell(i, price_col).value = nav
wb.save(excelpath)