구글 시트와 AI로 배당금 계산기를 만드는 기본 흐름

배당금 받을 때마다 엑셀 열어서 수동 계산하는 게 귀찮았는데, 구글 시트에 AI까지 붙이니까 종목 입력만 하면 예상 배당금이 실시간으로 뜨는 시스템이 만들어졌거든요.

처음엔 그냥 GOOGLEFINANCE 함수 하나로 시작했어요. 주가 정도만 자동으로 불러오면 되겠지 싶었는데, 막상 써보니까 욕심이 생기더라고요. 배당수익률도 자동으로 뜨면 좋겠고, 세후 실수령액까지 계산되면 완벽할 것 같고. 그래서 하나씩 기능을 붙여나갔는데, 결국 3개월 만에 제미나이 AI가 포트폴리오 분석 코멘트까지 달아주는 시트가 완성됐습니다.

솔직히 중간에 포기할 뻔한 적도 있어요. IMPORTHTML 함수가 어느 날 갑자기 먹통이 된 적이 있었거든요. 야후 파이낸스 쪽에서 구조를 바꿔버린 건데, 그때 대안을 못 찾았으면 프로젝트 자체를 접었을 거예요. 그 과정에서 알게 된 것들을 정리해볼게요.

엑셀 말고 구글 시트를 선택한 이유

배당금 관리용 엑셀 템플릿은 인터넷에 많아요. 근데 제가 엑셀을 안 쓰게 된 결정적인 이유가 있었는데, 바로 GOOGLEFINANCE 함수 때문이에요. 엑셀에는 이 함수가 없거든요. 구글 시트에서만 쓸 수 있는 내장 함수인데, 셀에 =GOOGLEFINANCE(“NASDAQ:AAPL”)이라고 치면 애플 현재 주가가 바로 뜹니다.

이게 왜 중요하냐면, 배당금 계산의 핵심이 현재 주가 대비 배당수익률이잖아요. 주가가 실시간으로 바뀌니까 수동으로 입력하면 계산할 때마다 숫자가 틀어져요. 처음에 엑셀로 만들었을 때 매번 네이버 증권 들어가서 주가 복사 붙여넣기 하다가 도저히 못 하겠더라고요.

구글 시트의 또 다른 장점은 클라우드 기반이라 어디서든 접근 가능하다는 거예요. 출퇴근 지하철에서 핸드폰으로 열어보면 최신 데이터가 그대로 반영돼 있어요. 그리고 결정적으로, 구글 제미나이 AI와의 연동이 가능합니다. =AI() 함수 하나로 시트 안에서 바로 AI 분석을 돌릴 수 있다는 건 엑셀에서는 꿈도 못 꾸는 기능이에요.

다만 단점도 있어요. GOOGLEFINANCE 함수의 주가 데이터는 최대 20분 지연됩니다. 실시간 매매 타이밍 잡는 용도로는 부적합해요. 배당 투자처럼 장기적 관점에서 포트폴리오 관리하는 용도에 딱 맞는 도구예요.

GOOGLEFINANCE 함수로 실시간 주가 연동하기

이 함수의 기본 구조는 간단해요. =GOOGLEFINANCE(“거래소:티커”, “속성”) 이렇게 생겼습니다. 거래소 코드를 빼면 구글이 알아서 맞는 거래소를 찾아주긴 하는데, 종종 엉뚱한 종목이 뜰 때가 있어서 거래소까지 명시하는 걸 추천해요. 저도 처음에 “GOOG”만 입력했다가 다른 나라 거래소 종목이 뜬 적 있거든요.

배당금 계산기에 필수적인 속성들을 정리해보면 이래요. price(현재가), pe(PER), eps(주당순이익), high52(52주 최고가), low52(52주 최저가), marketcap(시가총액), changepct(전일 대비 변동률). 이 속성들을 조합하면 종목 하나에 대한 기본 투자 정보가 한 행에 쫙 펼쳐집니다.

여기서 한 가지 아쉬운 점이 있는데, GOOGLEFINANCE 함수에는 개별 종목의 배당금 데이터를 직접 가져오는 속성이 없어요. yieldpct라는 속성이 있긴 한데, 이건 뮤추얼 펀드 전용이라 일반 주식에는 적용이 안 됩니다. 처음에 이걸 몰라서 한참 헤맸거든요. “왜 자꾸 에러가 뜨지?” 하면서요. 그래서 배당 데이터는 다른 방법으로 가져와야 합니다.

배당금 데이터를 자동으로 끌어오는 세 가지 방법

GOOGLEFINANCE로 배당 정보를 못 가져온다는 걸 알았을 때 꽤 당황했어요. 근데 찾아보니 우회 방법이 세 가지나 있더라고요. 각각 장단점이 확실해서, 본인 상황에 맞게 선택하면 됩니다.

첫 번째는 IMPORTHTML 함수로 야후 파이낸스에서 긁어오는 방법이에요. =IMPORTHTML(“https://finance.yahoo.com/quote/T”, “table”, 2) 이런 식으로 쓰면 AT&T의 재무 테이블을 통째로 가져올 수 있어요. 여기서 INDEX 함수로 Forward Dividend 행만 뽑아내면 배당금과 배당수익률이 나옵니다. 저는 이 방법을 처음에 썼는데, 문제는 야후 파이낸스가 페이지 구조를 바꾸면 함수가 깨진다는 거예요. 실제로 한 번 깨져서 테이블 인덱스를 다시 찾느라 반나절을 날린 적 있어요.

두 번째는 OpenDART API를 활용하는 방법이에요. 금융감독원 전자공시시스템에서 무료 API 키를 발급받으면, 국내 상장사의 배당 정보를 공식 데이터로 가져올 수 있습니다. 국내 배당주 위주로 투자한다면 이 방법이 데이터 신뢰도 면에서 가장 좋아요.

세 번째는 Apps Script로 커스텀 함수를 만드는 방법인데, 이게 제일 유연해요. 구글 시트에서 확장 프로그램 > Apps Script를 열면 자바스크립트 기반의 코드를 작성할 수 있거든요. UrlFetchApp으로 외부 API를 호출해서 배당 데이터를 받아오고, 그걸 시트에 자동으로 뿌려주는 거예요. 코딩을 조금이라도 할 줄 아는 분이라면 이 방법이 가장 안정적이에요. 외부 사이트 구조가 바뀌어도 API 자체가 바뀌지 않는 한 계속 작동하거든요.

배당금 계산기 시트 구조 설계하기

제가 실제로 쓰고 있는 시트 구조를 공유할게요. 시트를 3개 탭으로 나눴습니다. “포트폴리오”, “배당 캘린더”, “대시보드” 이렇게요. 처음에는 하나의 시트에 다 때려넣었다가 데이터가 엉키면서 결국 분리했어요.

“포트폴리오” 탭에는 종목별로 한 행씩 잡아요. A열에 티커(예: AAPL), B열에 종목명, C열에 보유 수량, D열에 평균 매수가. 여기까지가 수동 입력 영역이에요. E열부터는 전부 함수로 자동 계산되게 만들어야 합니다. E열은 =GOOGLEFINANCE(A2, “price”)로 현재가, F열은 연간 배당금(외부에서 가져온 데이터), G열은 =F2/E2*100으로 시가배당률, H열은 =F2*C2로 연간 예상 배당금 총액, I열은 =H2*0.846으로 세후 실수령액(미국주식 15.4% 원천징수 반영).

근데 세금 계산에서 한 가지 실수했던 게 있어요. 미국주식은 배당소득세 15%가 원천징수되잖아요. 처음에 단순히 0.85를 곱했는데, 실제로는 15.4%(소득세 14% + 지방소득세 1.4%)가 맞더라고요. 이 0.4% 차이가 배당금이 커지면 꽤 체감돼요. 연간 배당금이 500만 원이면 2만 원 차이가 나거든요.

“배당 캘린더” 탭은 월별로 어떤 종목에서 얼마가 들어오는지 한눈에 보이게 만들었어요. SUMIFS 함수로 배당월이 해당 월과 일치하는 종목의 배당금을 합산하는 구조예요. 이걸 만들고 나서야 “아, 3월이랑 6월에 배당이 몰려있구나” 하는 게 보이기 시작했습니다.

제미나이 AI 연동으로 분석까지 자동화

여기서부터가 진짜 재밌는 부분이에요. 구글 시트에서 제미나이(Gemini) AI를 쓸 수 있게 되면서, 단순 계산기를 넘어서 분석 도구로 업그레이드가 가능해졌거든요. 사용법은 두 가지입니다.

첫 번째는 =AI() 또는 =GEMINI() 셀 함수예요. 셀에 직접 입력하면 됩니다. 예를 들어 종목 티커가 A2에 있고 배당수익률이 G2에 있을 때, 옆 셀에 =AI(“이 종목의 배당수익률이 “&G2&”%인데, 배당 안정성에 대해 한 줄로 평가해줘”) 이렇게 쓰면 AI가 간단한 코멘트를 달아줘요. 물론 이걸 투자 판단의 근거로 쓰면 안 되고, 참고용 코멘트 정도로 활용하는 거예요.

두 번째는 우측 사이드바 패널을 여는 방법이에요. 시트 전체 데이터를 AI에게 보여주면서 “이 포트폴리오에서 배당 편중 리스크가 있는 월을 찾아줘” 같은 질문을 할 수 있어요. 제가 이걸로 분석을 돌렸더니 “6월에 전체 배당금의 38%가 집중돼 있어서 리밸런싱을 고려해볼 만합니다”라는 피드백이 나오더라고요. 실제로 확인해보니 맞았어요.

거기다 차트 생성도 됩니다. “월별 배당금 추이를 막대 그래프로 만들어줘” 하면 시트 안에 바로 차트가 생성돼요. 예전 같으면 차트 설정 들어가서 범위 잡고, 유형 선택하고, 라벨 달고 이 과정을 거쳤는데 AI한테 말 한마디면 끝나니까 생산성이 확 달라졌어요.

Apps Script로 매일 자동 업데이트 걸기

여기까지만 해도 꽤 쓸만한데, 한 단계 더 가면 완전 자동화가 됩니다. 구글 시트의 확장 프로그램 > Apps Script를 열어서 트리거를 설정하면, 매일 아침 시트가 알아서 데이터를 갱신하게 만들 수 있어요.

원리는 이래요. Apps Script에서 UrlFetchApp.fetch()로 외부 API를 호출하고, 받아온 JSON 데이터를 파싱해서 시트의 특정 셀에 값을 넣는 함수를 작성합니다. 그다음 트리거(Trigger) 메뉴에서 “매일 오전 9시에 이 함수를 실행해라”고 설정하면 끝이에요. 코드 자체는 자바스크립트 기반이라 문법이 어렵지 않아요.

저는 매일 오전 9시 30분에 미국장 종가 데이터와 배당 일정을 갱신하도록 설정해뒀어요. 한국 시간 기준으로 미국 장이 마감된 직후인 셈이죠. 처음에 자정에 돌렸다가 장중 데이터가 중간값으로 찍히는 바람에 수정했어요. 이런 시행착오가 실제로 운영해봐야 알게 되는 것들이에요.

여기서 주의할 점은 Apps Script에도 실행 할당량이 있다는 거예요. 무료 계정 기준으로 하루 UrlFetchApp 호출이 20,000회까지인데, 종목이 50개라면 API 하나당 50회 호출이니까 넉넉한 편이에요. 근데 매 분마다 돌리겠다고 욕심부리면 금방 한도에 걸립니다.

3개월 운영하며 겪은 실수와 꿀팁

첫 번째로 크게 당한 게 환율 반영 누락이었어요. 미국 배당주에서 받는 배당금은 달러잖아요. 근데 처음에 원화 환산 없이 그냥 달러 금액만 합산해놓으니까, 실제 통장에 찍히는 원화 금액이랑 시트 숫자가 안 맞더라고요. =GOOGLEFINANCE(“CURRENCY:USDKRW”)로 실시간 환율을 가져와서 곱해줘야 정확합니다.

두 번째 실수는 배당 삭감을 반영 못 한 거예요. 시트에 연간 배당금을 고정값으로 넣어놨는데, 어떤 종목이 배당을 줄여버렸거든요. 자동으로 최신 배당 정보를 가져오는 시스템을 만들어놓지 않으면, 옛날 데이터를 보고 잘못된 기대를 하게 돼요. 이게 생각보다 위험합니다.

3개월 운영하면서 가장 쓸모 있었던 건 “배당 캘린더” 탭이에요. 월별 예상 배당금이 시각화되니까 현금 흐름이 눈에 보이거든요. 특정 월에 배당이 0원인 걸 발견하고 그 달에 배당을 주는 종목을 추가 매수한 적도 있어요. 시트가 없었으면 그런 빈틈을 못 잡았을 거예요.

마지막 팁 하나. IMPORTHTML 함수가 갑자기 에러를 뿜으면 당황하지 말고 야후 파이낸스 페이지를 직접 열어보세요. 사이트 레이아웃이 바뀌면 테이블 인덱스가 달라지거든요. 이럴 때는 인덱스 번호를 하나씩 바꿔가며 확인하면 돼요. 아니면 아예 Apps Script로 API 기반으로 전환하는 게 장기적으로는 더 안정적입니다. 저는 결국 핵심 데이터는 전부 API 방식으로 옮겼어요.