超過萬次下載的【史上最懶股票管理表】,收集許多網友意見後,優化、修正出來的第二版,更好用、更方便,幫你更準確掌握持股損益狀況,加快財富自由速度!
本文目錄:
史上最懶股票管理表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 參數」不能為零的錯誤訊息
請檢查交易紀錄表中,除了買入和賣出,其他欄位是否有空白資料,尤其最右邊的手續費折數經常被遺忘。
沒看到你的問題?加入使用者社團吧!
請多利用使用者互助社團,發問時附上截圖或影片,好讓大家快速了解你的問題,如果有公式問題,請附上檔案共享連結,方便社友幫你檢查~ 新版本也會優先讓社團成員試用,趕快加入吧!
贊助我一杯咖啡
這份管理表上線以來,已經幫助超過萬名網友開始建立管理股票習慣,我也花了很多時間、精力不斷優化。如果覺得股票管理表幫助到你,歡迎贊助我一杯咖啡:
街口支付
信用卡、轉帳
或是幫我點五個讚:
謝謝版大,真的好用
您好 不好意思請問一下,我輸入完但在股數加總那邊有誤(買賣相抵之後應該為0 但卻是累加) 該如何修正呢? 謝謝
請把你的檔案連結共享,才能幫你解決問題喔!按右上角「共享」
請問現價今天一直無法顯示,使用ImportFromWeb外掛也顯示錯誤,有什麼方法可以修正呢?
目前測試感覺上是 googlefinance 出問題,提供一個抓現價的暫時解決方法。把現價的公式換成:
=IMPORTXML(“http://histock.tw/stock/”&C7,”/html/body/form/div[4]/div[3]/div/div[1]/div[1]/div[2]/div[1]/div[2]/ul/li[1]/span/span/span”)
其中,C7 是指儲存格,記得把 C7 替換成股票代號對應的欄位,設定好一次之後,用滑鼠把公式拉到所有現價的儲存格就可以囉。
不知道這個能撐多久,先用用看~再觀察一下 googlefinanace 什麼時候會好。
可以的話請加入社團,有最新動態會發布在那邊喔
感謝大大提供
這裡有小小的建議可以修正抓現價的問題,我自己測試是ok的
在指令碼編輯器裡 twprice 的指令裡面網址修正為 http://mis.twse.com.tw/
就可以正常的從證交所即時資訊API中抓取現價了
感謝大大寶貴的建議!晚點來試試。另外我發現用 importxml 函式抓取 histock 網站的現價也可行,萬一證交所又不給人抓,可以試試看喔。
感謝木木大大的表單,
街口奉上一點點小心意~~
另想問問如果是美股的話,我可以怎麼修改語法以獨到現價呢?
謝謝你的支持!
美股超簡單,直接用
=googlefinance(股票名稱)
就好了,例如 Google 的價格就是=googlefinance(GOOG)
。00878 無法新增
謝謝您
直接新增在股票代號表裡就可以了
您好:想請問「成交價金」金額,如券商使用無條件捨去,該如何設定公式呢?
目前金額會多一元,再請版大解答了,謝謝
請參閱常見問題「手續費比實際多一元或少一元」段落
=if(ROUNDUP(abs(F3+H3)>0,abs((F3+H3)*(G3+I3)),” “))
請問公式是這樣改嗎?目前這樣是錯誤的
無條件捨去:
=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)))))
您好,上面的範例是針對「手續費」,我是想詢問「成交價金」
抱歉看錯,=if(abs(F3+H3)>0,roundup(abs((F3+H3)*(G3+I3))),” “)
以解決,謝謝您的解答:)
您好,想請問手續費,我的卷商手續費折扣後,他沒有因為手續費不足20元而直接扣20,
而是照實算例如18元就扣18元,那這樣我公式要怎麼調整呢?謝謝~
手續費:=if(isblank(B3),” “,if(B3=”股利”,0,abs((F3+H3)*(G3+I3)*0.001425)))
折讓後手續費:=if(isblank(B3),” “,if(B3=”股利”,0,K3*’Read ME!’!B$1))
謝謝回覆。但是我公式代換完之後,它顯示”公式剖析錯誤”,不知道該怎麼辦…
請注意 “” 符號須為英文半形,貼上公式之後要自行修改
您好 不好意思請問一下,因為美股和港股的稅金, 手續費, 都是有不同的, 那該如何修正呢? 謝謝 [稅金, 手續費是根據富途]
直接修改公式即可,我沒有使用富途,請自行研究,或是加入社團發問
建立副本之後下載 股價一直無法自動更新
不好意思
我輸入完後數值好像也有誤
想請您幫忙看一下
感覺持股狀況數值好像有誤
要在哪提供連結給您 ?
另外好像有看到BUG
目前”交易紀錄頁面” 的N6格,是定值7500,可能公式跑掉了?
你是不是用錯版本了,要用V2喔?目前看範本的 N6 格公式正常喔。
因為希望幫忙看表格的人太多,我不會免費幫忙看表格問題,請仔細閱讀常見問題,並且加入使用者社團自行發問,如果希望我幫你直接修正問題,請先贊助800元喔~
想請問買入價格的彙整依據沒辦法不四捨五入嗎?這樣就不是用準確的資料算出來了
你可以在持股狀況的買入價格那一欄增加小數位數,就可以顯示正確價格了
您好,因未實現損益想算出 (現價)*(持有股數)-(成交價金) 以及未實現損益率想算出 ((現價)*(持有股數)-(成交價金))/(成交價金)*100% 的數值,但更改程式後出現ERROR,可以請問版大 程式的寫法嗎?
請到社團發問,和社友們一起討論喔~
請問3714富采, 新增至股票代號表之後,現價部分沒反應,請問如何設定? 謝謝
請問興櫃無法抓到資料對嗎?
剛剛測試是沒問題的,如果你抓不到興櫃現價,重新下載一次檔案看看
還是不行唷~股價有出來,但是名稱那邊顯示找不到值?
另外請問我交易紀錄key好後,持股現況沒有出現?
1. 把找不到名稱的股票新增在股票代號表
2. 持股狀況沒出現,請閱讀常見問題
找到了!!感謝說明
請問持股現況的總和那一列數字都不對?
交易紀錄有輸入9筆,但持股狀況只有顯示一筆??
朋友推薦所以加入了妳的社團,今天加入的,有看妳的文章寫的不錯!! 後續會再想繼續閱讀跟支持!想先詢問一下,有關下載股票的這個試算表,是不是下載到google打開後是只能檢視不能編輯的,對嗎?感謝您的回覆!
您好,看到您的檔案覺得不錯馬上下載使用,發現問題一堆無法順利使用
想跟你購買可以嗎?費用是800元嗎?
還請回覆,謝謝~
請到粉絲頁私訊給我喔
您好,經過整晚的作業終於正常,是因為找不到雲端硬碟已經找到了 ^^
現在又有個問題想請教:
關於輸入股票代碼名字出不來一事
選股票代號>格式>數值>勾選純文字後,還是無法出現公司名….
還請回覆,謝謝
檢查「股票代號表」是否有你輸入的股票,沒有的話,請自行新增
有輸入喔,謝謝
公式是不是被刪掉了?複製正常的一格,在跑不出名稱的格子,右鍵>選擇性貼上>僅貼上公式。再不行的話,請把問題發在社團,不然只看文字也看不出問題在哪
請問持股狀況的篩選器,只能選4檔股票同時查看,無法一次檢視全部,超過4檔,就無法顯示,出現”#REF!”,不知問題出在哪裡,我在網路上到處搜尋都找不到答案,所以請教您,請問有方法可以解決嗎?感謝您!
持股狀況底下的灰色說明刪除了嗎?「(閱讀後請將本行文字刪除,否則表格將無法使用)…」
您好,請問後續能增加美股ETF的部分嗎? 很需要~ 謝謝您~