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

作者: moodyblue   2020-03-31 23:23:55
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是工具,財務比率分析也是工具,最終還是要回到公司基本面本身,如
同本節最後擷取了公司年報所示,單純從財務比率的數字,無法瞭解到底發生了什麼,而
每家公司其實都很認真地準備股東報年報,買股票成為一家公司的小股東,是可以好好閱
讀公司財報和年報,如此更加瞭解所投資公司,這是價值投資法的根本。股神巴菲特有句
經典名言:「其他男人看花花公司,我讀公司財報。」這本書宗旨之一便是希望拋磚引玉
,鼓勵讀者多多閱讀會計師辛苦查核簽證的財務報告、公司用心準備的股東會年報。
延伸閱讀:
Excel設計VBA數列變數及迴圈事件取得遠東百貨財務報表,分析收現日數
https://www.b88104069.com/archives/4416
經營能力分析:周轉天數計算
https://www.b88104069.com/archives/4409
上班族常用財務報表:現金流量表
https://www.b88104069.com/archives/4394

Links booklink

Contact Us: admin [ a t ] ucptt.com