帮自己用 Google Sheet 和 GAS 做一个数值到了就用 mail 提醒的程序!身为一个不喜欢被通知绑住的工程师,我自己除了 Email 之外的通知都是关闭的。换句话说,如果今天我的股票突然到一个很惨的价格,我是要看到才知道挫赛;或是到我了理想价了,我往往都饮恨错过。所以就突发奇想,能不能结合 Google Apps Script 做个通知呢?就试着做做看了。
今天的关键问题是——
这边主要会今天主要会带到 Google Sheet 中 Google Finance 的使用。那就让我们开始吧!
我就先随便抓个几支股票,从美股、上市台股、上柜台股都有。
搞懂 Input 和 Output 後,让我们来看看中间的 Process 要怎麽写吧。
今天我们用 Google Sheet 作为连结 GAS 的管道,让我们借用 D14 的影片。
一样第一次按下 GAS 中的「执行」会有「存取验证」需要大家按一下。这边仍是借用一下 D2 的影片。
接着,我们要先设定这张 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)
),
"找不到资料")
iferror
,简单来说,就是有错误时的通知。ifs
是连续几层的 if
的意思,这边就是分别检查,是美股、上市与上柜的情况要怎麽处理。Google Sheet 的逻辑是会先检查第一个 IF,才会往後检查。这点在设计上可以注意一下。GOOGLEFINANCE
仔细看会知道是 Google Finance,是 Google 给 Google Sheet 中拉出财务资料的 API,我们拉出来介绍——vlook
的意思是找另外一张表单的资料,我在名叫「上柜资讯」的另一张试算表(Sheet)中,使用了 importHTML
来抓出资料。那我们分别来看看 Google Finacne
和 importHTML
。
这个 function 有五个参数可以调整——
那我这边用到的属性有预设的价格(Price)和股票名称(Name),设定起来的公式长这样——
这笔资料是抓 台湾证交所 的 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 寄信了。
我们先设定一系列数据如下——
以下因为前面有用过数次,第一次进来的朋友不清楚怎麽用的话,可以参考 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 天,盘点一下今天学的
googlefinance
vlook
那今天就到这边,铁人赛也接近了尾声。也进入了最关键的 Sheet 的部分,希望内容对大家有所帮助。如果还有问题,透过留言之外,也可以到 Facebook Group,想开很久这次铁人赛才真的开起来,欢迎来当 Founding Member。如果不想错过可以订阅按赞小铃铛(?),也欢迎留言跟我说你还想知道什麽做法/主题。我们明天见。
<<: 【Day10-去重】使用python优雅的一行解决list或DataFrame资料去重问题
>>: Day 25 利用transformer自己实作一个翻译程序(七) Scaled dot product attention
前言 昨天我们建立了Skill的migtaion 我们今天目标是把简单的API定义好来实作吧!! 除...
如果想要将从 line 上蒐集到的资料或数据呈现在自己的网页上,我们可以使用 flask 建立好网页...
嘿嘿 到了第28八天啦 雀跃的心情 就像在京都 看着漫天散落的粉嫩樱花 今天这题超简单 因为我要去补...
QUIC.cloud CDN 免费方案 近期来 QUIC.cloud 除了在,CSS、JS、影像,页...
不知道大家有没有手冲咖啡的经验?如果没有的话,应该也看过或用过滤挂式咖啡,在冲咖啡时,水不能一次倒太...