用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

用Google Sheets計算組合孖展保證金

我用Google Sheets來計算自己投資組合所需要的保證金，以確保不會爆煲。我會用這裡分享的一個例子做說明。

第一頁"portfolio"是輸入組合的地方，在例子中只需要填黃色的部份，即是持有甚麼股票，每支股票有多少股。在這一頁的上方填上借了多少孖展，會在第三頁用到。



第二頁"margin"是計算保證金的地方。我用Interactive Brokers，大多美股最少要保證金等於股票市值的15%。我加多項"個人設定保証金"，作為提示自己幾時開始要注意槓杆太高，這裡我用50%，你想保守點的話可以用75%或以上。



第三頁"equity-needed"就是看看組合離爆煲差多遠。差額負數越大，即越安全。如果離最少保証金的差額是正數，那你是爆煲了，所以最好在離個人設定保証金差額是正數的時候，就要停止再買貨，還要考慮賣股減磅。