[Side Project] Google Apps Script 實作 Google Sheet 抽股票的篩選工具

-

有抽過股票的人應該都知道 HiStock嗨投資有一個公開申購/股票抽籤日程表的網頁:


不過我們自己應該都會去有一些規則去篩選，例如最簡單的篩選規則我們可以這樣訂:

  • 承銷張數 >= 1000 (越多張中籤率越高)
  • 獲利 >= 5000 (獲利太少我幹嘛去抽?)
  • 報酬率(%) >= 10 (報酬率太低怕可能等抽到股價已經跌破承銷價)
  • 不要含有 "KY" 的股票

在 Google Sheets 試算表匯入網頁資料

我們可以打開一個空白的試算表，在最左上角的一格輸入:


=IMPORTHTML("https://histock.tw/stock/public.aspx", "table")

這樣表單就會大概一小時更新一次把上面網頁的資料匯入到工作表中:


條件式格式設定好難用

如何把我們想要篩選的列給標示出來? 我們可以用條件式格式設定 (Conditional formatting)。

為了套用顏色到每一列，我們只能先選全部範圍，再用公式的方式:


=AND($F1>=1000, $I1>=5000, $J1>=10)

可是光是套用前三條規則我們立刻就發現公式變得很長又不好讀:


條件式格式設定的缺點

條件式格式設定這麼難用，我盡量會避免的原因是:

  • 剪下、複製、貼上一些資料後，套用範圍經常會亂掉，會這邊缺一個洞、那邊缺一個洞
  • 要經常更改公式會變得很麻煩

在 Excel 也會碰到同樣的問題。第一個剪下貼上的問題可以透過一些小技巧避免，第二個問題基本上無解。

用 Google Apps Script 寫小工具: Smart 篩選

之前就有用 Google Apps Script 幫不會用公式的朋友寫一些好用的小工具的經驗，所以我就在想，我能不能做出一個比條件式格式設定更好用的工具?

於是我就花了一些時間用 Google Apps Script, jQuery, DataTables, Bootstrap 5 加上比較新的 ES6 來練習寫一個工具 Smart 篩選:


安裝方法我就放在 GitHub 不在這邊贅述了。

只要是 Table 格式的資料都可以用，我這邊只是用抽股票來當範例。

使用方式

把 GitHub 中的檔案都在 Apps Script 設置完後，重新整理 Google Sheets 應該就會看到選單多一個 "Smart 篩選":


點 "設定" 後右邊會先讀取目前工作表中最上面一列的欄位有哪些，之後就會看到漂亮的 Bootstrap 5 側欄:


如果我們想要讓符合條件的列用綠色標示，我們可以在 "篩選器(包含)" 下方點 "新增" 加入一個條件，"欄位"、"運算子"、"數值"都可以快點兩下來編輯，或是你先選完格子後再按上面的 "編輯" 也可以。之後就會看到我們可以對工作表的其中一個欄位來做篩選:


新增完很多條件後點 "更新" 後側欄才會真的把 client 端的資料傳給 Apps Script server 端去請他更新:


更新完後不只右下角會跳出一個吐司訊息，你應該也會注意到工作表的背景顏色有變化:


背景顏色也可以換成其他顏色，你可以點一下背景顏色後面那個小色塊來去做選擇。

接著我們同樣在 "篩選器(排除)" 那邊加入兩個條件後更新:


篩選規則

目前的 "篩選器(包含)" 裡的所有條件必須要全部符合才會讓那列資料用綠色背景顏色顯示，"篩選器(排除)" 則是任何一個條件符合就會用紅色背景顏色顯示。

如果同一列資料同時被包含又被排除的話，目前的設計是會當成是被排除的，也會被算進 "衝突書量" 的統計數字裡。

小技巧: 自己創篩選的數值

如果我們要篩選的數值比較複雜，甚至不在原始資料怎麼辦?

那我們就可以先用一些公式自己新增一欄 (column)，再用 Smart 篩選來對這個新增的欄位做篩選。

例如範例的資料是從政府資料開放平台的 "盤後資訊 > 個股日成交資訊" 來的，但他並沒有漲跌幅程度的欄位，我們就可以先創一欄用以下的公式來推估當天的漲跌程度 (通常是介於 -10% - 10%):


=IF(ISBLANK($E2), 0, $I2/$E2)

接著打開 Smart篩選工具來去篩選 "漲跌幅度" 的欄位，例如我們想要用很簡單但很爛的方式判斷是不是飆股，我們說一天大於 5% 就是的話，出來的結果會長這樣:


雖然它顯示 6.73% 但那只是因為我把那欄的顯示格式變成百分比(%)，實際他格子的數字是 0.0673，所以在 Smart篩選的數值我們是打 "0.05" 而不是 "5"。

寫這個 Project 學到的經驗

一開始用了一大堆 functions 來做，可是很快的整個程式架構就變得非常亂，況且篩選器有兩個，很明顯就可以創一個 class 兩邊一起 reuse。所以後來就借用了 Qt 的程式架構用類似 MVC 的方式去讓一個 XXXController 負責一塊 GUI 的部分。

我不會想用一些像是 Angular 或 React 的架構來寫是因為我還是想讓這個 project 的程式碼盡量簡單，希望初學者一看程式碼都可以很好找到要改的地方，我也盡量寫的讓大家一目了然知道我在做什麼，所以幾乎沒什麼 comments。

我覺得用 Google Apps Script 的地方在於做 client 這端的 debugging 還是不太好做，有時候不小心犯了一個 syntax error 但是看 console 卻沒有任何 stack trace 的資訊可以看。這和 server 端有 bug 其實可以寫一個 function 在 Apps Script 網頁來跑 Debug 是不太一樣的。這時候還是要靠額外的工具像是 Visual Studio Code 把程式碼貼過去用 ESLint 幫忙看一下哪邊可能有問題。本地端寫完一陣子測試沒問題最好就用 git 留個紀錄也會是個好習慣。只不過這次一開始都是完全在 Chrome 上面寫程式，好險中途沒發生什麼問題，下次我應該還是用 VSCode 當作主要編輯環境比較好。


留言

  1. 良良2022年3月2日 下午2:54:00

    是不是壞掉了
    google試算表好像抓不到網頁資料

    回覆刪除
    回覆
    1. Shawn Chang2022年4月1日 上午12:20:00

      我自己的經驗是 IMPORTHTML 會因為網頁不穩定有時候會抓不到資料，這種時候只能等一陣子再重試，或是把網址稍微換一下不影響結果，例如 https://histock.tw/stock/public.aspx 改成 https://histock.tw/stock/public.aspx?abc=123

      刪除

發佈留言

此網誌的熱門文章

[試算表] 追蹤台股 Google Spreadsheet (未實現損益/已實現損益)

-
圖片
在 Dcard 上發了一篇文結果引起超多網友回應想要看試算表，我就來整理一下我追蹤台股用的 Google 試算表給大家。 這個試算表我目標是以 "簡單" 為主，底下我也會講解公式的運作原理，希望大家能以此試算表輕鬆擴充自己想要的功能。 功能 未實現損益 (Unrealized Gain/Loss 表) 不管同一支股票是不是有部分已經賣出，只要你還持有未賣出的股票這邊都看的到。 已實現損益 (Realized Profit/Loss 表) 我過去的績效如何? 在這個表一目了然。 未實現損益: 詳細資料 (Unrealized Stats 表) 我還持有多少股? 每股市值多少? 每股的成本是多少? 在這個表可以快速追蹤。 手機友善 (Mobile Friendly) 我為了在手機上也可以快速看我想看的資訊，把 "Unrealized Gain/Loss" 做成比較窄的版型。 只要下載 Google Sheets app 打開試算表就可以了。 Google Spreadsheet 連結 Google Drive 連結 Stock Tracking 使用方式 請先點 "檔案" -> "建立副本"，copy 一份試算表到你自己的 Google Drive 將 "Current Price" 表單中填上你想要追蹤的股票代號和名稱 將 "Transactions" 表單中刪除範例資料，但請務必留下第 2 列 (因為有公式)，然後慢慢往下填入你的資料 Step 1: 檔案 -> 建立副本 Step 2: 新增/刪除股票代號和名稱 (紅框圈起處)，灰色底欄位代表有公式 Step 3: 在 "Transactions" 表填入資料到白色底欄位 (紅框圈起處)，灰色底欄位代表要讓公式自動計算 未實現損益的正確算法 其實在發 Dcard 文之前我自己也像 另外一個表單 犯了同樣的錯誤，就是只有在股票全部賣光光 (持有 0 股) 後計算的未實現損益才會正確。還沒全部賣光怎麼辦? 難道就不顯示了嗎? 飲料的例子 只要股票中途賣出一部分，目前還持有部分股票的話，感覺計算就突然變得很複雜。但不用怕，我們來用飲料當作股票舉個超簡單的例子 (1 杯=1...
閱讀更多

[Side Project] 互動式教學神經網路反向傳播 Interactive Computational Graph

-
圖片
成品 互動式網頁連結:  Interactive Computational Graph 微積分課經典的範例 df/dx sin(x) / x: 2 個 inputs, 1 個 output, 沒有 hidden layer 的 fully connected neural network: 為何要做這套工具 以前在學 machine learning 的時候覺得最難理解的部分就是 backpropagation，也就是神經網路到底是怎麼用數學來做訓練的。 我們知道神經網路就是長這樣子: Source: Wikipedia 但實際上每個 neuron (圓圈圈) 裡面有許多基本的數學運算，像是 sum 還有 activation function: Source:  Neural Networks and Machine Learning 當我們把所有東西綜合在一起，腦袋就會打結，到底 chain rule 是怎麼在這麼複雜的神經網路之間套用的? 就算了解基礎也很難相信實際上到底怎麼運作的。我又沒有要真的使用的話，網路上的教學就覺得看看就好 Source:  A Step by Step Backpropagation Example 但決心要了解 backpropagation 細節之後，不少教學都提到了 computational graph，從基礎的數學運算當作 nodes 來去理解似乎是比較簡單的方式。 後來找到了這篇文章  Calculus on Computational Graphs: Backpropagation  把 chain rule 講解的簡單至極，於是就在想，為何我不做一個網頁，讓大家可以自由地組合想要的 graph，然後看一下 chain rule 是怎麼流通的。 失敗作經驗 在做這次 side project 前，其實早在一年前我就開始作另一個 side project 是想展現神經網路的訓練過程。名稱叫做 explain-derivatives，是被 TensorFlow 的一個網頁 A Neural Network Playground 啟發的。 但由於一開始欠缺規劃，想到什麼就加什麼功能進去，造成架構一直修改，最後由於程式碼架構太過複雜就放棄了。 這個 project 是用 Cy...
閱讀更多

[插件] 在 Chrome 網頁做區分大小寫的搜尋

-
圖片
寫軟體的時候最常用到的功能就是上網查 API 文件，但是常常會遇到一個困擾就是，搜尋的文字在 Chrome 裡面是不區分大小寫 (case-insensitive) 的。有的時候是還好，多按一些 F3 就找到了，可是有時候實在是太多大小寫的東西混在一起，這時候我們有一些快速的解法: 用 Firefox 搜尋 (Firefox 的搜尋框裡面預設就提供有 match case 的功能了) 開 DevTools 按 Ctrl + Shift + F 進行全部東西的搜尋 (不過大概會發瘋) 用插件的解法算是意外發現的，原本只是在找有沒有可以用 regex 搜尋的插件，沒想到意外發現  regex 的插件也是可以拿來做區分大小寫 (case-sensitive) 的一般搜尋。 用 Chrome Regex Search 來做區分大小寫的搜尋 先裝好 Chrome Regex Search 之後，按 Ctrl + Shift + F 或是點工具列的圖示，打上想找的文字就可以了。 例如我最近在看 Qt QTextCursor 的文件，但是我只想找 "cursor"，很不幸的如果用 Chrome 預設搜尋他會把一大堆 QText Cursor 也會找到: 如果用 Chrome Regex Search 就會侷限在小寫的 "cursor": 用 Regex 的陷阱 如果你不知道 regex 是什麼，那你很有可能會發現在搜尋一些含特殊符號的東西會搜尋到怪怪的東西，不然就是搜尋會失敗。 這是因為 regex 本來就是偏工程師在用的東西，有一些符號是有特殊涵意的，所以如果搜尋的字包含以下任何特殊符號記得要在前面加一個反斜線 \ ( ref ): . + * ? ^ $ ( ) [ ] { } | \ 例如我想搜尋 1+1=(2-1)+1*1 ，那其實要打 1\+1=\(2-1\)\+1\*1  才會正確找的到。 是不是很麻煩? 不過如果你要找的東西有特殊符號的話，其實用一般 Chrome 的搜尋通常是不會有太多搜尋結果的，那就用一般搜尋就好。 Chrome 什麼時候要支援大小寫搜尋? 其實上面弄了那麼麻煩，還不如開個 Firefox 搜尋更快。那我就滿好奇，這麼基本的功能怎麼 Chrome 一直都沒做? 找了一下才發現 Super User 有人...
閱讀更多