엑셀 웹 데이터 자동 수집! 국내/해외 주식 재무정보 원클릭 정리 방법

실습 가이드

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

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

파워쿼리 웹 분석으로 주식 목록 취합하기

엑셀 파워쿼리의 '웹에서 데이터 가져오기' 기능을 사용하면 네이버 증권을 비롯한 여러 증권 사이트의 국내·해외 주식 데이터를 클릭 한 번으로 자동 수집할 수 있습니다. 이번 강의에서는 미국 주식 시장에 상장된 모든 종목의 시가총액, 종가, 거래량은 물론 PER, EPS, PBR, 배당수익률 등 핵심 재무지표까지 한 번에 취합하는 자동화 서식을 직접 제작해보겠습니다.

1. 먼저 아래 링크를 클릭해 네이버 증권 모바일 페이지로 이동한 후, [해외] 버튼을 클릭해 미국 주식 정보 페이지로 이동합니다.
   https://m.stock.naver.com/

   

2. 브라우저 개발자 도구를 실행한 후, [네트워크] 탭에서 [Fetch/XHR] 버튼을 클릭해 해외 주식 페이지가 받아오는 데이터 요청을 확인합니다. 페이지에서 [더보기] 버튼을 클릭하면 아래와 같이 'api.stock.naver.com/stock/exchange/NASDAQ/marketValue?...' 로 시작하는 요청이 표시됩니다.
   https://api.stock.naver.com/stock/exchange/NASDAQ/marketValue?page=1&pageSize=20
   // 나스닥에 상장된 시가총액 상위 20개 종목을 출력합니다.
   오빠두Tip : 초보자를 위한 웹 API 사용 방법은 이전 영상 강의에서 알기 쉽게 정리했으니 함께 참고하세요!👇
   
3. 위 요청에서 페이지 번호(page)와 페이지 크기(pageSize) 값을 변경하면, 원하는 페이지의 미국 주식 정보를 그대로 조회할 수 있습니다.
   https://api.stock.naver.com/stock/exchange/NASDAQ/marketValue?page=페이지번호&pageSize=페이지크기

   

4. 비어있는 엑셀 파일을 실행하고 [데이터] - [웹] 을 클릭한 후, 아래 URL 주소를 입력하고 [확인] 버튼을 클릭합니다. 데이터 개인 정보 보호 또는 웹 액세스 권한 안내가 나타나면 [자격 증명 편집] 또는 [계속] 버튼을 클릭한 뒤, '개인 정보 수준을 무시합니다'에 체크하고 [저장] 버튼을 눌러 다음 단계로 진행합니다.
   https://api.stock.naver.com/stock/exchange/NASDAQ/marketValue?page=1&pageSize=100

   

5. 파워쿼리 편집기가 실행되면 'stocks' 항목의 list를 클릭해 목록을 펼친 후, 목록을 우클릭 - [테이블로]를 클릭해 목록을 테이블로 변환합니다. '테이블로' 안내창이 표시되면 구분기호 및 열 처리 방법은 기본값 그대로 두고 [확인] 버튼을 클릭합니다.

   

6. 변환된 표의 머리글에서 펼침 버튼을 클릭하고 '원래 열 이름을 접두사로 사용' 옵션은 체크 해제한 후 [확인] 버튼을 클릭합니다. 그러면 시가총액 기준 상위 100개 주식 목록이 표시됩니다. 필드 중 Record로 표시된 항목을 하나씩 확인하며 필요한 열만 남기고 불필요한 필드는 제거합니다.

   

7. 아래 그림과 같이 필요한 항목만 남긴 후, [홈] 탭 - [고급 편집기] 로 이동합니다.

   

8. 고급 편집기가 실행되면 let 위쪽에 "(page as number) =>" 구문을 추가해 쿼리를 함수로 변환한 후, URL 주소의 페이지 번호 부분을 "Text.From(page)" 로 입력합니다. [완료] 버튼을 클릭하면 특정 페이지의 미국 주식 정보를 출력하는 사용자 함수가 완성됩니다.

   

9. 완성된 함수의 이름을 getNASDAQ 으로 변경하고, 쿼리 영역을 우클릭 - [새 쿼리] - [기타 원본] - [빈 쿼리] 를 선택해 비어있는 쿼리를 추가합니다.

   

10. 새로 만든 쿼리에 "= List.Numbers(1,5)" 수식을 입력하면 1부터 5까지의 순번 목록이 생성됩니다. 순번 목록을 확인한 후 목록을 우클릭 - [테이블로] 를 클릭해 테이블로 변환합니다.

    

11. 마지막으로 [열 추가] - [사용자 지정 함수 호출] 을 클릭하고 호출할 함수로 'getNASDAQ' 을 선택한 후 [확인] 버튼을 클릭합니다.

    

12. 새로 추가된 getNASDAQ 필드의 펼침 버튼을 클릭하면, 총 5페이지(500개 종목) 분량의 미국 주식 실시간 정보가 한 번에 출력됩니다.

    

13. [파일] 탭 - [닫기 및 로드] 를 클릭하면 완성된 쿼리가 시트에 출력됩니다.

    

특정 주식 재무 정보 출력 함수 만들기

주식 목록을 성공적으로 가져왔다면, 이제 특정 종목의 핵심 재무정보(PER, EPS, PBR 등)를 조회하는 함수를 만들어 보겠습니다.

1. 네이버 증권에서 특정 종목의 상세 페이지로 이동한 후, 브라우저 개발자 도구의 [네트워크] 탭에서 아래 basic 주소를 확인할 수 있습니다. 해당 주소로 이동하면 특정 주식(예제에서는 마이크로소프트)의 재무정보가 출력됩니다.
   https://api.stock.naver.com/stock/MSFT.O/basic

   

2. 이전과 동일한 방법으로 [데이터] 탭 - [웹] 을 클릭하고, 아래 URL 주소를 입력해 파워쿼리 편집기를 실행합니다.
   https://api.stock.naver.com/stock/MSFT.O/basic

   

3. 웹 데이터 목록에서 stockItemTotalInfos 항목의 목록을 클릭합니다. 이후 목록을 우클릭 - [테이블로] 를 클릭해 목록을 테이블로 변환합니다.

   

4. 테이블의 펼침 버튼을 클릭해 표를 펼치면 아래 그림과 같이 해당 종목의 52주 최저·최고가, PER, EPS 등 주요 재무정보를 확인할 수 있습니다.

   

5. 표에서 불필요한 항목은 제거하고 key 열에는 확인하려는 재무 항목만 남겨 표를 정리합니다.

   

6. 이후 [변환] 탭에서 [행/열 바꿈] → [첫 행을 머리글로 사용] 을 차례로 클릭해 데이터를 가로 방향으로 전환합니다.

   

7. [적용된 단계] 영역에서 머리글 승격 후 자동으로 추가된 '변경된 유형' 단계가 있다면, 해당 '변경된 유형' 단계를 삭제해 '승격된 헤더'가 마지막 단계가 되도록 정리합니다.

   

8. 쿼리 정리가 끝나면 [홈] - [고급 편집기] 를 클릭하고 이전과 동일한 방식으로 쿼리를 함수로 변환합니다. let 위쪽에 "(code as text) =>" 를 입력하고, URL 주소에서 종목 코드가 들어갈 영역을 "code" 로 변경합니다. [확인] 버튼을 클릭하면 함수가 완성됩니다.

   

9. 완성된 함수는 이름을 "getStockInsight" 로 변경해 둡니다.

   

모든 주식의 재무 정보 한 번에 취합하기

개별 주식의 재무 정보를 조회하는 함수를 만들었다면, 이제 이 함수를 활용해 수백~수천 개 주식의 재무 정보를 한 번에 취합할 수 있습니다.

1. 앞 단계에서 만든 주식 종목 쿼리에서 [열 추가] - [사용자 지정 함수 호출] 을 클릭하고, 함수 쿼리는 "getStockInsight", 입력 인수의 열 이름은 "reutersCode(로이터 코드)" 로 선택한 후 [확인] 버튼을 클릭해 열을 추가합니다.

   

2. 개인 정보 보호 관련 안내가 표시되면 [계속] 버튼을 클릭하고 '개인 정보 수준을 무시합니다'에 체크한 후 [저장] 버튼을 눌러 다음 단계로 진행합니다.
   
3. 잠시 기다리면 기존 주식 목록 오른쪽에 'getStockInsight' 필드가 추가되며 각 주식의 재무정보가 채워집니다. [펼침] 버튼을 클릭하고 '원래 열 이름은 접두사로 사용' 체크를 해제한 뒤 [확인] 버튼을 클릭해 필드를 펼칩니다.

   

4. 각 주식의 PER, EPS, PBR 등 주요 재무 정보가 한 번에 출력됩니다.

   

5. 이제 재무정보 필드를 모두 선택한 후, [변환] - [값 바꾸기] 기능으로 문자 형식의 기호를 제거합니다.
   ① "배" 를 찾아 빈칸으로 변경
   ② "-" 를 찾아 0 으로 변경
   ③ "N/A" 를 찾아 0 으로 변경

   

6. 이후 [변환] 탭 - [데이터 형식 검색] 을 클릭하면 PER, PBR, EPS 등 재무정보가 올바른 숫자 데이터 형식으로 변환됩니다.

   

7. 마지막으로 [파일] 탭 - [닫기 및 로드] 를 클릭해 쿼리를 출력하면 미국 시장 주요 종목의 재무정보가 한 번에 시트로 취합됩니다.

   

주식 재무 정보 원클릭 취합 서식 완성하기

마지막으로 취합된 주식 재무 정보에 조건부 서식과 FILTER 함수 등을 결합하면 완성도 높은 주식 분석 서식을 만들 수 있습니다. 원클릭 주식 분석 서식을 완성하는 전체 과정은 위캔두 멤버십 라이브 강의에서 자세히 확인하세요!👇