用Google sheets做簡單的短炒price trigger alerts

 最近研究一下短炒玩玩，用Google sheets做了個price alerts 方便自己看哪只股的10-day moving average向上cross 20-day moving average，哪支股的股價低個10-day moving average等等，表格可以自動執行給自己發email提示：

Google sheets 文件在這裡: link

有問題請留言 :)

一開始的Setup:

1. Use "File" -> "Make a copy" to copy this sheet ("檔案" -> "建立副本")

2. Click on "Tools" -> Script Editor (工具 -> 指令碼編輯器)

3. Click on "Editor" -> "Code.gs" -> Edit the value of EMAIL_ADDRESS (編輯器 -> "Code.gs" -> 改email)

4. Make sure "mvCrossingAlert" is selected, click on "Run", accept all permissions (選"mvCrossingAlert", "執行"來測試一下，授權所有權限)

5. Make sure things run properly. Run "priceCrossingMv10Alert" as well if you want. (也測試一下"priceCrossingMv10Alert", 看下面的執行紀錄就知ok與否)

6. Now schedule the scripts by clicking on "Triggers" -> "+ Add Trigger" ("觸發條件" -> "新增觸發條件")

7. Schedule a daily run for the mvCrossingAlert function. (用"時間驅動" + "日計時器"來每日去執行"mvCrossingAlert"一次)

8. Schedule a minutes timer to run priceCrossingMv10Alert (e.g. every 15 minutes) (用"時間驅動" + "分鐘計時器"來每十五分鐘去執行"priceCrossingMv10Alert"一次)

Triggers 都set好後是這樣子:

過一日之後按"Executions"可以看到成功的執行:

To add a symbol (加一只股):

1. Right click one of the sheet at the bottom (I usually choose the rightmost one), except for "all-stocks", then click "Duplicate"

2. In the new sheet, change the symbol

3. Rename the sheet, usually just use the symbol name

4. Click on cell A59, look at the formula. Depending on the stock exchange and where you are, you may want to use "today()" instead of "today() - 1" as the previous trading day's price.

5. Copy the last row on "all-stocks" sheet to the row below it

6. Change the "symbol" column of that new row to the name of the new sheet you just added