배당 포트폴리오를 관리해보자- 구글스프레드 배당주 시세 자동으로 불러오기(2)

배당 포트폴리오 관리해보자-구글스프레드 배당주 시세 자동 불러오기(2)

안녕하세요. 오늘은 구글 스프레드 시트를 이용해서 배당금을 가지고 오는 부분을 해보려고 합니다.

사실 여러개의 글을 보고 참고하여 작성해 보려고 하였지만, 배당금을 가지고 오는 부분이 쉽지 않은 것같습니다.

이전 글에서 구글 파이낸스의 경우에는, 일반 현물에서는 배당금이 나오지 않는 것 같습니다.

따라서, 검색을 해보면, 구글 스프레드시트에서 제공하는 IMPORTHTML 을 가지고 작업을 하는 글과 더불어, IMPORTDATA를 가지고 하는 2가지의 글로 크게 나뉘어 지고 있는 것 같습니다. 하지만 IMPORTDATA로 작업을 해보려고 하지만, 정상적으로 되지 않는 것 같습니다.

하루 웬종일 작업을 해서 찾아낸 방법을 공유해드리도록 하겠습니다.

저는 IMPORTHTML 함수를 사용하였습니다.

https://support.google.com/docs/answer/3093339?hl=ko

위 함수의 도움말은 위의 바로가기를 참조하세요. 

함수의 정의는 다음과 같습니다.

HTML 페이지에서 표 또는 목록에 있는 데이터를 가져옵니다.

일단 해외 주식의 배당금을 찾기 위해서는 야후 금융 서비스에 들어가시면 됩니다.

야후 금융에서 종목을 임의적으로 한개 찾아서, 배당 페이지를 들어갑니다. 아래 사진과 같이 배당금 목록이 나와있는 페이지 입니다. 이렇게 배당금 페이지를 복사해주면 됩니다.

https://finance.yahoo.com/quote/O/history?period1=1518612348&period2=1550148348&interval=div%7Csplit&filter=div&frequency=1d

위의 주소가 제가 야후에서 불러온 페이지 입니다.

이제 함수를 사용해 볼 시간입니다.

=IMPORTHTML("https://finance.yahoo.com/quote/O/history?period1=1518612348&period2=1550148348&interval=div%7Csplit&filter=div&frequency=1d","TABLE",1)

위의 함수를 사용하시면 아래 사진과 같이 배당금 내역을 불러옵니다. 쉽게 설명하면 해당 주소 페이지의 테이블구조로 되어 있는 부분들 중에서 1번째 테이블을 가지고 오라는 명령입니다. 불러오게 되면, 사진에서 보시듯이 전체 배당금 내역이 불러오게 됩니다. 

다음으로 해야될 작업은 최근 배당금 내역이 필요하니, 최근 배당금 내역만 불러 오도록 하겠습니다.

이번에 참조할 함수는 INDEX 함수 입니다. 인덱스 함수의 자세한 정보는 아래를 확인해주세요.

https://support.google.com/docs/answer/3098242?hl=ko

인덱스 함수의 정의는 행과 열 오프셋으로 지정된 셀 내용을 반환하는 값 입니다.

배당금 이력에서 2행과 2열에 최근 배당금내역이 나오니, 이를 가져오도록 합니다.

보시면 최근 배당금을 가지고 온 것을 확인 할수 있습니다. 하지만, 배당금의 숫자만 가지고 온 것이 아니고, 특수문자와 문자열도 같이 가지고 왔습니다. 배당금을 계산하기 위해서는 실제 숫자만 필요하니, 나머지를 자르는 작업을 해야 됩니다.

문자를 자세히 보시면 *0.226* Dividend 에서 Dividend 앞쪽에 스페이스바로 한칸이 띄어져 있는 것을 볼수 있습니다. 문자를 분리해내기 위해서는 split 함수를 통해서 잘라내야 됩니다.

아래 스플릿에 대한 정의 입니다.

https://support.google.com/docs/answer/3094136?hl=ko

보시면 별차이가 없다고 느낄수 있지만, 내부적으로는 변화가 있으니, 다음 단계로 넘어가도록 하겠습니다.

regexextract을 통해서 실제로 우리가 필요한 부분을 가지고 오는 함수 입니다. 즉, 정의를 살펴보면, 정규 표현식에 따라 일치하는 하위 문자열을 추출하는 것입니다.

자세한 사항은 아래 부분을 확인해주세요.

https://support.google.com/docs/answer/3098244?hl=ko

특수 문자에 싸혀 있는 부분을 가져올 예정이니깐 아래와 같이 작업하시면 됩니다.

위의 사진에서 보시는 바와 같이 불필요한 문자는 사라지고, 우리가 원하는 데이터 만을 불러온 것을 확인 할수있습니다.

=regexextract(split(index(IMPORTHTML("https://finance.yahoo.com/quote/O/history?period1=1518612348&period2=1550148348&interval=div%7Csplit&filter=div&frequency=1d","table",1),2,2),char(10)), "[0-9]*\.[0-9]*")

위의 구문을 따서 사용하시면 됩니다.

리얼리티인컴의 배당금을 불러 왔으니, 다른 배당금들도 불러와야 됩니다.

하지만 지금과 같이 사용을 하게 되면, 주소를 수기로 다 가지고 와야 되는 번거로움이 있습니다.

그렇기 때문에 이를 응용해서 다음과 같이 작업을 하면 됩니다.

CONCATENATE 함수를 사용하면 되는데, 문자열을 연결 시켜주는 함수 입니다. 자세한 사항은 아래를 참고하여 주세요.

https://support.google.com/docs/answer/3093339?hl=ko

자세히 보시면, 주소의 중간에 종목명이 들어가 있는 것을 볼수 있습니다. 이것을 변경하면서 자동으로 배당금을 불러 올수 있도록 하기 위한 것입니다.

우리가 정확한 종목명만 알고 있다면 자동으로 구글 스프레드시트에 배당금을 추가할수 있게 된 것입니다.

아마도 다음에 할 작업은 연도별 예상 배당금을 추가하는 작업을 해야 될것 같습니다. 그래야지, 월별 배당금 내역도 나올수 있을 것 같습니다.

이것으로 구글스프레드시트를 이용하여, 배당금을 추출하는 방법을 알아 보았습니다.

감사합니다.