엑셀 파워쿼리 크롤링 - 초보자도 가능한 원클릭 자동화 (데이터 가공까지 한 번에!)

실습 가이드

라이브 강의 전체영상도 함께 확인해보세요!

위캔두 회원에 가입하시면 매주 오빠두엑셀에서 진행하는 라이브 강의 풀영상을 시청하실 수 있습니다.

모든 크롤링 작업은 대상 웹페이지의 구조를 분석하는 단계에서 시작합니다. 본격적인 실습에 앞서 이번 강의에서 크롤링할 웹페이지의 구조를 하나씩 살펴보겠습니다.

1. 비트코인 시세 페이지 구조 살펴보기 : 가상화폐의 실시간 시세를 보여주는 웹사이트로 이동합니다. 이후 키보드 F12키를 누르면 브라우저의 개발자도구가 실행됩니다.
   https://kr.investing.com/crypto/

   

   오빠두Tip : 사용 중인 브라우저마다 개발자도구의 표시 형식이 조금씩 다를 수 있습니다.
2. 크롬 브라우저를 기준으로 개발자도구의 좌측 상단을 보면 '마우스 커서 모양'의 버튼이 위치해 있습니다. 해당 버튼을 클릭한 뒤, 크롤링할 데이터 위로 마우스 커서를 이동하면 데이터의 HTML 구조를 즉시 확인할 수 있습니다.

   

3. 커서를 데이터 위로 가져간 상태에서 클릭하면 해당 요소의 HTML 코드가 표시됩니다. 비트코인의 가격은 현재 <td> 태그 안에 작성되어 있는 것을 확인할 수 있습니다.

   

   오빠두Tip : 엑셀에서 제공하는 파워쿼리는 '<table>' 형태로 작성된 데이터만 불러올 수 있습니다. 따라서 <table>, <tr>, <td> 형태로 구성된 데이터만 크롤링이 가능하다는 점에 유의해야 합니다. (파워BI에서 제공하는 파워쿼리는 보다 다양한 형태의 데이터를 크롤링할 수 있습니다.)
4. 비트코인 기술요약 페이지 구조 살펴보기 : 이번에는 기술요약 정보를 보여주는 페이지의 구조를 분석하기 위해 아래 링크로 이동합니다.
   https://kr.investing.com/indices/investing.com-btc-usd
5. 마찬가지로 F12키를 눌러 개발자도구를 실행한 뒤, 크롤링할 데이터의 HTML 구조를 살펴봅니다. 기술요약 정보 역시 table 안에 입력되어 있는 것을 확인할 수 있습니다.

   

엑셀에서 제공되는 파워쿼리를 활용하면 웹페이지 안에 <table> 형태로 구성된 데이터를 손쉽게 크롤링하고 가공하는 업무를 자동화할 수 있습니다. 각 버전별 엑셀의 파워쿼리 지원 여부 및 파워쿼리의 기초 사용법은 아래 영상 강의를 참고해 주세요.

1. 웹에서 불러올 데이터 선택하기 : 새로운 통합문서를 실행한 뒤, [데이터] - [데이터 가져오기 및 변환] - [웹] 버튼을 클릭합니다. 그러면 '웹에서 데이터 불러오기' 대화상자가 실행됩니다.

   

   오빠두Tip : 사용 중인 엑셀 버전에 따라 화면 구성이 조금씩 다를 수 있습니다. 엑셀 파워쿼리를 추가 기능으로 설치해야 하는 2013 이전 버전의 경우, [파워쿼리] 탭 - [웹에서] 버튼을 클릭합니다.
2. 크롤링할 웹페이지의 URL 주소를 입력합니다. 아래 URL 주소를 입력한 후 [확인] 버튼을 클릭합니다.
   https://kr.investing.com/indices/investing.com-btc-usd
3. URL 주소 입력 후 잠시 기다리면 '탐색창'이 실행되면서 웹페이지 안에 포함된 테이블 목록이 표시됩니다. 테이블 목록에서 파워쿼리로 출력할 테이블을 선택한 후 [데이터 변환] 버튼을 클릭합니다.
   
   
   오빠두Tip : 대화상자 왼쪽 상단의 '여러 항목 선택' 옵션을 활성화하면 동시에 여러 테이블을 파워쿼리로 불러올 수 있습니다.
4. 파워쿼리 편집기에서 데이터 가공하기 : 파워쿼리 편집기가 실행되면서 직전에 선택한 테이블이 새로운 쿼리로 등록됩니다.

   

5. 현재 불러온 자료는 '적극매수~적극매도' 형태의 텍스트로 입력되어 있습니다. 그러나 보다 효율적인 데이터 분석을 위해 적극매도는 1, 적극매수는 5로 가정하여 값을 변경하겠습니다. '5분' 열을 선택한 뒤 키보드 Shift 키를 누른 상태로 '월간' 열을 선택하면 전체 영역이 한 번에 선택됩니다.

   

6. 그 상태에서 [변환] - [값 바꾸기]로 이동한 뒤, 각 값을 하나씩 변경합니다.

   

   찾을 값	바꿀 항목
   적극 매수	5
   매수	4
   중립	3
   매도	2
   적극 매도	1

   오빠두Tip : 고급 옵션에서 '전체 셀 내용 일치'를 활성화하면 전체 셀 내용이 일치하는 항목만 값을 변경할 수 있습니다. 예를 들어 '적극 매도'와 '매도'가 함께 존재하는 상태에서 '매도'의 값을 '4'로 바꾸면 '적극 4'와 '4'로 잘못 변경되는데, '전체 셀 내용 일치'를 활성화하면 이러한 문제를 방지할 수 있습니다.
7. 데이터 형식 변경하기 : 파워쿼리는 각 필드마다 개별적으로 데이터 형식을 지정합니다. 따라서 쿼리를 가공하고 출력하는 단계마다 각 필드의 데이터 형식이 올바른지 점검하는 습관을 갖는 것이 좋습니다.
8. 방금 변경한 값은 텍스트가 아닌 숫자 형식으로 인식되어야 합니다. 각 필드의 데이터 형식을 10진수로 변경합니다.

   

   오빠두Tip : 사용 중인 엑셀 버전에 따라 머리글 왼쪽에 데이터 형식 아이콘이 표시되지 않을 수 있습니다. 이 경우 [변환] - [데이터 형식]에서 데이터 종류를 변경합니다.
   
9. 데이터 가공이 완료되었습니다. 이외의 다른 웹페이지도 동일한 절차를 통해 데이터를 크롤링하고 가공할 수 있습니다.

   

데이터 가공이 끝났으니, 이제 크롤링한 결과를 시트 위로 출력해 보겠습니다.

1. 크롤링 결과를 시트 위로 출력하기 : [파일] - [닫기 및 다음으로 로드]를 클릭합니다.
   
2. [데이터 가져오기] 대화상자가 실행되면 '연결만 만들기' 옵션을 선택한 후 [확인] 버튼을 클릭합니다.

   

   오빠두Tip : 또는 데이터 가져오기 대화상자에서 '표' 옵션을 선택한 후 시트 위로 바로 출력할 수도 있습니다.
3. [쿼리 및 연결] 목록에 새로운 쿼리가 등록됩니다. 쿼리를 우클릭한 뒤 [다음으로 로드]를 선택하여 '표' 형태로 기존 워크시트에 출력합니다.

   

4. 열 너비 자동맞춤 문제 해결 : 파워쿼리로 출력된 표는 새로고침을 실행하면 표의 열 너비가 자동으로 재조정됩니다. 따라서 보고서를 작성할 때에는 열 너비 자동맞춤 옵션을 비활성화하는 것이 좋습니다. 표를 선택한 뒤 [표 디자인] - [속성]으로 이동합니다.
   
5. [열 너비 조정] 옵션을 비활성화한 뒤 [확인] 버튼을 클릭합니다.
   
6. 이제 표의 열 너비를 임의로 변경한 후 우클릭 - [새로고침]을 선택하면 표의 열 너비가 더 이상 자동으로 변경되지 않는 것을 확인할 수 있습니다.

   

이번에 크롤링한 데이터는 1~5 사이의 값으로만 입력되어 있습니다. 보고서를 작성할 때 1, 10, 100, 1000과 같이 자릿수에 큰 차이가 없고 한 자릿수 또는 두 자릿수의 값으로만 구성될 경우 값의 대소 차이를 한눈에 파악하기 어려운 문제가 발생합니다.

따라서 이번에는 크롤링한 데이터에 조건부서식을 적용하여 표를 시각화해 보겠습니다. 조건부서식에 대한 보다 자세한 사용법은 조건부서식의 모든 것 기초 입문강의를 참고하세요.

1. 조건부서식 적용하기 : 표 범위를 선택한 후 [홈] 탭 - [조건부서식] - [색조] - [기타규칙]으로 이동합니다.
   
2. 서식 스타일을 '3가지 색조'로 변경합니다. 이후 최소값과 중간값, 최대값의 종류를 모두 숫자로 변경한 후 각 값을 1, 3, 5로 입력합니다. 마지막으로 중간값의 색상을 노란색에서 흰색으로 변경한 후 [확인] 버튼을 클릭합니다.

   

3. 선택한 범위에 조건부서식이 정상적으로 적용되었습니다.

   

쿼리 새로고침 매크로 추가하기

이제 마지막 단계로 시트 위에 버튼을 추가하여 웹페이지의 데이터를 빠르게 불러오는 방법을 알아보겠습니다.

1. 매크로 편집기 실행 : [개발도구] - [Visual Basic] 버튼을 클릭하거나 단축키 Alt + F11 키를 눌러 매크로 편집기를 실행합니다. 만약 [개발도구] 탭이 보이지 않을 경우 아래 링크를 참고하여 개발도구를 활성화합니다.
   엑셀 개발도구 (매크로) 활성화 방법 :: 엑셀 위키
2. [삽입] - [모듈]을 클릭하여 새로운 모듈을 추가합니다. 이후 아래 명령문을 복사하여 모듈 안에 붙여넣기합니다.
   

   Sub RefreshAll()
   ThisWorkbook.RefreshAll
   MsgBox "모든 데이터를 새로고침하였습니다."
   End Sub

3. 매크로 실행 버튼 만들기 : 다시 시트로 이동한 뒤 [삽입] - [도형]에서 사각형 모양의 도형을 시트 위에 추가합니다.

   

   오빠두Tip : 키보드 Alt 키를 누른 상태로 도형을 삽입하면 셀 크기에 정확히 맞춰서 도형이 삽입됩니다.
4. 도형을 적절히 꾸민 뒤 도형을 우클릭하여 [매크로 지정]을 선택하면 '매크로 지정' 대화상자가 실행됩니다. 목록에서 RefreshAll 명령문을 선택한 후 [확인] 버튼을 클릭합니다.
   
5. 쿼리 새로고침 옵션 변경 : 이제 도형을 클릭하면 매크로가 실행되면서 쿼리가 새로고침됩니다. 다만 쿼리는 기본적으로 '다른 작업을 하면서 새로고침'되도록 설정되어 있어, 쿼리 새로고침이 시작되기 전에 안내 메시지가 먼저 출력되는 문제가 있습니다. 만약 쿼리 새로고침이 완료된 뒤에 안내 메시지가 출력되도록 하려면 쿼리의 새로고침 옵션을 변경합니다.
6. 표를 선택한 후 [쿼리] - [속성]으로 이동합니다. 이후 새로고침 옵션에서 '다른 작업하면서 새로 고침' 옵션을 비활성화한 후 [확인] 버튼을 클릭합니다. 이제 버튼을 클릭하면 모든 쿼리가 새로고침된 후, 새로고침이 완료되었다는 안내 메시지가 출력됩니다.