贊贊小屋

Excel VBA網路爬蟲,5家公司5個月份營業收入統計

上市櫃公司每個月公告營收,這是投資人掌握公司營運最新狀況的關鍵。本文介紹如何設計Excel VBA程式,利用定義變數和迴圈事件網路爬蟲取得營收彙總表。

在本書序有提到EPS每股盈餘是投資人最熟悉的股票指標,作為眾多財務比率之一,它受到歡迎的原因,來自於它是直接衡量公司賺錢的能力。前面第五章和第六章的財務結構和償債能力,主要是幫助篩選掉有財務危機的公司,第七章經營能力分析,主要幫助衡量公司是否穩健控管各項可能的風險,這三章其實都是安全性指標,避免賠錢的功能多一些。不過,投資人最希望的應該是買到一隻賺錢股票,而股票是否賺錢最終取決於公司是否賺錢,這一章是關於公司獲利能力分析,亦即借助財務比率瞭解公司的賺錢能力。

前面章節大抵依照股東會年報上的財務比率作介紹,但是到了獲利能力,這是比較靈活、強調即時性的東西,況且財務分析本來就是依照個需求偏好設計的,所以這一章會從每月營收快速比較、股神巴菲特最重視的股東權益報酬率(ROE)、最後到實務經典的杜邦方程式,希望能作為讀者更為精準的判斷工具。

如同本書第四章第一節所述,營收是一家公司經營損益表的火車頭,營收長期穩健成長、短期強勁增漲,都是優秀良好的綠燈指標,投資可以踩油門加碼,營收長期衰退萎縮、短期驟降下跌,都是值得警戒的黃燈指標,持股應該踩剎車減資。

一、每月營業收入彙總表

公開發行公司每月都必須公告當月營收數字,如圖所示,於公開資訊觀測站的「彙總報表」、「資訊揭露」、「每月營收」、「採用IFRS後營業收入彙總表」、「每月營業收入彙總表」,便可以綜覽各家公司的月營收統計。

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第1張

二、國內上市和國內上櫃

進入「每月營業收入彙總表」頁面,於「市場別」欄位有很多選項,通常投資人較為熟悉的是「國內上市」和「國內上櫃」,合起來便是上市櫃公司。

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第2張

三、營業收入統計表網頁網址結構

「公司107年8月份(累計與當月)營業收入統計表」,從網址結構很清楚看得出來,「sii」是上市公司,「otc」是上櫃公司,也可以看到兩個都有「「107 8」,表示是年度和月份,讀者有興趣自行在網址列將「107 8」改成「107 7」,網頁馬上更新為7月份的收入統計表,如同先前章節取得財務報表一樣,如果要利用Excel VBA取得多月份的營收資料,熟悉網址結構是先前條件。

另外注意到同樣是上市公司水泥產業,八月份營收公告的公司顯然少於七月份,這是因為《證券交易法》規定是「於每月十日以前,公告並申報上月份營運情形」,筆者此時剛好尚在九月十日之前,有些公司還未公告營收到證交所,因此網頁資料有缺。

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第3張

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第4張

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第5張

四、IF與VLOOKUP函數應用

同樣希望利用VBA先取得營收資料,再於Excel整理彙總分析。表格基本是沿用本書一貫的格式,只不過配合營收報表特性稍加修改,標黃色部份設計好公式,目的是在輸入「開始年度」、「開始月份」、「股票代碼」之後,即會自動更新「結束年度」、「結束月份」、「公司名稱」。E3儲存格公式為「=IF(B4>8,B3+1,B3)」,意思是如果開始月份大於8,連續五個月份便會跨年度,所以結束年度就要加1,否則不用。E4儲存格公式為「=IF(B4>8,B4-12+4,B4+4)」,同樣是處理是否跨月份的問題。B7儲存格公式為「=VLOOKUP(A7,股票代碼!A:B,2,0)」,和先前章節一樣是輸入股票代碼便會帶出公司名稱。

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第6張

五、年月計算工作底稿

上個步驟為分析總表,這個步驟為工作底稿,同樣只要上個步驟在B3和B4輸入好「開始年度(107)」和「開始月份(3)」,在這個步驟工作底稿中標黃色部份,也會馬上更新,有些僅僅是補充資訊,有些像「年+月(西元)」是在此計算後再回饋連結回分析總表,另外還有些像「年+月(加底線)」是方便VBA取得營收網頁資料用的。

如圖所示,資料編輯列中的函數公式看起來不是很容易理解,它主要是運用邏輯函數和文字函數,模擬手工操作的流程思惟,讓Excel自動轉換計算資料,在此不多作說明,有興趣讀者可以參考書本所附Excel範例檔案。在這裡分析總表和工作底稿是互相交流的,和先前章節由工作底稿單向傳送資料到分析總表較為不同。

這裡其實手工直接輸入月份也不會太花時間,但考量以後可能要常常更新,像這類取得資料和資料處理的重覆作業,能省則省,應該把時間專注於真正的財務分析。

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第7張

六、編寫VBA程式碼:

30-40:定義這個程式會用到的變數。
60-70:這程式到最後會將五個月的營收資料合併到同一張工作表「Combined Data」,為避免程式執行時出錯,利用MsgBox函數設置一道提示視窗,提醒使用者目前是否已存在此工作表,如果是第二次以後執行程式,當然會有上一次執行留下來的單月營收工作表,請先將這些工作表刪除,沒問題就按「是」,否則按「否」取消。
90:由程式將「Combined Data」工作表的內容清空。
110-400:設置N=1到5的程式迴圈,亦即取得五個月份的資料。
190-250:先取得上市公司營業收入統計表。
270-350:再取得上櫃公司營業收入統計表,這裡有兩行「R = ActiveSheet.UsedRange.Rows.Count」和「R = R + 5」,設計是計算目前工作表總共有多少行的資料,也就會是上市公司營業收入統計表的資料範圍,加上五行空格,接著再寫入上櫃公司營業收入統計表,如此一張工作表便是單個月份的上市櫃公司營收統計表。
370-380:將五個月份的營收資料都彙總到另一張工作表(「Combined Data」),這裡的「Cells(1, (N – 1) * 11 + 1)是配合每月營收統計表皆為A到K欄共11欄的資料。之所以極盡巧思要再把所有資料彙總在一起,是方便Excel用vlookup函數依照公司代碼搜尋五個月份的營收金額。

這個程式是利用MsgBox函數提示使用者先設置環境,以便順利執行避免出錯,其實較為成熟的程式設計師應該會把這一部份寫入程式中,例如讓程式直接判斷是否存在先前留下來的單月營收工作表,然後由程式自行處理。不過本書原本只是利用VBA作為工具,並非專門講解VBA或是設計軟體的書籍,所以在此不再深研,一方面保持VBA程式碼較容易理解,另方面也是呼應作者從《人人做得到的網路資料整理術》到本書的核心概念:Excel和VBA是互相搭配,Excel很容易不費時可以操作的,不一定非得寫成VBA程式。

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第8張

七、「MonthlyRevenue」VBA巨集

執行「MonthlyRevenue」巨集,果然會出現提示視窗,沒問題按「是」,成功取得1803-1807五個月份的營收統計表,分別放在清楚明瞭的工作表上,最後再利用簡單經典的Vlookup函數,將各個月份指定公司的營收資料帶到分析總表。

在此是以電子零組件業中的五家公司作為範例,特別挑選營收金額相差不大的,方便編製Excel折線圖,可以清楚看出2327國巨營收在近五個月持續大帳增長,當月較前五個月平均值差異幅度47.2%,由此哪家公司獲利能力較高毋庸置疑。

只要把「開始年度」改成「106」,「開始月份」改成「10」再執行一次「MonthlyRevenue」巨集程式,分析總表過一會兒馬上從1803-1807更新為1710-1802,同樣的五家公司,在這段期間卻是剛好相反,營收大抵是下滑的趨勢。

順帶一提,本書常常用到一個「股票代碼」清單,便是稍加整理這個得到的上市櫃公司營收統計表,只擷取公司代碼和公司名稱兩欄資料,加上產業別,其餘資料刪除,剛好是一張很方便的公司清單。

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第9張

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第10張

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第11張

Excel VBA網路爬蟲,5家公司5個月份營業收入統計 VBA財務分析 第12張

營收為最即時的財務資訊

如同本章開始所述,在股東會年報的獲利能力財務分析,並沒有營收趨勢這一項,因為財務比率本質上是兩個以上財報項目的比率計算,單單一項營業收入並沒有比率可言。不過即使如此,營收變化相當值得注意——雖然收入還要扣掉成本費用,營收爆發不一定跟著淨利井噴,不過在普遍完全競爭的市場壓力下,毛利率和利潤率通常不會變化太大,所以如果營收沒有增加,想要獲利增長那是困難度頗高,因此營收當然可以作為公司獲利能力的重要觀察指標,剛好公司每個月都必須公告營收,如此營收便成了最為即時的原始財務資料了。

本節範例選擇五家公司,但其實只要讀者接觸Excel檔案,應該很簡單可以變成了兩家三家、甚至是七家十家。另外範例是五個月平均值作為評估指標,並且設計了很主觀的打分數標準,但其實只要執行了VBA取得五個月份營收資料,每位讀者都可以依照自己需求加以改編客製化。股票投資本應如此,每個人累積的經驗都不一樣,所以每個人都會有自己的獨門心法,這本書的宗旨並非介紹獨門心法,而是分享如何取得資料、處理資料、分析資料,希望讀者能夠得到工具和概念,而不僅僅是曇花一現的股票明牌。

本文內容取自《讓上班族狠賺的EXCEL財報分析術》,書本仍然是最好的學習方法,今天就買本書吧!

博客來網路書店網址:

https://www.books.com.tw/products/0010801639