VBA執行百貨貿易業收現天數財務分析 - 會計

Annie avatar
By Annie
at 2020-03-31T23:24

Table of Contents


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

--


周末,我們繼續Excel:精華區=>21.心得=>5.其他=>3.office

會計人的Excel小教室: https://www.facebook.com/acctexcel


--
Tags: 會計

All Comments

Andrew avatar
By Andrew
at 2020-04-03T04:04
公開資訊觀測站已經改為使用加密的網路通訊協定
內文的網址應該是抓不到資料的
Carol avatar
By Carol
at 2020-04-03T21:56
謝謝補充,我已經有改了一版Excel範例檔了
Vanessa avatar
By Vanessa
at 2020-04-07T18:08
這本書已經談好要全部改版,只是排程說不定要明年了
Edith avatar
By Edith
at 2020-04-12T10:12
感謝分享

北ㄧ會計師高會課本一套

Hazel avatar
By Hazel
at 2020-03-30T13:31
如題,徵求北一2019/2020的高會課本一套, 麻煩站內信報書況及價格, 交易方式希望能夠店到店,感謝! - ...

成管會問題

Edith avatar
By Edith
at 2020-03-29T21:50
https://imgur.com/UuoIFDY 請問成管會一題(如上圖),期末在製品成本58500與39000怎麼算出來的? 再麻煩厲害的高手回答,謝謝! - ...

109台政成會研所考取心得(代po)

Irma avatar
By Irma
at 2020-03-28T15:22
一、 前言 準備考試時,常來逛本版學學讀書方式,今年有幸考上台大、政大、成大、中正會研所,因此來回饋本版,以下心得供大家參考。 二、個人背景 我是輔仁大學會計系的學生,會選擇報考研究所主要因為我覺得如果大學畢業直接進入事務所工作,會沒時間讀書考會計師,加上會研所的考科中、成、審也和會計師考科有重疊,所以我認 ...

徵求懂企業評價的會計達人教學

Valerie avatar
By Valerie
at 2020-03-28T11:50
我有個測驗是一個模擬的公司進行電廠開發,題目要求要做20年的現金流預測以及會計三 表來進行企業價值分析,關於DCF,損益表、資產負債表我大致上已完成,但因為對會計完 全是門外漢,需要有人指點一下看做的是否符合題目要求。 想問問版上有沒有人能夠撥 一個上午或下午的時間教學我一下,報酬可以再討論,麻煩有興趣的大大 ...

成管會分布成本一題

Edith avatar
By Edith
at 2020-03-27T17:11
https://i.imgur.com/q3fjV9W.jpg 材料Aandamp;加工成本算的出來,材料b完全不知道怎麼來的,答案是c請問我的算法哪裡有錯嗎? 謝謝 ----- Sent from JPTT on my iPhone - ...