最新消息

[公告2014/05/30] 如有需要將部落格中,任何一篇文章的程式碼使用在商業用途,請與我聯繫。

[公告2015/04/26] Line版的 iInfo程式與投資應用 群組已上線想加入的朋友們,請先查看 "入群須知" 再與我聯繫 Line : aminwhite5168,加入請告知身分與回答 "入群須知" 的問題。

[公告2018/04/22] 台北 Python + Excel VBA 金融資訊爬蟲課程,課程如網頁內容 金融資訊爬蟲班:台北班 Python 金融資訊爬蟲、EXCEL VBA 金融資訊爬蟲

[公告2019/01/08] 請注意:我再次重申,部落格文章的程式碼,是要提供各位參考與學習,一旦網頁改版請自行修改,別要求東要求西要我主動修改,你們用我寫東西賺錢了、交差了,請問有分我一杯羹嗎?既然賺錢沒分我,請問有什麼理由要求我修改,如果沒能力改,就花錢來找我上課。

[公告2019/12/01] 若各位有 Excel VBA 案子開發需求,歡迎與我聯繫,可接案處理。

[公告2020/05/22] 頁面載入速度慢,起因為部分JS來源(alexgorbatchev.com)失效導致頁面載入變慢,目前已做調整,請多見諒。

2012年10月9日 星期二

Excel VBA抓取上櫃交易明細

使用QueryTables與XMLHTTP方法快速抓取上櫃股票各分點劵商的交易明細。


上櫃個股買賣日報表

抓取交易明細完成畫面
VBA程式碼如下
Option Explicit

Dim TempSheet, oXMLHTTP, objStream, StartTime
Dim i As Integer, n As Integer, l As Integer, j As Integer
Dim qyt As QueryTable
Dim saledate As String, FilePath As String, FileName As String, webdate As String

Private Sub CommandButton1_Click()
    下載全部資料
End Sub

Sub 下載全部資料()
    StartTime = Now
    
    saledate = Year(Date) & Format(Month(Date), "00") & Format(Day(Date), "00")
    saledate = "20121012" '指定專屬日期
    
    Application.ScreenUpdating = False
    
    '建立暫存工作表
    If 確認工作表存在("Temp") <> True Then
        Application.StatusBar = "建立暫存工作表"
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Temp"    '建立指定工作到現存工作表的對後面
        Set TempSheet = Sheets("Temp")
    Else
        Application.StatusBar = "清除暫存工作表"
        Set TempSheet = Sheets("Temp")
        清除暫存工作表
    End If
    
    Application.StatusBar = "抓股票代碼"
    抓股票代碼
    
    Application.StatusBar = "刪除空白資料列"
    刪空特定列
    
    Application.StatusBar = "解析股票代碼"
    解析股票代碼
    
    '交易明細路徑可自行修改
    FilePath = "D:\OTC\"
    If Dir(FilePath, vbDirectory) = "" Then
        MkDir (FilePath)
    End If
    
    '交易明細路徑可自行修改
    FilePath = "D:\OTC\" + saledate + "\"
    Application.StatusBar = "建立" + FilePath + "資料夾"
    If Dir(FilePath, vbDirectory) = "" Then
        MkDir (FilePath)
    End If
        
    n = Sheet1.Cells(65536, 1).End(xlUp).Row
    For i = 2 To n
        下載交易明細 FilePath, Cells(i, 1), saledate
        Application.StatusBar = "正在抓取 " + CStr(Cells(i, 1)) + " " + CStr(Cells(i, 2)) + " 交易明細... 已完成" + Format(i / n * 100, ".00") + "%"
    Next
    Application.StatusBar = "上櫃交易明細抓取結束"

    Application.ScreenUpdating = True
    
    MsgBox saledate & "上櫃交易明細下載 共花費 " & Format(Now - StartTime, "HH時mm分ss秒") & " 下載完成。" & vbCrLf & "以秒計算 共花費 " & DateDiff("s", StartTime, Now) & " 秒下載完成", vbInformation
    
    Set TempSheet = Nothing
End Sub

Sub 下載交易明細(FilePath As String, stockid As String, saledate As String)
    Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
    Set objStream = CreateObject("ADODB.stream")
   
    webdate = Replace(saledate, Year(Date), CStr(CInt(Year(Date)) - 1911))
    FileName = FilePath + "\" + saledate + "_" + stockid + ".CSV"
    
    With oXMLHTTP
        .Open "POST", "http://www.gretai.org.tw/ch/stock/aftertrading/broker_trading/download_ALLCSV.php", False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        .send "curstk=" & stockid & "&stk_date=" & webdate
        
        If .readyState = 4 Then '.Status = 200
            With objStream
                .Type = 1       '以二進位方式操作
                .Open           '開啟物件
                .Write oXMLHTTP.ResponseBody
                
                If Dir(FileName) <> "" Then Kill FileName
                .SaveToFile FileName
                .Close
            End With
        End If
    End With
    
    Set oXMLHTTP = Nothing
    Set objStream = Nothing
End Sub

Sub 解析股票代碼()
    With TempSheet
        With Sheet1
            .Cells(1, 1) = "股票代碼"
            .Cells(1, 2) = "公司名稱"
            n = 2
            For i = 2 To .Cells(65536, 6).End(xlUp).Row
                If Trim(TempSheet.Cells(i, 6)) = "ESVUFR" Or _
                   Trim(TempSheet.Cells(i, 6)) = "EUOMSR" Or _
                   Trim(TempSheet.Cells(i, 6)) = "EMXXXA" Or _
                   Trim(TempSheet.Cells(i, 6)) = "ESVUFA" Then
                   
                    .Cells(n, 1) = Split(.Cells(i, 1), "  ")(0)
                    .Cells(n, 2) = Split(.Cells(i, 1), "  ")(1)
                    n = n + 1
                End If
            Next i
        End With
    End With
End Sub

Sub 抓股票代碼()
    With TempSheet.QueryTables.Add("URL;http://isin.twse.com.tw/isin/C_public.jsp?strMode=4", TempSheet.Cells(1, 1))
        .WebFormatting = xlWebFormattingNone
        .WebTables = "2"
        .Refresh 0
        If Err.Number <> 0 Then Err.Clear: MsgBox "資料查詢失敗"    '被免資料抓取不成功,而顯示訊息
        .Delete
    End With
End Sub

Sub 清除暫存工作表()
    With TempSheet                              '選取指定名稱工作表
        n = .Cells(65536, 6).End(xlUp).Row      '選取目前活頁簿從A1位置到最後一行的範圍
        l = .Cells(1, 1).End(xlToRight).Column
    
        If n = 1 Or l = 256 Then
            Exit Sub
        End If
        
        For Each qyt In TempSheet.QueryTables   '選取用QueryTables抓取的每一行
            qyt.Delete                          '將使用QueryTables方法所產生的行進行刪除,避免QueryTables用久了,造成系統堆積一堆QueryTables的垃圾,如此系統才部會變慢
        Next
    
        .Cells.Clear                            '清除所選取儲存格格式
        .Cells.ClearContents                    '清除所選取的資料
    End With
End Sub

Sub 刪空特定列()
    j = 0
    For i = 2 To TempSheet.Cells(65536, 6).End(xlUp).Row
        If TempSheet.Cells(i, 6).Value = Empty Then
            j = j + 1
            TempSheet.Rows(i & ":" & i).Delete Shift:=xlUp
            If n - j >= i Then
                i = i - 1
            End If
         End If
    Next
End Sub

Function 確認工作表存在(strWSName As String) As Boolean
    Dim Temp As Excel.Worksheet
    On Error Resume Next
    
    Set Temp = Worksheets(strWSName)
    If Not Temp Is Nothing Then
        確認工作表存在 = True
        On Error GoTo 0
        Set Temp = Nothing
        Exit Function
    End If
    
    確認工作表存在 = False
    On Error GoTo 0
    Set Temp = Nothing
End Function
若是不想寫code,考參考這裡下載

[2014/01/03]櫃買中心改版,程式碼修改部分
近期櫃買中心改版,影響交易明細下載,目前驗證出來的結果有以下幾點須注意:
  1. 網站會紀錄IP,可採行使用3G網卡,斷線再連線重試。
  2. 不能快速存取交易明細,須加延遲時間2~5秒。
  3. 在5秒內左右快速存取三次檔案,在第四筆檔案以後將會無法存取。
  4. 如上若第四次無法存取後,又一直以快速存取,網站伺服器將紀錄IP拒絕存取,連帶網頁也無法開啟,需等待20分鐘以上甚至更久,可參考第一點注意事項的解法。
  5. 參數傳送方式變更。
  6. 原始傳送:http://www.gretai.org.tw/ch/stock/aftertrading/broker_trading/download_ALLCSV.php
    現在傳送:http://www.gretai.org.tw/ch/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=5349&stk_date=1030102
2014/01/03櫃買中心網站修改修改版,請考參考這裡下載

以上櫃買中心網站變更項目僅是個人推估,如有錯誤請提供糾正,變更程式碼參考如下:
Sub 下載交易明細(FilePath As String, stockid As String, saledate As String)
    Dim url As String
    
    Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
    Set objStream = CreateObject("ADODB.stream")
   
    webdate = Replace(saledate, Year(Date), CStr(CInt(Year(Date)) - 1911))
    FileName = FilePath + "\" + saledate + "_" + stockid + ".CSV"
    
    Delay 3 '加入延遲
    
    url = "http://www.gretai.org.tw/ch/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=" & stockid & "&stk_date=" & webdate
    With oXMLHTTP
        .Open "POST", url, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        '.setRequestHeader "Referer", "http://www.gretai.org.tw/ch/stock/aftertrading/broker_trading/brokerBS.php"
        '.setRequestHeader "Host", "www.gretai.org.tw"
        .send
        
        Delay 1 '加入延遲
        
        If .readyState = 4 Then '.Status = 200
            With objStream
                .Type = 1       '以二進位方式操作
                .Open           '開啟物件
                .Write oXMLHTTP.ResponseBody
                
                If Dir(FileName) <> "" Then Kill FileName
                .SaveToFile FileName
                .Close
            End With
        End If
    End With
    
    Set oXMLHTTP = Nothing
    Set objStream = Nothing
End Sub
新增函數
Public Sub Delay(DelayTime As Single)
     Dim BeginTime As Single
     BeginTime = Timer
     While Timer < BeginTime + DelayTime
         DoEvents
     Wend
End Sub
[2014/07/11]櫃買中心改版,程式碼修改部分
近期櫃買中心改版,影響交易明細下載,目前驗證出來的結果有以下幾點須注意:
  1. 變更上櫃交易中心檔案下載網址。
  2. 原始傳送:http://www.gretai.org.tw/ch/stock/aftertrading/broker_trading/download_ALLCSV.php
    現在傳送:http://www.gretai.org.tw/web/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=5349&stk_date=1030711
Sub 下載交易明細(FilePath As String, stockid As String, saledate As String)
    Dim url As String
    
    Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")
    Set objStream = CreateObject("ADODB.stream")
   
    webdate = Replace(saledate, Year(Date), CStr(CInt(Year(Date)) - 1911))
    Filename = FilePath + "\" + saledate + "_" + stockid + ".CSV"
    
    Delay 3 '加入延遲
    
    url = "http://www.gretai.org.tw//web/stock/aftertrading/broker_trading/download_ALLCSV.php?curstk=" & stockid & "&stk_date=" & webdate
    With oXMLHTTP
        .Open "POST", url, False
        .setRequestHeader "Content-Type", "application/x-www-form-urlencoded"
        '.setRequestHeader "Referer", "http://www.gretai.org.tw/ch/stock/aftertrading/broker_trading/brokerBS.php"
        '.setRequestHeader "Host", "www.gretai.org.tw"
        .send
        
        Delay 1 '加入延遲
        
        If .readyState = 4 Then '.Status = 200
            With objStream
                .Type = 1       '以二進位方式操作
                .Open           '開啟物件
                .Write oXMLHTTP.ResponseBody
                
                If Dir(Filename) <> "" Then Kill Filename
                .SaveToFile Filename
                .Close
            End With
        End If
    End With
    
    Set oXMLHTTP = Nothing
    Set objStream = Nothing
End Sub

執行結果


2 則留言:

  1. 大大,是否櫃買資料源已經更改了呢? 目前都無法下載!

    回覆刪除