2022年6月30日 星期四

Python 學習筆記 : 使用 OpenPyXL 操作 EXCEL 試算表 (五)

自六月初上了 Python 與 Excel 的整合操作課程後, 覺得 Excel 的 xlsx 檔是非常棒的單檔簡單型資料庫, 適合在做原型開發時快速實現商業邏輯, 而毋須去配置標準資料庫中資料表的關聯與 SQL 操作. 它不僅可用 Excel 或 Libre Office 等軟體開啟, 也可以在沒有這些軟體環境下, 只用 OpenPyXL 套件即可存取與操控. 

於是我回頭把去年底開始但不久又暫停的 OpenPyXL 套件重新學習, 一口氣將用得到的主要功能測完, 今天終於大功告成啦! 這次總算將整隻魚從頭啃到尾. 不過, 最後還有個小尾巴要完成, 就是把課堂中的股票看板範例, 從原本的 xlwings 套件改成 openpyxl 版, 因為樹莓派上沒辦法跑 xlwings, 它必須安裝微軟的 Excel 軟體才能用, 而 openpyxl 則無此限制. 



9. 即時股票看板 :   

此股票看板的目標是根據 Excel 工作表中股票欄位所指定股號去聚財網 (也可以用 Yahoo 股市) 查詢即時股價, 計算漲跌幅, 然後利用 Line Notify 推播即時通知. 


(1). 建立工作簿並存成 xlsx 檔 :   

首先要建立一個 Excel 工作簿, 定義欄名並預先寫入要觀察的股票清單, 雖然此功能只執行一次, 但還是寫成一個函式如下 : 

import openpyxl as xl

def create_workbook(filename, stocks):
    wb=xl.Workbook()
    ws=wb.active
    ws.title='股票看板'
    # 建立第一列的欄名
    ws['A1'].value='股票代號'
    ws['B1'].value='股票名稱'
    ws['C1'].value='開盤價'
    ws['D1'].value='最高價'
    ws['E1'].value='最低價'
    ws['F1'].value='成交價'
    ws['G1'].value='昨日收盤價'
    ws['H1'].value='帳跌幅'
    ws['I1'].value='最近更新'
    ws['J1'].value='Line Notify Token'
    # 將觀察清單中的股票代號填入 A2, A3, A4, ...
    for idx, stock in enumerate(stocks): 
        ws[f'A{idx + 2}'].value=stock
    # 存檔
    try:    
        wb.save(filename)
    except Exception:
        print(f"Permission Error : {filename} 開啟中, 請關閉")
    return wb, ws

stock_list=['0050', '0056', '2330', '2303', '2412']
wb, ws=create_xlsx("twstock_dashboard.xlsx", stock_list)

此函式先建立一個空白程式庫, 然後在第一列的 A~J 欄位填入 11 個欄名, 並且將傳入的股票觀察清單依序寫入工作表 A 欄 (股票代號), 從第二列開始寫入儲存格, 然後將工作簿存成指定之 xlsx 檔 (例如 twstock_dashboard.xlsx) 後傳回工作簿與工作表物件. 用 Excel 開啟所建立之  twstock_dashboard.xlsx 檔結果如下 : 




(2). 撰寫爬蟲擷取聚財網股價資訊 :  

建立好試算表資料庫後, 下一步是從公開的資料源 (例如聚財網) 擷取股票資料 : 


此網頁會以倒排方式列出所搜尋之股票過去 20 個交易日之收盤資訊, 每個交易日的四點過後會更新資料, 例如 : 




檢視其 HTML 原始碼, 可知此網頁使用 big5 編碼 :

<meta http-equiv="Content-Type" content="text/html; charset=big5" >

網頁中有兩個 table 表格元素, 股價內容是放在第一個表格 (具有屬性 class="mobile_img" ) 內, 表格的第一列是合併儲存格的表格標題 :

<tr> 
    <td colspan="6" height="30"><div align="center">個股股價行情表﹝0050 元大台灣50﹞</div>        </td>
</tr>

表格第 2 列是 6 個欄位的標題 : 

  <tr> 
    <th bgcolor="#f0f0f0" width="90" class="table-first-child"><div align="center">日期</div></th>
    <th bgcolor="#f0f0f0" ><div align="center">開盤價</div></th>
    <th bgcolor="#f0f0f0" ><div align="center">最高價</div></th>
    <th bgcolor="#f0f0f0" ><div align="center">最低價</div></th>
    <th bgcolor="#f0f0f0" ><div align="center">收盤價</div></th>
    <th bgcolor="#f0f0f0" ><div align="center">成交量</div></th>
  </tr>

表格第 3 列以後才是過去 20 天的收盤資料 : 

  <tr class="stockalllistbg2">
    <td class="table-first-child">111/06/28</td>
    <td align="right">120.80&nbsp;&nbsp;&nbsp;</td>
    <td align="right">120.80&nbsp;&nbsp;&nbsp;</td>
    <td align="right">119.25&nbsp;&nbsp;&nbsp;</td>
    <td align="right">119.80&nbsp;&nbsp;&nbsp;</td>
    <td align="right">10,801&nbsp;&nbsp;&nbsp;</td>
  </tr>

  <tr class="stockalllistbg1">
    <td class="table-first-child">111/06/27</td>
    <td align="right">119.80&nbsp;&nbsp;&nbsp;</td>
    <td align="right">121.45&nbsp;&nbsp;&nbsp;</td>
    <td align="right">119.80&nbsp;&nbsp;&nbsp;</td>
    <td align="right">120.95&nbsp;&nbsp;&nbsp;</td>
    <td align="right">16,926&nbsp;&nbsp;&nbsp;</td>
  </tr>

  <tr class="stockalllistbg2">
    <td class="table-first-child">111/06/24</td>
    <td align="right">118.65&nbsp;&nbsp;&nbsp;</td>
    <td align="right">119.30&nbsp;&nbsp;&nbsp;</td>
    <td align="right">117.95&nbsp;&nbsp;&nbsp;</td>
    <td align="right">118.15&nbsp;&nbsp;&nbsp;</td>
    <td align="right">11,166&nbsp;&nbsp;&nbsp;</td>
  </tr>
  ..... (略) .....

  <tr class="stockalllistbg1">
    <td class="table-first-child">111/05/31</td>
    <td align="right">128.45&nbsp;&nbsp;&nbsp;</td>
    <td align="right">129.80&nbsp;&nbsp;&nbsp;</td>
    <td align="right">127.60&nbsp;&nbsp;&nbsp;</td>
    <td align="right">129.80&nbsp;&nbsp;&nbsp;</td>
    <td align="right">7,233&nbsp;&nbsp;&nbsp;</td>
  </tr>

這些成交資料可以利用 requests 與 BeautifulSoup 套件輕易地擷取出來, 以元大 0050 為例, 呼叫 requests.get(url) 會傳回一個 HTTP 的 Response 回應物件, 由於網頁是以 big5 編碼, 所以必須設定回應物件的 encoding 屬性設為 'big5' : 

>>> import requests     
>>> from bs4 import BeautifulSoup      
>>> res=requests.get("https://stock.wearn.com/cdata.asp?kind=0050")   
>>> res.encoding="big5"    

網頁的 HTML 原始碼是放在回應物件的 text 屬性內, 可以利用 BeautifulSoup 來解析 HTML 結構, 它會傳回一個 BeautifulSoup 物件 : 

>>> html=BeautifulSoup(res.text, "html.parser")      
>>> type(html)      
<class 'bs4.BeautifulSoup'>   

BeautifulSoup 物件代表經過剖析後的整個 HTML 語法樹結構, 它包含 Tag, NavigableString, 以及 Comment 三個子物件, 其中 Tag 物件是語法樹中的節點, 代表組成網頁的各個標籤元素. 利用 BeautifulSoup 物件的 findAll() 方法可找到全部指定之標籤, 它會傳回 Tag 物件串列, 例如 :

>>> table=html.findAll("table")[0]       # 從表格的 Tag 物件串列中取得第一個
>>> type(table)   
<class 'bs4.element.Tag'>      

這個 table 是整個表格的 Tag 物件, 但我們要的是其中的各列內容, 也就是 tr 標籤元素, 每一個 Tag 物件都繼承了 BeautifulSoup 物件的方法, 所以可以繼續使用 findAll() 方法從 table 中找尋所有的列 Tag 物件 : 

>>> trs=table.findAll("tr")        # 從 table 中找尋全部 tr 標籤元素
>>> type(trs)    
<class 'bs4.element.ResultSet'>       

這個 trs 是表格內所有 tr 元素的 Tag 物件依序組成的 ResultSet 物件 (這表示裡面含有一個以上 Tag 物件), 可以用 [索引] 取得裡面的每個 tr 元素 Tag 物件, 其 text 屬性儲存的是該元素開頭標籤與結尾標籤所夾的文字內容, 例如最近一個交易日收盤資料是放在表格的第三列, 故要用索引 2 取得 : 

>>> tr=trs[2]         # 取得第三列 Tag 物件
>>> type(tr)          
<class 'bs4.element.Tag'>    
>>> tr   
<tr class="stockalllistbg2">
<td class="table-first-child">111/06/28</td>
<td align="right">120.80   </td>
<td align="right">120.80   </td>
<td align="right">119.25   </td>
<td align="right">119.80   </td>
<td align="right">10,801   </td>
</tr>
>>> tr.text 
'\n111/06/28\n120.80\xa0\xa0\xa0\n120.80\xa0\xa0\xa0\n119.25\xa0\xa0\xa0\n119.80\xa0\xa0\xa0\n10,801\xa0\xa0\xa0\n'

可見用 print() 顯示 tr 這個 Tag 物件會顯示此元素的完整 HTML 內容, 而其 text 屬性則是 tr 元素內所有的文字內容部分. 雖然 tr.text 就可以濾掉 td 元素而取得開高低收 (OHLC) 收盤資料, 但裡面還是包含了過多資訊 (例如跳行與日期欄位), 應該再用 findAll() 將所有 td 都找出來 : 

>>> tds=tr.findAll("td")   
>>> type(tds)     
<class 'bs4.element.ResultSet'>     

同樣地, 因為 tr 裡面含有多個 td 的 Tag 物件, 所以 findAll() 傳回的是 ResultSet 物件, 其中第一欄 td 存放的是交易日期, 第二欄之後才是收盤資料, 可以用串列生成式與切片將第一欄以後的股價資料轉成串列, 例如 :

>>> data=[td.text for td in tds[1:]]    # 取出第二欄以後的 td 內容存入串列 : 
>>> data   
['120.80\xa0\xa0\xa0', '120.80\xa0\xa0\xa0', '119.25\xa0\xa0\xa0', '119.80\xa0\xa0\xa0', '10,801\xa0\xa0\xa0']

其中的 \xa0 是網頁空格 &nbsp; 字元 (non-breaking space), 屬於 Latin1 擴展字元集, 與 ASCII 的空格 (\x20) 不同, 參考 :


利用字串的 replace() 方法即可去除 \xa0 字元 : 

>>> data=[td.text.replace("\xa0", "") for td in tds[1:]]   # 將 \xa0 以空字元取代
>>> data     
['120.80', '120.80', '119.25', '119.80', '10,801']

這裡還需要處理最後一欄成交量中的千位逗號, 同樣可用 replace() 取代掉. 另外也要將所有串列元素從字串型態轉成浮點數型態 : 

>>> data=[float(td.text.replace("\xa0", "").replace(",", "")) for td in tds[1:]]    
>>> data      
[120.8, 120.8, 119.25, 119.8, 10801.0]    

接下來要從表格第一列 trs[0] 中取得股票名稱, 此列如上所示只有一個 td 儲存格 (合併), 因此可直接用 trs[0].td.text 取得其文字內容 : 

>>> name=trs[0].td.text     
>>> name   
'個股股價行情表﹝0050 元大台灣50﹞'  

但真正需要的是位於兩個中括號內後半部的 "元大台灣50", 它與前面的股票代號之間有一個空格, 因此可以用字串的 split() 方法拆分後取得, 例如 : 

>>> name=trs[0].td.text.split(" ")[1]    
>>> name  
'元大台灣50﹞'

可見後面還有個中括號要去除, 這可用切片輕易完成 :

>>> name=trs[0].td.text.split(" ")[1][:-1]   
>>> name   
'元大台灣50' 

最後, 為了計算漲跌幅, 必須取得前一日之收盤價, 也就是表格中第二個收盤資料 (列索引 3), 收盤價位於第五個欄位 (欄索引 4), 例如 : 

>>> trs[3].findAll("td")[4].text    
'120.95\xa0\xa0\xa0'    

同樣只要套用上面取得最近股票資料做法即可 :

>>> last_close=float(trs[3].findAll("td")[4].text.replace("\xa0", "").replace(",", ""))  
>>> last_close       
120.95   

這樣就取得所有的數據了, 茲將以上程式碼寫成如下函式, 它會接收股票代號參數, 並將取得之資料以字典傳回 : 

def wearn_crawler(sid):
    res=requests.get(f"https://stock.wearn.com/cdata.asp?kind={sid}")
    res.encoding="big5"
    html=BeautifulSoup(res.text, "html.parser")
    table=html.findAll("table")[0] 
    trs=table.findAll("tr")
    tr=trs[2]
    tds=tr.findAll("td")
    data=[float(td.text.replace("\xa0", "").replace(",", "")) for td in tds[1:]]
    name=trs[0].td.text.split(" ")[1][:-1]
    last_close=float(trs[3].findAll("td")[4].text.replace("\xa0", "").replace(",", ""))
    return {
        "name": name,
        "open": data[0],
        "high": data[1],
        "low": data[2],
        "close": data[3],
        "last": last_close
    }

例如 :

>>> data=wearn_crawler('0050')    
>>> data   
{'name': '元大台灣50', 'open': 120.8, 'high': 120.8, 'low': 119.25, 'close': 119.8, 'last': 120.95}


(3). 更新股票看板 :  

完成爬蟲程式後, 便可以撰寫股票看板工作表的更新程式, 基本構想是讀取上面所建立的 "股票看板" 的股票代號欄位, 然後在用迴圈走訪此股票清單, 針對每一支股票去呼叫上面的爬蟲函式, 取得資料後寫入該股票的相關欄位. 

首先是讀取所觀察的全部股票代號, 在上面呼叫 create_workbook() 建立工作簿時會傳回工作簿物件 wb 與工作表物件 ws, 股票代號位於工作表中的 B 欄位, 其長度可用工作表的 max_row 屬性或 B 欄位的儲存格物件長度取得 :

>>> wb     
<openpyxl.workbook.workbook.Workbook object at 0x000002A07FCFB208>
>>> wb.worksheets     
[<Worksheet "股票看板">]
>>> ws    
<Worksheet "股票看板">    
>>> ws.max_row          
>>> ws['B']    
(<Cell '股票看板'.B1>, <Cell '股票看板'.B2>, <Cell '股票看板'.B3>, <Cell '股票看板'.B4>, <Cell '股票看板'.B5>, <Cell '股票看板'.B6>)   
>>> len(ws['B'])     
>>> last_row=ws.max_row   
>>> last_row     
6

可見目前預設的觀察清單有 6-1=5 支股票 (扣掉第一列的欄索引), 可用 ws['A2'].value ~
ws['A6'].value 取得這些股票代號 :

>>> ws["A2"].value   
'0050'
>>> ws["A3"].value   
'0056'
>>> ws["A4"].value   
'2330'
>>> ws["A5"].value   
'2303'
>>> ws["A6"].value   
'2412'

由於股票清單長度是可變的, 可在迴圈中以 ws[f''A{i}"].value 來取得代號 :

>>> for i in range(2, last_row + 1):   
    stock_id=ws[f"A{i}"].value    
    print(stock_id)     
           
0050
0056
2330
2303
2412

然後在迴圈中以 stock_id 呼叫爬蟲函式擷取股票收盤資料, 清洗後寫入工作表儲存格中 : 

>>> for i in range(2, last_row + 1):   
    stock_id=ws[f"A{i}"].value      
    data=wearn_crawler(stock_id)       
    print(data)    
    
{'name': '元大台灣50', 'open': 120.8, 'high': 120.8, 'low': 119.25, 'close': 119.8, 'last': 120.95}
{'name': '元大高股息', 'open': 28.74, 'high': 28.74, 'low': 28.35, 'close': 28.53, 'last': 28.86}
{'name': '台積電', 'open': 496.0, 'high': 500.0, 'low': 496.0, 'close': 497.5, 'last': 498.5}
{'name': '聯電', 'open': 42.95, 'high': 42.95, 'low': 41.85, 'close': 41.85, 'last': 42.9}
{'name': '中華電', 'open': 128.5, 'high': 129.5, 'low': 128.5, 'close': 129.0, 'last': 128.5}

可見都能正確地爬到所要的資料, 將收盤價 close 減掉昨收價 last 後再除以昨收價即可得到計算今日漲跌幅, 也可以 close 除以 last 後再減 1 :

帳跌幅=data["close"]/data["last"] - 1 

然後就可以將它們存入工作表內 : 

>>> for i in range(2, last_row + 1):    
    stock_id=ws[f"A{i}"].value   
    data=wearn_crawler(stock_id)   
    print(data)    
    ws[f'B{i}'].value=data["name"]   
    ws[f'C{i}'].value=data["open"]      
    ws[f'D{i}'].value=data["high"]       
    ws[f'E{i}'].value=data["low"]      
    ws[f'F{i}'].value=data["close"]      
    ws[f'G{i}'].value=data["last"]   
    ws[f'H{i}'].value=data["close"]/data["last"] - 1      
    
{'name': '元大台灣50', 'open': 120.8, 'high': 120.8, 'low': 119.25, 'close': 119.8, 'last': 120.95}
{'name': '元大高股息', 'open': 28.74, 'high': 28.74, 'low': 28.35, 'close': 28.53, 'last': 28.86}
{'name': '台積電', 'open': 496.0, 'high': 500.0, 'low': 496.0, 'close': 497.5, 'last': 498.5}
{'name': '聯電', 'open': 42.95, 'high': 42.95, 'low': 41.85, 'close': 41.85, 'last': 42.9}
{'name': '中華電', 'open': 128.5, 'high': 129.5, 'low': 128.5, 'close': 129.0, 'last': 128.5}

將工作簿物件存檔後以 Excel 軟體開啟 : 

>>> wb.save('twstock_dashboard.xlsx')




可見股價資料都已正確寫入 xlsx 檔案內了. 不過, 還有最近更新欄位 (I2) 還沒處理, 此欄位用來記錄資料更新的時間, 這需要用到 time 模組的 strftime() 函式, 使用的日期時間格式為 "%Y%m%d %H:%M:%S", 例如 :

>>> import time    
>>> time.strftime("%Y%m%d %H:%M:%S")    
'20220629 14:02:57'   

關閉檔案後於 I2 欄位寫入更新時間, 再次存檔 : 

>>> ws['I2'].value=time.strftime("%Y%m%d %H:%M:%S")   
>>> wb.save('twstock_dashboard.xlsx')       

以 Excel 軟體開啟可見 I2 欄位已有資料 : 




茲將上面的程式碼寫成如下的 update_dashboard() 函式 :

def update_dashboard():
    last_row=ws.max_row
    for i in range(2, last_row + 1):
        stock_id=ws[f'A{i}'].value
        data=wearn_crawler(stock_id)
        print(data)
        ws[f'B{i}'].value=data["name"]
        ws[f'C{i}'].value=data["open"]
        ws[f'D{i}'].value=data["high"]
        ws[f'E{i}'].value=data["low"]
        ws[f'F{i}'].value=data["close"]
        ws[f'G{i}'].value=data["last"]
        ws[f'H{i}'].value=data["close"]/data["last"] - 1
    ws['I2'].value=time.strftime("%Y%m%d %H:%M:%S")
    print(f"最近更新時間 : {ws['I2'].value}")

呼叫此函式即可更新股票看板資料 :

>>> update_dashboard()  
{'name': '元大台灣50', 'open': 120.8, 'high': 120.8, 'low': 119.25, 'close': 119.8, 'last': 120.95}
{'name': '元大高股息', 'open': 28.74, 'high': 28.74, 'low': 28.35, 'close': 28.53, 'last': 28.86}
{'name': '台積電', 'open': 496.0, 'high': 500.0, 'low': 496.0, 'close': 497.5, 'last': 498.5}
{'name': '聯電', 'open': 42.95, 'high': 42.95, 'low': 41.85, 'close': 41.85, 'last': 42.9}
{'name': '中華電', 'open': 128.5, 'high': 129.5, 'low': 128.5, 'close': 129.0, 'last': 128.5}
最近更新時間 : 20220629 14:17:25

>>> wb.save('twstock_dashboard.xlsx')      

以 Excel 軟體再次開啟檔案可見 I2 欄位的時間已更新 : 




(4). 以 Line Notify 推播收盤訊息 :  

上面的股票看板雖然已能正確更新每日收盤資料, 但如果能將重要訊息例如股價漲跌幅等透過 Line Notify 訊息推播功能傳送到手機, 就可以不需要開啟股票 App 便能從最常用的 Line 得知所關心的股票收盤情形, 關於 Line Notify 用法參考 : 


首先要先在 Line Notify 官網申請一個權杖 (token), 然後開啟 twstock_dashbiard.xlsx 檔, 將此權杖填入 J2 儲存格中後存檔 : 




然後載入此 xlsx 檔後讀取 J2 儲存格即可得到權杖 :

>>> wb=xl.load_workbook('twstock_dashboard.xlsx')        
>>> ws=wb1.active     
>>> token=ws['J2'].value     
>>> token     
'ud7PaDL45fz849A0e1f5oaMCbRIkxMXapQCt7PfNkzz'     

發送推播訊息的功能可以用如下的函式來達成 : 

def notify(msg, token):
    url="https://notify-api.line.me/api/notify"
    headers={"Authorization": "Bearer " + token}
    payload={"message": msg}
    r=requests.post(url, headers=headers, params=payload)
    return "訊息發送成功!"

只要傳入訊息與權杖就可以完成推播了. 

最後要修改上面的 update_dashboard() 函式, 加入下列程式碼推播觀察股票的漲跌幅 : 

def update_dashboard():
    last_row=ws.max_row
    msg=['\n']   
    for i in range(2, last_row + 1):
        stock_id=ws[f'A{i}'].value
        data=wearn_crawler(stock_id)
        print(data)
        ws[f'B{i}'].value=data["name"]
        ws[f'C{i}'].value=data["open"]
        ws[f'D{i}'].value=data["high"]
        ws[f'E{i}'].value=data["low"]
        ws[f'F{i}'].value=data["close"]
        ws[f'G{i}'].value=data["last"]
        delta=round((data["close"]/data["last"] - 1) * 100, 2)    
        ws[f'H{i}'].value=delta   
        msg.append(f'{data["name"]} {data["close"]} ({delta}%)')               
    ws['I2'].value=time.strftime("%Y%m%d %H:%M:%S")
    print(f"最近更新時間 : {ws['I2'].value}")
    notify('\n'.join(msg), token)   

此處黃底高亮的是修改的部分, 在函式開頭新增了 msg 串列用來儲存各股收盤與漲跌幅資訊, 預建 '\n' 的目的式為了讓傳送的訊息與 Line 推播標題不要黏在一起. 漲跌幅計算部分增加了 delta 變數來記錄, 原始數據被乘以 100 轉成百分比, 並取到小數後第 2 位, 每支股票的股名, 收盤價, 與漲跌幅都放進 msg 串列中, 最後呼叫字串物件的 join() 以跳列字元串接, 結果如下 : 

>>> update_dashboard()    
{'name': '元大台灣50', 'open': 120.8, 'high': 120.8, 'low': 119.25, 'close': 119.8, 'last': 120.95}
{'name': '元大高股息', 'open': 28.74, 'high': 28.74, 'low': 28.35, 'close': 28.53, 'last': 28.86}
{'name': '台積電', 'open': 496.0, 'high': 500.0, 'low': 496.0, 'close': 497.5, 'last': 498.5}
{'name': '聯電', 'open': 42.95, 'high': 42.95, 'low': 41.85, 'close': 41.85, 'last': 42.9}
{'name': '中華電', 'open': 128.5, 'high': 129.5, 'low': 128.5, 'close': 129.0, 'last': 128.5}
最近更新時間 : 20220629 15:21:15
'訊息發送成功!'

這時我的 iPhone 手機的 Line 就馬上收到此訊息了 : 




Bingo! 


(5). 部署到樹莓派 :  

以上都是在互動環境中測試, 指令都是人為下達, 可以將程式部署到可 24 小時開機的低功率 Linux 嵌入式設備 (例如樹莓派或香蕉派等) 上, 然後用 crontab 來設定定時或週期性執行. 茲將上面的程式整理為如下之 twstock_dashboard.py 模組 : 

# twstock_dashboard.py
import time
import requests
import openpyxl as xl
from bs4 import BeautifulSoup

def create_dashboard(filename, stocks, token=''): # 初始化時執行一次
    wb=xl.Workbook()
    ws=wb.active
    ws.title='股票看板'
    # 建立第一列的欄名
    ws['A1'].value='股票代號'
    ws['B1'].value='股票名稱'
    ws['C1'].value='開盤價'
    ws['D1'].value='最高價'
    ws['E1'].value='最低價'
    ws['F1'].value='成交價'
    ws['G1'].value='昨日收盤價'
    ws['H1'].value='帳跌幅'
    ws['I1'].value='最近更新'
    ws['J1'].value='Line Notify Token'
    # 將欲觀察的股票代號填入 A2, A3, A4, ... 儲存格
    for idx, stock in enumerate(stocks): 
        ws[f'A{idx + 2}'].value=stock
    # 將 Line Notify token 填入 J2 儲存格
    ws['J2'].value=token
    # 將工作簿存檔
    try:
        wb.save(filename)
    except Exception:
        print(f"Permission Error : {filename} 開啟中, 請關閉")
    return wb, ws

def wearn_crawler(sid):
    res=requests.get(f"https://stock.wearn.com/cdata.asp?kind={sid}")
    res.encoding="big5"
    html=BeautifulSoup(res.text, "html.parser")
    table=html.findAll("table")[0] 
    trs=table.findAll("tr")
    tr=trs[2]
    tds=tr.findAll("td")
    data=[float(td.text.replace("\xa0", "").replace(",", "")) for td in tds[1:]]
    name=trs[0].td.text.split(" ")[1][:-1]
    last_close=float(trs[3].findAll("td")[4].text.replace("\xa0", "").replace(",", ""))
    return {
        "name": name,
        "open": data[0],
        "high": data[1],
        "low": data[2],
        "close": data[3],
        "last": last_close
    }

def notify(msg, token):
    url="https://notify-api.line.me/api/notify"
    headers={"Authorization": "Bearer " + token}
    payload={"message": msg}
    r=requests.post(url, headers=headers, params=payload)
    return "訊息發送成功!"

def update_dashboard(ws):
    last_row=ws.max_row  # 取得總列數
    token=ws['J2'].value
    msg=['']             # 儲存推播訊息之串列
    for i in range(2, last_row + 1):  # 讀取欲觀察股票清單
        stock_id=ws[f'A{i}'].value
        data=wearn_crawler(stock_id)  # 呼叫爬蟲函式擷取聚財網收盤資料
        print(data)
        ws[f'B{i}'].value=data["name"]
        ws[f'C{i}'].value=data["open"]
        ws[f'D{i}'].value=data["high"]
        ws[f'E{i}'].value=data["low"]
        ws[f'F{i}'].value=data["close"]
        ws[f'G{i}'].value=data["last"]
        delta=round((data["close"]/data["last"] - 1) * 100, 2) # 計算漲跌幅
        ws[f'H{i}'].value=delta
        msg.append(f'{data["name"]} {data["close"]} ({delta}%)')            
    ws['I2'].value=time.strftime("%Y%m%d %H:%M:%S") 
    print(f"最近更新時間 : {ws['I2'].value}")
    notify('\n'.join(msg), token)  # 呼叫 Line Notify 函式傳送推播訊息

此模組為上面互動測試中的函式總集, 在編輯時做了一些修正, 例如推播訊息串列 msg 的預設值改成了空字串, 因為原先用 '\n' 會使推播標題與訊息內容之間出現一個空列. 此模組的對外界面主要是下列兩個函式 :
  • create_dashboard(filename, stocks, token='') : 系統初始化用
  • update_dashboard(ws) : 更新股票看板用
使用前要匯入此模組 :

import twstock_dashboard as td

然後撰寫需要自動執行的程式 twstock_dashboard_update.py, 它主要的任務是開啟工作簿與工作表, 呼叫爬蟲與推播函式 update_dashboard(), 最後將工作簿存檔, 程式碼如下 : 

# twstock_dashboard_update.py
import openpyxl as xl
import twstock_dashboard as td

wb=xl.load_workbook('twstock_dashboard.xlsx')   # 開啟工作簿檔案
ws=wb.active   # 取得目前工作表
td.update_dashboard(ws)  # 更新股票看板 (爬聚財網 + Line Notify 推播)
wb.save('twstock_dashboard.xlsx')  # 將工作簿存檔

接著將這兩個檔案用 WinSCP 傳送到樹莓派上 : 




然後開啟樹莓派終端機, 進入 Python3 互動環境, 用下列指令手動初始化建立工作簿 : 

Python 3.7.3 (default, Jan 22 2021, 20:04:44)
[GCC 8.3.0] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import openpyxl as xl     
>>> import twstock_dashboard as td   
>>> filename='twstock_dashboard.xlsx'      
>>> stocks=['0050', '0056', '2330', '2303', '2412']   
>>> token='ud7PaDL45fz849A0e1f5oaMCbRIkxMXapQCt7PfNkzz'   
>>> td.create_dashboard(filename, stocks, token)   
>>> exit()   

這樣就會建立一個 twstock_dashboard.xlsx 工作簿檔案. 以上程式碼雖然也可以寫成一個 .py 模組, 但最好還是像上面這樣使用互動介面以人工方式下指令, 不要存成檔案, 因為 token 放在程式中很犯規. 用 ls 檢視檔案 : 

pi@raspberrypi:~ $ ls twstock* -ls    
4 -rw-r--r-- 1 pi pi 2918  6月 30 11:26 twstock_dashboard.py     
4 -rw-r--r-- 1 pi pi  308  6月 30 11:25 twstock_dashboard_update.py    
8 -rw-r--r-- 1 pi pi 5084  6月 30 14:24 twstock_dashboard.xlsx      

可見已經順利建立工作簿檔案了. 接著用下列指令手動更新收盤的股價資料 : 

pi@raspberrypi:~ $ python3 twstock_dashboard_update.py    
{'name': '元大台灣50', 'open': 118.7, 'high': 119.55, 'low': 118.5, 'close': 118.8, 'last': 119.8}
{'name': '元大高股息', 'open': 28.29, 'high': 28.39, 'low': 28.1, 'close': 28.14, 'last': 28.53}
{'name': '台積電', 'open': 496.0, 'high': 498.5, 'low': 491.0, 'close': 491.0, 'last': 497.5}
{'name': '聯電', 'open': 41.25, 'high': 41.55, 'low': 40.7, 'close': 40.9, 'last': 41.85}
{'name': '中華電', 'open': 129.0, 'high': 130.5, 'low': 128.5, 'close': 130.5, 'last': 129.0}
最近更新時間 : 20220630 14:35:46

手機馬上就收到 Line Notify 推播訊息了 :



 
接下來要用 chmod 指令將看板更新程式 twstock_dashboard_update.py 改為可執行檔 :

pi@raspberrypi:~ $ sudo chmod +x /home/pi/twstock_dashboard_update.py    
pi@raspberrypi:~ $ ls twstock* -ls      
4 -rw-r--r-- 1 pi pi 2918  6月 30 11:26 twstock_dashboard.py
4 -rwxr-xr-x 1 pi pi  308  6月 30 11:25 twstock_dashboard_update.py   
8 -rw-r--r-- 1 pi pi 5359  6月 30 14:35 twstock_dashboard.xlsx

這樣就可以設定 crontab 來自動執行看板更新程式了 : 

pi@raspberrypi:~ $ crontab -e    
no crontab for pi - using an empty one

Select an editor.  To change later, run 'select-editor'.
  1. /bin/nano        <---- easiest
  2. /usr/bin/vim.tiny
  3. /bin/ed

Choose 1-3 [1]: 1
crontab: installing new crontab

選擇 nano 來編輯 crontab, 先用每 5 分鐘觸發執行一次, 寫法如下 :

*/5 * * * * /usr/bin/python3 /home/pi/twstock_dashboard_update.py   




按 Ctrl + O 存檔後再按 Ctrl + X 跳出 Nano, 再用 crontab -l 確認內容 :

pi@raspberrypi:~ $ crontab -l    
*/5 * * * * /usr/bin/python3 /home/pi/twstock_dashboard_update.py

結果確實能每五分鐘收到 Line Notify 推播訊息 : 




測試 OK 後就可以修改 crontab 設定, 改為每周一到周五 (交易日) 的下午四點與五點各抓一次, 因為聚財網每日收盤資料大約在 15:50 左右更新, 為了避免爬蟲在 16:00 抓資料時網路剛好斷線, 所以五點再抓一次 : 

0 16-17 * * 1-5 /usr/bin/python3 /home/pi/twstock_dashboard_update.py 

將 crontab 存檔即生效, 果然 16:00 與 17:00 都有收到推播 :




OK! 這樣就完成樹莓派上的自動化部署了. 

參考 :


2022-06-30 補充 :

修改了 twstock_dashboard_update.py 裡面 update_dashboard() 函式的部分程式碼, 一是更改時間格式為 "%Y-%m-%d %H:%M:%S", 其次是將更新時間加到推播訊息最後面 :

def update_dashboard(ws):
    last_row=ws.max_row  # 取得總列數
    token=ws['J2'].value
    msg=['']             # 儲存推播訊息之串列
    for i in range(2, last_row + 1):  # 讀取欲觀察股票清單
        stock_id=ws[f'A{i}'].value
        data=wearn_crawler(stock_id)  # 呼叫爬蟲函式擷取聚財網收盤資料
        print(data)
        ws[f'B{i}'].value=data["name"]
        ws[f'C{i}'].value=data["open"]
        ws[f'D{i}'].value=data["high"]
        ws[f'E{i}'].value=data["low"]
        ws[f'F{i}'].value=data["close"]
        ws[f'G{i}'].value=data["last"]
        delta=round((data["close"]/data["last"] - 1) * 100, 2) # 計算漲跌幅
        ws[f'H{i}'].value=delta
        msg.append(f'{data["name"]} {data["close"]} ({delta}%)')            
    ws['I2'].value=time.strftime("%Y-%m-%d %H:%M:%S") 
    print(f"最近更新時間 : {ws['I2'].value}")
    msg.append(ws['I2'].value)  # 訊息以更新時間結尾
    notify('\n'.join(msg), token)  # 呼叫 Line Notify 函式傳送推播訊息

結果如下 :




這樣往回看訊息時就很清楚這是哪天的收盤資料. 

2022-07-02 補充 :

今天修改了 notify() 函式如下 :

def notify(msg, token):
    url="https://notify-api.line.me/api/notify"
    headers={"Authorization": "Bearer " + token}
    payload={"message": msg}
    r=requests.post(url, headers=headers, params=payload)
    if r.status_code==requests.codes.ok:   
        return '訊息發送成功!'   
    else:   
        return f'訊息發送失敗: {r.status_code}'   

原程式沒有判別回應碼一律回應 '訊息發送成功' 似乎不妥, 故加上 if else 判斷. 

高科大還書 1 本 (網管大殺器)

因為有預約書 "Python 股票演算法交易(第二版)" 取書期限今日截止, 中午午休時間到母校圖書館取書, 但目前是 30 冊滿借情況, 只好犧牲下面這本去換 :


借此書主要是裡面有談到正規表示法, 這在分析網管報表很有用. 這兩周要著手清從母校所借的書籍後再回借, RegExp 還有一些要收尾. 另外在架上挖到寶, 找到下面這本 "機器學習設計模式" :




上周 TensorFlow 內訓時老師大力推薦了這本書, 可惜目前滿借無法借出, 殘念 ~~~. 為今之計就是兩天快施展吸星大法, 本周只要消化掉一本就再跑一趟. 

2022年6月27日 星期一

訂同步輻射中心招待所住宿

因二哥報名參加 8/22 國家實驗研究院半導體研究中心的暑期電路設計實作課程, 地點在新竹交大隔壁的同步輻射中心, 為了上課方便就訂了對面的招待所, 兩人房一天 1400 元, 開放非員工入住 (當天 22:30 前亦可臨時入住), 參考 : 


2022年6月26日 星期日

Python 學習筆記 : 使用 OpenPyXL 操作 EXCEL 試算表 (四)

本篇繼續 OpenPyXL 套件的測試, 主題是統計圖表的繪製, 本系列之前的文章參考:


參考書籍如下 :
本篇測試中用到的資料來自之前 Matplotlib 測試所使用的數據, 參考 :
首先歸納整理前一篇 (三) 關於儲存格格式化的指令範例 :
  • 字型設定 :
    from openpyxl.styles import Font  
    sheet['A2'].font=Font(name='Arial', size=14, bold=True, color='0000ff')
    sheet['A3'].font=Font(name='微軟正黑體', size=12, italic=True, strike=True)
  • 列高與欄寬設定 : 
    sheet.row_dimensions[2].height=40             # 設定第 2 列列高
    sheet.column_dimensions['A'].width=60      # 設定 A 欄欄寬
  • 對齊設定 :
    from openpyxl.styles import Alignment 
    sheet['A2'].alignment=Alignment(vertical='top', horizontal='center')
    sheet['A3'].alignment=Alignment(vertical='center', horizontal='right') 
    vertical 參數 : 'top', 'center', 'bottom'
    horizontal 參數 : 'left', 'center', 'right'
  • 框線設定 :
    from openpyxl.styles import Border
    from openpyxl.styles import Side
    side=Side(color='FF0000', style='thick')
    border=Border(left=side, right=side, top=side, bottom=side)
    sheet['B3'].border=border 
    style 參數 : 'hair', 'thin', 'medium', 'double', 'thick', 'dotted', 'dashed', 'mediumDashed', 'dashdot', 'dashDotDot', 'slantDashDot', 'mediumDashDot', 'mediumDashDotDot'
  • 背景色設定 : 
    from openpyxl.styles import PatternFill
    sheet['D3'].fill=PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')  # 
    fill_type 參數 : 'solid', 'gray0625', 'gray125', 'lightGray', 'mediumGray', 'darkGray', 'lightGrid', 'darkGrid', 'lightHorizontal', 'darkHorizontal', 'lightVertical', darkVertical', 'lightUp', 'darkUp', 'lightDown', 'darkDown', 'lightTrellis', 'darkTrellis'
  • 格式化條件 (背景色) :
    from openpyxl.formatting.rule import CellIsRule
    from openpyxl.styles import PatternFill
    fill=PatternFill(start_color='FFFF00',  end_color='FFFF00', fill_type='solid') 
    rule=CellIsRule(operator='lessThan', formula=[60], stopIfTrue=True, fill=fill)  
    sheet.conditional_formatting.add('B3:D7', rule)   

8. 繪製統計圖表 :  

OpenPyXL 套件的 openpyxl.chart 子模組中提供了可用來繪製統計圖表的類別 : 
  • BarChart (長條圖)
  • LineChart (折線圖)
  • BarChart (圓餅圖)
  • AreaChart (區域圖)
  • ScaterChart (散佈圖)
  • BubbleChart (泡泡圖)
  • RadarChart (雷達圖) 
  • StockChart (股票圖)
  • BarChart3D (3D 直條圖)
  • PieChart3D (3D 圓餅圖)
  • AreaChart3D (3D 區域圖)
使用 OpenPyXL 繪製圖表除了要匯入這些類別外, 還需要匯入 openpyxl.chart.Reference 類別, 此類別用來將工作表的儲存格資料與軸的標籤資料引入圖表物件中, 呼叫此類別之建構子 Reference() 即可建立 Reference 物件 :  

Reference(worksheet, min_col, maxcol, min_row, max_row)

參數 worksheet 為工作表物件, min_col 為儲存格範圍最左欄索引; max_col 為最右欄索引; min_row 為最上列索引; max_row 為最下列索引, 這些索引均為整數. 由於工作表第一列通常是欄位名稱, max_row 可以傳入工作表物件的 max_row 屬性值, 這樣就不需要去查最後一列索引了. 

繪製圖表程序如下 :
  1. 建立圖表物件 (設定圖表標題與座標軸標題)
  2. 建立資料的 Reference 物件 (指定工作表中的儲存格範圍)
  3. 建立軸或標籤的 Reference 物件 (指定工作表中的儲存格範圍)
  4. 呼叫圖表物件的 add_data() 方法加入資料的 Reference 物件
  5. 呼叫圖表物件的 set_categories() 方法加入軸或標籤的 Reference 物件
  6. 呼叫工作表物件的 add_chart() 方法將圖表物件插入指定儲存格中
其中步驟 1, 2, 3 先後順序不重要. 

官網教學文件參考 :



(1). 繪製長條圖 :  

長條圖用來展示分類或分組資料的大小差異, 結構上有垂直與水平兩種類型, BarChart 類別預設是垂直長條圖. 官網範例參考 :


繪製長條圖須匯入 BarChart 與 Refernece 兩個類別 : 

>>> import openpyxl as xl   
>>> from openpyxl.chart import BarChart, Reference   
>>> Reference    
<class 'openpyxl.chart.reference.Reference'>   
>>> BarChart    
<class 'openpyxl.chart.bar_chart.BarChart'>   

呼叫 BarChart() 建立一個 BarChart 圖表物件 : 

>>> chart=BarChart()      
>>> type(chart)   
<class 'openpyxl.chart.bar_chart.BarChart'>    

利用 eval() 與 dir() 來檢視 BarChart 物件的公開成員 : 

>>> members=dir(chart)     
>>> for mbr in members:   
    obj=eval('chart.' + mbr)       
    if not mbr.startswith('_'):     
        print(mbr, type(obj))   
        
add_data <class 'method'>
anchor <class 'str'>
append <class 'method'>
axId <class 'list'>
barDir <class 'str'>
dLbls <class 'NoneType'>
dataLabels <class 'NoneType'>
display_blanks <class 'str'>
extLst <class 'openpyxl.descriptors.base.Typed'>
from_tree <class 'method'>
gapWidth <class 'float'>
graphical_properties <class 'NoneType'>
grouping <class 'str'>
height <class 'float'>
idx_base <class 'int'>
layout <class 'NoneType'>
legend <class 'openpyxl.chart.legend.Legend'>
mime_type <class 'str'>
namespace <class 'NoneType'>
overlap <class 'NoneType'>
path <class 'str'>
pivotFormats <class 'tuple'>
pivotSource <class 'NoneType'>
plot_area <class 'openpyxl.chart.plotarea.PlotArea'>
roundedCorners <class 'NoneType'>
ser <class 'list'>
serLines <class 'NoneType'>
series <class 'list'>
set_categories <class 'method'>
style <class 'NoneType'>
tagname <class 'str'>
title <class 'NoneType'>
to_tree <class 'method'>
type <class 'str'>
varyColors <class 'NoneType'>
visible_cells_only <class 'bool'>
width <class 'int'>
x_axis <class 'openpyxl.chart.axis.TextAxis'>
y_axis <class 'openpyxl.chart.axis.NumericAxis'>

常用的屬性如下表 : 


 BarChart 物件常用屬性 說明
 title 圖表之標題
 x_axis X 軸物件 (TextAxis), 可用其 title 屬性存取 X 軸標題文字
 y_axis Y 軸物件 (NumberAxis), 可用其 title 屬性存取 Y 軸標題文字
 type 圖表類型 : 'col'=垂直長條圖 (預設), 'bar'=水平長條圖
 style 樣式 (整數, 預設 None)
 width 寬度 (整數, 預設 15)
 height 高度 (整數, 預設 7.5)


常用的方法如下表 :


 BarChart 物件常用方法 說明
 append(series) 將一組資料 (Series 物件) 加入圖表中 
 add_data(y) 將 Y 軸資料 (Reference 物件) 加入圖表物件中
 add_categories(x) 將 X 軸類別 (Reference 物件) 加入圖表物件中


注意, add_data() 還有一個備選參數 titles_from_data, 若設為 True 會將第一列視為圖例 (legend) 的來源, 這時在建立 Y 軸資料的 Reference 物件時就要納入第一列. 

首先來繪製單組 (單變數) 的長條圖, 下面以台灣 2020 年總統大選得票數統計資料為例來繪製長條圖, 先建立一個空白將資料寫入工作表 : 

>>> wb=xl.Workbook()     
>>> ws=wb.active   
>>> ws     
<Worksheet "Sheet">   
>>> votes=[['候選人', '得票數'],        
       ['宋楚瑜', 608590],    
       ['韓國瑜', 5522119],       
       ['蔡英文', 8170231]]    
>>> for row in votes:           # 將串列逐列寫入工作表
    ws.append(row)    
    
>>> for row in ws.rows:      # 檢視工作表內容       
    for cell in row:           
        print(cell. value, end='\t')             
    print()     
   
候選人 得票數
宋楚瑜 608590
韓國瑜 5522119
蔡英文 8170231

接下來就可以根據這兩個範圍 X, Y 軸的 Reference 物件 : 

>>> x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row) 
>>> y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
>>> type(x)   
<class 'openpyxl.chart.reference.Reference'>
>>> type(y)    
<class 'openpyxl.chart.reference.Reference'>

此處因 X 軸分類都在第一欄, 故 min_col 與 max_col 均為 1, 分類內容從第二列至最後一列. Y 軸資料都在第二欄, 故 min_col 與 max_col 均為 2. 要注意的是, 雖然資料內容是從第二列至最後列, 但因為需要用第一列的欄名當圖例 (legend), 所以資料的 min_row 要從第一列開始, 這樣才會自動將欄名設為圖例. 

接著便可將 X 軸與 Y 軸的 Reference 物件分別呼叫圖表物件的 set_categories() 與 add_data() 加入圖表中, 並且設定圖表標題與兩座標軸之標題 : 

>>> chart.add_data(y, titles_from_data=True)      # 將 Y 軸資料加入圖表
>>> chart.set_categories(x)                                       # 將 X 軸類別加入圖表
>>> chart.title='2020 總統大選得票數'     # 圖表標題
>>> chart.x_axis.title='候選人'                  # X 軸標題
>>> chart.y_axis.title='得票數'                  # Y 軸標題

注意, 此處 add_data() 的參數 titles_from_data 設為 True 表示要以第一列的欄名當圖例 (所以在上面在建立 Y 軸資料的 Reference 物件時 min_row 要設為 1). 最後將圖表物件插入到儲存格 : 
  
>>> ws.add_chart(chart, 'C1')   
>>> wb.save('2020_presidential.xlsx')    

用 Excel 開啟檔案結果如下 : 




茲將以上繪圖程式碼整理如下 : 

範例 1 : 2020 總統大選得票數垂直長條圖 [原始碼]

import openpyxl as xl
from openpyxl.chart import BarChart, Reference
wb=xl.Workbook()
ws=wb.active
votes=[['候選人', '得票數'],        
       ['宋楚瑜', 608590],    
       ['韓國瑜', 5522119],       
       ['蔡英文', 8170231]]
for row in votes:    
    ws.append(row)
chart=BarChart()
chart.title='2020 總統大選得票數'
chart.x_axis.title='候選人'
chart.y_axis.title='得票數'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'C1')
wb.save('2020_presidential_1.xlsx')


如果要繪製垂直長條圖的話, 只要將圖表物件的 type 屬性設為 'bar' 即可 :

chart.type='bar'    

例如 : 

範例 2 : 2020 總統大選得票數水平長條圖 [原始碼]

import openpyxl as xl
from openpyxl.chart import BarChart, Reference
wb=xl.Workbook()
ws=wb.active
votes=[['候選人', '得票數'],        
       ['宋楚瑜', 608590],    
       ['韓國瑜', 5522119],       
       ['蔡英文', 8170231]]
for row in votes:    
    ws.append(row)
chart=BarChart()
chart.title='2020 總統大選得票數'
chart.x_axis.title='候選人'
chart.y_axis.title='得票數'
chart.type='bar'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'C1')
wb.save('2020_presidential_2.xlsx')

結果如下 : 




繪製多組資料的長條圖作法相同, 差別只是 Y 軸資料是一欄以上而已, 

範例 3 : 兩組資料之長條圖-ETF 殖利率比較  [原始碼]

import openpyxl as xl
from openpyxl.chart import BarChart, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['年份', '0056殖利率', '0050殖利率'],        
          ['2015', 4.33, 3.01],
          ['2016', 5.67, 1.28],
          ['2017', 3.78, 6.1],
          ['2018', 5.62, 7.1],
          ['2019', 6.7, 7.22]]  
for row in dividend:    
    ws.append(row)
chart=BarChart()
chart.title='0056 vs 0050 殖利率'
chart.x_axis.title='ETF'
chart.y_axis.title='殖利率'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=3, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'D1')
wb.save('ETF_dividend_yield.xlsx')

注意此處 Y 軸資料位於 B, C 兩欄, 故 max_col 設為 3, 對於多組資料 max_col 可以設為 ws.max_col.

結果如下 :




可見由於在 add_data() 中有傳入 titles_from_data=True, 且建立 Y 軸資料的 Reference 物件時 min_row 設為 1, 將第一列的欄位名稱納入資料中, 繪製圖表時就會自動以欄名作為圖例, 這使所繪製的多組資料很有可讀性. 

只要將上面範例中匯入的 BarChart 類別改成 BarChart3D 就能繪製 3D 長條圖了, 例如 :

範例 4 : 兩組資料之 3D 長條圖-ETF 殖利率比較  [原始碼]

import openpyxl as xl
from openpyxl.chart import BarChart3D, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['年份', '0056殖利率', '0050殖利率'],        
          ['2015', 4.33, 3.01],
          ['2016', 5.67, 1.28],
          ['2017', 3.78, 6.1],
          ['2018', 5.62, 7.1],
          ['2019', 6.7, 7.22]]  
for row in dividend:    
    ws.append(row)
chart=BarChart3D()   
chart.title='0056 vs 0050 殖利率'
chart.x_axis.title='ETF'
chart.y_axis.title='殖利率'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=3, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'D1')
wb.save('ETF_dividend_yield_3D.xlsx')

結果如下 : 




(2). 繪製折線圖 :  

折線圖是將各個資料點以直線連接的統計圖, 官網範例參考 :


以上面的 0056 vs 0050 殖利率為例, 只要匯入 LineChart 類別替換即可繪製折線圖 : 

範例 5 : 兩組資料之折線圖-ETF 殖利率比較  [原始碼]

import openpyxl as xl
from openpyxl.chart import LineChart, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['年份', '0056殖利率', '0050殖利率'],        
          ['2015', 4.33, 3.01],
          ['2016', 5.67, 1.28],
          ['2017', 3.78, 6.1],
          ['2018', 5.62, 7.1],
          ['2019', 6.7, 7.22]]  
for row in dividend:    
    ws.append(row)
chart=LineChart()
chart.title='0056 vs 0050 殖利率'
chart.x_axis.title='ETF'
chart.y_axis.title='殖利率'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=3, min_row=1, max_row=ws.max_row)  
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'D1')
wb.save('ETF_dividend_yield_linechart.xlsx')

結果如下 : 




(3). 繪製圓餅圖 :  

圓餅圖用來顯示一個數值資料相對於全部資料的大小相對關係, 只能用於單一變數 (一組序列資料) 繪圖. 官網範例參考 :


繪製圓餅圖須匯入 PieChart 與 Reference 類別, 其用法與上面直條圖/折線圖類似, 但 PieChart 物件沒有 x_axis 與 y_axis 屬性, 例如 : 

範例 6 : 資產配置圓餅圖  [原始碼]

import openpyxl as xl
from openpyxl.chart import PieChart, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['資產項目', '配置比率'],        
          ['股票', 60],
          ['基金', 20],
          ['現金', 10],
          ['黃金', 5],
          ['不動產', 5]]  
for row in dividend:    
    ws.append(row)
chart=PieChart()    
chart.title='資產配置比例'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'D1')
wb.save('asset_allocation.xlsx')

結果如下 :




只要將上面範例中的 PieChart 改成 PieChart3D 就可以繪製 3D 圓餅圖了, 例如 :

範例 7 : 資產配置 3D 圓餅圖  [原始碼]

import openpyxl as xl
from openpyxl.chart import PieChart3D, Reference
wb=xl.Workbook()
ws=wb.active
dividend=[['資產項目', '配置比率'],        
          ['股票', 60],
          ['基金', 20],
          ['現金', 10],
          ['黃金', 5],
          ['不動產', 5]]  
for row in dividend:    
    ws.append(row)
chart=PieChart3D()    
chart.title='資產配置比例'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
ws.add_chart(chart, 'C1')
wb.save('asset_allocation_3D.xlsx')

結果如下 : 




如果要凸顯圓餅途中的某個分類之扇形, 讓它從圓餅中分離 (explosion) 開來, 這需要從 openpyxl.series 子模組中匯入 DataPoint 類別來建立一個 DataPoint 物件 : 

>>> from openpyxl.series import DataPoint
>>> dir(DataPoint)    
['__add__', '__attrs__', '__class__', '__copy__', '__delattr__', '__dict__', '__dir__', '__doc__', '__elements__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__module__', '__namespaced__', '__ne__', '__nested__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', 'bubble3D', 'explosion', 'extLst', 'from_tree', 'graphicalProperties', 'idx', 'idx_base', 'invertIfNegative', 'marker', 'namespace', 'pictureOptions', 'spPr', 'tagname', 'to_tree']

此類別的 idx 屬性用來指定要分離的類別索引 (注意, 這是 0 起始索引); 而 explosion 屬性則用來指定分離的程度 (0~100), 例如 :

>>> data_point=DataPoint(idx=1, explosion=20) # idx=1 為第 2 個類別要分離  
>>> type(data_point)    
<class 'openpyxl.chart.marker.DataPoint'>   

此處 idx=1 表示是要分離第 2 個類別, 注意 idx 是 0 起始, 以上面資產配置比例為例, idx=1 是基金, 而 idx=0 是股票. 

然後將圖表中的 Series[0] 物件之 data_points 屬性指定為此 DataPoint 串列即可 : 

>>> type(chart.series[0])   
<class 'openpyxl.chart.series.Series'>      
>>> dir(chart.series[0])   
['__add__', '__attrs__', '__class__', '__copy__', '__delattr__', '__dict__', '__dir__', '__doc__', '__elements__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__module__', '__namespaced__', '__ne__', '__nested__', '__new__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', '__weakref__', 'bubble3D', 'bubbleSize', 'cat', 'dLbls', 'dPt', 'data_points', 'errBars', 'explosion', 'extLst', 'from_tree', 'graphicalProperties', 'identifiers', 'idx', 'idx_base', 'invertIfNegative', 'labels', 'marker', 'namespace', 'order', 'pictureOptions', 'shape', 'smooth', 'spPr', 'tagname', 'title', 'to_tree', 'trendline', 'tx', 'val', 'xVal', 'yVal', 'zVal']
>>> chart.series[0].data_points=[data_point]   

完整的程式碼如下 : 

範例 8 : 資產配置圓餅圖 : 分離凸顯某類別  [原始碼]

import openpyxl as xl
from openpyxl.chart import PieChart, Reference
from openpyxl.chart.series import DataPoint   
wb=xl.Workbook()
ws=wb.active
dividend=[['資產項目', '配置比率'],        
          ['股票', 60],
          ['基金', 20],
          ['現金', 10],
          ['黃金', 5],
          ['不動產', 5]]  
for row in dividend:    
    ws.append(row)
chart=PieChart()
chart.title='資產配置比例'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
data_point=DataPoint(idx=1, explosion=20)     
chart.series[0].data_points=[data_point]    
ws.add_chart(chart, 'C1')
wb.save('asset_allocation_with_explosion.xlsx')

結果如下 : 




將上面範例中的 PieChart 改成 PieChart3D 就可以繪製有凸顯分離效果的 3D 圓餅圖了, 例如 :

範例 9 : 資產配置 3D 圓餅圖 : 分離凸顯某類別  [原始碼]

import openpyxl as xl
from openpyxl.chart import PieChart3D, Reference
from openpyxl.chart.series import DataPoint   
wb=xl.Workbook()
ws=wb.active
dividend=[['資產項目', '配置比率'],        
          ['股票', 60],
          ['基金', 20],
          ['現金', 10],
          ['黃金', 5],
          ['不動產', 5]]  
for row in dividend:    
    ws.append(row)
chart=PieChart3D()   
chart.title='資產配置比例'
x=Reference(ws, min_col=1, max_col=1, min_row=2, max_row=ws.max_row)
y=Reference(ws, min_col=2, max_col=2, min_row=1, max_row=ws.max_row) 
chart.add_data(y, titles_from_data=True)
chart.set_categories(x)
data_point=DataPoint(idx=1, explosion=20)
chart.series[0].data_points=[data_point]      
ws.add_chart(chart, 'C1')
wb.save('asset_allocation_with_explosion_3D.xlsx')

結果如下 :