利用python twstock套件做台股爬蟲並記錄收支

$ pip3 install twstock pandas openpyxl

import shutil
from datetime import datetime
from time import sleep
import twstock
import pandas as pd
from openpyxl import Workbook
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.utils.dataframe import dataframe_to_rows
from openpyxl.styles import Font

#輸入以下資訊############################################################################
stock_array = ['2886', '5880', '0056', '00713', '00878', '00919', '00929', '00679B']
data_year = 2023
data_month = 3
########################################################################################

#更新台股股票代碼
twstock.__update_codes()

#檢查stock.xlsx是否存在
try:
    wb = load_workbook('stock.xlsx')
except FileNotFoundError:
    wb = Workbook()
    wb.save('stock.xlsx')
else:
    #複製一份原始檔當備份
    current_date = datetime.now().strftime('%Y-%m-%d')
    new_filename = f'stock_{current_date}.xlsx'
    shutil.copyfile('stock.xlsx', new_filename)

#更新每檔股票內容
for stock in stock_array:
    stock_name = twstock.realtime.get(stock)['info']['name']
    stock_data = twstock.Stock(stock)
    stock_history = stock_data.fetch_from(data_year, data_month)
    stock_table = pd.DataFrame(data=stock_history, columns=['日期', '總成交股數', '總成交金額', '開盤價', '最高價', '最低價', '收盤價', '漲跌價差', '成交筆數'])
    stock_table['日期'] = stock_table['日期'].dt.strftime('%Y-%m-%d')
    rows = dataframe_to_rows(stock_table, index=False, header=True)
    
    #若無列表中的股票則新增對應的分頁
    if stock in wb.sheetnames:
        sheet = wb[stock]
    else:
        sheet = wb.create_sheet(title=stock)
        sheet.cell(row=1, column=10, value='購入/出售')
        sheet.cell(row=1, column=11, value='購入價/出售價')
        sheet.cell(row=1, column=12, value='股息')
        sheet.cell(row=1, column=13, value='稅金/手續費')
        sheet.cell(row=1, column=14, value='成本')
        sheet.cell(row=1, column=15, value='總成本')
        sheet.cell(row=1, column=16, value='股票市值')
        sheet.cell(row=1, column=17, value='盈虧')

    #清空分頁內的原有資料
    for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=9):
        for cell in row:
            cell.value = None

    #更新每日股票資訊
    for r_idx, row in enumerate(rows, 1):
        for c_idx, value in enumerate(row, 1):
             sheet.cell(row=r_idx, column=c_idx, value=value)
    
    sheet.cell(row=1, column=1, value=stock_name)

    #計算與指定格式
    for row in range(3, sheet.max_row + 1):
        for col in sheet.iter_cols(min_row=row, max_row=row, min_col=11, max_col=17):
            for cell in col:
                if col[0].column == 11:
                    cell.number_format = '"$"#,##0.00'
                elif col[0].column == 12:
                    cell.number_format = '"$"#,##0'
                elif col[0].column == 13:
                    cell.number_format = '"$"#,##0'
                elif col[0].column == 14:
                    cell.value = f'=J{row}*K{row}+L{row}+M{row}'
                    cell.number_format = '"$"#,##0'
                elif col[0].column == 15:
                    cell.value = f'=N{row}+O{row-1}'
                    cell.number_format = '"$"#,##0'
                elif col[0].column == 16:
                    cell.value = f'=SUM($J$2:$J{row})*G{row}'
                    cell.number_format = '"$"#,##0'
                elif col[0].column == 17:
                    cell.value = f'=P{row}-O{row}'
                    cell.number_format = '"$"#,##0'

    #統一字體
    for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=17):
        for cell in row:
            cell.font = Font(name='Arial', size = "10")

    #統一列距
    for col_num in range(1, 4):
        col_letter = get_column_letter(col_num)
        sheet.column_dimensions[col_letter].width = 15
    for col_num in range(4, 10):
        col_letter = get_column_letter(col_num)
        sheet.column_dimensions[col_letter].width = 10
    for col_num in range(10, 18):
        col_letter = get_column_letter(col_num)
        sheet.column_dimensions[col_letter].width = 15

    sleep(3)

#確認summary分頁是否存在
if 'summary' in wb.sheetnames:
    new_ws = wb['summary']
else:
    new_ws = wb.create_sheet(title='summary')
    new_ws['B2'] = '股票代號'
    new_ws['C2'] = '股票名稱'
    new_ws['D2'] = '持有股數'
    new_ws['E2'] = '持有成本'
    new_ws['F2'] = '股票市值'
    new_ws['G2'] = '目前收益'
    new_ws['H2'] = '股息收益'

#更新summary分頁內容
z = 4
for sheet in wb:
    if sheet.title == 'Sheet':
        wb.remove(wb['Sheet'])
    if sheet.title != 'summary' and sheet.title != 'Sheet':
        new_ws[f'B{z}'] = sheet.title
        new_ws[f'C{z}'] = f'=\'{sheet.title}\'!A1'
        new_ws[f'D{z}'] = f'=SUM(\'{sheet.title}\'!J:J)'
        new_ws[f'E{z}'] = f'=OFFSET(\'{sheet.title}\'!O1,COUNT(\'{sheet.title}\'!O:O)+1,0)'
        new_ws[f'F{z}'] = f'=OFFSET(\'{sheet.title}\'!P1,COUNT(\'{sheet.title}\'!P:P)+1,0)'
        new_ws[f'G{z}'] = f'=F{z}-E{z}'
        new_ws[f'H{z}'] = f'=-SUM(\'{sheet.title}\'!L:L)'
        z += 1

#統一字體
for row in new_ws.iter_rows(min_row=1, max_row=new_ws.max_row, min_col=2, max_col=8):
    for cell in row:
        cell.font = Font(name='Arial', size = "10")

wb.save('stock.xlsx')