엑셀 파워쿼리, 웹데이터 추출하기 | 초보자를 위한 네이버 증권 검색 실전 예제

실습 가이드

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

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

개발자도구로 웹 페이지 분석하기

웹 브라우저가 기본으로 제공하는 개발자도구를 활용하면, 추출하려는 데이터의 위치와 출처 URL을 손쉽게 확인할 수 있습니다.

1. 개발자도구 실행하기 : 이번 강의에서는 네이버 증권에서 제공하는 주식 데이터를 파워쿼리로 엑셀에 불러오는 방법을 살펴보겠습니다. 먼저 아래 링크를 클릭하여 네이버 증권 홈페이지로 이동합니다.
   https://finance.naver.com/

   

2. 웹 브라우저를 실행한 상태에서 단축키 F12키를 누르면 개발자도구가 실행됩니다. 또는 Ctrl + Shift + i를 동시에 누르거나, [옵션] - [도구 더보기] - [개발자 도구]를 클릭해서도 실행할 수 있습니다.

   

   오빠두Tip : F12키를 눌렀을 때 개발자도구가 실행되지 않는다면, 노트북 키보드의 펑션키 설정을 확인해보세요!
3. 네트워크 도구로 웹 페이지 분석하기 : 개발자도구 화면에서 [네트워크] 탭으로 이동한 후, 검색창에 '삼성전자'를 입력하여 삼성전자의 주식 정보 페이지로 이동합니다. 또는 아래 링크를 클릭해 바로 이동할 수 있습니다.
   https://finance.naver.com/item/main.naver?code=005930

   

4. 삼성전자 주식 정보 페이지에서 표시할 항목으로 [차트]를 선택하면, 페이지를 출력할 때 사용된 모든 네트워크 정보(이미지, 문서, 데이터 등)가 네트워크 탭에 표시됩니다.

   

5. 해당 페이지에서 우리에게 필요한 항목은 '주식 데이터'입니다. 따라서 네트워크 탭의 항목 중 [Fetch/XHR]을 선택하여 'HTTP Request'로 받아온 데이터 항목만 표시합니다.

   

   오빠두Tip : XHR은 XMLHttpRequest의 약자입니다. HTTP Request를 활용하면 파워쿼리뿐만 아니라 VBA 매크로와 결합한 다양한 크롤링 자동화 보고서도 만들 수 있습니다.
   
6. 이제 [미리보기]를 선택해 해당 URL이 반환하는 결과값을 확인한 다음, 왼쪽 목록에서 URL을 하나씩 클릭하며 '주식 정보'를 반환하는 URL을 찾습니다. 목록에서 "siseJson.."으로 시작하는 항목을 선택하면, 주식 데이터가 반환되는 것을 확인할 수 있습니다.

   

7. URL 주소 및 반환되는 데이터 확인 : 주식 데이터를 반환하는 항목을 찾았다면, [머리글]을 선택해 전체 URL 주소를 확인합니다.
   https://api.finance.naver.com/siseJson.naver?symbol=005930&requestType=1&startTime=20230625&endTime=20231130&timeframe=day

   

8. 복사한 URL 주소를 브라우저의 새 탭에 붙여넣기하면, 주식 정보가 정상적으로 반환되는 것을 확인할 수 있습니다.

   

9. 찾은 URL 주소를 분석하는 방법은 아래 API 기초 사용법, 10분 총정리 강의에서 자세히 정리했으니 함께 참고해보세요.
   

파워쿼리로 웹 데이터 불러오기

파워쿼리를 활용하면 개발자도구로 찾은 URL 주소가 반환하는 웹 데이터를 엑셀로 손쉽게 추출할 수 있습니다.

1. 파워쿼리로 불러올 URL 확인하기 : 홈페이지에서 제공한 예제파일을 실행한 후, A8셀에 아래 수식을 입력하면 URL 주소가 작성됩니다.
   ="https://api.finance.naver.com/siseJson.naver?symbol=005930&requestType=1&startTime=20231001&endTime=20231031&timeframe=day"

   

2. 이제 붙여넣은 URL 주소에서, 실시간으로 주식 정보를 받아오는 데 필요한 항목을 하나씩 수정합니다. 수정할 항목은 다음과 같습니다.
   - symbol: 종목번호 (6자리 숫자)
   - startTime: 시작일 (8자리 숫자, yyyymmdd)
   - endTime: 종료일 (8자리 숫자, yyyymmdd)
   - timeframe: 단위 (day, week, month 중 하나)
3. 따라서 A8셀의 수식을 다음과 같이 수정하면, 예제파일의 B2(종목번호), B3(시작일), B4(종료일), B5(단위) 값을 변경할 때마다 URL 주소가 실시간으로 갱신됩니다.
   ="https://api.finance.naver.com/siseJson.naver?symbol="&C2&"&requestType=1&startTime="&C3&"&endTime="&C4&"&timeframe="&B5

   

   오빠두Tip : &기호와 큰따옴표(")를 활용해 문장 안의 단어를 다른 셀과 연동하는 방법은 영상 강의에서 자세히 다루었으니 함께 참고해보세요!
4. 원활한 실습을 위해 종목번호와 시작일, 종료일, 단위를 다음과 같이 수정합니다.
   - 종목번호: 005930
   - 시작일: 2023/10/01
   - 종료일 : 2023/10/31
   - 단위: day
5. 파워쿼리로 웹 데이터 불러오기 : [데이터] 탭 - [데이터 가져오기] - [기타 원본에서] - [웹]을 차례대로 클릭하여 '웹에서 불러오기' 대화상자를 실행합니다. 이후 아래 URL 주소를 입력창에 붙여넣은 후 [확인] 버튼을 클릭합니다.
   https://api.finance.naver.com/siseJson.naver?symbol=005930&requestType=1&startTime=20231001&endTime=20231031&timeframe=day

   

6. 입력한 URL이 반환하는 결과 데이터의 "Content-Type"은 "application/json;charset=utf-8"로 JSON 형식으로 표시됩니다. 하지만 실제로 반환되는 데이터는 JSON 형식이 아니므로 다음과 같은 오류가 출력됩니다. 이때 [편집] 버튼을 클릭합니다.
   
   오빠두Tip : JSON 데이터 형식에 대한 자세한 설명은 위캔두 멤버십 독점으로 제공되는 "API 사용법 총정리 특강"에서 다루었으니, 아래 영상 강의를 참고해주세요!👇
   
7. 새 창이 나타나면 [파일 열기 형식]을 "텍스트 파일"로 변경한 후 [확인] 버튼을 클릭합니다.
   
8. '데이터 로드' 대화상자가 나오면 [데이터 변환] 버튼을 클릭하여 파워쿼리 편집기를 실행합니다.
   

파워쿼리로 받아온 네이버 증권 데이터 가공하기

파워쿼리 편집기를 실행했으면, 다음 단계에 따라 네이버 증권의 주식 데이터를 가공합니다. 본 과정에서는 네이버 주식 정보를 가공하기 위한 핵심 단계만 다룹니다. 파워쿼리를 실무에서 활용하는 다양한 예제와 체계적인 설명은 위캔두 멤버십 회원에게 제공되는 파워쿼리 기초 마스터 챌린지 특강에서 확인하세요!👇

1. 불필요한 항목 제거하기 : 받아온 데이터에서 불필요한 항목(빈 값)을 필터로 제거합니다. 필터 버튼을 클릭한 후, (비어있음)과 공백으로 표시되는 항목의 체크를 해제하여 필터를 적용합니다.

   

   오빠두Tip : 글자 길이를 기준으로 필터를 적용하면 예측하기 어려운 예외 상황까지 빠짐없이 처리할 수 있습니다. 글자 길이를 기준으로 필터를 적용하는 방법은 영상 강의에서 자세히 다루었으니 함께 참고해보세요!
2. 이어서 데이터의 불필요한 기호를 하나씩 제거합니다. [변환] 탭 - [값 바꾸기]를 선택한 후, 다음 기호들을 차례대로 제거합니다.
   ① [ → 여는 대괄호
   ② ], → 닫는 대괄호+쉼표
   ③ " → 큰 따옴표
   ④ ' → 작은 따옴표
   ⑤ ] → 닫는 대괄호

   

3. 기호를 하나씩 제거하면, 아래 그림과 같이 데이터가 깔끔하게 정리됩니다.

   

4. 구분기호로 나눈 후 표 완성하기 : 이제 쉼표를 기준으로 열을 분할해 표로 변환합니다. [변환] 탭 - [열 분할] - [구분기호 기준]을 선택한 후, 구분기호로 "쉼표"를 지정하고 [확인] 버튼을 클릭하면 쉼표 단위로 나누어진 표가 완성됩니다.

   

5. 완성된 표에서 첫 행은 머리글에 해당하므로, [변환] 탭 - [첫 행을 머리글로 사용]을 클릭하여 첫 행을 머리글로 승격합니다.
   
6. 머리글 왼쪽에 표시되는 아이콘으로 데이터 형식이 모두 올바르게 설정되었는지 확인합니다. 날짜 항목은 '숫자'가 아닌 '날짜' 데이터이므로, 데이터 형식을 '날짜' 형식으로 변경합니다.

   

7. 머리글 왼쪽의 데이터 형식 아이콘은 엑셀 2021 이후 버전부터 제공됩니다. 데이터 형식 아이콘이 보이지 않는다면 [홈] 탭 또는 [변환] 탭에서 '데이터 형식'을 확인하면 됩니다.
   
8. 완성된 쿼리를 시트에 출력하기 : 마지막 단계로 완성된 쿼리를 시트에 출력합니다. [파일] 탭 - [닫기 및 다음으로 로드]를 선택하여 '데이터 가져오기' 대화상자를 실행합니다.
   
9. [데이터 가져오기] 대화상자가 실행되면, 표 형식으로 기존 워크시트의 적절한 위치를 지정한 후 [확인] 버튼을 클릭합니다.
   
10. 잠시 기다리면 주식 정보가 엑셀 시트에 정상적으로 표시됩니다.

    

시트 데이터와 쿼리를 연동해서 실시간 주식 정보 조회하기

이제 마지막 단계로, 예제파일에서 종목번호와 시작일, 종료일을 변경하면 해당 주식 정보가 실시간으로 업데이트되는 쿼리를 완성해보겠습니다.

1. 표의 특정 값을 반환하는 수식 만들기 : 먼저 실시간으로 변경되는 URL 주소를 표로 만든 후 파워쿼리로 불러옵니다. 예제파일에서 URL 주소가 작성된 A7:A8 범위를 선택한 후, [삽입] - [표]를 클릭하여 범위를 표로 변환합니다.

   

2. 표를 선택한 후 [테이블 디자인] 탭에서 표 이름을 'tblURL'로 변경합니다.

   

3. 표 이름을 변경했으면, [데이터] 탭 - [테이블/범위에서]를 클릭하여 URL 주소가 작성된 표를 파워쿼리 편집기로 불러옵니다.

   

4. 파워쿼리 편집기 우측의 '적용된 단계'를 확인합니다. 파워쿼리 버전에 따라 최신 버전에서는 '변경된 유형'이 자동으로 적용됩니다. 적용된 단계에 '변경된 유형'이 있다면, "X"를 클릭해 해당 단계를 제거합니다.

   

5. tblURL의 '원본' 단계를 선택하면, 수식입력줄에 다음과 같이 수식이 작성되어 있는 것을 확인할 수 있습니다.
   = Excel.CurrentWorkbook(){[Name="tblURL"]}[Content]

   

   오빠두Tip : 수식입력줄이 보이지 않는다면, [보기] 탭에서 '수식입력줄' 옵션을 활성화합니다.
6. 파워쿼리 함수에서 중괄호("{}")는 행을, 대괄호("[]")는 필드를 의미합니다. 따라서 작성된 수식을 단계별로 해석하면 다음과 같습니다.
   ① =Excel.CurrentWorkbook() → 현재 실행 중인 통합문서에 작성된 표 범위 목록을 반환합니다.
   ② {[Name="tblURL"]} → Name 필드의 값이 tblURL인 행을 반환합니다.
   ③ [Content] → 반환된 행에서 Content 필드를 반환합니다.
   오빠두Tip : M함수에서 중괄호와 대괄호로 원하는 필드를 선택하는 방법은 위캔두 멤버십 독점으로 제공되는 파워쿼리 기초 총정리 특강에서 자세히 다루었습니다.👇
   
7. 현재 반환된 표에서 URL 주소는 ① URL 필드에서 ② 첫 번째 행을 선택하면 얻을 수 있습니다. 따라서 작성된 수식을 다음과 같이 변경하면, URL 필드의 첫 번째 행이 선택되면서 URL 주소가 값으로 반환됩니다.
   = Excel.CurrentWorkbook(){[Name="tblURL"]}[Content][URL]{0}
   '[URL] : URL 필드를 선택합니다.
   '{0} : 첫번째 행을 선택합니다. (파워쿼리 배열은 0부터 시작)

   

8. 시트 데이터를 쿼리에 연동하기 : URL 주소를 반환하는 수식을 확인했으니, 해당 수식을 복사한 후 기존에 주식 데이터를 반환하는 쿼리로 이동합니다. 주식 데이터를 반환하는 쿼리를 선택한 후, [홈] 탭 - [고급 편집기]를 클릭하여 고급 편집기를 실행합니다.
   
9. 고급 편집기가 실행되면, "원본 = ..."이 시작되는 윗 단계에 복사한 수식을 붙여넣기한 후, 반환되는 값을 url 이라는 변수로 작성합니다. 이어서 붙여넣은 수식 뒤에 쉼표를 추가하여 다음 단계로 진행할 수 있도록 합니다.

   

   오빠두Tip : 고급 편집기에서는 '쉼표'가 다음 단계로 넘어가는 구분 기호로 동작합니다. 따라서 붙여넣은 수식 뒤에 쉼표(,) 기호를 반드시 추가해야 한다는 점을 주의하세요.
10. 이제 원본으로 받아오는 URL 주소를 방금 작성한 url 변수로 변경합니다. 파워쿼리는 대/소문자를 구분하므로, 반드시 대/소문자를 일치시켜 작성하는 점에 주의하세요.

    

11. 네이버 증권 실시간 업데이트 보고서 완성 : 고급 편집기를 모두 작성한 후 [완료] 버튼을 클릭하여 고급 편집기를 종료합니다. 수식에 이상이 없다면 기존과 동일하게 쿼리가 정상적으로 실행됩니다. 만약 수식에 오탈자가 있거나 기호를 빠뜨렸거나 표 이름을 잘못 작성했다면 오류가 발생할 수 있습니다. 이 경우 홈페이지에 올려드린 완성파일이나 아래 완성된 코드를 참고하여 오탈자가 없는지 다시 한 번 확인해보세요.
    url = Excel.CurrentWorkbook(){[Name="tblURL"]}[Content][URL]{0},
    원본 = Table.FromColumns({Lines.FromBinary(Web.Contents(url))}),

    

12. 쿼리가 정상적으로 실행되었다면, [파일] 탭 - [닫기 및 로드]를 클릭해 파워쿼리 편집기를 종료합니다. 이후 종목번호와 시작일, 종료일을 변경한 후 표를 우클릭 - [새로 고침]으로 쿼리를 갱신해보세요. 네이버 증권과 연동되어 주식 정보가 실시간으로 갱신되는 파워쿼리 보고서가 완성됩니다.
    
    오빠두Tip : 표를 새로 고쳤을 때 표의 열 너비가 바뀌어 불편하다면, 표를 선택 → [테이블 디자인] → [속성]에서 '열 너비 조정' 옵션을 체크 해제합니다.

완성파일 실행방법

이번 수업의 완성파일은 파워쿼리를 지원하는 윈도우용 엑셀 2016 이후 버전에서 사용할 수 있습니다.

1. 완성파일을 다운로드한 후, 좌측 상단에 표시되는 [편집 사용]과 [콘텐츠 사용] 버튼을 차례로 클릭하여 통합문서를 실행합니다. [편집 사용] 버튼은 시트 내용을 수정할 수 있도록 활성화하며, [콘텐츠 사용] 버튼은 통합문서에 포함된 파워쿼리를 활성화합니다.
   
2. '완성 예제'에 사용된 쿼리를 우클릭한 후 [새로 고침]을 클릭하여 쿼리를 갱신합니다.

   

3. [개인 정보 수준] 대화상자가 실행되면, '이 파일에 대한 개인 정보 수준 검사를 무시합니다. 개인 정보 수준을 무시하면 중요하거나 기밀인 데이터가 권한 없는 사람에게 노출될 수 있습니다' 확인란을 체크한 후 [저장] 버튼을 클릭합니다.
   
4. 버튼을 클릭하면 쿼리가 새로 고쳐지면서 네이버 증권의 주식 데이터가 갱신됩니다.

   

웹 데이터 불러오기 후, '파일 열기 형식' 옵션이 보이지 않을 경우

잘못된 JSON 데이터 형식 때문에 '파일 열기 형식' 옵션이 보이지 않을 때는, 파워쿼리 함수를 직접 입력해 손쉽게 해결할 수 있습니다.

1. [데이터] 탭 - [데이터 가져오기] - [기타 원본에서] - [빈 쿼리]를 선택해 비어 있는 쿼리를 생성합니다.
   
2. 수식입력줄에 아래 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 : 쿼리 편집기에 수식입력줄이 보이지 않는다면, [보기] 탭 - [수식 입력줄]을 선택해 수식입력줄을 활성화합니다.
3. 이후 과정은 영상 강의 또는 게시글을 참고하여 단계별로 진행합니다.