台股月營收

ddhh4053

請問有人分享要如何抓取公開資訊台股近6年全部月營收資料ㄇ

chen_cook

月營收.rar (440.12 KB)

內無巨集
下載次數: 31

2012-7-4 05:25

回復 1# ddhh4053


    因說明很簡略,這是你要的嗎??

chen_cook

回復 3# tsuneng

chen_cook

回復 3# tsuneng


    Dim myWeb(3) As String
y$ = 101: m$ = 5          <---可修正抓取年月

   myWeb(0) = "sii"
   myWeb(1) = "otc"
   myWeb(2) = "rotc"
   myWeb(3) = "pub"
      
For i = 0 To 3
   With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://mops.twse.com.tw/t21/" & myWeb(i) & "/t21sc03_" & y$ & "_" & m$ & ".html", Destination:=Cells(1, 1))
        '.Name = "zcx_" + stockCode$ + ".asp_5"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingAll
        .WebTables = "2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = True   'False
        .Refresh BackgroundQuery:=False
    End With
    Ed = [J65535].End(xlUp).row
    Range("A1:J" & Ed).Copy Worksheets("月營收").Cells(x + 2, 1)
    x = Worksheets("月營收").[a65536].End(xlUp).row
    Cells.Select
    Selection.Delete Shift:=xlUp
   
Next i

'
End Sub

tsuneng

回復 5# chen_cook

謝謝大大的回應, TRY 大大公告的code 有不明白處 請教

1.     y$ = 101: m$ = 5       <---可修正抓取年月 <==> 修改成  Syear = Year(Date) - 1911: Smon = Month(Date) - 1     

     "URL;http://mops.twse.com.tw/t21/" & myWeb(i) & "/t21sc03_" & Syear & "_" & Smon & ".html", Destination:=Cells(1, 1))

      執行 程式後會在下行之處發生錯誤，(執行階段錯誤,陣列超出範圍)

       Range("A1:J" & Ed).Copy Worksheets("月營收").Cells(x + 2, 1)

2, 要如何一次取得多月份的月營收資料

chen_cook

回復 6# tsuneng


    Sub 月營收()
'
' Macro2 Macro
' SuperXP 在 2012/7/7 錄製的巨集
'
Dim myWeb(3) As String

   myWeb(0) = "sii"
   myWeb(1) = "otc"
   myWeb(2) = "rotc"
   myWeb(3) = "pub"
     
y$ = Range("B1"): m$ = Range("C1"): Ey$ = Range("D1"): Em$ = Range("E1") 'B1&C1 始年月 E1&D1 終年月

For Lp = 1 To (Ey$ - y$) * 12 - m$ + 1 + Em$
  x = 0: Sheets.Add.Name = "sample": Sheets("sheet1").Select
  For i = 0 To 3
   With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://mops.twse.com.tw/t21/" & myWeb(i) & "/t21sc03_" & y$ & "_" & m$ & ".html", Destination:=Cells(1, 1))
        '.Name = "zcx_" + stockCode$ + ".asp_5"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = False
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingAll
        .WebTables = "2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = True   'False
        .Refresh BackgroundQuery:=False
    End With
    Ed = [J65535].End(xlUp).row
    Range("A1:J" & Ed).Copy Worksheets("sample").Cells(x + 2, 1)
    x = Worksheets("sample").[a65536].End(xlUp).row
    Cells.Select
    Selection.Delete Shift:=xlUp
  Next i
   
    Sheets("sample").Name = "C" & y$ & m$
    If m$ = 12 Then
        y$ = y$ + 1
        m$ = 1
    Else
        m$ = m$ + 1
    End If
   
    If Ey$ = y$ And Em$ < m$ Then
    Exit For
    End If
   
Next Lp

'
End Sub

chen_cook

回復 6# tsuneng


    y$ = 101: m$ = 5       <---可修正抓取年月 <==> 修改成  Syear = Year(Date) - 1911: Smon = Month(Date) - 1
        我的做法是 y$ = year(today())-1911: m$=month(today())
    你沒有 sheets("月營收") 再先改SHEET NAME!!
   連續抓的已通知你了...先用我的跑能出現資料後,等看懂了再自已改!!

devidlin

回復 3# chen_cook


    你好，像2317鴻海相關的營收資料都沒有，是漏掉了嗎?

chen_cook

回復 8# devidlin


    那這是什麼??

devidlin

回復 9# chen_cook


  你好，小弟指的是這個檔案為何無2317鴻海的資料，謝謝。