史上最懶股票管理表格!自動計算損益、成本、股價和報酬率, Excel 免費下載

懶人股票管理表 Excel

自動化股票管理 Excel,史上最懶,沒有之一!只要紀錄股票成交金額,除了自動計算買賣成本、方便記帳,Excel 還會自動更新股價、自動計算損益和報酬率,這份股票 Excel 幫你隨時輕鬆了解投資組合獲利狀況。

2020.8月 更新版本已上線
感謝網友們的愛用,史上最懶股票管理表已經超過一萬次下載了!期間我收集許多網友的建議,做了優化後更好用、更方便的新版本,請點我前往下載最新版本。

這篇文章將不再更新、回覆留言,有任何問題,請到最新版本的文章留言,或前往社團發問喔!

本文目錄:

史上最懶股票管理表下載

網路上雖然有很多股票管理 Excel,但是都設計成同一隻股票填在同一張工作表,如果我今天買了玉山金和 0050,就要紀錄在不同張工作表,又沒辦法把所有持股的狀況整合在一起!

我網路業打滾多年,最喜歡優化流程,所以自己動手設計這份「史上最懶股票管理表」,用最少的步驟紀錄,讓表單自動計算、統計,持股狀況一目瞭然!

我的設計邏輯是:只要輸入每次交易的股票、交易價和股數,表單會自動算出手續費、稅金和現金進出,還會自動抓取目前股價、自動計算未實現損益,讓你一眼就知道自己股票賺多少。

點我下載

股票理財相關文章:

【股票入門系列】

【投資理財入門】

史上最懶股票管理表使用教學

請先建立副本到自己的 Google 雲端硬碟。不建議下載轉成 Excel,因為表單有內建抓股價指令,不確定下載後是否會失效。

股票自動化管理 Excel 建立副本

1. 輸入下單折數

打開「Read Me」工作表,在框框內輸入 0~1 之間的數字,如果電子下單打6折,請輸入0.6;如果沒有折扣,請輸入1。

股票自動化管理 Excel

2. 輸入交易資料

接著打開「交易資料」工作表,輸入每次的交易資料。白底部分需要你手動輸入,黃底是自動化生成區,不需更動。

股票自動化管理 Excel 表格說明

買賣股票交易怎麼紀錄?

下拉選擇「買」「賣」或是「股利」,

股票自動化管理 Excel 表格說明

輸入股票代號和名稱,如果股票代號是0開頭,記得要在數字前加上「’」,0才不會被表單吃掉,例如0050輸入「’0050」。

輸入交易股數,請注意:如果是賣出股票,股數要加上負號,例如賣一張,就紀錄為「-1000」,可以理解成手上持股少了1000股。最後,輸入成交金額。

後面的手續費、交易稅和支出收入就會自動算出來啦!

股票自動化管理 Excel 表格說明

這裡的支出收入,指的是證券戶實際扣除或撥進來的錢,如果是買入股票,支出就是「成交價金+手續費」;如果賣出股票,收入是「成交價金-手續費-交易稅」。

*4/6更新:新增「成交價金」與「交易成本」欄位,會自動計算投入資本與手續費、稅金支出,方便記帳

最後一步,填寫決策原因,方便日後檢討。

為什麼現價怪怪的?

如果現價顯示「請手動輸入」或 #ERROR! 的錯誤訊息,是因為你買賣的股票是上櫃或興櫃,目前抓取股價使用的 googlefinance 函數,只能抓台灣上市股票現價。

為了抓取上櫃、興櫃現價,我沿用資工心理人提供的指令碼(感謝大大貢獻!),但是證交所似乎不給人多次抓取,如果有多筆上櫃交易資料,通常只有第一筆可以正確抓取。

如果你有多筆上櫃交易資料,可以把其他筆的現價連結到正確現價那格,後續即可自動連動。 例如:儲存格G8 可以正常抓取股價,而你在第12行有一筆同個上櫃股票的交易,你可以把儲存格G12 的公式改成「=G8」,即可自動連動上櫃股價。

如果覺得太麻煩,也可以手動輸入,只是這樣就要一直更改了。

領到現金或股票股利,怎麼紀錄?

下拉選擇「股利」,

股票自動化管理 Excel 表格說明

輸入股票代號和名稱,股數和成交價都不需要填,直接在收入欄填上稅後、實際收到的現金股利。

如果是配發股票,就在股數欄填寫領到的股數,其他欄位都不用動

3. 檢視持股狀況

點開「持股狀況」工作表,發現…當啷!!!!竟然已經統計完成了!這裏使用資料透視表,自動統計「交易資料」表單裡的資料,你什麼都不用做,就知道自己股票資產的損益狀況!

如果點開「持股狀況」工作表,發現什麼都沒有,是因為篩選器沒有勾選新輸入的股票。點灰色的部分,在右邊欄最下面的「篩選器」,勾選你想看的股票。

已經出清的股票,股數是0,已實現損益可以看出稅後報酬率:

如果還持有股份,會估算以現價全部賣出的真實損益(已扣除交易稅與手續費):

股票自動化管理 Excel 表格說明

*這邊要特別感謝 Jimmy Ray 和 Magic 兩位網友,他們建議未實現損益要先扣除賣出的手續費與交易稅,的確是突破我的設計盲點!在此特別感謝~~

在個股的列上點兩下,會產生個股歷史交易資料:

股票自動化管理 Excel 表格說明

史上最懶股票管理表的優點與限制

優點

  • 超級自動化!能不動手就不動!!我最懶!!!!
  • 所有持股整合在同一張表,不需要開多個工作表,也能看出個股的損益和報酬率
  • 直接扣除交易成本(包含下單折讓),計算真實損益
我就懶 梗圖

限制

  • 看不出趨勢:統計邏輯是靜態的,每次打開統計表,就像是幫你的股票資產拍張截圖,看不出來過去一年的變化
  • 無法計算現金殖利率:因為管理表整合所有歷史資料,而現金殖利率只看單一年份
  • 算不出資金比重:「支出」項目不會因為賣出持股而減少(因為拿到的現金列在收入),沒辦法算出「目前持股的支出」,故無法算出比重。
  • 承上,可以在「持股狀況」工作表拉圓餅圖或用公式計算,但是只要新增加一隻股票,資料範圍就要重拉,不符合懶人精神,故不設計進表格裡

待優化項目

  • 上櫃、興櫃抓即時股價的問題
  • 持股狀況的已實現、未實現損益總和錯誤問題
  • 成交均價計算錯誤
  • 輸入代號,自動抓股票名,目前有套用工程師 AVEX 的指令碼,但一樣吃證交所資料,和上櫃興櫃的股價一樣時好時壞
  • 未實現損益先以市價估算手續費與交易稅

史上最懶股票管理表常見問題

由於部落格留言和私訊的數量太多,我平常也要工作,無法一一回覆。我把常常被問到的問題統整在這裡:

「交易資料」工作表相關

為什麼我輸入交易資料,價格、成本沒有自動出現?

如果你手動輸入、黃色區沒有反應,可能你不小心把公式清除了,只要複製正常的一列,選擇性貼上 > 僅貼上公式,即可修復問題。

為什麼我的股票現價出現 #ERROR?

  • 如果你交易的是上櫃、興櫃股票,有時候會無法正常顯示,原因請看這裡的解釋,多重新整理幾次或許可以解決。
  • 如果你交易的是上市股票,現價卻顯示「請手動輸入」,可能股票代號是0開頭,記得在0前面加個「’」符號,0才不會被吃掉,例如:「’0050」。

「持股狀況」工作表相關

輸入交易資料之後,持股狀況沒有反應

持股狀況的篩選器記得勾選,操作方式請看這裡

賣出的股票不會被扣掉,反而被加起來

如果是賣出股票,股數要加上負號,例如賣一張,就紀錄為「-1000」。

持股狀況的總和列不正確

沒錯,「持股狀況」的總和是錯誤的,這是因為受限 Google sheet 的資料透視表的公式設計,目前沒辦法計算正確總和。

解決方法,是把想加總的範圍選起來,右下角會顯示正確總和。有點麻煩,但目前還沒有好的解法,還請包涵。

股票自動化表單

沒看到你的問題?加入互助社團吧!

請多利用使用者互助社團,發問時附上截圖或影片,好讓大家快速了解你的問題,如果有公式問題,請附上檔案共享連結,方便社友幫你檢查~ 新版本也會優先讓社團成員試用,趕快加入吧!

加入臉書社團

73 Comments

  1. 匿名訪客

    很棒的表格咧
    有幾個點覺得可以更好

    1. 持股部份或許可以加上年化報酬率的欄位
    2. 手續費跟折讓好像都是小數點無條件捨去, 但google sheet預設好像是四捨五入, 我自己是直接用Rounddown移掉
    (是說差那一塊也沒差吧XDDDD)

    • 王木木

      哈囉~謝謝你喜歡這份表格
      也很謝謝你提出的建議

      下一次改版應該會增加年化報酬率
      自己也覺得蠻需要的 XD
      至於手續費,好像各家券商做法不同
      有無條件捨去、也有四捨五入或無條件進位
      下一版本會試試看讓使用者自己選擇小數點處理方式
      讓每個人的數字都可以百分百精確

  2. Oschangkai

    價格的部分可以改為
    =if(ISBLANK(C3),””,iferror(GOOGLEFINANCE(“TPE:”&C3),iferror(value(twprice(C3)),iferror(IMPORTFROMWEB(“https://www.bloomberg.com/quote/”&C3&”:TT”,”//span[@class=’priceText__1853e8a5′]”, “outputErrors, shake”), “請手動輸入”))))

  3. 匿名訪客

    您好,請問如果買零股,沒有最低20元手續費的規定,該如何調整?

  4. 匿名訪客

    請問我填寫表格後,持股狀況檢視買入均價是錯的,已經有加入社團,但是還沒批准,想請教版主看我哪裡錯了,感恩

    • 王木木

      你的均價是怎麼計算的?我的算法是「每筆買入價格*每筆交易張數」的總和/總買入張數哦

      社團沒批准是問題沒有回答完整,或有些條件不符合

      • 匿名訪客

        版大妳好:我填寫交易紀錄後去持股狀況查看(有買入有賣出也有股息),發現跟我自己算出來的有誤差,我沒更改裡面的程式,所以不知道自己哪裡錯了,真心想用這表格,感恩回覆

    • 匿名訪客

      版大妳好,我知道了,買入平均價格不含賣出跟配息,我以為是持股的平均成本,請問可以看到目前持股的平均成本嗎?感恩

      • 王木木

        你指的是買入成本-賣出的獲利或虧損-配息媽?如果是的話,只要手上還有持股,就沒辦法計算獲利或虧損,因為沒辦法判斷賣出的獲利或虧損,是以哪一次的買入成本來計算的。

        • 匿名訪客

          版大妳好:我是想檢視買入平均價格,但是因為買入平均價格不含賣出跟配息(=sum(arrayformula(‘買入股數’*’買入價格’))/sum(‘買入股數’),所以導致表格顯示出來的平均價格比實際低,可以怎麼含賣出的價格跟配息呢?感恩

          • 王木木

            抱歉,我不懂什麼是「含賣出的價格」?可以舉例嗎?

          • 王木木

            你是不是用到舊版了啊?請用 V2 試試看符不符合你的需求?https://smarter01.com/2020/08/04/stock-management-form-v2/

          • 匿名訪客

            版大妳好:我是下載新版的,舉例 100價格買入5張,然後90價格再買入5張這樣的(買入均價)會顯示95,但是如果後續90賣出3張並沒有加進去算所以(買入均價)一樣是顯示95,所以導致可能會不知道自己目前持股的成本價在哪裡,感恩

          • 王木木

            你可以在持股狀況自己設計一個公式,(支出-收入)/持有股數,應該可以算出你想要的數字吧?試試看 =(E2-F2)/B2 (不要用資料透視表編輯器喔!)

          • 匿名訪客

            感謝版大,不會寫公式,已加入社團,感恩

  5. 匿名訪客

    請問一下 篩選器在哪….一直找不到
    謝謝大神~

發佈留言

發佈留言必須填寫的電子郵件地址不會公開。 必填欄位標示為 *