엑셀 파워쿼리, 웹데이터 추출하기 | 초보자를 위한 네이버 증권 검색 실전 예제
파워쿼리 웹 데이터 추출 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [엑셀고급] 엑셀 파워쿼리, 웹 데이터 크롤링 : 네이버 증권 예제예제파일[엑셀고급] 엑셀 파워쿼리, 웹 데이터 크롤링 : 네이버 증권 예제완성파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
개발자도구로 웹 페이지 분석하기
웹 브라우저에서 기본으로 제공하는 개발자도구를 활용하면, 웹 페이지에서 추출하고자 하는 데이터의 위치와 그 데이터를 불러오는 출처를 쉽게 확인할 수 있습니다.
- 개발자도구 실행하기 : 오늘 강의에서는 네이버 증권에서 제공하는 주식 데이터를 파워쿼리를 활용해 엑셀로 불러오는 방법을 알아보겠습니다. 먼저 아래 링크를 클릭하여 네이버 증권 홈페이지로 이동합니다.
네이버 증권 홈페이지로 이동합니다. - 웹 브라우저를 실행한 상태에서, 단축키 F12키를 누르면 개발자도구가 실행됩니다. 또는 단축키 Ctrl + Shift + i 를 동시에 누르거나, [옵션] - [도구 더보기] - [개발자 도구]를 직접 클릭하여 개발자도구를 실행할 수 있습니다.
F12키를 눌러 개발자도구를 실행합니다. 오빠두Tip : 만약 F12키를 눌렀을 때, 개발자도구가 실행되지 않는다면 노트북 키보드의 펑션키 설정을 확인해보세요! - 네트워크 도구로 웹 페이지 분석하기 : 개발자도구 화면에서 [네트워크] 탭으로 이동한 후, 검색에서 '삼성전자'를 검색하여 삼성전자의 주식 정보 페이지로 이동합니다. 또는 아래 링크를 클릭해서 삼성전자의 주식 정보 페이지로 이동합니다.
네트워크 탭을 선택한 후, 삼성전자 주식 정보 페이지로 이동합니다. - 삼성전자 주식 정보 페이지에서 표시할 항목으로 [차트]를 선택하면, 페이지를 출력할 때 사용한 모든 네트워크 정보(이미지, 문서, 데이터 등..)이 네트워크 탭에 표시됩니다.
페이지에서 표시할 항목으로 차트를 선택합니다. - 해당 페이지에 사용된 요소 중에서 우리에게 필요한 항목은 '주식 데이터' 입니다. 따라서, 네트워크 탭의 항목 중 [Fetch/XHR]을 선택하여 'HTTP Request'로 받아온 데이터 항목만 표시합니다.
네트워크 탭에서 표시할 항목으로 Fetch/XHR을 선택합니다. 오빠두Tip : XHR은 XMLHttpRequest의 약자입니다. HttpRequest(HTTP요청)을 사용하면, 파워쿼리 외에도 VBA 매크로를 활용한 다양한 크롤링 자동화 보고서를 작성할 수 있습니다.
- 이후 [미리보기]를 선택하여 해당 URL에서 반환되는 결과값을 표시한 후, 왼쪽 목록에서 URL을 하나씩 클릭해서 '주식 정보'를 반환하는 URL를 찾습니다. 목록에서 "siseJson.." 으로 시작하는 항목을 선택하면, 주식 데이터가 반환되는 것을 확인할 수 있습니다.
항목을 하나씩 클릭해서, 주식 정보를 반환하는 URL 주소를 확인합니다. - URL 주소 및 반환되는 데이터 확인 : 주식 데이터를 반환하는 항목을 찾았으면, [머리글]을 선택하여 전체 URL 주소를 확인합니다.
https://api.finance.naver.com/siseJson.naver?symbol=005930&requestType=1&startTime=20230625&endTime=20231130&timeframe=day
항목을 찾은 후, 머리글을 클릭하면 전체 URL 주소를 확인할 수 있습니다. - 복사한 URL 주소를 복사하여 브라우저의 새 탭에 붙여넣기하면, 주식 정보가 올바르게 반환되는 것을 확인할 수 있습니다.
새 탭을 추가한 후, URL 주소를 붙여넣기해서 주식 데이터가 잘 반환되는 것을 확인합니다. - 찾은 URL 주소를 분석하는 방법은 아래 API 기초 사용법, 10분 총정리 강의에서 자세히 정리했으니, 아래 관련 영상을 참고해주세요!
파워쿼리로 웹 데이터 불러오기
파워쿼리를 활용하면, 개발자도구로 찾은 URL 주소에서 반환되는 웹 데이터를 엑셀로 편리하게 추출할 수 있습니다.
- 파워쿼리로 불러올 URL 확인하기 : 홈페이지에 올려드린 예제파일을 실행한 후, A8셀에 아래 수식을 입력하면 URL 주소가 작성됩니다.
="https://api.finance.naver.com/siseJson.naver?symbol=005930&requestType=1&startTime=20231001&endTime=20231031&timeframe=day"
예제파일을 실행한 후, A8셀 안에 URL 주소를 수식 형태로 작성합니다. - 이제 붙여넣기한 URL 주소에서, 실시간으로 주식정보를 반환하기 위해 필요한 항목을 하나씩 수정합니다. 수정할 항목은 다음과 같습니다.
- symbol: 종목번호 (6자리 숫자)
- startTime: 시작일 (8자리 숫자, yyyymmdd)
- endTime: 종료일 (8자리 숫자, yyyymmdd)
- timeframe: 단위 (day, week, month 중 하나) - 따라서, A8셀에 작성된 수식을 다음과 같이 수정하면 예제파일에서 B2(종목번호), B3(시작일), B4(종료일), B5(단위)의 값을 변경했을 때 URL주소가 실시간으로 변경됩니다.
="https://api.finance.naver.com/siseJson.naver?symbol="&C2&"&requestType=1&startTime="&C3&"&endTime="&C4&"&timeframe="&B5
종목번호, 시작일, 종료일을 변경하면 URL 주소가 같이 업데이트 됩니다. 오빠두Tip : &기호와 큰따옴표(")로 문장 안의 단어를 다른 셀과 편리하게 연동하는 방법은 영상 강의를 참고하세요! - 원활한 실습을 위해 종목번호와 시작일, 종료일, 단위를 다음과 같이 수정합니다.
- 종목번호: 005930
- 시작일: 2023/10/01
- 종료일 : 2023/10/31
- 단위: day - 파워쿼리로 웹 데이터 불러오기 : [데이터] 탭 - [데이터 가져오기] - [기타 원본에서] - [웹]을 차례대로 클릭하여 웹에서 불러오기 대화상자를 실행합니다. 이후 아래 URL 주소를 입력창에 붙여넣기 한 후, [확인] 버튼을 클릭합니다.
https://api.finance.naver.com/siseJson.naver?symbol=005930&requestType=1&startTime=20231001&endTime=20231031&timeframe=day
[데이터] 탭 - [웹]을 선택한 후, URL 주소를 입력창에 붙여넣기합니다. - 입력한 URL 주소에서 반환되는 결과 데이터의 경우 "Content-Type"이 "application/json;charset=utf-8" 으로 JSON으로 되어 있습니다. 하지만 실제로 반환되는 데이터의 경우 JSON이 아니므로 다음과 같은 오류가 출력됩니다. [편집] 버튼을 클릭합니다.
연결할 수 없음 오류가 나오면, [편집] 버튼을 클릭합니다. 오빠두Tip : JSON 데이터 형식에 대한 설명은 위캔두 멤버십 독점으로 제공되는 "API 사용법 총정리 특강"에서 자세히 다루었으니, 아래 영상 강의를 참고해주세요!👇
- 새로운 창이 나오면 [파일 열기 형식]을 "텍스트 파일"로 변경한 후, [확인] 버튼을 클릭합니다.
열기 형식을 '텍스트 파일'로 변경 후, [확인] 버튼을 클릭합니다. - 데이터 로드 대화상자가 나오면, [데이터 변환] 버튼을 클릭하여 파워쿼리 편집기를 실행합니다.
[데이터 변환]을 클릭하여 파워쿼리 편집기를 실행합니다.
파워쿼리로 받아온 네이버 증권 데이터 가공하기
파워쿼리 편집기를 실행한 후, 다음 단계에 따라 네이버 증권의 주식 데이터를 가공합니다. 본 과정에서는 네이버 주식 정보를 가공하기 위한 핵심 과정만 다뤘습니다. 파워쿼리를 실무에서 활용하는 다양한 예제 및 체계적인 설명은 위캔두 멤버십 회원에게 제공되는 파워쿼리 기초 마스터 챌린지 특강을 확인하세요!👇
- 불필요한 항목 제거하기 : 받아온 데이터 중 불필요한 항목(빈 항목)을 필터로 제거합니다. 필터 버튼을 클릭한 후, (비어있음)과 공백으로 표시되는 항목을 체크 해제하여 필터를 적용합니다.
필터를 확장한 후, 비어있는 항목을 목록에서 제외합니다. 오빠두Tip : 글자 길이를 기준으로 필터를 적용하면 예측 불가능한 예외 상황까지 고려하여 필터를 적용할 수 있습니다. 글자 길이를 기준으로 필터를 적용하는 방법은 영상 강의를 참고하세요! - 데이터에서 불필요한 기호를 하나씩 제거합니다. [변환] 탭 - [값 바꾸기]를 선택한 후, 다음 기호들을 차례대로 제거합니다.
① [ → 여는 대괄호
② ], → 닫는 대괄호+쉼표
③ " → 큰 따옴표
④ ' → 작은 따옴표
⑤ ] → 닫는 대괄호
[변환] 탭 - [값 바꾸기]에서 불필요한 기호를 하나씩 제거합니다. - 기호를 하나씩 제거하면, 아래 그림과 같이 데이터가 깔끔하게 정제됩니다.
데이터가 깔끔하게 정제됩니다. - 구분기호로 나눈 후 표 완성하기 : 이제 쉼표를 기준으로 열을 분할해서 표로 변환합니다. [변환] 탭 - [열 분할] - [구분기호 기준]을 선택한 후, 구분기호로 "쉼표"를 선택하고 [확인] 버튼을 클릭하면 쉼표를 기준으로 나누어진 표가 완성됩니다.
쉼표를 기준으로 열을 분할합니다. - 만들어진 표에서 첫 행에 작성된 값은 머리글이므로, [변환] 탭 - [첫 행을 머리글로 사용]을 클릭하여 첫 행의 값을 머리글로 승격합니다.
[첫 행을 머리글로 사용]을 클릭하여 1행을 머리글로 승격합니다. - 머리글 왼쪽에 표시되는 아이콘으로 데이터 형식이 모두 올바르게 설정되었는지 확인합니다. 날짜의 경우 '숫자'가 아닌 '날짜' 데이터이므로, 데이터 형식을 '날짜'형식으로 변경합니다.
각 열의 데이터 형식을 올바른 데이터 형식으로 변경합니다. - 머리글 왼쪽의 데이터 형식 아이콘은 엑셀 2021 이후 버전부터 제공됩니다. 만약 데이터 형식 아이콘이 안 보일 경우, [홈] 탭 또는 [변환] 탭에서 '데이터 형식'을 확인할 수 있습니다.
2019 이전 버전에서는 [변환] 또는 [홈] 탭에서 데이터 형식을 변경할 수 있습니다. - 완성된 쿼리를 시트에 출력하기 : 마지막 단계로 완성된 쿼리를 시트에 출력하겠습니다. [파일] 탭 - [닫기 및 다음으로 로드]를 선택하여 '데이터 가져오기' 대화상자를 실행합니다.
[파일] 탭 - [닫기 및 다음으로 로드]로 완성된 쿼리를 출력합니다. - [데이터 가져오기] 대화상자가 실행되면, 표 형식으로 기존 워크시트의 적절한 위치를 지정한 후 [확인] 버튼을 클릭합니다.
[데이터 가져오기] 대화상자가 실행되면, '기존 워크시트'에 표 형식으로 출력합니다. - 잠시만 기다리면, 주식 정보가 엑셀 시트 위에 올바르게 표시됩니다.
네이버 증권의 주식 데이터가 시트 위에 출력됩니다.
시트 데이터와 쿼리를 연동해서 실시간 주식 정보 조회하기
이제 마지막 단계로, 예제파일에서 종목번호와 시작일, 종료일을 변경하면 해당 주식 정보가 실시간으로 업데이트되는 쿼리를 완성합니다.
- 표의 특정 값을 반환하는 수식 만들기 : 먼저 실시간으로 변경되는 URL 주소를 표로 변경한 후, 파워쿼리로 불러옵니다. 예제파일에서 URL주소가 작성된 A7:A8 범위를 선택한 후, [삽입] - [표]를 클릭하여 범위를 표로 변환합니다.
URL 주소를 작성한 범위를 표로 변환합니다. - 표를 선택한 후, [테이블 디자인] 탭에서 표의 이름을 'tblURL'로 변경합니다.
표 이름을 "tblURL"로 변경합니다. - 표 이름을 변경하였으면, [데이터] 탭 - [테이블/범위에서]를 클릭하여 URL 주소가 작성된 표를 파워쿼리 편집기로 불러옵니다.
표를 파워쿼리 편집기로 불러옵니다. - 파워쿼리 편집기에서 우측의 '적용된 단계'를 확인합니다. 파워쿼리 버전에 따라, 최신 버전에서는 '변경된 유형'이 자동으로 적용됩니다. 만약 적용된 단계에 '변경된 유형'이 있다면, "X"를 클릭하여 변경된 유형 단계를 제거합니다.
적용된 단계에 '변경된 유형'이 있을 경우, 단계를 삭제합니다. - tblURL의 '원본' 단계를 선택하면, 수식입력줄에 다음과 같이 수식이 작성된 것을 확인할 수 있습니다.
= Excel.CurrentWorkbook(){[Name="tblURL"]}[Content]
단계에서 원본을 선택한 후, [수식입력줄]에 작성된 수식을 확인합니다. 오빠두Tip : 만약 수식입력줄이 보이지 않는다면, [보기] 탭에서 '수식입력줄' 옵션을 활성화합니다. - 파워쿼리 함수에서 중괄호("{}")는 행을, 대괄호("[]")는 필드를 선택합니다 따라서 작성된 수식을 단계별로 해석하면 다음과 같습니다.
① =Excel.CurrentWorkbook() → 현재 실행중인 통합문서에 작성된 표 범위 목록을 반환합니다.
② {[Name="tblURL"]} → Name 필드의 값이 tblURL인 행을 반환합니다.
③ [Content] → 반환된 행에서 Content 필드를 반환합니다.오빠두Tip : M함수에서 중괄호, 대괄호로 원하는 필드를 선택하는 방법은 위캔두 멤버십 독점으로 제공되는 파워쿼리 기초 총정리 특강에서 자세히 다루었습니다.👇
- 현재 반환된 표에서 URL 주소는 ① URL 필드에서 ② 첫번째 행을 선택하면 됩니다. 따라서 작성된 수식을 다음과 같이 변경하면, URL 필드의 첫번째 행이 선택되면서 URL 주소가 값으로 반환됩니다.
= Excel.CurrentWorkbook(){[Name="tblURL"]}[Content][URL]{0}
'[URL] : URL 필드를 선택합니다.
'{0} : 첫번째 행을 선택합니다. (파워쿼리 배열은 0부터 시작)
수식 뒤로 [URL]{0}을 추가하면, URL 필드의 첫번째 행이 선택됩니다. - 시트 데이터를 쿼리에 연동하기 : 이제 URL 주소를 반환하는 수식을 확인하였으니, 해당 수식을 복사한 후 기존에 주식 데이터를 반환하는 쿼리로 이동합니다. 주식 데이터를 반환하는 쿼리를 선택한 후, [홈] 탭 - [고급 편집기]를 클릭하여 고급편집기를 실행합니다.
기존 주식데이터를 반환하는 쿼리를 선택한 후, [고급 편집기]를 실행합니다. - 고급편집기가 실행되면, "원본 = ..." 이 시작되는 윗 단계로 복사했던 수식을 붙여넣기한 후, 반환되는 값을 url 이라는 변수로 작성합니다. 이후 붙여넣기한 수식 뒤에 쉼표를 추가하여 다음 단계로 진행할 수 있도록 합니다.
원본의 이전 단계로, url 이라는 변수를 추가합니다. 오빠두Tip : 고급편집기는 '쉼표'가 다음 단계로 넘어가는 동작을 합니다. 따라서, 붙여넣기 한 수식 뒤로 쉼표(,) 기호를 반드시 추가하는 것을 주의합니다. - 이제 원본으로 받아오는 URL 주소를 방금 작성한 url 이라는 변수로 변경합니다. 파워쿼리를 대/소문자를 구분하므로, 반드시 대/소문자를 일치하게 작성하는 것을 주의하세요.
기존에 원본으로 받아오던 URL 주소를, 새롭게 만든 url 변수로 변경합니다. - 네이버 증권 실시간 업데이트 보고서 완성 : 고급편집기를 모두 작성한 후, [완료] 버튼을 클릭하여 고급편집기를 종료합니다. 수식에 이상이 없다면, 기존과 동일하게 쿼리가 잘 실행됩니다. 만약 수식에 오탈자가 있거나, 기호를 빼 먹었어나, 표 이름을 잘못 작성하는 등의 실수가 있을 경우 오류가 발생됩니다. 만약 오류가 발생한다면, 홈페이지에 올려드린 완성파일 또는 아래 완성된 코드를 참고하여 오탈자는 없는지 다시 한번 확인해보세요.
url = Excel.CurrentWorkbook(){[Name="tblURL"]}[Content][URL]{0},
원본 = Table.FromColumns({Lines.FromBinary(Web.Contents(url))}),
고급 편집기를 종료한 후, 기존과 동일하게 네이버 증권에서 주식 정보를 잘 받아오는 것을 확인합니다. - 쿼리가 잘 실행되었으면, [파일] 탭 - [닫기 및 로드]를 클릭하여 파워쿼리 편집기를 종료합니다. 이후 종목번호와 시작일, 종료일을 변경한 후 표를 우클릭 - [새고로침]으로 쿼리를 갱신해보세요! 네이버 증권과 연계되어 주식 정보가 실시간으로 갱신되는 파워쿼리 보고서가 완성됩니다.
쿼리로 출력된 표를 우클릭한 후, [새로고침]을 클릭해서 네이버 증권의 주식 정보가 실시간으로 갱신됩니다. 오빠두Tip : 표를 새로고침했을 시, 표의 열너비가 바뀌어서 불편할 경우 표를 선택 → [테이블 디자인] → [속성] 에서 '열 너비 조정' 옵션을 체크 해제합니다.
완성파일 실행방법
이번 수업의 완성파일은 파워쿼리를 제공하는 윈도우의 엑셀 2016 이후 버전에서 사용가능합니다.
- 완성파일을 다운받은 후, 좌측 상단에 표시되는 [편집 사용]과 [콘텐츠 사용] 버튼을 각각 클릭하여 통합문서를 실행합니다. [편집 사용] 버튼은 시트의 내용을 사용자가 수정할 수 있도록 활성화시키며, [콘텐츠 사용] 버튼은 통합문서에 사용한 파워쿼리를 활성화합니다.
[편집사용] 및 [콘텐츠 사용] 버튼을 클릭하여 완성 파일을 실행합니다. - '완성 예제'에 사용된 쿼리를 우클릭한 후, [새로 고침]을 클릭하여 쿼리를 새로고침합니다.
쿼리를 우클릭 - 새로고침으로 쿼리를 갱신합니다. - [개인 정보 수준] 대화상자가 실행되면, '이 파일에 대한 개인 정보 수준 검사를 무시합니다. 개인 정보 수준을 무시하면 중요하거나 기밀인 데이터가 권한 없는 사람에게 노출될 수 있습니다' 확인란을 체크한 후 [저장] 버튼을 클릭합니다.
확인란을 체크한 후, [저장] 버튼으로 쿼리를 실행합니다. - 버튼을 클릭하면 쿼리가 새로고침 되며 네이버 증권의 주식 데이터가 갱신됩니다.
네이버 증권 데이터가 갱신됩니다.
웹 데이터 불러오기 후, '파일열기형식' 옵션이 보이지 않을 경우
만약 잘못된 JSON 데이터 형식으로 인한 '파일열기형식' 옵션이 보이지 않을 경우, 파워쿼리 함수를 직접 입력하여 문제를 쉽게 해결할 수 있습니다.
- [데이터] 탭 - [데이터 가져오기] - [기타 원본에서] - [빈 쿼리]를 선택하여 비어있는 쿼리를 생성합니다.
[데이터] 탭 - [데이터 가져오기] - [기타 원본에서] - [빈 쿼리]로 빈 쿼리를 생성합니다. - 수식입력줄에 아래 M함수를 입력하면 URL주소에서 반환되는 데이터가 텍스트 형식으로 출력됩니다.
= Table.FromColumns({Lines.FromBinary(Web.Contents("https://api.finance.naver.com/siseJson.naver?symbol=005930&requestType=1&startTime=20231001&endTime=20231031&timeframe=day"))})
수식입력줄에 함수를 입력하면 네이버 증권 데이터가 파워쿼리 편집기에 출력됩니다. 오빠두Tip : 만약 쿼리편집기에 수식입력줄이 보이지 않을 경우, [보기] 탭 - [수식 입력줄]을 선택하여 수식입력줄을 활성화합니다. - 이후 과정은 영상 강의 또는 게시글을 참고하여 단계별로 진행합니다.
방법 좀 알려주세요 ㅠㅠㅠ
[데이터] 탭 - [데이터 가져오기] - [기타 원본에서] - [빈 쿼리]
로 비어있는 쿼리를 실행합니다.
이후 아래 함수를 수식입력줄에 작성해세요.
= Table.FromColumns({Lines.FromBinary(Web.Contents("URL주소"))})
그러면 강의에서 사용한 표 형태로 데이터가 출력될겁니다.제시해드린 답변이 문제를 해결하시는데 도움이 되었길 바랍니다. 감사합니다.
엑셀 고민은 오빠두 엑셀에서 답을 찾을 수 있고 무궁무진한 활용 방법에 놀랍니다.
완성파일 실행중에 엑셀 프로그램이 퉁기는 문제가 발생합니다.
해결 방법이 있을가요?
강의에서도 안내해드린 것 처럼, 셀 안에 작성 가능한 최대 글자수인 30,000자 이상이 출력되도록 범위를 지정하면 엑셀이 강제 종료됩니다.
그럴 경우 기간을 일->주로 바꾸거나, 더 짧은 기간으로 변경 후 사용해보시길 바랍니다.
편집을 누르면 기본란에 캡쳐한거같이 파일형식 누르는곳이 없던데
해결할방법이있을까요
아래 '파일 원본' 영역을 확인해보세요. :)
감사합니다.
파일원본누르면 사진처럼 나오던데
엑셀의 설치가 잘못된게 문제일까요?
혹시 엑셀 2016 버전을 사용하고 계실까요? 그러시다면, 아래 단계로 쿼리를 추가해보시길 바랍니다.
[데이터] - [데이터 가져오기] - [기타 원본에서] - [빈 쿼리] →
수식입력줄에 아래 수식 입력
= Table.FromColumns({Lines.FromBinary(Web.Contents("URL 주소"))})
만약 수식입력줄이 안보일 경우, 쿼리편집기의 [보기] 탭 → 수식입력줄 체크 후, 위 수식을 입력해보세요. 그러면 바로 문제가 해결될겁니다. :)남겨드린 답변이 문제를 해결하시는데 도움이 되었길 바랍니다. 감사합니다.
만약 매크로가 포함된 파일을 사용중이시라면, 우선은 파일을 제작한 담당자분께 문의를 해 보시는게 가장 좋을 것 같습니다.
추가로 시트를 통째로 옮기지 마시고 범위를 복사 → 붙여넣기 하는 방식으로 데이터를 옮기면 문제가 해결될 듯 하니 확인해보시길 바랍니다.
감사합니다.
해외종목 ETF의 경우에도 종목별 로이터 코드가 있습니다.
로이터 코드로 검색하도록 쿼리를 작성해보시길 바랍니다.
감사합니다.