超過萬次下載的【史上最懶股票管理表】,收集許多網友意見後,優化、修正出來的第二版,更好用、更方便,幫你更準確掌握持股損益狀況,加快財富自由速度!
本文目錄:
史上最懶股票管理表v2下載
網路上雖然有很多股票管理 Excel,但是都設計成同一隻股票填在同一張工作表,如果我今天買了玉山金和 0050,就要紀錄在不同張工作表,又沒辦法把所有持股的狀況整合在一起!
我網路業打滾多年,最喜歡優化流程,所以自己動手設計這份「史上最懶股票管理表」,用最少的步驟紀錄,讓表單自動計算、統計,持股狀況一目瞭然!
我的設計邏輯是:只要輸入每次交易的股票、交易價和股數,表單會自動算出手續費、稅金和現金進出,還會自動抓取目前股價、自動計算未實現損益,讓你一眼就知道自己股票賺多少。
下載前請注意:表格有使用問題,請先閱讀常見問題,如果常見問題裡沒有你遇到的困難,請把問題附上截圖發在使用者社團。如果你的問題在常見問題已經有解答,無論是留言或社團,我都不會再回覆。
請先建立副本到自己的 Google 雲端硬碟。不建議下載轉成 Excel,因為表單有內建抓股價指令,不確定下載後是否會失效。
這份管理表使用許多 Excel 基本技巧:if、vlookup、樞紐分析表、下拉式選單(資料驗證)等等,雖然管理表用 Google 試算表設計,但邏輯和 Excel 完全一樣。
如果你完全沒用過 Excel,會容易遇到使用困難,建議先花點時間先學習 Excel 入門技巧,除了使用管理表更順利,還能按照自己想要的方式修改表格!
我推薦 Hahow 的這堂課《職場必備的 Excel 關鍵技法 – 入門》,課程內容幾乎涵蓋所有股票管理表用到的技術,售價也不貴,才650元,學會了一輩子受用!
史上最懶股票管理表使用教學
第一次使用:輸入下單折數
打開「Read Me」工作表,在框框內輸入 0~1 之間的數字,如果電子下單打6折,請輸入0.6;如果沒有折扣,請輸入1。
2. 輸入交易資料
接著打開「交易資料」工作表,輸入每次的交易資料。白底和紅、綠色部分需要你手動輸入(股票名稱除外),黃底是自動化生成區,不需更動。
買賣股票如何紀錄?
下拉選擇「買」「賣」或是「股利」,
輸入股票代號,就會自動帶入股票名稱,
填入買入或賣出交易股數和價格,就會自動計算手續費、交易稅、成交價金和現金支出或收入
- 成交價金單指「股數*成交價」,交易成本是「手續費+稅金」的總和,分出這兩項是為了區分投入資本和交易雜費的金額,方便記帳。
- 支出和收入,指的是證券戶實際扣除或撥進來的錢,如果是買入股票,支出就是「成交價金+手續費」;如果賣出股票,收入是「成交價金-手續費-交易稅」。(延伸閱讀:股票手續費怎麼計算?)
最後一步,填寫決策原因,方便日後檢討。
股利分配如何紀錄?
下拉選擇「股利」,
現金股利:輸入股票代號和名稱,股數和成交價都不需要填,直接在收入欄填上稅後、實際收到的現金股利。
股票股利:如果是配發股票,就在「買入股數」欄填寫領到的股數,其他欄位都不用動。(延伸閱讀:現金股利、股票股利差在哪?)
為什麼現價怪怪的?
如果現價顯示「請手動輸入」或 #ERROR! 的錯誤訊息,是因為你買賣的股票是上櫃或興櫃,目前抓取股價使用的 googlefinance 函數,只能抓台灣上市股票現價。
目前上櫃股票的現價,是抓取 Histock 網站的資料,可能隨時會掛點。如果現價持續抓取失敗、顯示「請手動輸入」,我會把替代方案更新在這篇文章。
檢視持股狀況
第一次使用
點開「持股狀況」工作表,發現什麼都沒有,是因為篩選器沒有勾選新輸入的股票。點灰色的部分,在右邊欄最下面的「篩選器」,勾選你想看的股票。
接著,所有的統計就完成了!這裡使用 Google 試算表的資料透視表功能,自動計算「交易資料」的資料,你什麼都不用做,就知道自己股票資產的損益狀況!
已經出清的股票
已經出清的股票,股數是0,已實現損益可以看出稅後報酬率:
尚持有股份
如果還持有股份,會估算以現價全部賣出的真實損益(已扣除交易稅與手續費):
*這邊要特別感謝 Jimmy Ray 和 Magic 兩位網友,他們建議未實現損益要先扣除賣出的手續費與交易稅,的確是突破我的設計盲點!在此特別感謝~~
在個股的列上點兩下,會產生個股歷史交易資料:
史上最懶股票管理表的優點與限制
優點
- 超級自動化!能不動手就不動!!我最懶!!!!
- 所有持股整合在同一張表,不需要開多個工作表,也能看出個股的損益和報酬率
- 直接扣除交易成本(包含下單折讓),計算真實損益
限制
- 看不出趨勢:統計邏輯是靜態的,每次打開統計表,就像是幫你的股票資產拍張截圖,看不出來過去一年的變化
- 無法計算現金殖利率:因為管理表整合所有歷史資料,而現金殖利率只看單一年份
- 算不出資金比重:「支出」項目不會因為賣出持股而減少(因為拿到的現金列在收入),沒辦法算出「目前持股的支出」,故無法算出比重。
- 承上,可以在「持股狀況」工作表拉圓餅圖或用公式計算,但是只要新增加一隻股票,資料範圍就要重拉,不符合懶人精神,故不設計進表格裡
史上最懶股票管理表常見問題
「交易資料」工作表相關
為什麼我輸入交易資料,價格、成本沒有自動出現?
如果你手動輸入、黃色區沒有反應,可能你不小心把公式清除了,只要複製正常的一列,選擇性貼上 > 僅貼上公式,即可修復問題。
為什麼我的股票現價出現「請手動輸入」?
如果現價顯示「請手動輸入」或 #ERROR! 的錯誤訊息,是因為你買賣的股票是上櫃或興櫃,目前抓取股價使用的 googlefinance 函數,只能抓台灣上市股票現價。
目前上櫃、興櫃股票的現價,是抓取 Histock 網站的資料,可能隨時會掛點。如果現價持續抓取失敗、顯示「請手動輸入」,我會把替代方案更新在這篇文章。
為什麼輸入股票代號,名字出不來?
請按照以下步驟檢查:
- 檢查股票代號的資料格式是否勾選為純文字(如下圖)
- 檢查「股票代號表」是否有你輸入的股票,沒有的話,請自行新增
- 檢查跑不出名稱的格子公式是否被刪掉,是的話,請複製正常的一格,在跑不出名字的格子右鍵 > 選擇性貼上 > 僅貼上公式
手續費有優惠活動,低於20,要怎麼調整?
假設券商舉辦優惠活動,最低手續費只收5塊,你需要把「手續費」和「折讓後手續費」的函數中,所有有20的地方,都改成5,例如(粗體字為修改的部分):
- 手續費:
=if(isblank(B3)," ",if(B3="股利",0,if(abs((G3+I3)*(F3+H3)*0.001425)<5,5,abs((F3+H3)*(G3+I3)*0.001425))))
- 折讓後手續費:
=if(isblank(B3)," ",if(B3="股利",0,if(K3*'Read ME!'!B$1<5,5,K3*'Read ME!'!B$1)))
表格計算的手續費比實際多一元(或少一元)
這是因為表格對小數點的處理採四捨五入,如果比實際手續費多一元,代表你的券商是採無條件捨去;比實際少一元,代表券商採無條件進位。
只要在手續費公式增加一個指令,就可以修正問題(粗體字為增加的部分):
- 手續費比實際多一元:
=if(isblank(B14),” “,if(B14=”股利”,0,if(abs((G14+I14)*(F14+H14)*0.001425)<20,20,ROUNDDOWN(abs((F14+H14)*(G14+I14)*0.001425)))))
- 手續費比實際少一元:
=if(isblank(B14),” “,if(B14=”股利”,0,if(abs((G14+I14)*(F14+H14)*0.001425)<20,20,ROUNDUP(abs((F14+H14)*(G14+I14)*0.001425)))))
「持股狀況」工作表相關
輸入交易資料後,持股狀況沒有反應
持股狀況的篩選器記得勾選,操作方式請看這裡。
輸入交易資料後,持股狀況中的已實現損益呈現空白的
如果該股票持股數>0,就不會顯示已實現損益,持股數是0才會顯示已實現損益。
這個表單的設計是只看單一股票的「全部損益狀況」,如果持股不是0,已經賣出的損益會連同沒賣出的損益,一起算在未實現損益裡,因為表單不知道你今天賣出的股票,要以哪一天的買入價作為損益計算基礎。
持股狀況的總和列不正確
沒錯,「持股狀況」的總和是錯誤的,這是因為受限 Google sheet 的資料透視表的公式設計,目前沒辦法計算正確總和,包含市值、實現損益、未實現損益的總和都會是錯的。
解決方法,是把想加總的範圍選起來,右下角會顯示正確總和。有點麻煩,但目前還沒有好的解法,還請包涵。
為什麼賣出股票後,沒有計算已實現損益?
假設你買了八張台積電,今天賣出一張,表單會不知道要以哪一張的買入價來計算損益,只有全部八張都賣出了,才算得出以實現損益。
如何計算同一支股票,賣出後又買入的股票損益?
史上最懶股票管理表的損益,是全部一起計算的,目前還沒辦法判斷買賣時間。提供比較粗糙的解法:
在股票名稱加上自訂文字,讓表單識別這是後來買入的。
例如我6/29買入006208,6/30全部賣出,7/1 再度買入的006208,股票名稱叫做「富邦台50第二波」,這樣表單就會把6月買賣的006208和7月買入的006208當成不同的資料計算:
不過這樣自動查股票名稱就會失效,需要手動輸入股票名稱。
手機上如何編輯持股狀況的資料透視表?
沒有辦法,目前 Google 試算表不支援手機 app 編輯資料透視表,請見官方文件。建議在電腦上調整好後,再用手機瀏覽。
出現「DIVIDE 函式第 2 參數」不能為零的錯誤訊息
請檢查交易紀錄表中,除了買入和賣出,其他欄位是否有空白資料,尤其最右邊的手續費折數經常被遺忘。
沒看到你的問題?加入使用者社團吧!
請多利用使用者互助社團,發問時附上截圖或影片,好讓大家快速了解你的問題,如果有公式問題,請附上檔案共享連結,方便社友幫你檢查~ 新版本也會優先讓社團成員試用,趕快加入吧!
贊助我一杯咖啡
這份管理表上線以來,已經幫助超過萬名網友開始建立管理股票習慣,我也花了很多時間、精力不斷優化。如果覺得股票管理表幫助到你,歡迎贊助我一杯咖啡:
街口支付
信用卡、轉帳
或是幫我點五個讚:
您好,
想詢問您一下,關於”未實現損益”的公式:
=if(sum(‘買入股數’)-sum(‘賣出股數’)>0,(sum(‘買入股數’)-sum(‘賣出股數’))*average(‘現價’)+sum(‘收入’)-sum(‘支出’)-(sum(‘買入股數’)-sum(‘賣出股數’))*average(‘現價’)*0.001425*average(‘手續費折數’)-average(‘現價’)*sum(‘買入股數’)*0.003,”-“)
最後一段,如果我的認知沒錯,應該是要計算交易稅?
average(‘現價’)*sum(‘買入股數’)*0.003,”-“)
為什麼是”買入股數”而非”買入股數-賣出股數”?
以及請問最後的 “-“-“代表什麼意思呢?
打擾了,
感謝
是我手殘打錯了~已經修正為(sum(‘買入股數’)-sum(‘賣出股數’))了!
您好,
真心感謝您迅速的回覆,長知識了!!!
想請教一下,因為我稍早已經副本到自己的google雲端,
這樣的話您這邊改公式,我那邊應該不會同步對嗎?
這樣我有沒有什麼比較有效率的方式可以更新?
因為稍早花了一點時間輸入自己的交易紀錄
最後的”-“是和 if() 函式一起的,如果 sum(‘買入股數’)-sum(‘賣出股數’)>0 條件沒有滿足,就顯示 – (” ” 是告訴試算表 ” ” 中間的內容是文字)
您好,
感謝您迅速地回覆,長知識了!
謝謝指點~~
請問更新後的公式,不會反映到我們自己的google上面對吧?
因為稍早花了點時間,輸入自己的紀錄,
有沒有比較有效率的方法可以修正呢?
還是我只能再重新建立一個副本,重新輸入?
是的,修改無法同步,但是不用重新下載副本,只需要改一個地方
1. 到「持股狀況」點一下灰色部分,叫出編輯器
2. 到「未實現損益」,把以下公式貼上,取代原本公式:
=if(sum(‘買入股數’)-sum(‘賣出股數’)>0,(sum(‘買入股數’)-sum(‘賣出股數’))*average(‘現價’)+sum(‘收入’)-sum(‘支出’)-(sum(‘買入股數’)-sum(‘賣出股數’))*average(‘現價’)*0.001425*average(‘手續費折數’)-average(‘現價’)*(sum(‘買入股數’)-sum(‘賣出股數’))*0.003,”-“)
這樣應該就可以了。
你好,請問持股狀況分頁裡最多只能顯示6檔股票嗎?篩選器勾了第7檔就全部都無法顯示,或者是我設定的問題?感謝你的回覆。
可能有動到其他設定,篩選器的顯示數量沒有限制。建議你把檔案分享到社團,才能幫你找到錯誤喔
我也有遇到一樣的問題,可以麻煩您用原始檔加到7檔股票測試看看嗎? 謝謝!
請把持股狀況最下面的小字「**如果你是第一次使用本表格…」刪掉喔
篩選器的部分就卡住了
請問要怎麼叫出來?
用滑鼠點持股狀況的表格內隨便一個地方就有了
同為懶人真心感謝你提供這麼好用的東西!!
想詢問當沖時
第二個欄位選擇買或賣是代表先買後賣或先賣後買嗎?
啊… 真是不好意思
當沖是因應使用者需求加上去的(為了正確計算稅金)
其實我沒有當沖的經驗
所以不太確定你的問題是什麼
建議你加入社團,問問有當沖的股友
是怎麼運用這張表紀錄當沖的
抱歉耶~
我今天第一次下載,請問為何 持股狀況內無公式呢?
持股狀況本來就沒有公式,要在交易記錄輸入資料,持股狀況才會有東西
你好,我有在交易紀錄輸入資料,但是我的持股狀況 是空白的,是因為我使用平板嗎?
你沒勾選篩選器,操作方式請看這裡:https://smarter01.com/2020/08/04/stock-management-form-v2/#filter-check
篩選器只能在電腦上操作哦。
原來是因為我使用平板,謝謝您迅速的回覆
你好,請問我想要建立副本,但是一直出現錯誤?
錯誤訊息是什麼?
「Google文件發生錯誤」
請參考 google 官方說明哦,沒有遇過這種狀況 https://support.google.com/drive/answer/7505592?hl=zh-Hant
原來是今天下午 google 有當機 https://buzzorange.com/techorange/2020/08/20/google-shutdown/
謝謝分享 真的很需要一個懶人包 幫助新手~ 太感謝你了
很開心有幫到你!祝你發大財哈哈
總市值的算式會將持股為0的股價也納入均價計算
常見問題有說明喔~ https://smarter01.com/2020/08/04/stock-management-form-v2/#total-error
謝謝您解惑
謝謝大大!!!
請問為什麼我的現價欄位是空白的?
麻煩了謝謝
整欄都空白的嗎?還是只有幾格空白?
整欄都空白,但公式都還在
要截圖或丟副本到社團才能幫你看哪裡出錯哦 https://www.facebook.com/groups/283186793075715
想知道為啥我券商對帳單的手續費有幾筆都跟表格有1元的差距謝謝
小數點處理的關係,表格預設四捨五入,券商可能無條件捨去或無條件進位
請問股票代號後,名字出不來,已照你的步驟更正為純文字,跳不過去純文字,都會跳回自動?
「股票代號表」是否有你輸入的股票?股票代號是否輸入錯誤?股票代號是0開頭嗎?
您好,謝謝大大分享超實用表格~新手入門請多指教!
我的股票名稱也出不來呢,股票代號表裡有我輸入的股票,股價也都有帶出來,就是無顯示股票名稱?
想請問是哪部份設定需調整呢,謝謝
顯示股票名稱的公式被刪除了,複製正常顯示的一格,右鍵>選擇性貼上>僅貼上公式就可以了
請問,我有買賣股票,在交易記錄上都有記錄,為什麼在持股狀況上,已實現損益和已實現損益率都是空白的?
嗨~我沒有遇過這種情況呢
可以請你把檔案副本貼到社團裡嗎?可以幫你看看哪裡有問題
請問,我有買賣股票,在交易記錄上都有記錄,為什麼在持股狀況上,已實現損益和已實現損益率都是空白的?
並且,未實現損益和未實現損益率的總計是不對的。
請見常見問題
– 損益空白:https://smarter01.com/2020/08/04/stock-management-form-v2/#profit-is-empty
– 總和錯誤:https://smarter01.com/2020/08/04/stock-management-form-v2/#total-error
您好 非常謝謝你提供了這麼好用的表格.
想請問一下, 對於現金增資或減資, 我選項用股利(才不會計算到交易稅等). 減資的價格會是零 所以我欄位填寫零
但在持股狀況裡的買入均價似乎沒有計算到正確的成本(成本應該是更高), 我是哪個步驟錯誤了嗎?
謝謝
請把檔案建立副本(可刪除與問題無關的交易資料)分享到社團,才方便幫你找出問題所在哦
您好
謝謝妳無私提供股票管理表格
我在使用上無法抓到更新後的股票現價,一直抓到原表單的股票現價 謝謝
不太懂你的意思,股票現價沒有新舊表單呀
請問為什麼表格不用ROUNDDOWN()
手續費全部券商都是小數點無條件捨去吧
沒人在四捨五入吧
每家券商的規則都不同,也有無條件進位的哦。你可以自行修改公式哦
請問可以把”持股狀況”最下面總和那列刪掉嗎
那行感覺沒什麼意義,一堆亂加沒用的數字
而且只要一有庫存股,總和的已實現損益就會全部跳去總和未實現損益
我想刪掉總和那列在自己從打好像不行QQ
不可以,那是自動產生的
您好DR類股證交稅也是1%是不是選ETF類型?
DR 類股和 ETF 交易稅一樣都是0.1%,選擇 ETF 沒錯哦
請問在黃底現價出現”請手動輸入”,現價他不是會自己抓取嗎?
請看常見問題說明
https://smarter01.com/2020/08/04/stock-management-form-v2/#price-error-faq