小狐狸事務所: 6月 2022

2022年6月30日星期四

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

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

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

本系列之前的文章參考:

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

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

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

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

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). 撰寫爬蟲擷取聚財網股價資訊 :

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

# https://stock.wearn.com/cdata.asp?kind=0050

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

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

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

<tr>

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

</tr>

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

<tr>

</tr>

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

</tr>

</tr>

</tr>

..... (略) .....

</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)

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

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

>>> type(table)

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

>>> trs=table.findAll("tr") # 從 table 中找尋全部 tr 標籤元素

>>> type(trs)

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

>>> tr=trs[2] # 取得第三列 Tag 物件

>>> type(tr)

>>> tr

</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)

同樣地, 因為 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 是網頁空格   字元 (non-breaking space), 屬於 Latin1 擴展字元集, 與 ASCII 的空格 (\x20) 不同, 參考 :

# python中去掉字符串中的\xa0、\t、\n

利用字串的 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

>>> 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-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 用法參考 :

# Python 學習筆記 : 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! 這樣就完成樹莓派上的自動化部署了.

參考 :

# https://crontab.guru/monday-to-friday

# 在线crontab每周一,三,五的下午3:00执行解析验证

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 冊滿借情況, 只好犧牲下面這本去換 :

# 網管大殺器 : 用Python精控企業IT架構

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

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

2022年6月27日星期一

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

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

# https://gh.nsrrc.org.tw/news/newsInfo

2022年6月26日星期日

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

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

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

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

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

參考書籍如下 :

本篇測試中用到的資料來自之前 Matplotlib 測試所使用的數據, 參考 :

# Python 學習筆記 : 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 屬性值, 這樣就不需要去查最後一列索引了.

繪製圖表程序如下 :

建立圖表物件 (設定圖表標題與座標軸標題)
建立資料的 Reference 物件 (指定工作表中的儲存格範圍)
建立軸或標籤的 Reference 物件 (指定工作表中的儲存格範圍)
呼叫圖表物件的 add_data() 方法加入資料的 Reference 物件
呼叫圖表物件的 set_categories() 方法加入軸或標籤的 Reference 物件
呼叫工作表物件的 add_chart() 方法將圖表物件插入指定儲存格中

其中步驟 1, 2, 3 先後順序不重要.

官網教學文件參考 :

# https://openpyxl.readthedocs.io/en/stable/charts/introduction.html

# https://openpyxl.readthedocs.io/en/latest/api/openpyxl.chart.html

(1). 繪製長條圖 :

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

# https://openpyxl.readthedocs.io/en/stable/charts/bar.html

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

>>> import openpyxl as xl

>>> from openpyxl.chart import BarChart, Reference

>>> Reference

>>> BarChart

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

>>> chart=BarChart()

>>> type(chart)

利用 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

>>> 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)

>>> type(y)

此處因 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). 繪製折線圖 :

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

# https://openpyxl.readthedocs.io/en/stable/charts/line.html

以上面的 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). 繪製圓餅圖 :

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

# https://openpyxl.readthedocs.io/en/stable/charts/pie.html

繪製圓餅圖須匯入 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)　　　　

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

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

>>> type(chart.series[0])

>>> 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')

結果如下 :

參考 :

# Creating a chart

# Python 使用 openpyxl 模組繪製 Excel 圖表教學

訂閱：文章 ( Atom )

小狐狸事務所

2022年6月30日星期四

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

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

2022年6月27日星期一

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

2022年6月26日星期日

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

文章標籤

常用連結

2022年6月30日 星期四

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

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

2022年6月27日 星期一

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

2022年6月26日 星期日

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

2022年6月30日星期四

2022年6月27日星期一

2022年6月26日星期日