[Excel] 웹 페이지의 데이터를 엑셀 시트로 실시간 연동하기

Excel에는 외부 데이터를 연결해 사용하는 기능이 있다. 버전에 따라 메뉴를 조금씩 달리하면서 지원되었는데, 바로 전에는 Power Query(파워 쿼리)라는 이름으로 있다가, Excel 2016(엑셀 2016)부터 데이터 메뉴 아래 가져오기 및 변환(Get & Transform) 기능으로 통합되었다.
특히, 웹 페이지에 표(table) 형태로 제공되는 데이터를 엑셀에 바로 불러와서 사용하는 기능은 아주 예전부터 있었고, 조금씩 인터페이스나 기능이 달라져 왔다. 그렇다고 기능 자체가 획기적으로 좋아진 것 같지는 않고, 되기는 된다 수준이다.
아래는 관련해서 약 5년 전에 올린 글이다.
참고: "[Excel 2016] 웹 페이지의 데이터 표를 엑셀에 연결하여 사용하기", 2015. 10. 27., cantips.com/2726
그동안 웹 페이지를 분석하고 불러오는 기능이 더 좋아져서 새로 글을 쓰는 것은 아니고, 웹 페이지의 테이블을 가져올 때, 바로 불러오지 못하고, 페이지 소스 분석을 통해 뭔가 추가 작업을 해야 하는 예를 살펴보고자 한다.
예를 들어, 우리나라 기상청의 날씨누리 사이트에서 실시간 날씨 정보를 제공하는 페이지의 정보를 가져온다고 하자.
이 페이지에 정리된 데이터를 엑셀에 실시간으로 연동하기 위해 웹 쿼리 기능을 사용하게 된다. 그러나 이 페이지의 주소(URL)를 그대로 사용하면 해당 표를 불러오지 못한다.
나름대로 원본 페이지의 소스를 분석해 해당 데이터를 가져올 수 있는 주소를 찾아야 한다.
결론적으로는 모든 웹 페이지의 데이터를 다 가져올 수 있는 것은 아니며, 해당 페이지마다 소스 분석을 통해 나름대로 필요한 주소를 추출해야 한다는 것이다. 이 방법도 사이트마다 모두 다르다.
Excel 자체에서 편리한 기능을 제공하지 않는 한 어쩔 수 없고, 다소 불편하지만, 필요하다면 사용해볼 만은 하다.
참고: "[Excel] 웹 페이지의 데이터를 엑셀 시트로 실시간 연동하기", 2020. 9. 9., cantips.com/3276
#엑셀 #웹쿼리 #Excel

[@user-dy6ke7wq8m]

증권사 준비하는 대학생입니다. 자료 모을게 너무 많았는데 영상 덕분에 도움 많이 받았습니다 ^^

[@cantips]

감사합니다

[@user-kq2vx5bp4u]

좋은정보 네요... 맞아요 ..유튜브 따라 했더니 ..데이타가 따라오지않아 비어 있었어 .ㅠㅠ 뭐지 개인것아니면 이런식으로 만들어진건 못 불러오겠더라고요.. 그래서 잘 알거나 개인것 아니면 불러오기 힘든경우가 많을것 같더군요... 이걸 알아야 그나마 찾아 보고 시도라도 해볼수 있겠넹..ㅛ

[@CTULUDODOSAE]

과거날씨 기록용으로 찾고있었는데 유용한 자료 고맙습니다. 다만, 제가 능력이 없어서 이걸 못 하고 있네요 ㅠ.ㅠ) 열심히 찾아서 성공해봐야겠습니다.

[@cantips]

조금만 더 파고드시면 쉽게 할 수 있을 겁니다

[@sergioygkeke]

궁금했던 부분이 많았는데 끝까지 보았습니다. 고맙습니다!

[@cantips]

감사합니다

[@kkkkk5593]

자세히 알려주셔서 편하게 잘 찾았습니다 감사합니다

[@cantips]

감사합니다

[@rion6027]

너무 설명을 잘하시네요 감사합니다.

[@cantips]

감사합니다

[@dvesfvhssss]

안보이는 표 찾아오는건 혁신적입니다

[@cantips]

감사합니다

[@ngcold]

VBA 크롤링을 공부 중이었는데, 이런 엄청난 기능이 있는줄은 몰랐네요....

[@cantips]

감사합니다

[@aaswswsws]

오..이거 주식하는데에 유용할것 같네요. 컴맹이지만 조만간 도전해봐야겠어요. 감사합니다

[@cantips]

감사합니다

[@user-ju4xj1iu5r]

최고입니다

[@cantips]

감사합니다

[@chess9393]

너무 쉽게 설명 해주셔서 감사합니다ㅜㅜ

[@cantips]

감사합니다

[@junkorea427]

오우 !! 좋은 영상 감사합니다... !!! 😄 구독하였습니다.

[@cantips]

감사합니다

[@mineme3617]

와 정말 미친 기능이네요! 꼭 해봐야겠어요! 감사합니다

[@cantips]

감사합니다

[@ahmirjaxen9480]

i know im asking randomly but does anybody know of a way to log back into an instagram account..? I somehow forgot my account password. I would appreciate any tricks you can give me.

[@jeremiasalexzander9838]

@Ahmir Jaxen instablaster =)

[@ahmirjaxen9480]

@Jeremias Alexzander i really appreciate your reply. I got to the site on google and I'm waiting for the hacking stuff atm. Looks like it's gonna take quite some time so I will get back to you later when my account password hopefully is recovered.

[@ahmirjaxen9480]

@Jeremias Alexzander It did the trick and I actually got access to my account again. Im so happy:D Thanks so much, you really help me out!

[@hardy4038]

정말 귀에 쏙쏙박히는 설명이네요 대단하십니다! 잘봤습니다!!

[@cantips]

감사합니다

[@wallqook1]

감사합니다^^

[@cantips]

감사합니다

[@meditation4076]

이걸 이제 알았네요

[@cantips]

감사합니다

[@cdg-f3d]

쿼리가 데이터베이스인가요? 배우고 싶은데 ㅜ

[@user-mz8um6dn7b]

좋은 정보 감사합니다! 혹시 로그인해서 들어가서 연동해야되는경우는 어떤 방법으로 해야되나요?

[@cantips]

웹 쿼리를 사용하여 로그인이 필요한 웹 페이지를 엑셀로 연동하려면 다음 절차를 따르세요: Power Query를 사용하여 로그인 인증 정보를 저장합니다. Power Query는 엑셀에 내장된 데이터 가져오기 및 변환 도구입니다. 이 도구를 사용하면 웹 페이지의 로그인 인증 정보를 저장할 수 있습니다. 엑셀에서 '데이터(Data)' 탭을 클릭하고 '데이터 가져오기(Get Data)'를 선택한 다음 '웹(Web)'을 선택합니다. '기본(From Web)'을 클릭한 다음 웹 페이지 URL을 입력하고 '확인(OK)'을 클릭합니다. Power Query 에디터가 열리면, 웹 페이지가 로그인을 요구하는 경우, 인증 방식을 선택하고 로그인 정보를 입력합니다. 웹 페이지의 데이터를 가져옵니다. 로그인이 완료되면 웹 페이지의 데이터를 선택하고 가져올 수 있습니다. Power Query 에디터에서 원하는 데이터를 선택하고 '추가(Add)' 또는 '로드(Load)'를 클릭하여 엑셀에 데이터를 추가합니다. 쿼리를 새로 고치기 및 자동 새로 고침 설정을 구성합니다. 로그인 정보를 저장한 후에도 웹 페이지의 데이터가 변경되면 엑셀에서 데이터를 새로 고쳐야 합니다. 이를 수동으로 수행하거나 자동으로 설정할 수 있습니다. 엑셀에서 '데이터(Data)' 탭을 클릭한 다음 가져온 데이터가 있는 테이블을 선택합니다. '쿼리(Query)' 탭을 클릭하고 '새로 고침(Refresh)'을 선택하여 데이터를 새로 고칩니다. 필요한 경우 새로 고침 주기를 설정하여 데이터가 자동으로 업데이트되도록 할 수 있습니다. 이러한 단계를 따르면 엑셀에서 로그인이 필요한 웹 페이지를 웹 쿼리로 연동할 수 있습니다. 그러나 이 방법은 웹 사이트의 보안 정책에 따라 다소 제한적일 수 있으며, 웹 사이트의 구성이 변경되면 쿼리가 작동하지 않을 수도 있습니다. 이러한 문제를 해결하려면 엑셀 VBA 또는 웹 자동화 도구를 사용하여 보다 직접적인 로그인 및 데이터 추출 프로세스를 구현하는 것을 고려해 보세요.

[@user-fg2tb1ej6c]

웹페이지데이타가 가능하다면 증권(키움)사의 필요한데이타 가져올수 있나요? 가져온 데이타로 (시고저)활용 함수표랑 기술적 지표도 엑셀에 표시가 가능한가요?

[@cantips]

가능 여부나 방법은 웹 페이지마다 다릅니다. 해당 웹 페이지를 적절히 분석해서 어떤 방법을 써야할지 고민해 봐야 하고, 불가능할 수도 있습니다.

[@yesno9065]

와~

[@metagalaxy_KR]

넘 감사합니다. 한가지 궁금한건.. 전체 불러오기말고 그중에 딱 한데이터.. 예를들어 거제시의 현재온도만 특정셀로 불러올수있을까요? ..

[@cantips]

파워 쿼리 편집에서 처리하거나 이게 어렵거나 불편하다면 전체 데이터를 불러온 다음에 그걸 바탕으로 조회 수식을 하나 적용하면 간단할 것 같습니다.

[@papalael5285]

좋은영상 감사드려요 ^^ 혹시 해당일자 고시환율 1회차값을 불러오고 싶은데 그렇게도 가능한가요??? 예시) 2022년11월21일 1회차 매매기준값을 구할때는 어떻게하는지... 생각보다 복잡하네요 ㅠㅠ

[@cantips]

해당 정보를 구성할 때 서버 뒤쪽에서 작업한 후 만들어진 정보만 하드코딩 형태로 보여주면 적절하게 입력값을 바꿔가면서 조회하기 어렵습니다. 이런 경우 비교적 접근이 쉬운 은행 홈페이지 등을 이용하는 것이 어떨까합니다. 예를 들어 국민은행 데이터를 당겨다 쓰는 한국무역협회 사이트의 경우 원하는 회차로 쉽게 웹 쿼리가 가능한 것으로 보입니다. 보이는 정보가 적합한 지는 모르겠지만 우선 다양한 방법으로 쉬운 경로를 찾아보는 것도 한 방법입니다.

[@user-ot1pj9tu5l]

안녕하세요 유용한 정보 감사합니다! 로그인해야 보이는 데이터도 연동이 가능할까여 ㅠ?

[@cantips]

로그인하지 않아도 되는 페이지들도 데이터를 가져오는 일이 쉽지 않은 경우가 많습니다. 로그인해야 하는 페이지는 더욱 그렇습니다. 로그인 이후 데이터를 가져올 수 있는 구조면 그나마 괜찮습니다. 하지만 일반적으로는 따로 VBA 코딩 등 별도의 고난도 작업을 해야하는 경우가 많습니다. 이것도 다 된다고 장담할 수 없습니다.

[@user-qj5on5tb1q]

제가 사용중인 엑셀2010은 웹쿼리가 내부적으로 Internet Explorer로 동작하는 것 같은데요. 2022.6월 이후에는 MS사에서 Internet Explorer를 사용중지한다고 합니다. 그러면 어떻게 해야 되나요? 엑셀 상위 버전에서는 문제가 없나요?

[@cantips]

특히 다른 프로그램이나 웹과 연동하는 기능이라면 가급적 엑셀도 최신 버전을 유지하는 것이 좋을 듯합니다.

[@a.7506]

혹시 웹 버전엑셀 데이터를 앱버전 엑셀로연동시키는 방법이있을까요 웹버전엑셀이 매크로를 사용할수없다보니 매번 데이터 복붙해와서 써야하는데 그절차를 생략하고싶습니다

[@cantips]

웹에서 만든 파일이라면 원드라이브에 저장이 가능할테고 이는 데스크톱에서도 동기화하여 접근 가능하니 그냥 사용하면 될텐데 다른 특별한 환경인건가요?

[@a.7506]

@@cantips 네 쉐어포인트에 올려놓고 실시간연동해서 팀원들과 쓰고닜습니다 엑셀 오피스365입니다

[@a.7506]

@@cantips 웹에서 만든파일아니라 오피스365 엑셀 쉐퍼포인트에 올려서 팀원들과 실시간으로 공유해서 쓰고 있습니다 그런데 여기에선 매크로 기능을 찾지를 못하겠습니다

[@user-gv7ym8cv7f]

감사합니다. 그런데 만약 실시간으로 정보가 변경된다면 어떤수치가 변동되었는지 색이든 어떻게 표현 할 수 있는 방법은 없을까요? 변경수치를 이용해야하는데 어디가 변동됐는지 알 수 없을것 같아서요

[@cantips]

이것도 워크플로를 다시 생각해 보기로 하고, 일단 연동한 자료를 일정 시간 간격으로 자동으로 불러와 계속 저장하는 방법을 제 블로그에 올려 두었습니다. 필요하다면 참고해 보세요: cantips.com/3797

[@user-bu5ox5sg3s]

이거 맥os로도 똑같이 작동하나요?

[@cantips]

macOS용 엑셀에서는 웹 쿼리가 지원되지 않습니다. 새로 고침 정도 지원됩니다. macOS에서는 안 되는 기능이 꽤 있습니다.

[@user-ji4ck6mz4k]

홈페이지에 액쎌 수식을 설치하여 계산에 사용할 수있는 방법이 있는지 알려주십시오.

[@cantips]

원드라이브 계정이 있고 엑셀 시트를 임베드할 수 있는 웹 페이지가 있다면 비교적 쉽게 가능합니다.

[@user-eh2rf9bi3v]

회사 시스템상 쓰는 웹 페이지가 있는데, 여기 영상과 같이 해보고 싶은데 잘 되지 않네요. 혹시 다른 방법이 또 있을까요

[@cantips]

모든 웹 페이지에 일률적으로 적용할 수 있는 방법은 찾기 어렵습니다. 이런 예를 통해 웹 페이지에 접근하는 몇 가지 빙법을 확인하고 개별 페이지마다 거기에 맞는 형식으로 응용해야 하며 거의 불가능한 사이트도 광장히 많습니다.

[@metagalaxy_KR]

주소를 웹에 넣었더니 원하는 테이블이 떠서 로드를 했더니 다운로드를 완료하지 못했다면서 계속 로드가 안되네요.. 관세청 유니패스 주간고시환율 수입 테이블인데... 방법이없을까요?ㅠㅠ

[@cantips]

관련 영상을 올렸습니다. 참고해 보세요. kzbin.info/www/bejne/moCog4uamcyqhK8

[@luckypapa-]

안녕하세요 혹시 네이버 증권의 다양한 종목의 종가 데이터만 한페이지로 가져올려면 어떻게 해야 할까요 관련 영상이 있으면 링크 부탁드립니다

[@cantips]

정밀하고 다양한 원시 데이터가 필요하다면 한국 거래소(KRX)를 이용하는 것도 좋을 것 같습니다.

[@별별님]

새쿼리에서 웹선택했는데요 브라우저를 업데이트 하라고 나오네요 ㅠㅠ.. 크롬과 인터넷 익스플로러 깔려있는데 어떻게 해야할지 모르겠네요..

[@cantips]

크롬이 기본 브라우저로 설정되지 않아서 그런 거 아닐까요? 기본 웹 브라우저를 크롬이나 최신 에지로 설정해 보시기 바랍니다.

[@user-iu7bh2lp4h]

하이퍼링크가 클릭표시가 안떠서 안들어가지는데 어떻게 해야하나요?

[@cantips]

어떤 링크를 말씀하시는건지요?

[@iliiililililiiililil9451]

실시간 연동하는 경우에 데이터값이 링크인데 크룰링으로 가져오면 텍스트로 변하네요 링크 그대로 가져오는 방법은 없을까요? 클릭하면 바로 보일 수 있게 끔요

[@cantips]

어쩌면 당연한 것일 수 있습니다. 데이터 자체를 문자값으로 가져오는 것이지 링크가 포함된 어떤 기능을 가져오는 것이 아니기 때문입니다. 하지만 데이터 자체가 URL 링크 모양을 완벽하게 갖추었다면 엑셀이 자동으로 링크를 만들어주기도 할 겁니다. 만약 엑셀이 지동으로 링크를 만들어주지 않는다면 불러온 데이터가 들어있는 셀을 참조하여 다른 적당한 셀에 HYPERLINK 함수를 써서 링크로 만들면 될 것 같습니다.

[@milta-x6l]

이것 일정 시간 간격 자동으로 연속 저장할 수도 있는가요?

[@cantips]

그것은 자동화(프로그래밍) 영역이라 아주 간단하게 해결할 수는 없겠지만 쿼리를 일정 시간 간격으로 새로고침하면서 또 적당한 간격으로 새로운 워크시트를 계속 만들고 그곳으로 내용을 복사하는 매크로(VBA)를 작성하면 됩니다.

[@investcho777]

쿼리 넣어서 표로 안 넣고 딱 하나의 데이터만 셀에 넣을 수 있나요?

[@cantips]

가장 일반적인 방법은 파워 쿼리 편집기에서 원하는 부분(셀)만 나오도록 조건 등을 추가하고 워크시트로 로드하는 것입니다. 아니면 워크사트에 나온 전체 결과에서 필요한 부분만 조화하는 수식을 다른 부분에 작성하는 것도 좋습니다.

[@user-ml7fb1nn2q]

구글스프레드 시트에는 쿼리가없는데 방법이없을까요

[@cantips]

여기 예제 사이트에서도 잘 되는지는 모르겠지만 구글 스프레드시트에도 xml, json 형태의 웹 페이지 데이터를 임포트 하는 명령어가 있습니다. 관련 튜토리얼 등을 잘 찾아보고 시도해 보면 좋을 것 같습니다.

[@cantips]

구글 스프레드시트에서 하는 방법을 게시했습니다. 참고하세요. kzbin.info/www/bejne/aaCXe4CYar2Ym6s

[@user-ji1lt7md1z]

업비트 웹커리 부탁드립니다

[@cantips]

업비트 개발자 페이지(docs.upbit.com/)를 통해 Open API를 확보한 후 시도해 보시기 바랍니다. API를 이용한 데이터 연동은 사이트마다 방법이 다를 수 있으나 제 다른 영상을 참조하면 기초적인 사항은 확인할 수 있을 겁니다.

[@your_quotation]

로그인이 필요한 웹사이트는 어떻게 하나요?

[@cantips]

사이트, 페이지 구성에 따라 다르기 때문에 상황에 따라 다르지만 보통은 로그인한 이후에 합니다.

[@redmung14]

혹시 통장 잔여 계좌도 가능한가요?

[@cantips]

제가 시도해 보거나 생각해 본 적이 없어서 잘 모르겠지만, 안 되지 않을까 합니다. 한 번 시도해 보시면 좋은 경험이 되지 않을까 합니다.

[@TVDabokTV]

사용중인 액셀을 무슨버전인가요?

[@cantips]

Excel MS 365로 항상 최신 버전이 유지됩니다.

[@user-vw1oy8cp1h]

@@cantips 컴터 밀고 다시 깔려구요.. 버전은 아무거나 하셔도 되요.. 구체적인 내용은 말로 설명해야 할꺼 같은데.. 연락할수 있는 연락처 하나 부탁 드림니다.. 작업비 드리겠습니다.

[@cantips]

@@user-vw1oy8cp1h 주변에 관련 분야에 능통한 분이나 믿을만한 컴퓨터 가게로 문의해 보시면 어떨까 합니다.

[@cbg1992]

저는 쿼리를 쓰면 탐색창에 아무것도 안떠요 ㅠㅠ 왜그런걸까요

[@cantips]

당연한 얘기지만 해당 사이트의 구성 방법에 따라 가능 여부나 추출 방법이 달라집니다.

[@cbg1992]

안되는 사이트들이 있는거네요..ㅋ

[@cbg1992]

@@cantips 제가 원하는건 주식에 따라 배당금 자료가 바뀌는건데 주식 코드가 있으면 그 코드를 웹사이트에 자동으로 넣어서 값이 변하게 하려면 어렵겠져..?

[@cantips]

@@cbg1992 엑셀 웹 쿼리로 안 되는 사이트는 엄청 많습니다. 자신의 용도에 맞게 적용 가능한지 확인하면서 사용해야 합니다. 해당 조건도 딱 된다 안 된다 단언하기 어렵습니다. 다각도로 분석해 보기를 추천합니다.

[@user-bo1vv8dz4x]

쇼핑몰도 가능한가요??

[@cantips]

하는 절차가 어렵지 않으니 한 번 시도해 보고 판단하는 것이 좋을 듯 합니다.

[@user-yo1wk8tj1b]

만약 페이지가 있어서 하단에 몇십까지 페이지수로 나뉘어져있는 실시간 표는 어떻게 불러와야할까요?

[@cantips]

페이지 구성에 따라 다를 수 있지만 불러올 수 있는 페이지라면 페이지 하나씩 엑셀 워크시트 하나씩 연동하고 전체 시트를 종합하는 워크시트를 만들면 어떨까 합니다.

[@user-vw1oy8cp1h]

웹 쿼리 제작해주실수 있나요.. 제작비 드리겠습니다.. 부탁 합니다.

[@cantips]

아마 관련해서 전문적으로 하는 개인이나 회사가 있을 겁니다. 웹 서비스도 있고. 간단한 거라면 연습겸 해볼 수도 있을 거 같고요.

[@user-gz9mm1qg5w]

해드리죠

[@covenantpeoplefellowship9762]

기발한 기능을 쉽게 섧명해 주셔서 잘 배웠습니다. 저는 ETF 국내시장지수를 가져오려고 하면서 장벽에 부딛혔습니다. inspect/network에서 찾아 보았는데, API 형태로 보여 주네요. 이런 때는 어떻게 하는지 알려 주십시요. 감사합니다.

[@cantips]

관련해서 영상을 올렸습니다, 참고해 보세요. kzbin.info/www/bejne/pKnPeXh4fZ1jZ80

[@covenantpeoplefellowship9762]

@@cantips 감사합니다.