自從財報改制為 IFRSs 後, 要用 Excel VBA 去批次下載財報資料變得相對麻煩。
底下是我搜集資料常用到的網址模板, 希望不會再變來變去:- 財報資料來源網址不同;
- IFRSs 前、後的合併報表名詞、項目不同;
- 有些網頁為 UTF-8 編碼, 有的為 Big5;
- Excel 沒辦法匯入 UTF-8 編碼的外部資料
- IFRSs 前的合併報表資料更新頻率不一致;
- 2006 年以前是半年更新一次;
- 2004 年以前是 一年更新一次;
'''''''''''''''''''''''''''''' IFRS 前 '''''''''''''''''''''''''''''''
'資產負債表
'http://mops.twse.com.tw/mops/web/ajax_t05st31?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=2330&year=89&season=01
'
'資產負債表 (合併)
'http://mops.twse.com.tw/mops/web/ajax_t05st33?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=2330&year=94&season=02
'
'損益表
'http://mops.twse.com.tw/mops/web/ajax_t05st32?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=2330&year=89&season=01
'損益表 (合併)
'http://mops.twse.com.tw/mops/web/ajax_t05st34?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=2330&year=94&season=02
'
'現金流量表
'http://mops.twse.com.tw/mops/web/ajax_t05st36?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=2330&year=89&season=01
'
'現金流量表 (合併)
'http://mops.twse.com.tw/mops/web/ajax_t05st39?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=2330&year=94&season=02
'
'
''''''''''''''''''''''''''''''' IFRS 後 '''''''''''''''''''''''''''''''
'資產負債表 (合併)
'http://mops.twse.com.tw/mops/web/ajax_t164sb03?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=2330&year=102&season=01
'
'損益表 (合併)
'http://mops.twse.com.tw /mops/web/ajax_t164sb04?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=2330&year=102&season=01
'
'現金流量表 (合併)
'http://mops.twse.com.tw/mops/web/ajax_t164sb05?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=2330&year=102&season=01
Function GetURL1(index As String, year As Integer, urlStr)
Select Case index
Case "BS"
If year < 2013 Then
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t05st31?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
Else
urlStr = "2012 IFRS 前才有 BS 資料"
Debug.Print "Error in Case BS"
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t05st31?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
End If
Case "BSc"
If year < 2013 Then
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t05st33?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
Else
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t164sb03?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
End If
Case "IS"
If year < 2013 Then
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t05st32?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
Else
urlStr = "2012 IFRS 前才有 IS 資料"
Debug.Print "Error in Case IS"
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t05st32?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
End If
Case "ISc"
If year < 2013 Then
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t05st34?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
Else
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t164sb04?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
End If
Case "CF"
If year < 2013 Then
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t05st36?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
Else
urlStr = "2012 IFRS 前才有 CF 資料"
Debug.Print "Error in Case BS"
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t05st36?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
End If
Case "CFc"
If year < 2013 Then
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t05st39?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
Else
urlStr = "http://mops.twse.com.tw/mops/web/ajax_t164sb05?encodeURIComponent=1&step=1&firstin=1&off=1&keyword4=&code1=&TYPEK2=&checkbtn=&queryName=co_id&TYPEK=all&isnew=false&co_id=[STOCK_ID]&year=[YEAR]&season=[QUARTER]"
End If
Case Else
urlStr = "index 必需為 BS/BSc/IS/ISc/CF/CFc"
Debug.Print
End Select
End Function
沒有留言:
張貼留言