小狐狸事務所: Python 學習筆記 : 使用 OpenPyXL 操作 EXCEL 試算表 (五)

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 判斷.

沒有留言 :

張貼留言

訂閱：張貼留言 ( Atom )

小狐狸事務所

2022年6月30日星期四

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

沒有留言 :

文章標籤

常用連結

2022年6月30日 星期四

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

沒有留言 :

2022年6月30日星期四