엑셀 VBA 웹 데이터 가져오기(웹 크롤링)

VBA를 활용하여 정보 요청 보내고 받기

HTTP요청 객체 만들고 요청하기

엑셀 VBA로 웹 데이터를 가져오는 과정은 서비스 웹 서버에 처리 요청-> 데이터 수신 -> 데이터 처리의 과정을 거친다. 엑셀 VBA에서는 그림 1과 같이 서버에 "요청"을 하나의 객체로 만들어서 웹 서버로 보내고 그 결과를 수신한다.

그림1: 엑셀 VBA 웹데이터 송수신 과정

이때 만드는 객체(Object)가 "MSXML2.XMLHTTP.6.0"으로 Microsoft XML Core Services 라이브러리의 HTTP 요청 객체이다. 기본적인 구조는 아래와 같이 객체를 Set 하고 Open 메서드로 "요청"을 구성한 다음에 Send 메서드로 "요청"을 보내면 된다. 

Sub 웹데이터가져오기()

    Dim Http요청 As Object
    
    ' HTTP요청 객체 생성
    Set Http요청 = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' GET 요청 보내기
    Http요청.Open "GET", "정보를 요청하는 URL 주소", False
    Http요청.Send
    
End Sub

 

응답을 html file 객체에 받기

우리가 하고 싶은 것은 웹 데이터를 가져오는 것이기 때문에 Http요청을 보낼 때 "GET"으로 보내면 된다. 응답을 받으면 Http요청이라는 객체에 결과가 담겨서 오게 된다. 이 결과를 HTML 속성을 가진 객체를 만들어서 여기에 다시 담는다. 이때 HTML 속성을 가진 객체를 만들기 위해서 아래 그림 2와 같이 Microsoft HTML Object Library 참조를 추가하는 과정이 필요하다.

그림2: Microsoft HTML Object Library의 참조 추가

 

HTML 속성을 가진 객체에 Http요청 결과가 담기면 HTML DOM 메서드 사용이 가능해진다. Http요청 결과값을 getElementById 같은 익숙한 메서드를 사용해서 특정 tag의 text 값을 골라내는 등의 작업을 할 수 있는 것이다.

Sub 웹데이터가져오기()
    Dim Http요청 As Object
    Dim html문서 As Object
    
    ' HTTP요청 객체 생성
    Set Http요청 = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' GET 요청 보내기
    Http요청.Open "GET", "정보를 요청하는 URL 주소", False
    Http요청.Send
    
    ' HTMLDocument 객체 생성 및 응답 HTML 로드
    Set html문서 = CreateObject("HTMLfile")
    html문서.body.innerHTML = Http요청.responseText
    
    ...html문서 객체를 후처리하여 원하는 상세 정보를 뽑아내는 작업...
    
 End Sub

 

네이버금융 메인 화면의 TOP 종목 정보 가져오기

네이버금융 HTML 문서 분석

위에서 살펴본 기본 구조를 바탕으로 네이버금융 TOP 종목 정보를 받아서 엑셀 시트에 기록하는 엑셀 VBA 기능을 만들어보자.

그림3: 네이버금융 TOP 종목

크롬 브라우저의 검사 기능을 사용하면 아래 그림 4와 같이 HTML 문서의 구조를 쉽게 파악할 수 있다. 

그림4: 네이버금융 HTML 문서상 TOP 종목 tag 정보

TOP종목은 "_topItems1"라는 id를 가진 <tbody> 태그에 존재하고, 각 row의 종목명은 <th> tag에 담겨있으며, 가격 및 등락률 등의 정보는 <td> tag 에 담겨 있다. HTML 문서구조를 정리하면 아래와 같다. 

<tbody id="_topItems1">
<tr class="down">
    <th><a href="...">KODEX 200선물인버스2X</a></th>
    <td>2,590</td>
    <td><em class="bu_p bu_pdn"><span class="blind">하락</span></em> 35</td>
    <td>-1.33%</td></tr>
<tr class="down">
    <th><a href="...">한국패러랠</a></th>
    <td>188</td>
    <td><em class="bu_p bu_pdn"><span class="blind">하락</span></em> 27</td>
    <td>-12.56%</td></tr>
...
</tbody>

 

네이버금융 HTML 문서 TOP종목 기록하기

네이버금융 HTML 문서 TOP종목을 내 엑셀 파일에 기록하는 작업은 "https://finance.naver.com/"에 HTTP 요청을 하고 HTML객체에 정보를 저장하는 것부터 시작한다. 다음으로 저장된 HTML객체에서 DOM 메서드를 이용해서 필요한 부분을 추출한다.(위에서 분석한 홈페이지 구조 참조) 마지막으로 추출한 정보를 반복문(for 문)을 사용해서 반복적으로 내 엑셀파일에 기록하면 작업이 완료된다.

Sub 네이버TOP종목가져오기()

    Dim Http요청 As Object
    Dim html문서 As Object
    'tbody 의 각각의 <tr> tag 요소들을 rows 객체로 정의
    Dim rows As Object
    'tr tag 에 종속된 th 태그 객체를 firstcell 객체로, 나머지 td 태그 객체들을 cells 객체로 정의
    Dim firstcell As Object
    Dim cells As Object
    

    Worksheets("main").cells.ClearContents
    
    ' HTTP요청 객체 생성
    Set Http요청 = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' GET 요청 보내기
    Http요청.Open "GET", "https://finance.naver.com/", False
    Http요청.Send
    
    ' HTMLDocument 객체 생성 및 응답 HTML 로드
    Set html문서 = CreateObject("HTMLfile")
    html문서.body.innerHTML = Http요청.responseText
    
    ' 거래상위 TOP 종목 테이블 (id="_topItems1") 찾기
    Set rows = html문서.getElementById("_topItems1").getElementsByTagName("tr")
    
    ' 엑셀의 첫 번째 행에 열 이름(A, B, C, D) 설정
    Worksheets("main").cells(1, 1).Value = "종목명"
    Worksheets("main").cells(1, 2).Value = "현재가"
    Worksheets("main").cells(1, 3).Value = "전일대비"
    Worksheets("main").cells(1, 4).Value = "등락률"
    
    ' 각 <tr> 요소 순회하며 데이터 추출
    i = 2 ' 데이터는 두 번째 행부터 시작
    For Each row In rows
        j = 1 '첫 번째 열
        'th tag의 종목명 뽑아내서 워크시트에 기록(첫 번째 열)
        Set firstcell = row.getElementsByTagName("th")
            Worksheets("main").cells(i, j).Value = firstcell(0).innerText
            
        'td tag의 구성요소들을 순서대로 워크시트에 기록(나머지 열)
        Set cells = row.getElementsByTagName("td")
        j = 2 '두 번재 열부터 시작
        For Each cell In cells
            Worksheets("main").cells(i, j).Value = cell.innerText
            j = j + 1
        Next cell
        i = i + 1
    Next row
    
    ' 객체 RESET
    Set Http요청 = Nothing
    Set html문서 = Nothing
    
End Sub

 

결과적으로 아래와 같이 완성된 파일을 만들어 볼 수 있다.

241103_웹스크래핑연습.xlsm

0.03MB