[心得] VBA執行百貨貿易業收現天數財務分析
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
--
中共來的朋友誒
IP 正確
VBA...
幫我算一下明天該跑還是抱,謝謝
維尼熊吃屎......
原po是台灣人吧謝謝你在會計版的實用小工具~
哇!厲害
推你,常看到你在會計版的發文,不過怎來到會有審查
政治的版發文
謝謝,我是在中國工作的台灣人,沒有特別的政治想法
合併報表會將所有關係人交易消除掉,這是不正確的
推
關係人不止有母子公司關係,還有其他9種情形
謝謝細心指教,已更正。
推
請 Google 財報紅綠燈。
PUSH
thank you very much
b88的同屆...
43
[問題] 很會excel可以做什麼工作?如題 國立大學財務背景的學歷 相關工作經驗約一年 但excel還蠻好的 樞紐分析、vlookup不在話下17
[閒聊] Python比VBA更適合做金融報表吧?陳年報表一點開,常常令人懷疑人生 裡面的VBA比狗屎還亂 令人懷疑最初的撰寫者是否根本沒受過教育 低劣混亂的程式,固然與設計者本身的大腦有關 但蠻大程度也跟VBA的語言特性脫不了關係6
[問卦] 沒人發現Excel VBA在職場上很好用嗎!任何Excel的重複動作 只要錄製下來 用VBA改一改 就可以自動跑 別人手動整理要整理好幾個小時7
[請益] VBA新手教學請問各位高手大大 小弟因為公司的資訊處太爛 太忙 有時候資料太多太大需要判讀 想自學一點程式 自己目前因為都在處理excel的問題X
[情報]EXCEL VBA線上課程~~有興趣可以參考介紹下面2堂關於Excel Vba的課程, 有興趣的朋友可以參考看看,謝謝 ----------------------------------- 課程屬性:線上課程 課程名稱:- Excel公式跟樞紐分析弄熟我覺得蠻基本的 最好要會多個公式一起使用,尤其是有條件判斷式的公式 Excel書多看幾本大概就OK了 邏輯訓練得還不錯之後就可以玩VBA 一個人再怎麼厲害終究還是一個人
- 以下資訊為今日至2020/06/24有效 嗨,all 除了我的課程,其他網站內也有很多不錯課程 可以參考,並將資料帶上 -----課程優惠適用-----
- 介紹下面2堂關於Excel Vba的課程, 有興趣的朋友可以參考看看,謝謝 ----------------------------------- 課程屬性:線上課程 課程名稱:
69
[請益] 韭菜健檢爆
[情報] 2317鴻海4月營收24
[標的] 4973廣穎 財報噴噴 多24
[標的] 2377.TW 微星 冒險多14
Re: [新聞] 中國上市公司全年利潤下滑3% 五年來首次14
[情報] 6213 聯茂 113年4月營收27
Re: [新聞] 股神狠砍蘋果!波克夏對蘋果持股大減13%40
[請益] 續扣0050還是換台股基金10
Re: [心得] AI時代來臨,技術分析真的已經沒用了13
[情報] 6446 藥華藥 113年4月營收6
Re: [請益] 技術分析是不是沒用了啊?4
[標的] 6285 啟碁 腐爛多10
Re: [請益] 殖利率倒掛,是不是假議題?12
[情報] 3587 閎康 113年 Q1 EPS: 2.1115
[請益] 債券ETF債息收入報稅的問題3
Re: [請益] 殖利率倒掛,是不是假議題?3
[情報] 4137麗豐-KY 2024Q1 EPS 0.9110
[情報] 3008 大立光 113年4月營收