青蛙的綠色池塘

對股票投資族來說，
使用 Excel 或類似的試算表工具來記錄、或管理投資明細與計算報酬率，
並不是什麼新鮮的想法，
在 google 上也可以找到許多人分享如何製作自己的股票試算表，
但對習慣 (或者說是偏好) 使用 Excel 的人來說，
最讓人頭痛的部份應該就屬「如何抓取股價資訊」以自動計算庫存損益了。

在過去，
我們可能得透過手動建立自網頁匯入表格的連結功能、
逐筆將個股資訊加入 Excel 中，
或是透過撰寫 VBA 的方式來抓取。
Excel 裡一直缺乏像 google 試算表裡可以直接用內建函式來快速取得股票資訊的功能，
不過這一點在 MS Office 365 中的 Excel (以下簡稱 Excel 365) 內終於有解－
Excel 365 在 2003 (2020 年 3 月版) 更新版本中新增了「股票」與「地理」資料類型，
其中「股票」資料類型便可以讓我們以更優雅的方式取得股價資訊，
雖然因為非即時更新、所以還無法取代其它看盤工具，
但若是要整理自己的股票投資交易明細的話、則是綽綽有餘了。
另外，此功能目前貌似只支援在集中市場掛牌的股票，
在櫃買市場掛牌的股票則是都搜尋不到，
只能再等等看看未來是否會加入支援了...

目前此功能僅支援 Office 365 內的 Excel 應用程式，
若以其它買斷型的 Office (如 Office 2016、2019 等) 開啟包含股票資料類型的 xlsx 檔案，
跟股票資料類型相關的儲存格內容會顯示錯誤，
而網頁版的 Excel App 雖然可正確顯示、但無法更新，
微軟未來會不會把此功能下放給 Office 365 之外的其它版本與平台還不確定。
(官網沒把話說死...)

以下，便以我自己製作的範本檔搭配幾個常用的股票操作、
來介紹如何以 Excel 365 的這個新功能建立股票投資試算表。
考慮到目前 Excel 365 並未完整支援所有上市櫃股票，
這個範本檔 (v2) 中我另外至證交所與櫃買中心取得所有個股最後收盤價，
以作為 Excel 365 未支援個股的備援方案。
這與內建的股票資料類型差別在於，
股票資料類型可取得盤中延遲股價 (約延遲 20 分鐘)，
最後收盤價則只有最近一個已收盤交易日的收盤價。

如果您沒有 Office 365，
您還是可以試試看這個範本在您的 Excel 版本中是否可用，
如果使用上有問題或功能在您的 Excel 中不被支援 (例如範本中使用了部份新的 Excel 函式)，
您可以留言告知您的 Excel 版本與遇到的問題，
也許日後我可以協助提供其它版本 Excel 適用的範本。

此外，以下範例所提及的股票與股價皆只是為了介紹如何使用此範本，
並沒有推薦之意、交易內容也是虛構的，
還是要先免責聲明一下... XD

 

D. 準備步驟－範本下載：

D-1.
以瀏覽器開啟這個線上範本連結 (v2) 後，點一下網頁功能表中的 [檔案]。

D-2.
按一下網頁中的 [另存新檔] 後，
再按 [下載複本] 以下載 Excel 範本檔案至本地端電腦上。

 

0. 確認步驟－查詢 Excel 版本：

0-1.
若要確認您的 Excel 版本，請在 Excel 中按一下 [檔案]。

0-2.
點選左側的 [帳戶] 後，
確認您的 Excel 為 Microsoft Office 365，且版本為 2003 或更新版。

 

1. 新增股票資訊：

若要讓庫存股票在 Excel 中可以取得最新報價並試算出現值與報酬率的話，
我們需要先將所有會參考到的個股股價欄位先準備好。
以下以 Excel 365 為例進行操作，
若您使用的 Excel 非 365 訂閱版本且無對應功能的話，
您會需要使用其它方式建立這些個股資訊欄位。

1-1.
切換至 [股價資訊] 工作表，
將表格內第一條股票資訊的 [股票] 欄 (A2) 修改為欲加入的股票代碼並前綴 XTAI: (或 TW 與一個空格)，
(例如 "XTAI:00692" 或 "TW 00692"，加上此前綴可讓 Excel 比較不會找到其它國家的股票)
再點選上方 [資料] 功能區內的 [股票] 按鈕。
(若功能區內無此按鈕，表示您的 Excel 版本不支援此功能，請跳至 1-7 手動輸入代號欄位)

1-2.
若 Excel 彈出安全性注意事項，按 [確定] 以連線至外部資料來源查詢股票資訊。
(若您不想每次都跳這個確認視窗的話，可以回到 Excel 內按一下橘色那條安全性警告內的 [啟用內容] 按鈕)

1-3.
Excel 會根據股票代碼自動取得對應的股票資訊，並自動更新剩餘欄位。

1-4.
往後若要新增新的股票資訊，
請在表格下方的第一個空行對應的 [股票] 欄填入欲新增的股票代碼 (前綴 XTAI: 或 TW 與一個空格)，
然後再按一下 [資料] 功能區內的 [股票] 按鈕。

1-5.
在 Excel 自動取得新增的股票資訊後，會自動更新剩餘欄位。

1-6.
若輸入的股票代碼在 Excel 365 中尚未支援 (例如上櫃股票)，
股票資訊因此取得失敗。

1-7.
若股票資訊取得失敗，可改以手動在 [代號] 欄填入股票代號，
這時範本 (v2 版) 會改搜尋 [最後收盤價] 工作表內的資訊並用以更新其它欄位。
(由於 [最後收盤價] 內並無產業資訊，因此透過此方式建立的項目皆會顯示為 Others)

 

2. 刪除股票資訊：

由於 [股價資訊] 工作表內的個股資訊主要用於庫存股票的管理，
當某檔個股已完全獲利了結賣光光時，
這檔個股對應的資訊就可以從 [股價資訊] 工作表中功成身退，
以改善整個 Excel 的更新效率。

2-1.
若要刪除 [股價資訊] 工作表內的某檔股票，
請先點選表格中該個股對應的任何一個欄位，
在上方 [常用] 功能區內點選 [刪除]下方的 v 箭頭，
再於展開的子功能表中選擇 [刪除工作表列] 或 [刪除表格列]。

 

3. 買進股票：

當我們買進某檔股票、或申購中籤、或參與認股，
就需要至 [持股] 工作表內新增對應的買進交易紀錄。

3-1.
若要新增庫存持股，
切換至 [持股] 工作表，點一下表格區第一個空行的 [股票] 欄位，
此時儲存格右方會出現一個倒三角形，
按一下便可從下拉式選單裡選擇欲新增的股票。
(須先至 [股價資訊] 工作表中新增該檔股票的資訊)

3-2.
選擇股票後，
接著請手動輸入 [買進日期]、[買進股數]、[買進價]、
與 [買進成本] (含手續費) 等欄位。
(其它欄位會自動抓取與更新，請勿手動編輯更動)
(自動計算的 [預估現值]、[預估損益]、與 [預估報酬率] 已扣除賣出牌告交易手續費與證交稅)

 

4. 賣出股票：

當我們將持股賣出時，
便需要將該筆持股的買進與賣出紀錄輸入至 [已實現] 工作表，
同時自 [持股] 工作表刪除該筆持股的買進紀錄。
若有多次買進一次賣出的情形，
請手動合併計算買進交易成本、或分拆賣出交易與對應的買進紀錄配對。
(若您希望在後續更精確計算出年化報酬率，建議分拆賣出交易紀錄)

4-1.
若要新增賣出股票紀錄，
請先切換至 [持股] 工作表，選擇要賣出庫存持股的 [股票] 至 [買進成本] 欄位後，
按一下上方 [常用] 功能區內的複製按鈕 (或 Ctrl+c)。
(若 [持股] 工作表無此筆買進紀錄，請略過此步驟)

4-2.
切換至 [已實現] 工作表，按一下表格區第一個空白行的 [股票] 欄位，
再按上方 [常用] 功能區內 [貼上] 下方的 v 箭頭，
點一下展開後 [貼上值] 裡最左邊的按鈕 (值)。
(請勿以 Ctrl+v 貼上，以避免連同儲存格的內容檢查一併被帶入，造成未來輸入上的問題)
(若 [持股] 工作表內無此筆買進紀錄，請略過貼上步驟，改手動輸入 [已實現] 工作表內的買進相關欄位)

4-3.
貼上買進資訊後，請再手動編輯 [賣出日期]、[賣出價]、
與 [賣出總額] (含手續費與證交稅) 等欄位。
(其它欄位會自動更新，請勿手動編輯修改)

4-4.
回到 [持股] 工作表，
透過上方 [常用] 工作區裡、[刪除] 按鈕展開後的 [刪除工作表列] 或 [刪除表格列]，
將該筆賣出股票對應的庫存持股紀錄刪除。
(若 [持股] 工作表無此筆買進紀錄，請略過此步驟)

 

5. 配股：

5-1.
若要新增配股資訊，
請切換至 [持股] 工作表，點一下表格區內第一行空白的 [股票] 欄，
按一下儲存格右方的倒三角形，並於彈出的選單中選擇欲新增的股票。
(須先至 [股價資訊] 工作表中新增該檔股票的資訊)

5-2.
選擇股票後，
請手動輸入 [買進日期] (配股日)、[買進股數] (配發股數)、
[買進價] (0)、與[買進成本] (0) 等欄位。
(其它欄位會自動抓取與更新，請勿手動編輯更動)

 

6. 配息：

雖然有些投資人會把配息視為持股成本的減項 (因此放久了可以達到零成本的效果)，
但這裡我是把配息當作已實現損益的一部份。

6-1.
若要新增配息資訊，請切換至 [已實現] 工作表，
於表格區內的第一行空白手動輸入 [股票]、
[買進日期] (配息日)、[買進股數] (0)、[買進價] (0)、[買進成本] (0)、
[賣出日期] (配息日)、[賣出價] (0)、[賣出總額] (實領股息)、
與 [備註] ("配息") 等欄位。
(其它欄位會自動更新，請勿手動編輯修改)

 

7. 申購未中籤：

我將申購未中籤的手續費視為已實現的損失，
因此會加至 [已實現] 工作表中。

7-1.
若要新增申購未中籤紀錄，請切換至 [已實現] 工作表，
於表格區內的第一行空白手動輸入 [股票]、
[買進日期] (申購日)、[買進股數] (0)、[買進價] (0)、[買進成本] (20)、
[賣出日期] (申購日)、[賣出價] (0)、[賣出總額] (0)、與 [備註] ("申購") 等欄位。
(其它欄位會自動更新，請勿手動編輯修改)

 

8. 交易報表分析：

此範本中內含 [最後收盤價]、以及幾份基於 [持股] 與 [已實現] 工作區內容的簡易分析報表，
但需要手動觸發這些報表的內容更新。

8-1.
按一下上方 [資料] 功能區裡的 [全部重新整理] 按鈕，
可一併更新 Excel 中的 [最後收盤價]、[股價資訊]、[持股分析]、[已實現分析]、與 [交易總表] 等工作表。
(若您有自行新增外部資料連結，也會同時更新)

8-2.
若 Excel 彈出安全性注意事項，按 [確定] 以連線至外部資料來源更新股票資訊。
(若您不想每次都跳這個確認視窗的話，可以回到 Excel 內按一下橘色那條安全性警告內的 [啟用內容] 按鈕)

8-3.
若 Excel 彈出存取 Web 內容對話框，
請確認是使用匿名後按一下 [連接] 按鈕，
以允許 Excel 連線至證交所 (twse) 下載上市股票最後收盤價。

8-4.
若 Excel 再次彈出存取 Web 內容對話框，
請一樣確認是使用匿名後按一下 [連接] 按鈕，
以允許 Excel 連線至櫃買中心 (tpex) 下載上櫃股票最後收盤價。

8-5.
若 Excel 彈出隱私權等級對話框，
請將 twse 與 tpex 兩個網頁的等級設定為 [公用] 後按 [儲存]。

8-6.
更新完成後，
[最後收盤價] 工作表內會自動合併取得來自證交所與櫃買中心的上市櫃股票最後收盤價，
以作為 Excel 365 股票資料類型不支援個股的備援。

8-7.
根據 [持股] 工作表更新後的 [持股分析] 工作表，
統整了目前庫存持股依個股與產業總結出的成本、現值、未實現損益、與投資組合佔比。

8-8.
根據 [已實現] 工作表更新後的 [已實現分析] 工作表，
統整了個股已實現的報酬損益等資訊。

8-9.
綜合 [持股] 與 [已實現] 工作表內容更新後的 [交易總表] 工作表，
結合所有交易紀錄 (A) 並依此計算出年化報酬率 (B)。

以上是這個 Excel 範本檔案的使用方式，
若您需要更進一步客制化交易分析的部份，
[持股分析] 與 [已實現分析] 工作表皆是使用 [樞紐分析表] 製作，
您可以選取表格中任一儲存格，
便可使用上方 [樞紐分析表分析] 功能區裡的功能進行調整。
[交易總表] 工作表則可透過上方 [資料] 功能區裡的 [查詢與連線] 進行調整。

最後還是要再次重申，
上述範例中使用的股票交易假資訊都只是為了介紹這個範本的使用，
並無推薦之意喔~