[試算表] 追蹤台股 Google Spreadsheet (未實現損益/已實現損益)

-

在 Dcard 上發了一篇文結果引起超多網友回應想要看試算表，我就來整理一下我追蹤台股用的 Google 試算表給大家。

這個試算表我目標是以 "簡單" 為主，底下我也會講解公式的運作原理，希望大家能以此試算表輕鬆擴充自己想要的功能。

功能

未實現損益 (Unrealized Gain/Loss 表)


不管同一支股票是不是有部分已經賣出，只要你還持有未賣出的股票這邊都看的到。

已實現損益 (Realized Profit/Loss 表)


我過去的績效如何? 在這個表一目了然。

未實現損益: 詳細資料 (Unrealized Stats 表)


我還持有多少股? 每股市值多少? 每股的成本是多少?

在這個表可以快速追蹤。

手機友善 (Mobile Friendly)


我為了在手機上也可以快速看我想看的資訊，把 "Unrealized Gain/Loss" 做成比較窄的版型。

只要下載 Google Sheets app 打開試算表就可以了。

Google Spreadsheet 連結

Google Drive 連結

Stock Tracking

使用方式

  1. 請先點 "檔案" -> "建立副本"，copy 一份試算表到你自己的 Google Drive
  2. 將 "Current Price" 表單中填上你想要追蹤的股票代號和名稱
  3. 將 "Transactions" 表單中刪除範例資料，但請務必留下第 2 列 (因為有公式)，然後慢慢往下填入你的資料
Step 1: 檔案 -> 建立副本


Step 2: 新增/刪除股票代號和名稱 (紅框圈起處)，灰色底欄位代表有公式


Step 3: 在 "Transactions" 表填入資料到白色底欄位 (紅框圈起處)，灰色底欄位代表要讓公式自動計算

未實現損益的正確算法

其實在發 Dcard 文之前我自己也像另外一個表單犯了同樣的錯誤，就是只有在股票全部賣光光 (持有 0 股) 後計算的未實現損益才會正確。還沒全部賣光怎麼辦? 難道就不顯示了嗎?

飲料的例子

只要股票中途賣出一部分，目前還持有部分股票的話，感覺計算就突然變得很複雜。但不用怕，我們來用飲料當作股票舉個超簡單的例子 (1 杯=1 股)：

  1. 我們在 1/1 買了 10 杯飲料，$50 元/杯
  2. 1/2 飲料突然不可思議地大漲，漲到 $10000元/杯，這時我們賣掉 5 杯飲料大賺一筆，留 5 杯自己喝
  3. 1/3 飲料跌回比較合理的價格 $90元/杯，我們再買 5 杯飲料
  4. 1/4 飲料漲到 $100 元/杯

請問 1/4 我們目前 10 杯飲料的未實現損益多少?

用比較簡單的話來說，如果我們現在把 10 杯飲料全部賣光光，我們可以賺多少錢?

計算成本

  • 我們有 5 杯飲料是 1/1 買的，成本是 $50 元/杯。
  • 我們有 5 杯飲料是 1/3 買的，成本是 $90 元/杯。
  • 我們總共就花了 5 * $50 + 5 * $90 = $700 在這 10 杯飲料上
  • 也可以說我們平均花了 $700 / 10 = $70 在每杯飲料上

計算賣出多少$

  • 1/4 我們還持有 10 杯飲料，這時全部賣掉可以拿到 10 * $100 = $1000
  • 也可以說 1 杯平均賣了 $100 元 (就是當天的市價)

計算未實現損益

  • 未實現損益 = 賣出 - 成本，我們賺了 $1000 - $700 = $300
  • 也可以說我們每杯賺了 $100 - $70 = $30
  • 換成 % 數則是 獲利/成本 = $300 / $700 ≒ 42.86%

計算的陷阱

第一個陷阱是 1/2 飲料不管漲到多少錢，那時候賣掉的獲利就該歸在 "已實現損益" 下，和 "未實現損益" 一點關係都沒有，所以未實現損益計算不應該扯到任何已經發生的收入。

第二個陷阱是成本價其實要考慮到所有過去的歷史，只要你還沒賣掉的飲料中有一杯是民國初年做的飲料好了 (加了超多防腐劑)，那一定會拉低你的平均成本。

想到這邊我就一點頭緒都沒有，那到底該怎麼算比較好? 我又不是學這方面的科系，當然要求助 Google 啦。

找了一下英文文章就發現其實有三種算法: Weighted average, FIFO, LIFO。有興趣了解的話可以看一下這篇 Weighted Average vs. FIFO vs. LIFO: What's the Difference?。這三個方法都需要試算表回去找不同列的資料，對公式而言就已經很頭痛了，但為了要正確做出未實現損益 (加上受到 Dcard 上熱烈回應的壓力) 也只能硬著頭皮去學會。

於是我又找到了兩個幫助非常大的網路資源，第一個是有人詢問 Python Pandas DataFrame 中要如何計算 weighted average，這對後續在了解 weighted average 公式有很大的幫助。第二個是找到美股作未實現損益公式的試算表，但是裡面公式超複雜我才不想直接拿來用，但可以大概知道他的概念。

公式實作解析

累計成本的算法

從上面的飲料例子可以看出來計算賣出多少非常簡單，持有股數乘上當天市價就好，最困難的是要追蹤成本的部分。

如果我們選擇用 weighted average 來計算成本，在每次股票交易時成本的變化時機只有兩個：

  • 買入：會增加持有的成本
  • 賣出：會減少持有的成本

如果我們把飲料例子套用在這個飲料假資料試算表，可以很容易看的出來這個變化：


尤其是 2022-01-02 賣掉的時候，由於我們賣掉了 50% 的股份，也就代表我們一半的成本都消失了。但你可能覺得這樣不公平，賣掉的那 5 杯有可能是比較早買的飲料阿，怎麼不用比較便宜的成本?

很抱歉 weighted average 就是把所有目前持有的飲料都當作一樣，如果要每杯飲料分開計算成本那是 FIFO 或 LIFO 的算法。


如果你點飲料假資料試算表中 Transactions 的 S3，可以看到公式如何決定目前 "成本的變化" (Changed Cost):

  • 如果 $B3 是 "Buy"，那這次變化的成本則是 $J3 (也就是買入的金額)
  • 如果 $B3 是 "Sell"，那這次變化的成本則是 $R3 (之前的累計成本) * $P3 (賣掉的股數) / $O3 (之前的累計股數)
    • $R3 的成本等於上次 0050 的累計成本，也就是他會去找到 $T2 的位置
    • $O3 的股數等於上次 0050 的累計股數，也就是他會去找到 $Q2 的位置
    • (怎麼找的後面一點會解析)

($P3 / $O3 就是這次賣掉股數的 % 數變化，不知道你有沒有發現?)

因此最後 2022-01-02 的變化成本就是 $R3 * $P3 / $O3 = $500 * (-5 / 10) = -$250：


最後我們再更新這次賣完後的累計成本 $T3 = $R3 (之前的累計成本) + $S3 (這次的變化成本) = $500 - $250 = $250：


之後只要有買賣同一支股票，目前的 $250 就會繼續被拿來使用，以此類推。

已實現損益 Pivot Table

我們切換到 "Realized Profit/Loss" 表可以看到由於我們在 1/2 大賺一筆的錢，這邊是利用 Pivot table 來進行統計：


大部分計算就只要把 Transactions 的 columns 做 SUM 的統計就好，只有需要特殊計算的部分才會用公式像是 "% Profit/Loss" 因為要防止除以 0 的錯誤 (例如有股息，卻還沒有賣出，因此成本是 0)，整個完整的公式是:


=IF('Realized P/L Cost' > 0, 'Realized Profit/Loss' / 'Realized P/L Cost', "")

這邊要特別注意的是如果底下 "Summarized by" 選的是 SUM，代表 'Realized P/L Cost' 和 'Realized Profit/Loss' 都會先經過 SUM 的計算，其實就等同於這樣寫：


=IF(SUM('Realized P/L Cost') > 0, SUM('Realized Profit/Loss') / SUM('Realized P/L Cost'), "")

這時 "Summarized by" 不管選 SUM 還是 Custom 效果都一樣。但沒必要寫這麼長當然越短越好。

未實現損益: 兩輪 Pivot Table

未實現損益的部份我們先用一個 pivot table 統計一次：


這時候我們就可以把假設全部賣出的話，會要扣多少手續費和證交稅先算一算。

此時把這個 pivot table 當成資料來源，再用另外一個 pivot table 去算每支股票的未實現損益的話就很輕鬆了：


未實現損益 = 收入 - 成本。

假設現在賣掉，那公式就變成：未實現損益 = 市值 - (累積成本 + 手續費 + 證交稅)。

這邊有趣的地方就在於 pivot table 的來源資料竟然可以是另一個 pivot table，我們就可以不斷地在統計的資料上繼續做統計，就沒必要一次到位寫一堆恐怖的公式。

怎麼找之前同一支股票的資料?

上面 "累計成本的算法" 範例中 $O3 是怎麼找到 $Q2 的位置? $R3 又是怎麼找到 $T2 的位置? 看似好像很簡單但現實的表單會混雜其他支股票，像今天來了一支不相關的 0056 卡在中間：


Row=2, 4, 6 全部都是 0050，但從 Row=6 的那列我們可以只找到前一個最後 0050 出現的那一列 ($O6 正確找到 $Q4、$R6 正確找到 $T4) 這是怎麼辦到的?

這其實就用到一個叫做 Reverse Vlookup 的技巧，但這不是什麼很厲害的關鍵字，你只要搜尋 "google sheets how to find value backward" 就可以找到了。

這邊我就用了一個 column 叫做 "Previous Row" 來找同一支股票最後出現在前面哪一 row:


如果是 #N/A 就是找不到的意思。如果我們來看他公式一開始會覺得滿可怕的，但其實他是 array programming 的角度去寫的：


把 ROW($C$1:$C5) 丟給 FILTER 應該是某種進階用法，但我也找不到官方文件說明，去看 FILTER 或 ROW 的文件也只有給一些像是 ArrayFormula 的提示感覺他可以拿來這樣用而已。

我的理解是這樣，這邊應該就是要求 FILTER 找橘色部分每 row 值是不是等於紫色的 $C6，這邊 FILTER(...) 結果如果有興趣的話你可以去把公式改成只剩下 =FILTER(...)，結果會顯示 2 和 4，代表 row=2 和 row=4 都符合 FILTER 的第二個參數 xxx=$C6 的條件。

但我們只想要最後一個，所以我們外面再用一個 MAX(...) 取得 4。最外面用 IFERROR 包起來是因為如果 FILTER(...) 找不到東西就會回傳 error，但那個會讓表單上的格子出現紅色錯誤警告很醜，所以我們把它改成 #N/A，也比用 -1 或是 0 意義更明確，後面也可以用 ISNA(xxx) 去判斷是不是 #N/A，而不是什麼 IF(xxx<>-1) 或 IF(xxx>0)。

知道 0050 前一次出現位置在 row=4 後，"Previous Units" 或 "Previous Cost" 就可以利用 "Previous Row" 去抓取其他格子的資料：


如果 $N6 是 #N/A，代表前面沒有 0050 的資料，那就讓 $O6 顯示 0。但如果 $N6 不是 #N/A ，INDEX($Q$1:$Q5, 4) 就是去抓紫色範圍的第 4 個資料，也就是 5。

整個試算表最複雜的公式到這邊已經解說完畢了，另外一個追蹤美股的試算表公式就搞得超級複雜，其實是因為他把 "Previous Row" + "Previous Cost" 的概念同時做在一起，才變得那麼長：


嚇死人了。

而且我真的滿討厭 ArrayFormula 出現在試算表，因為這概念太深奧，要讓大家都理解是滿困難的。我的試算表就只用了一個 "Previous Row" 盡量減少這種 array programming 出現的次數。

表單其他小細節與問答

  • "Unrealized Gain/Loss" 表中綠色紅色什麼意思? 綠色底代表 10% 以上，紅色底代表 -10% 以下，這個是那個表中的條件式設定 (conditional formatting) 造成的，你可以自己調整
  • "Unrealized Stats" 中的手續費 "Pending Fee" 怎麼沒有考慮手續費優惠 (折讓)? 同一支股票你可能在不同券商下單，又有 app 下單或是活動等考量，把這個考慮進去只會讓表單變很複雜又不一定精準。反正都是預估的還沒真實發生，我寧願多扣一點。等真實手續費出來登記上去 "Transactions" 中的 "Fee" 欄位才是最準確的
  • "Transactions" 裡面為何 Fee 和 Tax 要自己寫? 怎麼不用公式聰明一點? 同上的理由，而且我怕會因為四捨五入或無條件捨去等小問題讓公式結果跟實際結果差 1 元，這邊就以實際情形為主，手動填比較保險
  • "Transactions" 表頭欄位有淺灰和深灰的背景顏色有什麼特定涵意嗎? 沒有，都是公式的欄位，只是用深淺的交界處來代替框線，因為框線在複製資料時很容易被一起複製，所以我很討厭框線，用背景色來分比較乾淨
  • 為何 "Transactions" 在捲動時 A-I 欄會固定不動? 那是凍結 (Freeze) 設定，你也可以自己解除或調整
  • 為什麼表單要全部用英文呢? 因為我覺得英文閱讀起來比較順暢，要找資料的話也比較好直接用關鍵字找英文的文章
  • 哪些數值應該要和券商 app 一樣? 哪些是預估的? 只要 "Transactions" 表中輸入沒有錯誤， "Unrealized Stats" 表中欄位 "Units", "Cost", "Cost/Unit" 應該要非常精準。但由於 "Pending Fee" 欄位不考慮手續費優惠因此會高估，會造成 "Unrealized Gain/Loss" 表中的數值可能比實際情形稍微低一點，但可能都是小數點後的差異。"Realized Stats" 表應該要非常精準
  • 0050 的配息是不是在範例表單中漏記了? 對，我後來才發現，不過就算真的忘了記 cash dividend 也只會影響到 "Realized Stats"，並不會影響到 "Unrealized Stats"

結語

以這個試算表當基礎也可以拿去追蹤基金和美股，只不過基金會多轉換匯率 (例如如果有買 USD 類型的基金)、美股則是多了 Split 的概念。

我也才入門股票不久，或許還有很多小細節沒有在試算表上顧慮到，如果哪邊有錯誤或可以改進的地方也請歡迎留言給我知道。

參考試算表

留言

  1. jianyi2023年5月18日 下午9:47:00

    Hello你好，請問有可能可以新增XIRR算年化報酬嗎

    回覆刪除
    回覆
    1. Shawn Chang2023年6月22日 下午3:53:00

      XIRR 是比較複雜的功能，如果你想要在複製的表單中做到的話是可以的。我稍微研究了一下，如果我們先去看 XIRR 需要些什麼東西，會發現它需要 values (正的值代表收入、負的值代表支出) & dates (日期)，但這些數值都要是屬於某一支股票的才行 (e.g., 0050)。如果我們有辦法生出一個表單只含有特定一支股票的 values & dates，要做 XIRR 就可以辦到的。

      本篇表單的 Transactions sheet 包含了各種 Symbols 以及除了 Type=Buy/Sell 以外還有 Dividends，我們要讓 XIRR 運作的話就對這些項目做 filtering，你可以在 Transactions 右邊加入下面這些 columns:
      Column X (Buy or Sell): X2 cell 寫公式 `=IF($B2="Buy", TRUE, IF($B2="Sell", TRUE))` 然後套用到下面所有的 rows
      Column Y (XIRR Values): Y2 cell 寫公式 `=IF($X2, IF($B2="Buy", -$E2, $E2), "")` 然後套用到下面所有的 rows
      Column Z (XIRR Dates): Z2 cell 寫公式 `=IF($X2, $A2, "")` 然後套用到下面所有的 rows
      Column AA (Filtered Cash Flow): AA2 寫公式 `=ARRAYFORMULA(IF($C2:$C="TPE:0050", $Y2:$Y, ""))` 下面的 rows 會自動有值生出來
      Column AB (Filtered Date): AB2 寫公式 `=ARRAYFORMULA(IF($C2:$C="TPE:0050", $Z2:$Z, ""))` 下面的 rows 會自動有值生出來

      最後要算 XIRR 我們就可以用 columns AA + AB，把空的 cells 濾掉就好:
      Column AC (XIRR): AC2 寫公式 `=XIRR(FILTER($AA2:AA, $AA2:AA<>""), FILTER($AB2:AB, $AB2:AB<>""))` 就會顯示專屬於 TPE:0050 的 XIRR 數值

      上面的 TPE:0050 請換成你想要套用 XIRR 的股票(Symbol)。

      做到這邊我們可能還是會小小抱怨說，這樣要看不同支股票還要把公式中的 TPE:0050 換掉，有沒有辦法可以一次計算所有股票的 XIRR?
      答案是可以的，我們可以看到只有 columns AA & AB 的公式含有 TPE:0050，但 ArrayFormula 生出來的表單是虛擬的，所以我們其實可以把它套用到 column AC 展開來。

      我們再加一個 column AD (XIRR2): AD2 寫公式 `=XIRR(FILTER(ARRAYFORMULA(IF($C2:$C="TPE:0050", $Y2:$Y, "")), ARRAYFORMULA(IF($C2:$C="TPE:0050", $Y2:$Y, ""))<>""), FILTER(ARRAYFORMULA(IF($C2:$C="TPE:0050", $Z2:$Z, "")), ARRAYFORMULA(IF($C2:$C="TPE:0050", $Z2:$Z, ""))<>""))`

      會發現數值和 AC2 一模一樣，這時候我們就可以把 columns AA & AB 刪除啦。

      接著 cells AD3, AD4, AD5 你就可以學 AD2 formula 把 TPE:0050 換成其他支股票就好。

      如果我們還要再更偷懶一點，有沒有辦法自動做到這種換股票的動作? 我們會發現 column AD 的排序和 sheet "Realized Stats" 的排序很像。如果我們在 Realized Stats 的 pivot table 再加入一個 Values/Calculated Field (column F)，把上面 column AD 的公式稍微變換一下，在 calculated field 的 Formula 填入 `=XIRR(FILTER(ARRAYFORMULA(IF(Symbol=Symbol, 'XIRR Values', "")), ARRAYFORMULA(IF(Symbol=Symbol, 'XIRR Values', ""))<>""), FILTER(ARRAYFORMULA(IF(Symbol=Symbol, 'XIRR Dates', "")), ARRAYFORMULA(IF(Symbol=Symbol, 'XIRR Dates', ""))<>""))
      `
      我們就可以在 Realized Stats 的表單中的 column F 看到所有股票的 XIRR 數值。

      注意: 同一支股票至少要有一次 Buy 與一次 Sell，XIRR 才會有數值，否則他會顯示 "Error: In XIRR evaluation, the value array must include positive and negative numbers."，pivot table 中的 column F 則會顯示 #NUM!

      刪除
    2. jianyi2023年6月29日 上午12:50:00

      感謝耐心地回覆，我目前模仿了最後這段，但卻出現ERROR，不是很清楚哪邊做錯了
      最後都有添加假的SELL
      https://docs.google.com/spreadsheets/d/13ooXnihoocjXWNRfoiJsUDYQ1AuYMF71Q2WxsV6k6kc/edit?usp=sharing

      刪除
    3. Shawn Chang2023年7月8日 下午7:08:00

      Hi jianyi,
      我發現前一個留言中有兩個錯誤:
      1. XIRR 應該也要考慮到股息 (cash dividend & stock dividend)
      2. 我忘記說 Realized Stats 的 pivot table 中的 calculated field 要設定 "使用自訂公式計算值" (Custom)，不能用 使用 SUM 計算值 (SUM)

      所以其實不需要 columnx X,Y,Z，XIRR Values 直接使用 Flow 就好了，XIRR Dates 直接使用 Dates。
      你可以參考一下下面這個修正的表單連結，我還有另外新增一個 XIRR Validation 的 tab，用最簡單的 XIRR 公式來驗證結果，看起來跟 Realized Stats 中結果是一致的。
      https://docs.google.com/spreadsheets/d/1jDDqz-xyZrjKPC6U1_ddpq1wG3ky-YriL_ZPhtjJC5E/edit?usp=sharing

      刪除
    4. jianyi2023年7月11日 上午12:07:00

      Hi Shawn, 了解，目前已成功非常感謝。
      現在才發現確實直接使用flow就可以，或許還能新增一項若以當天價格全數賣出的XIRR，這樣也可以幫助評估投資效果如何

      刪除
    5. Shawn Chang2023年7月13日 下午8:41:00

      讚~謝謝你讓我知道結果。XIRR和你說的功能其實就像是 extension 一樣，基於這個表單希望大家就能客製化出自己想要的功能 :)

      刪除

發佈留言

此網誌的熱門文章

[Side Project] 互動式教學神經網路反向傳播 Interactive Computational Graph

-
圖片
成品 互動式網頁連結:  Interactive Computational Graph 微積分課經典的範例 df/dx sin(x) / x: 2 個 inputs, 1 個 output, 沒有 hidden layer 的 fully connected neural network: 為何要做這套工具 以前在學 machine learning 的時候覺得最難理解的部分就是 backpropagation，也就是神經網路到底是怎麼用數學來做訓練的。 我們知道神經網路就是長這樣子: Source: Wikipedia 但實際上每個 neuron (圓圈圈) 裡面有許多基本的數學運算，像是 sum 還有 activation function: Source:  Neural Networks and Machine Learning 當我們把所有東西綜合在一起，腦袋就會打結，到底 chain rule 是怎麼在這麼複雜的神經網路之間套用的? 就算了解基礎也很難相信實際上到底怎麼運作的。我又沒有要真的使用的話，網路上的教學就覺得看看就好 Source:  A Step by Step Backpropagation Example 但決心要了解 backpropagation 細節之後，不少教學都提到了 computational graph，從基礎的數學運算當作 nodes 來去理解似乎是比較簡單的方式。 後來找到了這篇文章  Calculus on Computational Graphs: Backpropagation  把 chain rule 講解的簡單至極，於是就在想，為何我不做一個網頁，讓大家可以自由地組合想要的 graph，然後看一下 chain rule 是怎麼流通的。 失敗作經驗 在做這次 side project 前，其實早在一年前我就開始作另一個 side project 是想展現神經網路的訓練過程。名稱叫做 explain-derivatives，是被 TensorFlow 的一個網頁 A Neural Network Playground 啟發的。 但由於一開始欠缺規劃，想到什麼就加什麼功能進去，造成架構一直修改，最後由於程式碼架構太過複雜就放棄了。 這個 project 是用 Cy...
閱讀更多

[插件] 在 Chrome 網頁做區分大小寫的搜尋

-
圖片
寫軟體的時候最常用到的功能就是上網查 API 文件，但是常常會遇到一個困擾就是，搜尋的文字在 Chrome 裡面是不區分大小寫 (case-insensitive) 的。有的時候是還好，多按一些 F3 就找到了，可是有時候實在是太多大小寫的東西混在一起，這時候我們有一些快速的解法: 用 Firefox 搜尋 (Firefox 的搜尋框裡面預設就提供有 match case 的功能了) 開 DevTools 按 Ctrl + Shift + F 進行全部東西的搜尋 (不過大概會發瘋) 用插件的解法算是意外發現的，原本只是在找有沒有可以用 regex 搜尋的插件，沒想到意外發現  regex 的插件也是可以拿來做區分大小寫 (case-sensitive) 的一般搜尋。 用 Chrome Regex Search 來做區分大小寫的搜尋 先裝好 Chrome Regex Search 之後，按 Ctrl + Shift + F 或是點工具列的圖示，打上想找的文字就可以了。 例如我最近在看 Qt QTextCursor 的文件，但是我只想找 "cursor"，很不幸的如果用 Chrome 預設搜尋他會把一大堆 QText Cursor 也會找到: 如果用 Chrome Regex Search 就會侷限在小寫的 "cursor": 用 Regex 的陷阱 如果你不知道 regex 是什麼，那你很有可能會發現在搜尋一些含特殊符號的東西會搜尋到怪怪的東西，不然就是搜尋會失敗。 這是因為 regex 本來就是偏工程師在用的東西，有一些符號是有特殊涵意的，所以如果搜尋的字包含以下任何特殊符號記得要在前面加一個反斜線 \ ( ref ): . + * ? ^ $ ( ) [ ] { } | \ 例如我想搜尋 1+1=(2-1)+1*1 ，那其實要打 1\+1=\(2-1\)\+1\*1  才會正確找的到。 是不是很麻煩? 不過如果你要找的東西有特殊符號的話，其實用一般 Chrome 的搜尋通常是不會有太多搜尋結果的，那就用一般搜尋就好。 Chrome 什麼時候要支援大小寫搜尋? 其實上面弄了那麼麻煩，還不如開個 Firefox 搜尋更快。那我就滿好奇，這麼基本的功能怎麼 Chrome 一直都沒做? 找了一下才發現 Super User 有人...
閱讀更多