Main Menu

Google Apps Script 自動化地創造與客製 Google Sheet？（二）結合股票價格通知與信件（2021 鐵人賽 D25）

內容目錄 隱藏
目標：
要怎麼用 GAS 做一個股票價格提醒器？
Input
Output
Step 1 從 Google Sheet 進入 GAS
Step 2 從 Google Sheet 中讀取股票名稱與現在股價
Google Finance
ImportHTML
Step 3 依照出場與停損價格，寄出通知 Email

目標：

幫自己用 Google Sheet 和 GAS 做一個數值到了就用 mail 提醒的程式！身為一個不喜歡被通知綁住的工程師，我自己除了 Email 之外的通知都是關閉的。換句話說，如果今天我的股票突然到一個很慘的價格，我是要看到才知道挫賽；或是到我了理想價了，我往往都飲恨錯過。所以就突發奇想，能不能結合 Google Apps Script 做個通知呢？就試著做做看了。

今天的關鍵問題是——

  1. 要怎麼用 GAS 做一個股票價格提醒器？

這邊主要會今天主要會帶到 Google Sheet 中 Google Finance 的使用。那就讓我們開始吧！

要怎麼用 GAS 做一個股票價格提醒器？

Input

  • 希望觀察的清單（Google Finance 代碼）
    • 可能是美股或台股
    • 台股可能是上市或上櫃
  • 希望通知的頻率（Google Trigger）

我就先隨便抓個幾支股票，從美股、上市台股、上櫃台股都有。

Output

  • 一封「你的期望價格已到」的信
    • 且發過後就不要重發

搞懂 Input 和 Output 後，讓我們來看看中間的 Process 要怎麼寫吧。

Step 1 從 Google Sheet 進入 GAS

今天我們用 Google Sheet 作為連結 GAS 的管道，讓我們借用 D14 的影片。

一樣第一次按下 GAS 中的「執行」會有「存取驗證」需要大家按一下。這邊仍是借用一下 D2 的影片。

接著，我們要先設定這張 Google Sheet 上面的參數們。

Step 2 從 Google Sheet 中讀取股票名稱與現在股價

這邊我們主要會用到 googlefinance 來讀美股和上市台股，用 importHTML 來讀上櫃台股。讓我們開始吧！

先給大家看最後的 Google Sheet 公式，我們先一層層寫，比較看得懂有幾層——

=iferror(
	ifs(A2="美股",GOOGLEFINANCE(C2,"price"),
		B2="上市",GOOGLEFINANCE("TPE:"&C2,"price"),
		B2="上櫃",vlookup(value(C2),'上櫃資訊'!A:C,3,0)
		),
"找不到資料")
  1. 最外層有個 iferror，簡單來說，就是有錯誤時的通知。
  2. ifs 是連續幾層的 if 的意思，這邊就是分別檢查，是美股、上市與上櫃的情況要怎麼處理。Google Sheet 的邏輯是會先檢查第一個 IF，才會往後檢查。這點在設計上可以注意一下。
  3. GOOGLEFINANCE 仔細看會知道是 Google Finance，是 Google 給 Google Sheet 中拉出財務資料的 API，我們拉出來介紹——
  4. vlook 的意思是找另外一張表單的資料，我在名叫「上櫃資訊」的另一張試算表（Sheet）中，使用了 importHTML 來抓出資料。

那我們分別來看看 Google Finacne 和 importHTML。

Google Finance

這個 function 有五個參數可以調整——

  1. Ticker——基本上就是股票代碼，可以透過 Google Finance 查到
  2. （選填）屬性（Attribute）—— 預設是查即時價格（Price），要從 Google 財經服務擷取的代號相關屬性。不斷在新增中，功能越來越強大，可以參考此表
  3. 開始時間、結束時間與間隔，也是參考同一張表最後的範例，上面的清晰度覺得夠了，就不再多做介紹。

那我這邊用到的屬性有預設的價格（Price）和股票名稱（Name），設定起來的公式長這樣——

ImportHTML

這筆資料是抓 台灣證交所 的 API，透過這個網址我們可以讀取到上櫃股票的資料。我們來看看直接用瀏覽器讀會怎麼樣——

"https://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430_print.php?l=zh-tw&se=EW&s=0,asc,0&d="

跑起來長這樣，就基本上是一張大表單（Table）輸出有交易的上櫃資料們。

而我們透過 ImportHTML 設定的話，要弄成以下這樣，指定我要最大的這張表格（Table）

=IMPORTHTML("https://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430_print.php?l=zh-tw&se=EW&s=0,asc,0&d=","table",1)

跑起來長這樣——

而接著就是透過 vlookup 去取得另外一張表單的內容。這邊先說明一下在 Google Sheet 中叫另外一張表單的資訊，使用的程式碼是——

='試算表名稱'!位置

// e.g.
='上櫃資訊'!B4

跑起來長這樣——

所以搭配起來的 vlookup 程式碼是這樣—— vlookup(value(C9),'上櫃資訊'!A:C,2,0)

跑起來長這樣——

好，最後則是會回到我們 Step 2 最一開始的程式碼，將上面這些整合起來，依照不同的資料用不同的公式。

=iferror(ifs(A2="美股",GOOGLEFINANCE(C2,"price"),B2="上市",GOOGLEFINANCE("TPE:"&C2,"price"),B2="上櫃",vlookup(value(C2),'上櫃資訊'!A:C,3,0)),"找不到資料")

跑起來長這樣——

那基本上設定好後，這個數值會不斷更新，預設頻率是每二十分鐘一次。但我們可以設定讓它每分鐘/每小時有自動更新，方式如下——

那當這些都設定完後，接著就是寫上「預計通知數值」並且設定 Gmail 寄信了。

Step 3 依照出場與停損價格，寄出通知 Email

我們先設定一系列數據如下——

以下因為前面有用過數次，第一次進來的朋友不清楚怎麼用的話，可以參考 D4 – 如何透過 Google Apps Script 來整合 Google Form / Google Sheet 並自動寄出客製的 Email？ 1. 取得 Google Sheet 的資料架構 ，裡頭有完整介紹 GAS 取得 Google Sheet 的架構。

那，對於知道的朋友，我們就先直接上讀數據的程式碼——

function readData(){
  let sheet = SpreadsheetApp.getActiveSheet()
  let start_row = 2;
  let start_col = 5;
  let num_row = sheet.getLastRow()-1;
  let num_col = 3;
  let data = sheet.getRange(start_row, start_col, num_row, num_col).getValues();
  Logger.log(data);
  return data;
}

看一下對照是否正確——

看來數值沒錯，那接著我們就繼續玩寄信。

function stockPriceAlert(){
  let data = readData();
  let emailAddress = "your_mail_here";
  for (row_data of data){
    let curr_price = row_data[0];
    let higher_target = row_data[1];
    let lower_target = row_data[2]
    if((curr_price > higher_target) || (curr_price < lower_target)){
      MailApp.sendEmail(emailAddress, "Your stock alert!");
    }
  }
}

寄信也很簡單，就單純寫個程式碼，在數值超過這個範圍時進行通知。這邊比較特殊的是，要來設定「Trigger」，我自己是習慣設定每十分鐘一次。操作影片如下——

好，那這樣就設定完了。但，我們總不會希望它每十分鐘就寄一次通知，所以這邊設定一天只要寄送一次。方式是當今天有寄出信時，就在 GAS 上面寫上最後提醒日期，如果這日期跟今天一樣，那就不再寄送，完整的程式碼如下——

解釋function readData(){
  let sheet = SpreadsheetApp.getActiveSheet()
  let start_row = 2;
  let start_col = 5;
  let num_row = sheet.getLastRow()-1;
  let num_col = 4;
  let data = sheet.getRange(start_row, start_col, num_row, num_col).getValues();
  return data;
}

function stockPriceAlert(){
  let data = readData();
  let emailAddress = "your_mail_here";
  for (let i=0; i < data.length; i++){
    let row_data= data[i]
    let curr_price = row_data[0];
    let higher_target = row_data[1];
    let lower_target = row_data[2];
    let last_sent_date =row_data[3];
    let todat_date = new Date().getDate();
    if((curr_price > higher_target) || (curr_price < lower_target)){
      if(todat_date != last_sent_date){ 
        // MailApp.sendEmail(emailAddress, "Your stock alert!");
        SpreadsheetApp.getActiveSheet().getRange(i, 6).setValue(todat_date)
      }
    }
  }
}

有朋友問說今天的試算表能不能給一份，給拉哪次不給的，是谷歌的連結，基本上谷歌沒事就是無毒保證。

好，那今天就到這邊！今天比較是應用題，更多是 Google Sheet 的操作。 Google Sheet 本身就是很強大的工具，搭配 GAS 更是會讓大家如虎添翼。

今天是我們的第 25 天，盤點一下今天學的

  1. Google Sheet 公式
    1. googlefinance
    2. vlook
    3. 從另外一張表單讀數據的方式
  2. 回顧 D4 的寄信

目錄

Scroll to Top