취미생활/IT 관련

[증권취미] 엑셀로 증권데이터 긁어오기 : 엑셀 웹크롤링 예제

. . . 2017. 11. 30. 08:59
반응형

개인적으로 취미로 증권을 하기 시작했습니다.

- 아주 소액으로, 경제공부겸 해서 하고있습니다. ^^;;

 

hts 나 각종 증권정보사이트에서 주는 정보들을 좀더 잘 활용하기 위해서 엑셀로 데이터를 긁어올 필요성이 있더군요.

그래서 엑셀로 네이버증권에서 거래량을 갖고오는 메크로를 작성했습니다.

엑셀 웹크롤링 예제소스

엑셀에서 웹크롤링하는 방법들 중에 가장심플하고 잘 동작하는 방법은 다음의 API 인듯 싶다.

Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")

XMLHTTP.Open "GET", url, False
XMLHTTP.setRequestHeader "Content-Type", "text/xml"
XMLHTTP.send

Set html = CreateObject("htmlfile")
html.body.innerHTML = XMLHTTP.ResponseText

위의 API 를 이용하여 네이버증권 사이트(http://finance.naver.com) 에서 특정 증권코드를 갖고오는 API를 제작했다.

 

Private Sub finance_data_type1_from_naver(ExcelRowStart As Long, ExcelColStart As Long, StockCode As String, ParsingPage_max As Integer, TargetSheet As String)
    Dim url As String, lastRow As Long
    Dim XMLHTTP As Object, html As Object
    Dim tbl As Object, obj_tbl As Object
    Dim TR As Object, TD As Object
    Dim row As Long, col As Long

    Dim i As Long
    Dim j As Long
    
    Dim RowChangFlag As Integer
    Dim ParseTableRowCnt As Integer
    Dim ParseTableColCnt As Integer
    
    Worksheets(TargetSheet).Activate
    
    lastRow = Range("A" & Rows.Count).End(xlUp).row
    
    row = ExcelRowStart
    col = ExcelColStart

    Dim idx As Integer

    For idx = 1 To ParsingPage_max
            
        ' naver용 url 을 만든다.
        url = "http://finance.naver.com/item/frgn.nhn?code=" & StockCode & "&page=" & idx
        
        ' debug info.. 만든 url 을 적어놓는다.
        Cells(2, 9) = url
        
        ' -----------------------------------------------
        ' webpage parsing load context
        ' -----------------------------------------------
        Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
        
        XMLHTTP.Open "GET", url, False
        XMLHTTP.setRequestHeader "Content-Type", "text/xml"
        XMLHTTP.send
    
        Set html = CreateObject("htmlfile")
        html.body.innerHTML = XMLHTTP.ResponseText
        Set obj_tbl = html.getelementsbytagname("table")
    
        ' -----------------------------------------------
        ' 페이지 내의 모든 테이블 갖고온다.
        ' -----------------------------------------------
        For Each tbl In obj_tbl
            ' -----------------------------------------------
            ' 테이블의 summary 로 실제 거래량 테이블을 판별한다.
            ' 타 사이트 파싱시 tbl.id 라던가 다른방법을 써야한다.
            ' naver 의 경우 tbl.id 가 없고, summary 만있다보니 다음과 같이 하였다.
            ' -----------------------------------------------
            If InStr(tbl.Summary, "거래량에 관한표") > 0 Then
                ' 각 Row 를 갖고와서..
                Set TR = tbl.getelementsbytagname("TR")
                For Each obj_row In TR
                    RowChangFlag = 0
                    ParseTableRowCnt = ParseTableRowCnt + 1
                    ' -----------------------------------------------
                    ' 실제 코드를 돌려보면 4번째 row 부터 실제 거래량 데이터다.
                    ' -----------------------------------------------
                    If ParseTableRowCnt >= 4 Then
                        ' 거래량 데이터를 row 별로 파싱한다.
                        ParseTableColCnt = 0
                        For Each TD In obj_row.getelementsbytagname("TD")
                            ParseTableColCnt = ParseTableColCnt + 1
                            ' 실제 데이터가 있는것만 추린다.
                            If Len(TD.innerText) > 1 Then
                                ' 1: 거래일
                                If ParseTableColCnt = 1 Then
                                    Cells(row, col) = TD.innerText
                                    col = col + 1
                                    RowChangFlag = 1
                                End If
                                ' 2: 종가
                                If ParseTableColCnt = 2 Then
                                    Cells(row, col) = TD.innerText
                                    col = col + 1
                                    RowChangFlag = 1
                                End If
                                ' 5: 거래량
                                If ParseTableColCnt = 5 Then
                                    Cells(row, col) = TD.innerText
                                    col = col + 1
                                    RowChangFlag = 1
                                End If
                                ' 6: 기관순매량
                                If ParseTableColCnt = 6 Then
                                    Cells(row, col) = TD.innerText
                                    col = col + 1
                                    RowChangFlag = 1
                                End If
                                ' 7: 기관순매량
                                If ParseTableColCnt = 6 Then
                                    Cells(row, col) = TD.innerText
                                    col = col + 1
                                    RowChangFlag = 1
                                End If
                            End If
                        Next
                        
                        col = ExcelColStart    ' reseting the value
                        If RowChangFlag = 1 Then
                            row = row + 1
                        End If
                    End If
                        
                Next
            End If
        Next
    Next idx
    


    'getData
End Sub

출처:

http://xenolife.tistory.com/595

[살림하는 프로그래머]