Excel開發人員面板開啟,VBA巨集程式取得財務報表

Excel善用VBA不但能自動化快速操作,更能突破既有函數指令的限制。本文以財務報表網路爬蟲為例,介紹開發人員選項、Visual Basic編輯器、檢視巨集等相關操作。

第二篇到目前為止,都是以單一公司進行財務分析,需要取得公司股東會年報和財務報告,實務上大多會是多家公司同時進行分析,如果是一家一家取得財務資料PDF檔案,顯然不容易進行。上一節有介紹過用Excel取得財務報表,雖然成功將財報資料下載到Excel工作表,很方便進一步處理,但每下載一份報表,便需要操作一次,在想要同時分析多家公司或是多個期間的場合,不是那麼有效率,這一節進一步介紹如何運用VBA程式,達到較為自動化地取得財務報表。

一、開發人員面板

接下來會頻繁用到Excel的VBA功能,已經是Excel程式設計師了,所以第一步是將開發人員的面板打開。在上方功能區點選最右上角的「檔案」,在跳出來的視窗選擇最左下角的「選項」,最後在「Excel選項」視窗中,移到「自訂功能區」頁籤,將右邊自訂功能區的「開發人員」勾選。在這裡,還以點選最右邊的上下按紐、調整眾多指令在上方功能區的順序。

在上方功能區點選最右上角的「檔案」
在跳出來的視窗選擇最左下角的「選項」
將右邊自訂功能區的「開發人員」勾選

二、Visual Basic編輯器

回到Excel上方功能區,多出了一個「開發人員」頁籤,選點「程式碼」中的Visual Basic,當滑鼠游標停留在這個指令的時候,會彈出輔助說明:開啟Visual Basic編輯器,並且還會提示「Alt+F11」為快速組合鍵。

三、Excel VBA程式編輯

如果讀者是使用本書附帶的Excel檔案,開啟VBA後,會看到如下的畫面,這便是Excel編寫VBA程式碼的界面,在程式模組中的「Module 1」,已經寫好了一個「BalanceSheet」的巨集程式。

四、檢視巨集程式

回到Excel操作界面,在上方功能區移到「開發人員」頁籤,於「程式碼」群組中選擇「巨集」,一樣會彈出輔助說明:「查看可使用巨集的清單,一樣有提示「Alt+F8」為快速組合鍵。

五、巨集執行清單

點開「檢視巨集」後,跳出「巨集」視窗,初看有很多巨集程式可供選擇。

跳出「巨集」視窗

將下面有個「巨集存放在」的選單下拉,發現其實還有一個「PERSONAL.XLSB」,這個是Excel內置的一個隱藏工作表,只要開啓任何一個Excel檔案,就會連帶隱藏開啟這個檔案,可想見例如將A巨集放在此個人活頁簿,效果等同於在所有Excel檔案都能執行這個A巨集。

在此用不到「PERSONAL.XLSB」,直接下拉改選為「現用活頁簿」即可,表示只需要列出目前活頁簿所具有的巨集清單。另外,讀者如果沒有在使用「PERSONAL.XLSB」,開啓書本範例檔案應該也只看到「BalanceSheet」,這裡因為剛好筆者有這些東西,順便作個分享。

改為「現用活頁簿」之後,清爽了許多,按右上角的「執行」,讓Excel開始跑第三步驟所看到的那些程式。

直接下拉改選為「現用活頁簿」即可

六、五年財務報表

刷刷刷,稍加等待,Excel果然一口氣下載了五個年度的資產負債表,很多資料,這裡礙於篇幅僅能截圖其中一部份。年度從左到右是2013年到2017年,不過把坐標往右邊拉,會看到有個2012年1月1日,通常財報是兩個比較年度,2013年較為特別有三個年度,我的程式設定每個年度財報佔三欄:會計欄位、本年度金額、上年度金額,每次VBA取得資料會以插入方式新增欄位,所以第一次資料中的2012年,於是被擠到最後面去,由於不致於影響財務比率分析,不另外作程式調整。

Excel果然一口氣下載了五個年度的資產負債表

七、VBA程式碼

將這一節的VBA程式碼列示如下,依照列號作說明。

將這一節的VBA程式碼列示如下

10:開始一個取名為「BalanceSheet」的巨集程序程式。

30:定義Year為整數變數,定義WebAddress, DesCell為文字變數。

50:先將工作表所有儲存格清除。

70:以變數Year設置2013到2017的迴圈事件,這個省略「Step 1」,因為迴圈預設是正一的等差級數,亦即整數的流水號。

80-90:依照公開資訊觀測站的年度財務報書網址,設定WebAddress的值,其中的「Year」依照迴圈進行的話,剛好會是股票代碼2409的2013到2017五個年度的第四季財務報告書。

100:設定DesCell變數為所取得財報資料要開始貼上的儲存格坐標位址,其中的「*3」形成公差為3的等差級數,用意便是上個步驟所說的每個年度財報佔三個欄位,依序往右邊接續。

110:VBA取得網路資料的固定程式,其「Connection」為網址參數,「Destination」為目的儲存格位址參數。

120:設定取得網頁資料的特定表格。

130:設定取得網頁上的第2個表格。

140:設定不在背景執行,也是要等到完整資料後再執行下一行程式,因為Excel並非專門取得網頁資料的應用軟體,而且網頁太多的話Excel可能會不穩定,所以都會把這個設定為False。

150:結束With,這一行和110行形成一個VBA的With…End With事件,作用是方便針對同一物件設置許多參數,毋須在每一行程式再一次重申所關聯的對象物件。

160:這一行和70行成一個VBA的迴圈事件。一開始70行的Year是2013,執行到160行再跳回到70行,Year變成是下一個2014,往下再一次執行程式到160行,直到Year是2017將整個等差迴圈執行完了,才會從160行往下跳到170行。

180:取得所有資料後,將工作表所有欄寛都設定為15,方便閱讀。

200:結束此巨集程式。

贊贊小屋關於VBA的兩本著作

筆者上一本書為《人人做得到的網路資料整理術》,全書以八章四十個小節,介紹Excel VBA如何取得網路資料,作為範例的網站類型有藝文展覽、天氣預報、借閱排行、銀行匯率等,其中有個類型便是公開資訊觀測站的財務報表,這一節的VBA程式碼便是引用自上一本書。

當時那本書《人人做得到的網路資料整理術》側重於Excel VBA的技術基礎,這本書主題是財報分析,僅僅利用Excel VBA作為工具取得原始財報,所以在接下來章節有用到VBA的地方,還是會稍加說明,但在技術層面不會像上一本書那麼詳盡,讀者有需要可以使用本書附帶的Excel檔案,直接執行巨集程式即可。

不過如同這一節提到的2013年較為特殊有三個年度,網頁資料難免結構可能有變動,甚至網址規則都有可能更改。建議讀者真的很需要常常取得財務報表進行分析,建議可閱讀《人人做得到的網路資料整理術》,完整學習如何編寫VBA程式取得網路資料。

每天學習,每天充電:VBA財務分析文章

最新文章: