[VBA]「 本益比多少合理 ? 」史上最佛心的VBA教學 / 算出 合理本益比
目錄 :
請問「 本益比多少才合理 ? 」
※ 很常聽到人問「 本益比多少才合理 ? 」，但這個問題並沒有固定答案，因為不同行業具有不同的平均本益比水平，且會受不穩定的市場環境，而給予不同本益比的估值。
※ 既然沒有固定答案，為何大家還一直在討論「 合理本益比 」?
◎ 首先要先了解 — 何謂「 合理本益比 」?
→ 合理本益比 ( Reasonable Price-to-Earnings Ratio ) : 是指一家公司的股票在特定時刻或特定市場環境下，被認為是合理價值的價益比率。
◎ 至於本益比的「 合理性 」認定，有幾種決策方式 :
A : 與同業公司比較：比較一家公司的P/E比與其同業公司的P/E比，可以幫助投資者找到被低估且較為合理的股票。
B : 根據股票估值模型推算 : 藉由 股票現金流模型(DCF) 或 相對估值模型(比較市值法)，以估算股票的合理價格。
C : 根據個股歷史本益比推算 : 投資者根據個股本益比的歷史均值，計算出合理本益比。
D : 根據大盤本益比推算 : 投資者根據大盤本益比平均計算出合理本益比。
[ 公式 / 大盤本益比 ] :
☆ 大盤本益比 = 所有公司市值 / 所有公司淨利潤。( PS. 證交所提供的公式 / 數據 : 大盤及各產業類股月報資料。)
[ 大盤及各產業類股的月報資料 ] :
[ 根據大盤歷史本益比高低推算結果 ] :
◎ [ 昂貴 ] : P/E > 20 / 代表 : 樂觀。
◎ [ 合理 ] : P/E 15 上下 / 代表 : 均值。
◎ [ 便宜 ] : P/E < 10 / 代表 : 悲觀。
個人對「 合理本益比 」的看法
[ 個人看法 ] :
※ 對於以本益比來估算個股的高低合理價位，有著一定的參考性。根據觀察上市櫃1700多檔股票，能夠符合由大盤本益比推算出來的估值結果，僅有部分穩定成長的公司，對於某些高成長/高衰退的公司，本益比的表現都會有嚴重的偏差。
◎ 既然，個股本益比的表現，不一定合乎大盤本益比的推算結果，那就改用其他的方法，來驗證其合理性。
常用的「 合理本益比 」股價推算法
※ 最常用的本益比股價推算方式 : 歷史本益比均值法，是藉由本益比的歷史均值，推算出合理的個股價位。
[ 公式 / 本益比 ] :
( PS. 至於每股盈利(EPS)的目標值，是取用上年度的數據，還是歷年來均值，請自行決定。)
◎ 另外，還有以歷年本益比 最小值 / 均值 / 最大值 的平均數，推算出 高 / 低 / 均值 本益比記錄的算法。
( PS. 估算法很多種，到底哪種方法適合/準確，都必須經過演算/分析，才能論定。)
VBA實作 / 利用本益比均值法 — 推算股票的合理價位
※ 利用本益比均值法，推算合理股價的步驟 :
[ 1 ] : 建立上市櫃公司16年歷史本益比資料
[ 2 ] : 設定條件 — 計算歷史本益比均值
[ 3 ] : 爬取並整理年度EPS資料
[ 4 ] : 設定DDE公式 — 爬取上市櫃股票即時價位
[ 5 ] : 計算上市櫃股票之合理價位
[ 推算合理股價之目標畫面 ] :
[ 前置作業 ] :
[ 1 ] : 新增活頁簿 — 設定檔名 : 爬取歷史本益比資料.xlsm。( PS. 已實作過 [VBA] 自動爬取上市櫃股票近16年歷史本益比，將其檔案開啟，即可。)
[ 2 ] : 新增工作表 — 設定名稱 : 分析合理股價。
[ 3 ] : 製作表頭 : 依照目標畫面(上圖)，于 分析合理股價 工作表中，設定表頭欄位名稱。
建立上市櫃公司16年歷史本益比資料
[ 1 ] : 首先請依照文章 : [VBA] 自動爬取上市櫃股票近16年歷史本益比 的操作方法及程式碼，將上市櫃股票的16年歷史本益比資料擷取出來，製作成下面完成圖的表格樣式。
[ 完成圖 / 上市櫃公司16年歷史本益比資料 ] :
[ 完整程式碼 ] :
※ 請參考 — 爬取上市櫃公司16年歷史本益比資料。
設定條件 — 計算歷史本益比均值
[ 前置作業 ] :
◎ 填入上市櫃股票代號及名稱 : 首先將工作表( 年度本益比 )中 A / B行的股票代號/名稱，複製到工作表( 分析合理股價 )。
[ 2 ] : 符合設定條件，才准予計算本益比均值 :
[ 條件一 ] : 近3年個股本益比等於 0 或大於 40，皆不列入計算。
[ 條件二 ] : 16年內，若不到 3/4 (0.75) 個年度符合計算條件(個股本益比等於 0 或大於 40)的話，也不列入計算。
( PS. 其中，本益比大於多少 與 符合多少個年度條件，才列入計算，這沒有確切答案，請自行分析後，再做定論 ! )
[ 程式碼 / 整理並計算歷史本益比均值 ]
Sub 整理並計算歷史本益比均值()
Dim lastRow%, lastCol%, xR%, xCheck%, xSum%, xStep%, tailCol%, xC%
Sheets("年度本益比").Select ' 切換至"年度本益比"工作表
lastRow = [A65536].End(xlUp).Row ' 列號最尾數值
lastCol = [A1].End(xlToRight).Column ' 行號最尾數值
For xR = 2 To lastRow ' 迴圈 -- 執行所有上市櫃股票列號。
' ------ ＜ 條件一 ＞ -----------------------------------------------
xCheck = 0: xSum = 0: xStep = 0 ' xCheck : 條件開關 / xSum : 符合條件之本益比總和 / xStep : 累計符合多少個年度次數。
For tailCol = lastCol - 2 To lastCol ' 先檢查近三年本益比是否合乎計算條件
If Cells(xR, tailCol) = 0 Or Cells(xR, tailCol) > 40 Then ' 設定條件 -- 個股本益比必須:等於0或大於40，才列入總年度計算。
xCheck = 1 ' 條件開關 : 打開 -- 1 : 允許計算 / 0 : 不允許。
End If
Next
' ------ ＜ 條件二 : 必須符合條件一，才准予進行本益比加總計算 ＞ -----------------------------------------------
If xCheck = 0 Then ' 判別是否准予進行總年度計算。
For xC = 3 To lastCol ' 計算各行號的本益比資料 -- 由C行( 3 )開始。
If Not (Cells(xR, xC) = 0 Or Cells(xR, xC) > 40) Then ' 條件 -- 本益比不( 等於 0 或大於 40 )，才列入計算。
xSum = xSum + Cells(xR, xC) ' 本益比加總
xStep = xStep + 1 ' 累計 -- 符合條件的年度次數
End If
Next
If xStep >= (lastCol - 2) * 0.75 Then ' 判別累計次數是否大於等於(18-2)*0.75 = 12次。
Sheets("分析合理股價").Range("D" & xR) = xSum / (lastCol - 2) ' 合乎條件 -- 計算本益比均值，填入D欄(平均本益比)。
Else
Sheets("分析合理股價").Range("D" & xR) = 0 ' 不合乎條件 ' 不合乎條件，故在D欄(平均本益比)，填入 : 0 值。
End If
Else
Sheets("分析合理股價").Range("D" & xR) = 0 ' xCheck = 0 : 表示近三年本益比條件，不合乎條件，故在D欄(平均本益比)，填入 : 0 值。
End If
' ------ ＜ 結束 ＞ -----------------------------------------------
Next
End Sub
[ 顯示畫面 / 列出本益比均值 ] :
爬取並整理年度EPS資料
[ 3 ] : 爬取並整理年度EPS資料 :
※ 利用 Excel 內建的 QueryTable 網路資料擷取工具，爬取 Histock 網站的個股EPS數據。
→ Histock 除權息網址 : https://histock.tw/stock/1101/除權除息。
[ Histock 除權息網址畫面 ] :
錄製巨集 — 爬取個股年度EPS
[ 操作步驟 / 錄製巨集 ] :
◎ 錄製巨集 : 請參考 錄製巨集(舊版) 內的錄製步驟，錄製完，會在新增的工作表( 名稱設定 : 爬蟲資料 )，產生個股年度EPS的資料。( PS. 開頭定位在 A1。)
◎ 在製作過程中，須注意的三點 :
1. 新增活頁簿，設定檔名 : 本益比均值法_推算合理股價.xlsm。
2. 新增工作表，設定名稱 : 爬蟲資料。
3. 從WEB(舊版) 抓取資料時，URL 網址輸入框內，需輸入欲擷取的台泥(1101)個股年度EPS網址 : https://histock.tw/stock/1101/除權除息。
◎ 從 Histock 投資網站擷取出來的台泥(1101)個股年度EPS表格資料，會存放於 爬蟲資料 工作表內，顯示如下 :
[ 顯示畫面 / 個股年度除權息資料表 — 內含 各年度EPS ] :
[ 程式碼 / 爬取個股年度EPS ]
Sub 爬取Histock個股EPS()
Dim lastRow%, xR%, stockCode%, companyName$
lastRow = Sheets("分析合理股價").[A65536].End(xlUp).Row ' 列號最尾數值
Sheets("爬蟲資料").Select ' 切換至"爬蟲資料"工作表
For xR = 2 To lastRow ' 迴圈 -- 執行所有上市櫃股票列號。
Cells.ClearContents ' 清除"爬蟲資料"工作表中所有內容，才不會發生舊資料覆蓋不完全，而造成新資料的錯誤。
stockCode = Sheets("分析合理股價").Range("A" & xR) ' 設定變數 stockCode = 證券代號 (1101)
companyName = Sheets("分析合理股價").Range("B" & xR) ' 設定變數 companyName = 證券名稱 (台泥)
With ActiveSheet.QueryTables.Add(Connection:= _ ' 運用Excel 內建的QueryTable爬蟲工具，連結histock網站擷取除權息資料。
"URL;https://histock.tw/stock/" & stockCode & "/除權除息", Destination:=Range("$A$1")) ' 資料存放於A1欄(絕對位址)。
' .CommandType = 0
' .Name = "除權除息"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlAllTables
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
.Delete
End With
' ------ ＜ 年度判別 ＞ -----------------------------------------------
If [B4] = 2023 Then ' 判別工作表(爬蟲資料)B4欄，是否等於2023(年)。
Sheets("分析合理股價").Range("E" & xR) = [B4] ' 符合條件 -- 目標E行xR列(發放年度)，填入[B4](2023)年度資料。
Sheets("分析合理股價").Range("F" & xR) = [F4] ' 目標F行xR列，填入[F4](股票股利)資料。
Sheets("分析合理股價").Range("G" & xR) = [G4] ' 目標G行xR列，填入[G4](現金股利)資料。
Sheets("分析合理股價").Range("H" & xR) = [H4] ' 目標H行xR列，填入[H4](EPS)資料。
Sheets("分析合理股價").Range("I" & xR) = [I4] ' 目標I行xR列，填入[I4](配息率)資料。
Else
Sheets("分析合理股價").Range("E" & xR) = 2023 ' 不符合條件 -- 目標E行xR列(發放年度)，填入 : 2023 (年度資料)。
Sheets("分析合理股價").Range("F" & xR) = 0 ' 不符合條件 -- F / G / H / I行xR列，均填入 : 0 值。
Sheets("分析合理股價").Range("G" & xR) = 0
Sheets("分析合理股價").Range("H" & xR) = 0
Sheets("分析合理股價").Range("I" & xR) = 0
End If
Application.Wait Now + TimeValue("00:00:05") ' 暫停05秒 -- 等待網頁完成。
Next
End Sub
[ 注意 ] : 使用舊版爬取出來的資料，會在執行 .CommandType = 0 時，出現錯誤訊號。此時，只要將其註解( ‘ ) 掉，就沒事了。
( PS. 另外，在程式後段加上 .Delete (切斷連線)，這樣系統就不會造成連線膨脹，導致當機。)
[ 設定年度判別的原因 ] :
◎ 主要是上市櫃股票中，有部分股票並沒有除權息資料，所以工作表(爬蟲資料)B4欄位，就不會有 2023 年度的記錄。( PS. 以大飲(1213)公司為例。)
[ 參考圖 / B4欄位不是 2023 的情況 ] :
[ 顯示畫面 / 整理個股年度除權息資料 ] :
設定DDE公式 — 爬取上市櫃股票即時價位
[ 4 ] : 設定DDE公式 — 爬取上市櫃股票即時價位 :
※ 記錄上市櫃股票價位的方法 :
A. 即時數據 : 運用DDE語法，連結XQ，擷取上市櫃股票即時價位。( 優點 : 可以盤中進行數據分析 / 缺點 : 必須打開XQ，佔用系統資源 )
B. 盤後數據 : 運用QueryTable爬蟲工具，擷取上市櫃股票盤後數據。( 優點 : 不太佔系統資源，數據可多方面運用 / 缺點 : 盤後下載數據時間較長 )
◎ 本文就以DDE語法，來做介紹 — 爬取上市櫃股票即時價位。
[ 程式碼 / 設定DDE公式 -- 爬取上市櫃股票即時價位 ]
Sub 設定DDE公式()
Sheets("分析合理股價").Select ' 切換至"分析合理股價"工作表
lastRow = [A65536].End(xlUp).Row ' 列號最尾數值
For xR = 2 To lastRow ' 迴圈 -- 執行所有上市櫃股票列號。
Range("C" & xR) = "=@XQLITE|Quote!'" & Range("A" & xR) & ".TW-Price'" ' 在C行xR列 -- 填入DDE語法公式，爬取上市櫃股票收盤價位。
Next
End Sub
[ DDE語法與參數的用法 ] :
※ 請參考 EXCEL VBA 功能筆記 — DDE / RTD 語法與參數。
[ 顯示畫面 / 爬取上市櫃股票即時價位 ] :
計算上市櫃股票之合理價位
※ 完成所有數據的收集，再藉由本益比公式，便可計算出上市櫃股票之合理價位。( ☆ 運用本益比公式，導出 : 股價 = 本益比 ＊ 每股盈餘(EPS)。)
[ 本益比公式 — 導出合理價 ] :
◎ 合理價(K2) = 本益比均值(D2) ＊ EPS(H2)
◎ 便宜價(J2) = 合理價(K2) ＊ 0.382 (黃金切割率)
◎ 昂貴價(L2) = 合理價(K2) ＊ 1.618 (黃金切割率)
( PS. 至於以黃金切割率來定義 : 便宜價 與 昂貴價，是否準確 ? 請自行研判，因為這沒有確實的解答。)
[ EXCEL 儲存格 / 合理價公式 ] :
[ 程式碼 / 計算上市櫃股票之合理價位 ]
Sub 計算股票之合理價位()
Sheets("分析合理股價").Select ' 切換至"分析合理股價"工作表
lastRow = [A65536].End(xlUp).Row ' 列號最尾數值
For xR = 2 To lastRow ' 迴圈 -- 執行所有上市櫃股票列號。
If Range("H" & xR) > 0 Then ' 判別EPS(H行)值，是否大於 0。
Range("K" & xR) = Range("D" & xR) * Range("H" & xR) ' EPS值 > 0 : 合理價(K行xR列) = 平均本益比(D行xR列) * EPS(H行xR列)
Else
Range("K" & xR) = 0 ' EPS值 > 0 : 合理價(K行xR列) = 0
End If
Range("J" & xR) = Range("K" & xR) * 0.382 ' 便宜價(J行xR列) = 合理價(K行xR列) * 0.382
Range("L" & xR) = Range("K" & xR) * 1.618 ' 昂貴價(L行xR列) = 合理價(K行xR列) * 1.618
Next
End Sub
[ 顯示畫面 / 計算出的合理價列表 ] :
注意事項
※ 總結 : 本文中所有該注意的事項 —
[ 1 ] : 務必先完成 — 建立上市櫃公司16年歷史本益比的資料，否則無法進行本文的程式介紹。
[ 2 ] : 本文中的四組程式碼，為求方便解說，各自以獨立模組來設計，若想自動執行，請多開一模組，運用CALL指令呼叫四組模組，即可。
[ 程式碼 / 自動執行 ] :
Sub 自動執行()
Call 爬取_Histock_個股EPS
Call 設定DDE公式
Call 整理歷史本益比均值
Call 計算股票之合理價位
End Sub
( PS. 也可以四組結合成一組，精簡程式碼，該如何設計? 就看各自的程式功力了。)
[ 3 ] : 合理價的制定 — 本文是以黃金切割率(0.382及1.618)來定義便宜價及昂貴價，至於合理價的界定，可以修改成 : 範圍區間，設定解說如下 :
[ 進出場的價格設定 ] :
□ 昂貴價 = 合理價 * 1.618
□ 合理價(上限) = 合理價 * 1.382 ( 股價超過上限 — 開始賣出 )
□ 合理價(下限) = 合理價 * 0.618 ( 股價跌破下限 — 開始買進 )
□ 便宜價 = 合理價 * 0.382
[ 顯示畫面 / 新界定的合理價區間 ] :
結論
※ 合理本益比 是指一家公司的股票在特定時刻或特定市場環境下，被認為是合理價值的價益比率。文中，藉由VBA程序計算歷史本益比均值 / 運用QueryTable工具爬取個股EPS / 借助DDE語法抓取個股即時數據，求得個股合理價位，最後再以黃金切割率來定義便宜價與昂貴價，完成上市櫃所有股票的數據設定。
[ 提醒 ] :
※ 合理本益比 是一個相對主觀的概念，並且隨時間和情況而變化。投資者應該綜合考慮多個因素，包括 : 行業標準 / 公司基本面 / 市場情況 和 個人投資目標，以評估一家公司的本益比是否合理。
[ 建議 ] :
※ 投資者不應該只依賴於本益比分析，還需搭配其他技術指標，才能更全面評估，以確保做出明智的投資決策。
[ 程式伏筆 ] :
※ 請自行製作程式碼 –「 自動找出便宜價與昂貴價的進出場股票 」。
( 提示 : 已知上市櫃股票之便宜與昂貴價位，加上即時收盤價數據，便可利用FOR迴圈 + IF判別式，自動算出當前可進出場的股票。)
完成圖 / 運用均價法 — 算出 便宜 / 合理 / 昂貴價
[ 完成圖 / 運用均價法 — 算出 便宜 / 合理 / 昂貴價 ] :
相 關 文 章
[ 1 ] : 自動上網爬取「 本益比 」 — 找出被低估的股票
※「 本益比 」是股票投資中經常使用的一種重要指標，可以幫助投資者初步評估公司的投資價值。本文透過VBA的腳本設計，每日自動爬取上市櫃股票最新的本益比數據，並利用排序的方式，將其本益比資料由小到大排序，有利於查找目前最有可能被低估的股票資料。
[ 2 ] : 自動爬取上市櫃股票近16年「 歷史本益比 」
※ 「 歷史本益比 」是透過分析一家公司過去一段時間的本益比趨勢，讓投資人從中了解一家公司的投資價值和市場評價。文中，藉由VBA的腳本設計，一鍵執行 / 自動將上市櫃所有股票近16年的本益比資料，全部抓進EXCEL，有利於投資人洞察所有個股的本益比趨勢。
[ 3 ] :「 本益比多少合理 ? 」史上最佛心的VBA教學 / 算出 合理本益比
※「 合理本益比 」是指一家公司的股票在特定時刻或特定市場環境下，被認為是合理價值的價益比率。文中，藉由VBA程序計算歷史本益比均值 / 運用QueryTable工具爬取個股EPS / 借助DDE語法抓取個股即時數據，求得個股合理價位，最後再以黃金切割率來定義便宜價與昂貴價，完成上市櫃所有股票的數據設定。
V B A 資 源
