엑셀 웹 데이터 자동 수집! 주식 재무 정보 원클릭 정리법
엑셀 파워쿼리 웹 분석 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- ✨ 엑셀 해외 주식 재무지표 분석 서식회원자료
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
파워쿼리 웹 분석으로 주식 목록 취합하기
엑셀 파워쿼리에서 제공하는 '웹 데이터 불러오기' 기능을 사용하면, 네이버 증권을 포함한 여러 증권 사이트에서 제공하는 국내/해외 데이터를 클릭 한 번으로 자동 수집할 수 있습니다. 이번 글에서는 미국 주식 시장에 상장된 모든 주식 종목의 시가 총액, 종가, 거래량은 물론, PER, EPS, BPR, 배당수익률 등 핵심 재무정보까지 한 번에 취합하는 자동화 서식을 제작해보겠습니다.
- 먼저 아래 링크를 클릭해 네이버 증권 모바일 페이지로 이동한 후, [해외] 버튼을 클릭해 미국 주식 정보 페이지로 이동합니다.
[해외] 버튼을 클릭해 미국 주식 페이지로 이동합니다. - 개발자 도구를 실행한 후, [네트워크] 탭에서 [Fetch/XHR] 버튼을 클릭해 해외 주식 정보 페이지에서 받아오는 데이터 요청을 확인합니다. [더보기] 버튼을 클릭하면 아래와 같이 'api.stock.naver.com/stock/exchange/NASDAQ/markeValue?...' 로 시작하는 요청을 확인할 수 있습니다.
https://api.stock.naver.com/stock/exchange/NASDAQ/marketValue?page=1&pageSize=20
// 나스닥에 상장된 시가총액 상위 20개 종목을 출력합니다.오빠두Tip : 초보자를 위한 웹 API 사용 방법은 이전 영상 강의에서 알기 쉽게 정리했으니 참고하세요!👇
- 위 요청에서 페이지 번호(page)와 페이지 크기(pageSize) 를 변경해 특정 페이지의 미국 주식 정보가 조회되는 것을 확인할 수 있습니다.
https://api.stock.naver.com/stock/exchange/NASDAQ/marketValue?page=페이지번호&pageSize=페이지크기
페이지번호와 페이지 크기를 입력해 데이터를 조회합니다. - 비어있는 엑셀 파일을 실행하고 [데이터] - [웹] 을 클릭한 후, 아래 URL 주소를 입력하고 [확인] 버튼을 클릭합니다. 만약 데이터 개인 정보 보호 또는 웹 접근 관련 엑세스 권한 설정 안내가 나올 경우, [자격 증명 편집] 또는 [계속] 버튼을 클릭하고 '개인 정보 수준을 무시합니다'를 체크한 후 [저장] 버튼을 클릭해 다음 단계를 진행합니다.
https://api.stock.naver.com/stock/exchange/NASDAQ/marketValue?page=1&pageSize=100
데이터 - 웹 불러오기로 네이버 증권의 해외 주식 정보를 조회합니다. - 파워쿼리 편집기가 실행되면 'stocks' 의 list를 클릭해 목록을 펼친 후, 목록을 우클릭 - [테이블로]를 클릭해 목록을 테이블로 변환합니다. 테이블로 안내창이 나오면 구분기호 및 열 처리 방법은 기본값 그대로 [확인] 버튼을 클릭합니다.
목록을 우클릭 - 테이블로 를 클릭해 표로 변환합니다. - 이후 표의 머리글에서 펼침 버튼을 클릭하고 '원래 열 이름을 접두사로 사용'은 체크 해제한 후 [확인] 버튼을 클릭합니다. 그러면 시가총액 기준 상위 100개 주식 목록을 확인할 수 있습니다. 필드 중 Record로 된 항목을 하나씩 확인하며 필요한 항목만 남기고 불필요한 필드는 제거합니다.
펼침 버튼을 클릭해 필드를 확장합니다. - 아래 그림과 같이 필요한 항목을 남긴 후, [홈] 탭 - [고급 편집기]로 이동합니다.
원하는 항목을 남긴 후, 홈 - 고급편집기로 이동합니다. - 고급 편집기가 실행되면 let 위로 "(page as number) =>" 를 넣어 쿼리를 함수로 변환한 후, URL 주소의 페이지 번호 부분을 "Text.From(page)"로 입력합니다. [완료] 버튼을 클릭하면 특정 페이지의 미국 주식 정보를 출력하는 함수가 완성됩니다.
쿼리를 함수로 변환합니다. - 완성된 함수 이름을 getNASDAQ 으로 변경하고, 쿼리 영역을 우클릭 - [새 쿼리] - [기타 원본] - [빈 쿼리]로 비어있는 쿼리를 추가합니다.
비어있는 쿼리를 하나 추가합니다. - 쿼리에 "= List.Numbers(1,5)" 로 수식을 입력하면 1~5 까지 순번이 생성됩니다. 순번 목록을 만든 후, 목록을 우클릭 - [테이블로]를 클릭해 목록을 테이블로 변환합니다.
List.Numbers 함수로 순번을 생성합니다. - 마지막으로 [열 추가] - [사용자 지정 함수 호출]을 클릭하고 호출할 함수로 'getNASDAQ'을 선택한 후 [확인] 버튼을 클릭합니다.
열 추가 - 사용자 지정 함수 호출로 getNASDAQ 함수를 호출합니다. - 출력된 getNASDAQ 필드에서 펼침 버튼을 클릭하면, 총 5 페이지(500개 종목)의 미국 주식 실시간 정보가 출력됩니다.
총 5개 페이지의 미국 주식 목록이 출력됩니다. - [파일] 탭 - [닫기 및 로드]를 클릭하면 완성된 쿼리가 시트에 출력됩니다.
파일 - 닫기 및 로드로 완성된 쿼리를 시트에 출력합니다.
특정 주식 재무 정보 출력 함수 만들기
주식 목록을 성공적으로 가져왔다면, 이제 특정 종목의 핵심 재무정보(PER, EPS, PBR, 등)을 확인하는 함수를 만들어 보겠습니다.
- 네이버 증권에서 특정 종목의 상세 페이지로 이동한 후, 브라우저의 개발자도구 - [네트워크 탭]을 확인하면 아래 basic 주소를 확인할 수 있습니다. 아래 주소로 이동하면 특정 주식(예제에서는 마이크로소프트)의 재무정보가 출력됩니다.
특정 주식의 재무정보를 출력하는 URL 주소를 확인합니다. - 이전과 동일하게 [데이터] 탭 - [웹] 을 클릭하고, 아래 URL 주소를 입력해 파워쿼리 편집기를 실행합니다.
https://api.stock.naver.com/stock/MSFT.O/basic
데이터 - 웹 불러오기를 실행하고 URL 주소를 입력합니다. - 웹 데이터 목록에서 stockItemTotalInfos 의 목록을 클릭합니다. 이후 목록을 우클릭 - [테이블로]를 클릭해 목록을 테이블로 변환합니다.
stockItemTotalInfos의 목록을 연 후, 표로 변환합니다. - 테이블의 펼침 버튼을 클릭해 표를 펼치면 아래 그림과 같이 특정 종목의 52주 최저, 최고, PER, EPS 등 재무정보를 확인할 수 있습니다.
표를 펼치면 해당 주식의 핵심 재무정보가 표시됩니다. - 표에서 불필요한 항목은 제거하고 key 에서 확인할 재무정보만 남겨 표를 완성합니다.
표에서 필요한 항목만 남기고 나머지는 제거합니다. - 이후 [변환] 탭에서 [행/열 바꿈] → [첫 행을 머리글로 사용]을 클릭해 데이터를 가로 방향으로 전환합니다.
행/열바꿈, 첫 행을 머리글로 사용을 클릭해 표를 가공합니다. - [적용된 단계]에서 머리글 승격 후 자동으로 추가되는 '변경된 유형'이 있을 시, 변경된 유형 단계를 삭제하고 '승격된 헤더'가 마지막 단계가 되도록 합니다.
오류 방지를 위해 변경된 유형 단계를 삭제하고, 승격된 헤더를 마지막 단계로 설정합니다. - 쿼리를 모두 완성했으면 [홈] - [고급 편집기]를 클릭하고 이전과 같이 쿼리를 함수로 변경합니다 let 위로 "(code as text) =>" 를 입력하고, URL 주소에서 종목 코드가 들어갈 영역에 "code"를 입력합니다. 이후 [확인] 버튼을 클릭하면 함수가 완성됩니다.
고급 편집기에서 기존 쿼리를 함수로 변경합니다. - 완성된 함수는 "getStockInsight" 로 함수 이름을 변경합니다.
완성된 함수의 이름을 getStockInsight 로 변경합니다.
모든 주식의 재무 정보 한 번에 취합하기
개별 주식의 재무 정보를 가져오는 함수를 만들었다면, 이제 이 함수를 활용해 수천 개 주식의 재무 정보를 한 번에 취합할 수 있습니다.
- 이전 단계에서 만든 주식 종목 쿼리에서 [열 추가] - [사용자 지정 함수 호출]을 클릭하고, 함수 쿼리는 "getStockInsight", 입력할 인수로는 열 이름으로 "reutersCode(로이터 코드)"를 선택한 후 [확인] 버튼을 클릭해 열을 추가합니다.
열 추가에서 getStockInsight 함수로 열을 추가합니다. 함수에 사용할 인수 필드로는 'reutersCode'를 선택합니다. - 개인 정보 보호 관련 안내가 나올 경우, [계속] 버튼을 클릭하고 '개인 정보 수준을 무시합니다'를 체크한 후 [저장] 버튼을 클릭해 다음 단계를 진행합니다.
개인정보 보호 관련 안내가 나올 경우, [계속] 버튼을 클릭해 다음 단계로 진행합니다. - 잠시만 기다리면 기존 주식 목록 오른쪽에 'getStockInsight' 필드가 추가되면서 각 주식의 재무정보를 확인할 수 있습니다. [펼침] 버튼을 클릭하고 '원래 열 이름은 접두사로 사용'은 체크해제 후, [확인] 버튼을 클릭해 필드를 펼칩니다.
getStockInsight 열의 펼침 버튼을 클릭해 데이터를 확장합니다. - 각 주식의 PER, EPS, PBR 등 재무 정보가 한 번에 출력됩니다.
PER, EPS 등 재무 정보가 한 번에 출력됩니다. - 이제 재무정보 필드를 모두 선택한 후, [변환] - [값 바꾸기] 에서 문자 형식의 기호를 제거합니다.
① "배"를 찾아 빈칸으로 변경
② "-"를 찾아 0으로 변경
③ "N/A"를 찾아 0으로 변경
값 바꾸기에서 불필요한 문자와 기호를 찾아 제거합니다. - 이후 [변환] 탭 - [데이터 형식 검색]을 클릭하면 각 PER, PBR, EPS 등 재무정보가 올바른 숫자 데이터로 변환됩니다.
'데이터 형식 검색' 버튼을 클릭하면 올바른 숫자 데이터로 변환됩니다. - 마지막 단계로 [파일] 탭 - [닫기 및 로드]를 클릭해 쿼리를 출력하면 미국 모든 주식의 재무정보가 한 번에 취합됩니다.
미국 주식의 재무 정보가 한 번에 취합됩니다.
주식 재무 정보 원클릭 취합 서식 완성하기
마지막으로, 이렇게 취합된 주식 재무 정보에 조건부서식와 FILTER 함수 등을 활용하면 완벽한 주식 분석 서식이 완성됩니다. 원클릭 주식 분석 서식을 만드는 전체 과정은 위캔두 멤버십 라이브 강의를 참고하세요!👇
- 먼저 아래 링크를 클릭해 네이버 증권 모바일 페이지로 이동한 후, [해외] 버튼을 클릭해 미국 주식 정보 페이지로 이동합니다.
