Excel VBA財報下載,分析比較三家公司平均收現天數

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

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

一、年報及股東會報告

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

E14g11

二、Excel VBA底稿結構

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

E14g21

三、個別與合併財報

首先,直接複製「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代表合併。

E14g31
E14g32
E14g33
E14g34

四、閱讀財報修改程式

修改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頁,在關係人交易的附註中這是因為有「重大影響投資者之關聯企業」等的緣故,此財報中應收關係人餘額也可以和上個步驟所取得資產負債表核對,金額是一致的。

E14g41
E14g42
E14g43

五、調整公式評估比率

理清了問題,對策隨之而來,修改工作底稿中的公式,將儲存格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天以內,和遠東百貨接近,互相印證兩家公司經營穩健,而且財務指標符合百貨公司現金銷售為主的特性。

E14g51
E14g52

六、快速複製其他公司

再一次操作第三步驟的流程,直接複製「第七章 經營能力分析(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天左右,表示公司在銷售方面的經營能力維持穩定。

E14g61

七、財務比率追查財報

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

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

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

E14g71

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

E14g72

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

E14g73


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

E14g74

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

E14g75

Excel VBA只是輔助工具

這一節最後有三點補充:

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

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

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

每天學習,每天充電:VBA財務分析文章

最新文章: