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 --

※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 112.22.232.21 (中國) ※ 文章網址: https://webptt.com/m.aspx?n=bbs/Accounting/M.1585668294.A.312.html ※ 編輯: moodyblue (112.22.232.21 中國), 04/01/2020 00:14:19