1-6 [사례 실습] 파워쿼리로 엑셀에 뉴스 가져오기

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

 

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

 

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

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

 

감사합니다.

---

[목표]

1. 포털 뉴스 수집 이야기

2. 국내 주요 포털의 최신 뉴스 RSS 주소 찾기

3. 최신 뉴스 XML 문서 가져오기

4. 블로그 새 글과 뉴스 합치기

5. 데이터 연결만 가져오는 파워쿼리로 만들기

6. 쿼리 복제하여 사용하기

7. 고급 편집기에서 키워드를 직접 수정하여 뉴스 수집하기

 

이번에는 파워쿼리를 통해 포털에 올라오는 최신 뉴스를 엑셀로 수집해오는 작업을 진행해보겠습니다.

 

우리나라의 대표 기업인 '삼성전자'와 'LG전자'가 포함된 뉴스를 수집해보겠습니다.

 

1. 포털 뉴스 수집 이야기

포털 뉴스 목록을 가져올 수 있는 방법은 크게 3가지가 있습니다.

(1) 웹(web)을 통한 수집 : 가장 기본적인 방법이지만, 웹에는 우리가 필요로하는 컨텐츠 외에 이미지나 스크립트 등 많은 요소가 함께 포함되어 있어서, 필요한 정보를 가져오기 위해서는 정제 작업을 해야 합니다.

(2) RSS를 통한 수집(이 글에서 다룰 내용) : XML주소를 찾은 후 블로그 글 가져오기에서 했던 것처럼 파워쿼리를 통해 가져오는 방법입니다.

(3) API를 통한 수집 : 네이버 검색 오픈 API를 통해 글을 가져올 수 있습니다. API 통신에 성공하면 다양한 정보를 가져올 수 있기 때문에 편리하지만, 처음부터 접근하기에는 다소 어려운 부분이 있기 때문에 나중에 다른 글에서 소개할 예정입니다.

 

이 중 2번째 방법인 RSS를 통한 방법을 블로그 글 수집에 이어 다시 한 번 작업해보겠습니다.

 

2. 국내 주요 포털의 최신 뉴스 RSS 주소 찾기

 

RSS방식을 통해 가져올 수 있는 XML을 엑셀로 가져오는 기본적인 방법에 대해서는 지난 블로그 글 가져오기에서 경험해보셨으리라 생각합니다.

 

- 네이버 뉴스 RSS

네이버 뉴스 검색 결과를 RSS로 가져올 수 있다는 것을 아는 분이 많지 않으신데요. 순서대로 주소를 찾아가 보겠습니다.

 

네이버에서 '삼성전자'를 검색한 후 상단 메뉴 중 '뉴스'를 클릭하면, 아래와 같은 화면이 보여지고, 오른쪽 제일 아래에 보면, '뉴스검색RSS보기'라고 된 부분이 있습니다. 이 부분을 클릭합니다.

 

 

블로그 글 가져올 때 보았던 문서와 비슷한 형태의 문서를 보실 수 있습니다. 바로 이 URL 주소를 파워쿼리에서 가져다 사용할 것입니다.

 

 

다음의 경우도 네이버와 유사합니다.

 

 

 

 

 

 

 

- 네이버 '삼성전자' 뉴스 RSS 주소 : http://newssearch.naver.com/search.naver?where=rss&query=%EC%82%BC%EC%84%B1%EC%A0%84%EC%9E%90&field=0&nx_search_query=&nx_and_query=&nx_sub_query=&nx_search_hlquery=

- 다음 '삼성전자' 뉴스 RSS 주소 : http://tab.search.daum.net/dsa/search?w=news&m=rss&SortType=1&DA=RSN&q=%BB%EF%BC%BA%C0%FC%C0%DA

 

[팁] 일반적으로 뉴스 검색을 하면 검색 결과를 관련도 또는 정확도 순서대로 보여주지만, RSS문서는 최신의 글을 전달하는 방식이라서 최신글 순서대로만 글 목록을 보내줍니다. 따라서, 뉴스 검색 결과가 최신 순서일 때, 웹에서 보여지는 글과 RSS의 글이 동일하게 나타납니다.

 

3. 최신 뉴스 XML 문서 가져오기

 

블로그 글을 가져왔던 파일을 열어 이어서 작업을 진행하시거나, 아래 첨부 파일을 다운로드하신 후 작업을 진행해주시기 바랍니다.

1-5 사례 실습 파워쿼리로 엑셀에 블로그 글(RSS XML) 가져오기3.xlsm

다운로드

 

혹시 엑셀 메뉴 아래 부분에 컨텐츠 사용에 대한 경고가 나타난다면, '컨텐츠 사용하기'를 클릭하셔야 매크로 등이 정상적으로 동작합니다. XML 가져오기에 대한 내용은 전에 올린 글을 참고해주시기 바랍니다.

 

- 파워쿼리 메뉴 > 웹에서 > 삼성전자 뉴스 RSS 주소 입력 > 확인 클릭 > '편집' 클릭 > channel 아래 Table 클릭 > item 아래 Table 클릭 > title, link, pubDate, author 열만 선택 > 우마우스 > 다른열 제거 > title은 '제목', link는 'URL', pubDate는 '일시', author는 '출처'로 열 이름 변경 > 열 순서를 제목, URL, 일시, 출처의 순서로 맞춤 > 쿼리이름을 '삼성전자네이버뉴스'로 수정한 후 로드합니다.

 

 

다음의 뉴스도 동일한 방법으로 가져옵니다.

만약, 가져오는 도중 한글이 깨져보인다면 아래의 방법으로 한글 인코딩 방식을 변경해주시기 바랍니다.

 

- 쿼리 편집기 > 쿼리 설정 중 > 적용된 단계에서 원본 오른쪽에 있는 톱니 모양 클릭

 

 

 

아래와 같은 창이 나타나면, 파일 원본 부분을 클릭하여 한국어(EUC)를 선택한 후 '확인' 클릭

 

 

이제 아래 화면처럼 다음의 최신 뉴스 목록도 갖게 되었습니다.

 

 

 

4. 블로그 새 글과 뉴스 합치기

 

우선, 새로 가져온 뉴스 글목록을 기존의 '삼성LG전자블로그통합'에 합쳐보겠습니다.

이와 함께 쿼리 이름도 '삼성LG전자통합'으로 바꿔야겠습니다. 매크로에 사용된 이름도 함께 바꿔주시기 바랍니다.

 

'삼성LG전자블로그통합' 쿼리 부분을 더블클릭하여 쿼리편집기 상태로 들어갑니다. 블로그글과 뉴스글의 데이터 구조가 같기 때문에 '병합'이 아닌 '추가'를 통해 합칠 수 있습니다.

쿼리 편집기 > 홈 메뉴 > 조합 리본 부분 > 쿼리 추가를 선택합니다.

 

 

'3개 이상의 테이블'을 선택하면 아래와 같은 화면이 나타납니다. 아래 화면에서 새로 추가할 뉴스 관련 쿼리 2가지를 선택한 후 추가버튼을 눌러 오른쪽 '추가할 테이블'에 표시되도록 한 후 '확인' 버튼을 클릭합니다.

쿼리 이름도 '삼성LG전자통합'으로 변경해준 후 '닫기 및 로드'를 통해 엑셀로 돌아옵니다.

 

가져온 글을 보니, 중복된 글이 여러 개 있습니다. 아무래도 뉴스라서, 여러 신문사가 한꺼번에 같은 제목으로 올리는 일이 빈번한가봅니다. 이러한 중복을 제거하기 위해서 다시 쿼리 편집기로 들어가보겠습니다.

 

제목에 대한 중복을 제거할 것이기 때문에,

'제목' 열 선택 > 홈 메뉴 > 행 감소 리본 > 중복 제거 선택을 하면 같은 내용의 제목 중 하나만 남고 중복되는 행은 모두 제거됩니다.

 

 

중복이 제거된 후의 모습

 

닫기 및 로드를 선택하여 다시 엑셀로 돌아옵니다.

 

같은 요령으로 LG전자의 네이버 뉴스와 LG전자 다음 뉴스도 합쳐 줍니다.

 

 

5. 데이터 연결만 가져오는 파워쿼리로 만들기

 

이제... 삼성전자 블로그, LG전자 블로그, 삼성전자 네이버 뉴스, 삼성전자 다음 뉴스 총 4개의 글목록이 '삼성LG전자통합'이라는 쿼리로 합쳐졌습니다.

 

이렇게 파워쿼리로 문서를 많이 가져오다보면, sheet도 많아져서 관리가 어려워집니다.

따라서, 직접 사용하지 않는 쿼리문의 경우는 데이터 연결 관계만 유지하게 하는 것이 좋을 수 있습니다. 이를 위해서는 쿼리를 약간 수정해야 합니다.

 

먼저 삼성전자블로그 쿼리 > 우마우스 > 로드를 선택합니다.

 

 

아래 화면처럼 '다음으로 로드' 화면이 나오면, '연결만 만들기', '이 데이터를 데이터 모델에 추가'를 선택한 후 '로드' 버튼을 클릭합니다.

 

 

 

아래와 같은 경고가 나타나지만, '계속'버튼을 클릭합니다.

이 경고는 블로그 글목록 자체의 sheet 값을 직접 다른 sheet에서 가져간 경우 오류가 발생할 수 있다는 내용입니다. 우리는 '통합' sheet에서만 값을 가져갔기 때문에 무시해도 상관없습니다.

 

 

아래 화면처럼 sheet의 내용이 모두 사라졌습니다만, 오른쪽 쿼리 영역에 '삼성전자블로그'쿼리는 그대로 남아 있습니다.

이제 불필요하게 자리만 차지하고 있는 '삼성전자' sheet도 삭제합니다.

 

 

sheet가 아래 화면처럼 총 3개로 줄어들었습니다. 쿼리는 그대로 5개입니다.

 

 

 

6. 쿼리 복제하여 사용하기

 

삼성전자 뉴스는 추가되었지만, LG전자의 뉴스를 추가해야 합니다.

LG전자 뉴스는 삼성전자 뉴스에서 사용한 쿼리를 복제해서 사용해보겠습니다.

 

- 통합 문서 쿼리에서 > 삼성전자네이버뉴스 복사(ctrl+c) > 붙이기(ctrl+v)를 하시면 삼성전자네이버뉴스 (2)라는 쿼리가 생깁니다.

 

이 쿼리를 더블클릭하여 쿼리 편집 상태로 들어갑니다.

 

- 먼저 쿼리 이름을 'LG전자네이버뉴스'로 바꿔줍니다.

 

7. 고급 편집기에서 키워드를 직접 수정하여 뉴스 수집하기

 

이제 고급 편집기를 통해 복제한 쿼리문에서 키워드만 수정해보겠습니다.

 

- 홈 메뉴 > 고급 편집기를 클릭하여 고급 편집기 화면을 띄웁니다.

- 두번째 줄에서 빨간색 밑줄 친 부분이 바로 키워드 부분입니다. 현재 쿼리는 삼성전자의 것을 가져왔기 때문에 이 부분만 LG전자로 변경하면 LG전자에 대한 뉴스를 수집해 옵니다.

 

 

[변경 전]

원본 = Xml.Tables(Web.Contents("http://newssearch.naver.com/search.naver?where=rss&query=%EC%82%BC%EC%84%B1%EC%A0%84%EC%9E%90&field=0&nx_search_query=&nx_and_query=&nx_sub_query=&nx_search_hlquery=")),

 

[변경 후]

원본 = Xml.Tables(Web.Contents("http://newssearch.naver.com/search.naver?where=rss&query=LG전자&field=0&nx_search_query=&nx_and_query=&nx_sub_query=&nx_search_hlquery=")),

 

같은 요령으로 LG전자다음뉴스 쿼리도 만들어줍니다.

 

그 후 LG전자네이버뉴스 쿼리와 LG전자다음뉴스 쿼리도 '삼성LG전자통합'쿼리에 추가해줍니다.

 

이제 총 6개의 새로운 글목록을 하나의 데이터로 가져오고 새로운 글을 표시할 수 있게 되었습니다.

 

궁금하신 부분이 있으시면 빅엑셀 페이스북 페이지(https://www.facebook.com/bigexcel.kr)로 문의해주시기 바랍니다.

 

다음 글에서는 파워쿼리를 통해 엑셀로 회사의 주식 정보를 가져오는 작업을 진행할 계획입니다.

 

감사합니다.

 

[사례 실습 파일]

1-6 사례 실습 파워쿼리로 엑셀에 최신 뉴스 가져오기.xlsm

다운로드