1-7 [사례 실습] 파워쿼리로 엑셀에 주가 정보 가져오기

안녕하세요? 그동안 파워쿼리 내용을 업데이트하지 못했는데요.

 

얼마전 기회가 있어 위키독스(wikidocs.net)에 2개의 책을 공개했습니다. 이 블로그 내용을 업그레이드한 것이니 파워쿼리와 가까워지는데 조금이나마 도움이 되길 바랍니다.

 

1. 클릭클릭! 코딩없이 시작하는 엑셀 크롤링(https://wikidocs.net/book/7776)

2. 클릭클릭! 엑셀로 이해하는 인공지능(https://wikidocs.net/book/7800)

 

감사합니다.

---

[목표]

1. 주가 정보를 가져올 곳 찾기

2. 웹(web) 페이지 중 테이블 형태의 내용 가져오기

3. 오류행, 중복행 제거

 

 

이번에는 기업의 주가 정보를 가져오는 작업을 해보겠습니다. 주가 정보는 종종 기업의 신제품에 대한 기대가 반영되기 때문에 함께 모니터링해보겠습니다.

 

주식 정보를 가져오는 방법도 여러가지가 있겠습니다.

웹사이트에 가서 마우스로 내용을 긁은다음 복사할 수도 있고,

주가 정보가 있는 엑셀 파일을 다운로드해서 사용할 수도 있고,

증권사 API를 활용하여 가져오거나, 각종 프로그래밍 언어를 이용해서 수집할 수도 있겠습니다.

오늘은 엑셀에서 사용할 수 있는 가장 간편한 방법으로 주가 정보를 제공하는 웹사이트에서 정보를 가져오는 방식에 대해 설명드리겠습니다.

 

전에 진행했던 RSS방식의 XML을 가져오는 것과 달리, 이 번에는 실제 웹페이지를 바로 가져오는 작업을 진행하겠습니다. 

나중에 다시 설명드리겠습니다만, 웹 페이지의 경우 구조화되지 않은 페이지를 가져오는 것은 다소 까다롭습니다. 다행이 아래 실습 웹페이지는 Table형태로 주가 정보를 따로 가져올 수 있기 때문에 수월한 편입니다.

 

하지만, 우리가 수집해야할 정보의 대부분은 XML형태가 아닌 일반 웹페이지(HTML) 형태이기 때문에 다소 까다롭다 하여도 웹사이트 수집 방법을 알아가는 것은 꼭 필요하다고 생각합니다.

 

웹사이트 내용을 수집할 때 한 가지 주의할 점은 사이트 정책에 따라 기계적인 사이트 수집을 거부하는 경우도 있으므로 이를 고려하여 작업하시는 것이 좋습니다. (참고 정보 :  http://www.robotstxt.org/robotstxt.html)

 

1. 주가 정보를 가져올 곳 찾기

 

우선 주가 정보를 가져올 곳을 찾아야 하는데요. 국내 포털에서도 주가 정보를 제공하고 있지만, 이번에는 yahoo.com 사이트를 통해 정보를 얻도록하겠습니다.

 

- yahoo.com을 방문한 후 왼쪽 메뉴 중 Finance를 클릭합니다.

 

 

- 검색창에 samsung을 입력하면 삼성전자를 추천해줍니다. 이 부분을 클릭하면 삼성전자 주식 정보 페이지로 이동합니다.

 

 

아래 페이지가 삼성전자의 주식 정보 페이지입니다. 우리는 이 중 주가 추이를 사용할 것이기 때문에 

- 중간에 있는 'Historical Data'를 클릭합니다.

 

 

아래 페이지가 우리가 사용할 주가 추이 페이지입니다. 이 주소를 파워쿼리에 가져다 사용할 계획입니다.

같은 방법으로 LG전자의 페이지 주소도 보관해둡니다.

 

삼성전자 yahoo 주가 추이 페이지 : https://finance.yahoo.com/quote/005930.KS/history?p=005930.KS

LG전자 yahoo 주가 추이 페이지 : https://finance.yahoo.com/quote/066570.KS/history?p=066570.KS

 

 

 

2. 웹(web) 페이지 중 테이블 형태의 내용 가져오기

 

이제 엑셀로 가서

- 파워쿼리 메뉴 > 웹에서 > URL입력 > 확인 버튼 클릭

 

 

- 탐색 창 > 왼쪽 주소 있는 부분 아래 > Table0 선택 > 편집 버튼 클릭

 

정상적으로 주가 정보를 불러왔습니다.

이제 사용할 열만 남기고 나머지는 삭제할텐데요. 이번 사례에서는 간단하게 날짜(Date), 종가(Close), 거래량(Volume)만 사용하겠습니다.

필요없는 Open, High, Low, Close, Adj Close열을 선택 > 우마우스 클릭 > 열 제거 선택

 

 

- Date열의 이름을 '날짜'로, Close 열의 이름을 '종가'로, Volume 열의 이름을 '거래량'으로 변경하고,

- 쿼리 이름도 '삼성전자주가'로 바꿔줍니다.

- 종가와 거래량 열을 선택하여 데이터 형식을 '정수'로 바꿔줍니다.

 

 

 

 

3. 오류행, 중복행 제거

 

하지만, 2016-09-19의 거래량에 오류가 있습니다. 값이 없는 부분이 정수로 변환되면서 오류가 난 것인데요. 이 부분을 제거하겠습니다.

- 종가와 거래량 열을 선택 > 홈메뉴 > 행 감소 리본 > 오류 제거 클릭

 

 

다시 보니, 날짜에 2016-09-13이 같은 정보로 2번 나옵니다.

- 날짜 열 선택 > 홈 메뉴 > 행 감소 리본 > 중복 제거 클릭하여 중복을 제거해줍니다.  

 

 

중복 제거가 잘 되었다면

- 홈 메뉴 > 닫기 및 로드를 클릭하여 엑셀로 내용을 불러옵니다.

 

아래 화면처럼 데이터를 잘 가져왔나요?

데이터를 가져온 sheet의 이름도 '삼성전자주가'로 바꿔주면 끝입니다.

 

 

 

LG전자의 주가 정보도 가져와야 하는데요.

이전 글을 참고하시어 뉴스 목록을 가져올 때 진행했던 쿼리 복제 방법으로 삼성전자주가 쿼리를 복사&붙이기 합니다.

삼성전자주가 (2)가 생기면 편집 > 고급편집기에 가서 아래와 같은 쿼리 내용을 LG전자의 것으로 바꾸고, 쿼리 이름과 sheet이름도 LG전자주가로 바꿔주시기 바랍니다.

 

[삼성전자주가 쿼리 2번째 줄]

    원본 = Web.Page(Web.Contents("https://finance.yahoo.com/quote/005930.KS/history?p=005930.KS")),

 

[LG전자주가 쿼리 2번째 줄]

    원본 = Web.Page(Web.Contents("https://finance.yahoo.com/quote/066570.KS/history?p=066570.KS")),

 

다음 실습 사례에서는 필터와 슬라이서를 이용하여 수집한 데이터를 인터렉티브하게 시각화 하는 작업을 진행하겠습니다.

 

감사합니다.

 

[사례 실습 파일]

1-7 사례 실습 파워쿼리로 엑셀에 주가 정보 가져오기.xlsm

다운로드