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

엑셀 파워쿼리를 활용해 Investing에서 제공되는 비트코인 시세 및 기술요약 정보를 실시간으로 크롤링하는 실전 예제

홈페이지 » 엑셀 파워쿼리 크롤링 | 초보자도 가능한 원클릭 자동화

엑셀 파워쿼리 크롤링 - 초보자도 가능한 원클릭 자동화

엑셀 파워쿼리 크롤링 목차 바로가기
영상 강의
큰 화면으로 보기

예제파일 다운로드

오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.

이번 강의는 별도의 예제파일 없이 비어있는 통합문서에서 시작합니다.

  • [엑셀고급] 엑셀 파워쿼리 크롤링 기초 실습
    완성파일

.

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

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


웹 페이지 구성요소 살펴보기

모든 크롤링의 시작은 크롤링 할 대상 웹페이지의 구조를 살펴보는 것 입니다. 따라서 이번 강의에서 크롤링할 웹페이지의 구조를 하나씩 살펴보겠습니다.

페이지설명 URL링크
비트코인시세 https://kr.investing.com/crypto/
기술요약정보 https://kr.investing.com/indices/investing.com-btc-usd
오빠두Tip : 영상강의에서는 기술요약정보 페이지에서 비트코인 시세와 기술요약정보를 동시에 가져왔지만, 현재 웹페이지 구조가 변경되어 비트코인 시세와 기술요약 정보를 각각 다른 페이지에서 크롤링합니다.
  1. 비트코인 시세 페이지 구조 살펴보기 : 각 가상화폐의 현재 시세를 보여주는 웹사이트로 이동합니다. 이후 키보드 F12키를 누르면 개발자도구가 실행됩니다.
    웹 페이지 구조 분석 개발자도구
    인터넷 브라우저에서 F12키를 누르면 개발자도구가 실행됩니다.
    오빠두Tip : 사용중인 브라우저마다 개발자도구의 표시형식이 조금씩 다를 수 있습니다.
  2. 크롬 브라우저 기준, 개발자도구의 좌측 상단을 보면 '마우스 커서모양'의 버튼이 있습니다. 버튼을 클릭한 뒤, 크롤링할 데이터 위로 마우스 커서를 이동하면 데이터의 HTML 구조를 확인할 수 있습니다.

    HTML 구조 분석
    HTML 개체 선택 버튼을 클릭 후 마우스 커서를 개체 위로 이동합니다.

  3. 커서를 데이터 위로 가져간 상태에서 클릭하면 해당 개체의 HTML 코드를 확인할 수 있습니다. 비트코인의 가격은 현재 <td> 태그 안에 표시되는 것을 확인할 수 있습니다.
    TABLE 데이터 확인
    이후 마우스를 클릭하면 해당 개체의 HTML 코드를 볼 수 있습니다.
    오빠두Tip : 엑셀에서 제공하는 파워쿼리는 '<table>' 형태로 이루어진 데이터만 가져올 수 있습니다. 따라서 <table>, <tr>, <td>.. 형태로 이루어진 데이터만 크롤링할 수 있는 것을 주의합니다. (파워BI에서 제공하는 파워쿼리는 더 다양한 형태의 데이터를 크롤링할 수 있습니다.)
  4. 비트코인 기술요약 페이지 구조 살펴보기 : 이번에는 기술요약을 보여주는 페이지 구조를 살펴보기 위해 아래 링크로 이동합니다.
  5. 마찬가지로 F12키를 눌러 개발자도구를 실행한 뒤, 크롤링할 데이터의 HTML 구조를 살펴봅니다. 기술요약 정보도 table 안에 입력되어 있는 것을 확인할 수 있습니다.

    비트코인 기술요약 TABLE 구조
    기술요약 표의 구조도 Table로 작성된 것을 확인할 수 있습니다.

파워쿼리 크롤링 실습

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

  1. 웹에서 불러올 데이터 선택하기 : 새로운 통합문서를 실행한 뒤, [데이터] - [데이터 가져오기 및 변환] - [웹] 버튼을 클릭합니다. 버튼을 클릭하면 '웹에서 데이터 불러오기' 대화상자가 실행됩니다.
    엑셀 파워쿼리 웹에서 크롤링
    데이터 - 웹 버튼을 클릭해서 웹에서 불러오기 대화상자를 실행합니다.
    오빠두Tip : 사용 중인 엑셀 버전마다 화면구성이 조금씩 다를 수 있습니다. 엑셀 파워쿼리를 추가기능으로 설치해야 하는 2013 이전 버전일 경우, [파워쿼리] 탭 - [웹에서] 버튼을 클릭합니다.
  2. 크롤링할 웹페이지의 URL 주소를 입력합니다. 아래 URL 주소를 입력 후, [확인] 버튼을 클릭합니다.
  3. URL 주소 입력 후 조금만 기다리면 '탐색창'이 실행되면서 웹페이지 안에 있는 테이블 목록이 표시됩니다. 테이블 목록에서 파워쿼리로 출력할 테이블을 선택 후 [데이터 변환] 버튼을 클릭합니다.
    파워쿼리 크롤링 탐색창 데이터 변환
    불러올 테이블을 선택 후, [데이터 변환] 버튼을 클릭합니다.

    오빠두Tip : 왼쪽 상단에 '여러 항목 선택' 옵션을 활성화하면 동시에 여러 테이블을 파워쿼리로 불러올 수 있습니다.
  4. 파워쿼리 편집기에서 데이터 가공하기 : 파워쿼리 편집기가 실행되면서 방금 전에 선택했던 테이블이 쿼리로 만들어집니다.

    웹 페이지 파워쿼리 불러오기
    선택한 표가 파워쿼리 편집기로 불러와집니다.

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

    값 바꿀 데이터 영역 선택
    값을 바꿀 데이터 영역을 선택합니다.

  6. 그 상태에서 [변환] - [값 바꾸기]로 이동한 뒤, 각 값을 하나씩 변경합니다.
    파워쿼리 값 바꾸기
    변환 - 값 바꾸기에서 값을 하나씩 변경합니다.
    찾을 값 바꿀 항목
    적극 매수 5
    매수 4
    중립 3
    매도 2
    적극 매도 1
    오빠두Tip : 고급 옵션에서 '전체 셀 내용 일치'를 활성화 하면 전체 셀 내용이 일치하는 항목만 값을 바꿀 수 있습니다. 예를 들어 '적극 매도'와 '매도'가 있는 상태에서 '매도'의 값을 '4'로 바꾸면 '적극 4', '4' 로 변경되는데 '전체 셀 내용 일치'를 활성화하면 이런 문제를 방지할 수 있습니다.
  7. 데이터 형식 변경하기 : 파워쿼리는 각 필드마다 데이터 형식을 지정합니다. 따라서 쿼리를 가공하고 출력하는 단계마다 각 필드의 데이터 형식이 올바른지 확인하는 습관을 갖는 것이 좋습니다.
  8. 우리가 방금 변경한 값은 텍스트 형식이 아닌 숫자 형식입니다. 따라서 각 필드의 데이터 형식을 10진수로 변경합니다.
    엑셀 파워쿼리 데이터 형식 변경
    머리글 좌측의 데이터 형식을 10진수로 변경합니다.
    오빠두Tip : 사용 중인 엑셀 버전에 따라, 머릿글 왼쪽에 데이터형식이 보이지 않을 수 있습니다. 그럴 경우, [변환] - [데이터 형식] 에서 데이터 종류를 변경합니다.
    파워쿼리 데이터 형식 바꾸기 10진수
  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. 표를 선택 후, [쿼리] - [속성]으로 이동합니다. 이후 새로고침 옵션에서 '다른 작업하면서 새로 고침' 옵션을 비활성화 후 [확인] 버튼을 클릭합니다. 이제 버튼을 클릭하면 쿼리가 모두 새로고침 된 후, 새로고침이 다 되었다는 안내메시지가 출력됩니다.

    엑셀 파워쿼리 크롤링 다른 작업하면서 새로고침
    쿼리 속성에서 '다른 작업하면서 새로고침'을 비활성화하면, 쿼리 업데이트를 마친 후 매크로가 실행됩니다.

5 3 투표
게시글평점
guest
3 댓글
Inline Feedbacks
모든 댓글 보기
삿갓삿갓김
삿갓삿갓김
2021년 8월 9일 6:34 오전
게시글평점 :
     

유익한 내용 잘 봤씁니다

남성현
남성현
2021년 8월 9일 5:54 오후
게시글평점 :
     

신세계를 경험합니다~^^

이정남
이정남
2021년 8월 18일 8:00 오후
게시글평점 :
     

감사합니다
필요한 공부였는데^ ^

3
0
여러분의 생각을 댓글로 남겨주세요.x