D25 - 如何用 Apps Script 自动化地创造与客制 Google Sheet?(二)结合股票价格通知与信件

今天的目标:

帮自己用 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 FinacneimportHTML

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 的寄信

那今天就到这边,铁人赛也接近了尾声。也进入了最关键的 Sheet 的部分,希望内容对大家有所帮助。如果还有问题,透过留言之外,也可以到 Facebook Group,想开很久这次铁人赛才真的开起来,欢迎来当 Founding Member。如果不想错过可以订阅按赞小铃铛(?),也欢迎留言跟我说你还想知道什麽做法/主题。我们明天见。


<<:  【Day10-去重】使用python优雅的一行解决list或DataFrame资料去重问题

>>:  Day 25 利用transformer自己实作一个翻译程序(七) Scaled dot product attention

[Day16] 第十六章-Skill的API 服务建构 (migration,route,controller,model)

前言 昨天我们建立了Skill的migtaion 我们今天目标是把简单的API定义好来实作吧!! 除...

用 Python 畅玩 Line bot - 23:Flask(一)

如果想要将从 line 上蒐集到的资料或数据呈现在自己的网页上,我们可以使用 flask 建立好网页...

[Day 28] Crypto 小替换

嘿嘿 到了第28八天啦 雀跃的心情 就像在京都 看着漫天散落的粉嫩樱花 今天这题超简单 因为我要去补...

QUIC.cloud CDN 免费方案

QUIC.cloud CDN 免费方案 近期来 QUIC.cloud 除了在,CSS、JS、影像,页...

RxJava - Backpressure

不知道大家有没有手冲咖啡的经验?如果没有的话,应该也看过或用过滤挂式咖啡,在冲咖啡时,水不能一次倒太...