[心得] VBA執行百貨貿易業收現天數財務分析

moodyblue 發表於 2020/3/31 下午11:22:37

看板Stock標題[心得] VBA執行百貨貿易業收現天數財務分析作者

moodyblue

()時間Mar 31 23:22:37 2020推噓 9 推:11 噓:2 →:6

PTT推薦

VBA網路爬蟲高效率抓取投資資訊，新增工作表執行同業財務比率分析

網誌圖文版：

https://www.b88104069.com/archives/4468

VBA網路爬蟲高效率抓取投資資訊，最好配合工作表新增刪除及名稱命名等程式，本文以欣欣大眾、遠百、特力的應收帳款收現天數為例執行同業比率分析。

上一節以遠百作為範例，瞭解其應收帳款周轉比率，但除了單一公司外，也想要知道其他公司的應收帳款指標作為參考。另外上一節的VBA程式碼已經能同時取得三份財務報表，不過即使取得原始資料，仍需要再拉一下工作底稿的公式，才能彙總到分析表，這一節以和遠百類似性質公司為例，分享如何稍微修改VBA程式碼，希望可以讓工作底稿自動去抓更新後的資料。

一、在公開資訊觀測站「年報及股東會相關資料(含存託憑證資料)」的「公司代碼或簡稱」欄位，輸入「29」，網頁會自動跳出智慧選單，出現「上市貿易百貨」類別的公司，本節即以其中的「2901 欣欣大眾」和「2908 特力」為例介紹。

二、首先修改VBA程式碼，重點在圖片標藍色下列所示部份，這一段是純粹用錄製巨集得到的程式。Excel有個很好用的巨集指令，可以所操作的過程錄製下來，除了再次播放執行，Excel還會自動將這過程翻譯成程式原始碼，作為VBA開發人員的參考。我是在不改變原本「網頁資料=>工作底稿=>彙總分析表」的結構公式前提下，直接將新公司的資料下載到另外新的工作表，再將此資料複製值到原本的網頁資料工作表上面，等於完成了資料更新，同時又不改變原本三層工作表的公式連結。

三、首先，直接複製「7.2 平均收現日數」Excel檔案，重新命名檔案為「第七章經營能力分析(7.3 2901-2017)」，除了「Analysis」、「Calculation」、「Balance Sheet」、「Income Statement」、「Cash Flows」、「股票代碼」這五個工作表，其他予以刪除。將「Analysis」工作儲中的「B1」儲存格改為「2901」，可以看到「B2」儲存格會自動變成「欣欣大眾」。

執行巨集「FinancialReportsUpdate」，結束後會發現三張工作表：「Balance
Sheet-2901」、「Income Statement-2901」、「Cash Flows-2901」，但很快也很發現這三張表都是空無一物，連帶使得被複製覆蓋的「Balance Sheet」、「Income Statement」、「Cash Flows」工作表也變成空無一物。

一開始可能會覺得是VBA程式碼寫錯，但經過測試其他公司執行正常，所以回到資訊觀測站驗證網址，竟才發現這家公司較特別，它只有個別財報，並沒有合併財報，再仔細檢查個別財報XBRL的網址結構，重點是在結尾的「ID=A」，先前範例都是合併財報，網址結尾固定為「ID=C」，因此可驗證A代表個別、C代表合併。

四、修改VBA程式碼網址那一行：「WebAddress =
"URL;http//mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & Stock &
"&SYEAR=" & Y & "&SSEASON=4&REPORT_ID=A"」，結尾由C改成A，如圖所示。先把「
Balance Sheet-2901」、「Income Statement-2901」、「Cash Flows-2901」這三張工作表刪除，再執行巨集「FinancialReportsUpdate」，成功取得三份個別財務報表。

這裡之所以要將原來的三張工作表刪除，是在VBA程式碼指示要新增這三張工作表，但如果沒刪除，執行時會發現已經有了，為何還要新增，程式會報錯，所以記得重新執行時務必要先予刪除。另外在此可以將修改過的巨集改名為「
FinancialReportsUpdate_StandAlone」，表示是取得個別財務報表，這不僅僅是「欣欣條款」，也可作為往後偶爾有需要取得個別財報的備用程式。

不過，財務報表雖然更新，工作底稿卻有一項「應收帳款-關係人淨額」出錯，這是因為如同本書第二章第三節「合併報表」所述，合併報表會將母子公司關係人交易消除掉，只剩下集團外交易往來，上一節的遠百剛好比較特別，合併報表中尚有應收關係人款項，翻閱遠百106年合併財報中的第90頁，在關係人交易的附註中這是因為有「重大影響投資者之關聯企業」等的緣故，此財報中應收關係人餘額也可以和上個步驟所取得資產負債表核對，金額是一致的。

五、理清了問題，對策隨之而來，修改工作底稿中的公式，將儲存格D11的公式改成「
=IFERROR(MATCH("*"&$B11,'Balance Sheet'!A:A,0),0)」，表示如果無此科目則計算為零，接著再將D21的公式改成「=IF(D11=0,0,INDEX('Balance Sheet'!B:B,D11))」，表示如果D11為零、這裡金額也帶零。

修改好公式，適當地作整行的公式複製，最後終於得到了應收帳款財務比率。看起來2017周轉次數較少，但欣欣百貨應收帳款接近於零，2017只是剛好期末有些餘額，造成此異常，但應收金額仍然很少，其實沒什麼擔憂。

整體而言，欣欣百貨的收現日數也在10天以內，和遠東百貨接近，互相印證兩家公司經營穩健，而且財務指標符合百貨公司現金銷售為主的特性。

六、再一次操作第三步驟的流程，直接複製「第七章經營能力分析(7.3 2901-2017)」
Excel檔案，重新命名檔案為「第七章經營能力分析(7.3 2908-2017)」，除了「
Analysis」、「Calculation」、「Balance Sheet」、「Income Statement」、「Cash
Flows」、「股票代碼」這五個工作表，其他予以刪除。將「Analysis」工作儲中的「B1」儲存格改為「2908」，可以看到「B2」儲存格會自動變成「特力」。

由於2901較特殊是個別財報，當時修改VBA程式碼網址那一行：「WebAddress =
"URL;http//mops.twse.com.tw/server-java/t164sb01?step=1&CO_ID=" & Stock &
"&SYEAR=" & Y & "&SSEASON=4&REPORT_ID=A"」，這裡已回歸大部份是合併財報的情況，要再把這行網址的結尾由A改成C，順便也將巨集名稱從「
FinancialReportsUpdate_StandAlone」改成單純的「FinancialReportsUpdate」。

執行巨集「FinancialReportsUpdate」，這次非常順利，待VBA取得資料並複製後，分析表即自動更新為特力2908的應收帳款相關財務比率。從特力五個年度的平均收現日數來看，區間都落在25天到30天左右，表示公司在銷售方面的經營能力維持穩定。

七、將這一節三家公司的分析表，Excel簡單放在一起作比較，發現特力的平均收現日數較高。

針對特力，參考其105年股東會年報第53頁，該公司成立四十年，剛開始是專營手工具五金外銷的貿易商起家，接著將貿易商導入國內零售市場，最後是自己設立大型賣場，也就是在台灣大家都知道的B&Q特力屋，近年還進軍大陸市場。所以翻到年報第52頁「合併營業收入比重」，105年度特力傳統貿易業務營收佔比為36.0%、台灣零售事業為49.6%，由此不但更加瞭解這家公司，並且也能理解其平均收現日數會較欣欣大、遠百等單純百貨公司較為長的合理原因。

針對欣欣大眾，參考其105年股東會年報第6頁，該公司非常特別，是由退輔會與民間共同投資創辦，目的之一是提供榮民及其子女就業機會。由此可想見其已是上市公司，但沒有發展成集團企業，仍然維持單一公司的背景原因。

最後作個小小補充，有興趣可以看看同樣是「上市貿易百貨」的統一超（股票代碼：2912），其105年股東會年報第65頁，在「財務分析（合併）」裡「應收帳款週轉率」和「平均收現日數」都是空白，註解為「本公司因屬零售現銷作業，故不適用」。由此可見即使在同一類別中，各家公司仍然有非常大的差異性，在分析財務比率時並沒有絶對的判斷標準，應儘量避免妄下結論。

特力105年股東會年報第53頁：

特力105年股東會年報第52頁：

欣欣大眾105年股東會年報第6頁：

統一超105年股東會年報第65頁：

這一節最後有三點補充：

（1）如果想自己利用Excel和VBA取得資料、整理資料，學習和困難是在所難免。以欣欣大眾為例，必須發現它不是合併財報，需要修改VBA中設定的網址，也必須瞭解它沒有應收關係人款，需要修改Excel中設的公式。不過，再以特力為例，由於這類型網路資料一定會有些共同的特性，所以在欣欣大眾已經作了修正，接下來在取得特力資料時便一次過關。從這裡來看，解決問題的效益並不等差級數，一個加一個，而是成等比級數，處理掉一個問題，同時可能處理掉好幾個潛在的問題了，因此如果有心，還是值得學習。

（2）從本節最後一個步驟來看，一旦在Excel取得了公司財報，設定好函數公式模型，靈活度很高，輕易能把許多公司放在一起綜合評估，另外也可以同時計算出先前用過的財務比率，例如財務結構、償債能力等。

（3）Excel和VBA是工具，財務比率分析也是工具，最終還是要回到公司基本面本身，如同本節最後擷取了公司年報所示，單純從財務比率的數字，無法瞭解到底發生了什麼，而每家公司其實都很認真地準備股東報年報，買股票成為一家公司的小股東，是可以好好閱讀公司財報和年報，如此更加瞭解所投資公司，這是價值投資法的根本。股神巴菲特有句經典名言：「其他男人看花花公司，我讀公司財報。」這本書宗旨之一便是希望拋磚引玉，鼓勵讀者多多閱讀會計師辛苦查核簽證的財務報告、公司用心準備的股東會年報。