0.前言
一直以來都是使用EXCEL製作股票的投資紀錄，也有用過其他作者的版本，不過總覺得不夠自動化，
除了將股票投資的紀錄下來，讓EXCEL自動計算成本外，還希望可以彙整成一份投資報表，這樣一目了然目前的投資配置損益。
投資配置的表格圖片化，有助於資產配置時快速的了解投資的比例應該如何做調整。但是以往的方式每年的股票配息
或其中各時期的買賣紀錄繁雜，再加上股票的檔數隨著時間變多，管理的複雜度也會增加不少。
圖片畫成圓餅圖也是一件累人的事情，尤其投資標的新增跟刪除，圓餅圖每次都要重畫，要是可以自動跑圖片就太好了
進而在計算XIRR(內部報酬率或稱年化報酬率)時，更發現要人工將每檔標的的買賣紀錄與領股息紀錄，
彙整成EXCEL可以吃的XIRR數據，也是相當累人的事情，
例如有十檔標的就得將十檔標的歷年來的買賣紀錄與股息紀錄彙整成一列，再使用XIRR函數計算。
手動做完大概就暈倒了。有了XIRR數據才能做投資績效的計算，有的朋友喜歡跟大盤比較，有的喜歡跟定存比較都是不錯的。
因此大概設計會有以下的功能需求，因為這是個人客製化的產品，如果功能需求有跟我類似的就可以使用看看
(1)存股紀錄表，可以自動計算配息後或買賣後的持股成本
(2)可以畫成圓餅圖，要自動更新標的刪減
(3)要一次可以算很多標的，統整成一份投資組合XIRR
(4)可以自己自動更新股價，因為我很懶得自己爬網頁填數據
(5)要可以記錄台股跟美股，都要可以更新股價
(6)要自動將美元資產轉成台幣計算，所以要可以自動抓銀行匯率做美元資產轉台幣計算
(7)可以一鍵將各股股息配息紀錄配息金額抓下來，在EXCEL上呈現，同(4)很懶得一個一個網頁找資料很累
(8)要可以彙整成一份報表，眼睛看就知道哪檔成本多少，有沒有虧錢，還是有賺錢，最好可以把虧錢列紅字
(9)操作介面的按鈕不要太多，簡單幾個就好
(10)新增標的時，要可以自動幫我新增一個新標的的存股表，方便填寫，不要我還要手動移動複製增加一份
(11)股號填寫時，不小心填到重複的股號要提醒我有重複填寫，才不會白癡多填一份一樣的內容
(12)圖表圖形最好一張圖就好，可以快速用產業分類，看比例，也可以在同一份看台幣資產與美元資產的比例各是多少
(13)減資或增資或是美股股票分割，可以讓我自己用存股表調整市價與股數
(14)可以把每日投資組合的市價與成本價都記錄成一個表格，自己計算出目前(市價-成本)/成本=報酬率，
而且按一個鍵會自動更新，在幫我把每日報酬畫成折線圖
(15)可以把每年投資金額與股息列出
大概以上這些功能就是我心目中認為最好的存股表，下面這個版本有1~13項的功能
第14項已經做好，限個人使用，第15項還沒空做
外觀介面:
(1) SHEETS
打開頁面會有四個SHEETS，分別是簡易操作說明頁面跟歷史紀錄；更新股價頁面，主要操作按鈕的地方；
一個持股總列表，就是把各存股表彙整的地方；Demo_From 就是每個存股表參考copy的範本，所以這頁不能刪掉
(2)
按鈕共四個，"開始建立存股表"在股票代號黃底填寫好一堆股號後，就可以按這個按鈕，對應產生複數個存股表
"更新持股種列表"壓一下就可以把各頁存股表計算好的成本價格資訊都彙整成一頁
"更新各股股利資訊"按一下可以抓全部股號的歷史股利資訊都列在該存股表
"重置存股表"當存股的內容不想要了或是想重新開始製作存股表，按一下就可以把各存股表刪除
點圖可放大
１．更新股價頁面說明
(1) 填寫股票代號
美股的部分，在欄位14要填寫1
例如VTI股票號的最後方填１
注意事項:
A.更新股價頁面，股號與股號之間要連續填入
B.請勿填寫重複股號與不存在的股號或異常字元
C.填寫黃底股票代號，一共支援存股一百支
D.是美股標的在Column 14，填1，反之不填，(不支援台股與美股以外的標的)
(2).點選開始建立存股表
會依據填寫的股票代號開始展開存股表，ＳＨＥＥＴ的名稱就為股票代號
(3)更新股價的頁面會開始自動抓取及時股價
最多可以支援一百檔股票，美股或台股
(4)若收盤價美國網頁尚未更新，會自填寫填最後一個交易日的價格
2.存股表說明
(1) 每個存股表，可以進入填寫買賣交易紀錄，還有股息發放紀錄，
(2) 在黃底的區域填寫交易日期，需要注意的是買進股數請填正號，賣出股數請填負號
買進總金額填負號，賣出總金額填正號
其中買賣的交易金額，請包含對帳單上的手續費
後方註解1~5可以自由填寫內容，買賣手續費紀錄或買賣原因心得
(3) 股息的部分，若前幾年沒有股息的部分，請填數字0，請一定要填值
例如2019才領股息，2016~2018就填寫0
台股一般可以照領到的股息通知書填寫，若美股則照1042表填寫
股息配發的時間各標的都不同，預設都填年底，實際可以照自己收到股息的時間填，格式如上圖
(4) 上方會自動計算買賣交易後最後的每股成本
對於長期投資的買賣紀錄與領股息紀錄，程式可以達到自動計算，節省人力時間統計計算
(5) 計算區基本上大多不用填寫，可以觀察美股成本與庫存股數等資訊，最後這些資訊會彙整到持股總列表
(6)各項說明:
A. 庫存股數=>目前持有的股數，含歷年配股
B. 總股數價值=>目前庫存的股數乘上市價
C.目前每股成本=>(總賣出金額+現金股息-總買入金額)/庫存股數
基本上就是買賣的金額加總與現金股息的總數除上目前持有的股數
D.總損益 =>目前賺或賠
E.是否結清=>填1代表結清，這個項目代表若已經曾經持有的股數都已經賣光了，這個欄位手填1，代表已經結算完畢
F.結清總損益=>根據上個項目算出損益
G.已領現金股息=>投資至今領到的現金股息
H.是否是美股?1表示"是"=>此項目不用填，程式會自動填入
(7)
點選歷年股利按鈕可呼叫歷年股息與EPS
若網站1抓不到就看網站2的資訊
3.更新持股種列表
以下皆為範例數據
(1)點一下更新持股總列表
會將各頁存股表資訊整合到持股總列表
美股VTI、TLT、VT都會轉換成台幣計價
(2)
更新後，會有總結目前的狀況
其中美股的部分會自動抓台銀的匯價更新持股總表，將美元資產換算成台幣資產
股票現值很好理解，就是目前投資組合市價總合
目前成本就是扣掉配息後，或是中間有作價差資本利得後的持股成本
浮虧%數就是從一開始投資到現在這段時間，(股票現值扣掉持股成本)/持股成本的報酬率
資本利得價差就是股票現值扣掉持股成本
XIRR 就是依據買賣與股息現金流試算後，每年的年化報酬率，如投資5年，就是這5年每年的年化報酬率是多少
如上圖是14.51%
(3)下方圖表由EXCEL樞紐分析生成，會自動畫報表，當上方持股總表更新時，下方圖形會再下一次點選更新持股總列表時連動更新
可以在股名上方點顯示欄位清單，就可以叫出樞紐分析表調整項目
(4) 資產XIRR計算
500ROW開始會將各頁的買賣紀錄與現金股利計算成現金流
算出XIRR，回填到總結欄位
(5)R欄填寫產業類別
可以填寫產業，也可以填寫國內資產或國外資產
(6)舉例以產業來分類:
(7) 將產業類別放到股名上方
再更新樞紐列表，可以更新圓餅圖，或點選更新持股列表按鈕
點選重新整理
(8)
將項目折疊後，圓餅圖也會更新
(9)舉例:想知道美元資產與台幣資產比例，可以將是否是美股的選項拖到產業的上方
4.更新各股利資訊
點選後需等待數分鐘做資料更新，會將各頁的股息資訊作拉取，若結果回空白請在到該存股表中手動點選更新股息資訊
更新完畢會出現訊息告知
5.重置存股表
若要刪除所有資料，請點選"重置存股表"
6.其他
(1)若輸入股號有重複會出現警告視窗，程式也會停止運作
重複項請自行刪除
(2)有存股表，可是更新股價股號列表沒有此號碼
例如有2317存股表，可是股票代號沒有2412，點選更新持股總列表後
程式判斷有多出2412存股表，會有提醒訊息，程式會停止
(3)新增股號
依照下圖步驟更新
此時新的存股表就會出現
大致上功能如此
有新功能以後再加上
End-user license agreements:
發表文章
0 留言