본문 바로가기

컴퓨터 활용/구글스프레드시트 응용

구글스프레드시트로 하는 해외 주식 포트폴리오 관리

현재 해외주식을 미래에셋대우와 NH나무에서 하고 있는데 자산현황에 수익률이 국내 주식과 섞여 버리고 같은 종목도 다른 증권사에 분리되어 있어 정확한 수익률을 알 수가 없더군요.  그래서 구글 스프레드시트로 하는 국내주식 관리를 해외 주식에 맞게 수정하여서 몇달간 사용 중인데 정상 동작을 하는지 확실치 않지만 그럭저럭 잘 쓰고 있습니다.

기본 베이스는 국내주식 시트를 이용했고 필요한 정보만 놔두고 나머지는 지우고 간소화 했습니다. 개인적으로 쓰는 쉬트는 이것저것 알고 싶은 것을 다 구현해 놔서 느리네요.

될 수 있는대로 대부분을 스크립트로 자동화 했습니다. 국내 주식과 마찬가지로 구글이 제공하는 스케줄러에 매일 수익률을 기록하는 스크립트를 등록시켜서 매일 지정된 시간에 수익률을 기록해주기 때문에 수익률 그래프도 보실 수 있습니다.

수식과 구글 앱 스크립트 (Google Apps Script) 소스도 보실 수 있으므로 공부하실 분이면 훌륭한 교재로서의 역할도 될 것 같습니다. 메뉴 만들기 및 데이터 입력을 위한 폼 만들기, 입력 루틴, 시트 셀 다루기, 메일 보내기 등 필요할 만한 모든 기능이 총망라되어 있습니다. 공부하면서 조금씩 만든 것인데 초보 수준의 자바스크립트만 알고 계시면 분석하시다 보면 이해하실 수 있을 것입니다.

수익률 계산

1) 잔고 수익률 : 환전된 달러로 투자했을 때의 수익률
2) 입출금 기반 수익률: 계좌에 입출금 기반으로 계산한 수익률


배당금은 계좌내에서 전액 재투자 되는 것으로 됩니다. 출금하면 입출금 기반 수익률을 참고하시면 됩니다.

첫 화면(종합 시트)은 다음과 같습니다.

 

종합 시트 구성 현황

왼쪽 상단은 수익률 표시 입니다. 

1) 수익률 구간별 등락 종목
2) 섹터/종류/지역/성격 별 비중 및 평가금액 및 수익률
3) 해외 3대 지수 및 환율
4) 투자종목의 주가 및 추세 그래프

기능 실행을 위한 버튼 및 상단 사용자 메뉴

1) 신규종목 생성.
2) 매수/매도 내역 입력.
3) 시트 복사하기.

그래픽 버튼로 만들어 두었고 상단 메뉴에 보시면 사용자 메뉴에서 선택해도 됩니다.

 

신규종목 생성

신규 종목을 생성할 때 국내 주식 시트와 같이 종목명을 검색하면 실시간으로 종목코드를 가져오는 루틴을 아직 만들지 못했으므로 주식명과 Symbol 을 수동으로 입력해 주셔야 합니다. 대신 Stock Info 시트에 종목명과 Symbol 을 채워 넣어 주시면 왼쪽과 같이 목록이 나오게 되며 선택하면 자동으로 종목명과 종목코드가 채워집니다.

매수/매도 내역 입력

매수 시간을 입력하고 주식명을 선택하면 됩니다. 주식명은 주식 목록에 등재되어 있는 종목이 보이게 됩니다. 단가와 수량와 수수료를 입력하고 매수(Buy)/ 매도(Sell)여부의 라디오 박스를 선택해서 Insert 를 누르면 매수와 매도 쉬트에 자동으로 매수/매도 내역이 들어가게 됩니다. 

1) 주식 목록 시트

계산을 위한 다양한 항목(데이터 필드)들이 있어 한 화면에 표시를 할 수 없어서 두 부분으로 나누어 캡처 했습니다.


주식 목록에서 제공하는 데이터 필드

1) 투자잔액 : 평균단가 * 주식수 이며 총매수금액 - 총매도금액 + 총손익금액 으로 계산 됩니다.
2) 평균단가 : 투자잔액 / 주식수

2) 입력 카테고리 - 구성 시트

- 매수 / 매도 : 매수 및 매도 내역

만들어 놓은 입력 폼을 이용하여 넣으면 됩니다. 행을 복사해서 수동으로 입력해도 됩니다. 수동입력 시 반드시 열 중간에서 삽입해 주세요.

1) 매수 시트의 영역이 둘째 행에서 마지막행까지 이름영역으로 잡혀있습니다. 2행 앞이나 마지막 행에 넣게 되면 이름영역에서 제외 됩니다.
2) 입력한 시트의 수식은 값으로 복사 하여 반드시 고정시켜 주세요.
로딩시간 개선 및 순환 참조 수식으로 에러가 나거나 재계산으로 인한 미미한 값의 변화를 방지하기 위한 목적입니다.

순환참조 수식으로 에러는 구글스프레드 시트 설정의 반복계산으로 해결하고 있습니다 

- 배당금 : 배당금 받으면 수동으로 입력
- 입출금(환전) : 환전 한 데이터 입력

환전매수 = 주식산돈, 환전매도 = 주식판돈
예수금 = 주식사고 남은 돈

그래서 300달러를 환전후 100달러 주식을 매수 했다면 2번에 걸쳐 입력해야 합니다. 이유는 입출금기반 수익률에서 예수금은 투자금에서 제외하기 때문입니다.
- 일일 기록 : 매일 스케줄러와 스크립트에 자동으로 기록됩니다.
- 투자금 배분 : 투자 중인 종목에 비중과 투자금을 설정하면 자동으로 투자할 금액이 계산되도록 하는 시트

3) 출력 카테고리 - 구성 시트

- 일일 기록 : 매일 스케줄러와 스크립트에 의하여 투자 잔액, 수익률 등의 기록을 남깁니다.
- 차트 :  일일 기록 시트의 데이터를 기반으로 - 주식 운용 그래프, 현재 평가액 vs 잔고 금액을 그립니다.
- 기간 손익 : 금일 기준으로 특정 기간 전에 손실 상황을 보여 줍니다.

 

4) 데이터 카테고리 - 구성 시트

1. Stock Info : 미국 종목명 목록
현재는 투자하는 종목만 입력되어 있으나 차후에 데이터가 구해지는 대로 입력 예정

2. Stock History : 투자 종목의 1년간의 이후 히스토리
=SORT(GOOGLEFINANCE("SPYG","close",TODAY()-365,TODAY()),1,FALSE) 1년간의 종가를 표시하여 최근일 역순으로 정렬

3. 환율/지수 : 구글 파이낸스에서 20분 전
4. Code : 주식 유형, 성격, 섹터, Location, Market, 수수료율 등을 정의한 코드들이 있음.
5
. 휴일 : 미국 시장 휴장일 목록

 

구글 스프레드시트 접속하기

수정권한을 가지려고 하면 아래의 복사를 이용해 주세요.
https://docs.google.com/spreadsheets/d/1tGEJxPFKjwOn-n5VNuY7bBuXj5H3Sy_U9Yf-UsT4c9g

1) 구글 가입하셔서 일단 개인 드라이브로 복사해 주시고
https://docs.google.com/spreadsheets/d/1tGEJxPFKjwOn-n5VNuY7bBuXj5H3Sy_U9Yf-UsT4c9g/copy

2) 일일 기록 스크립트 등록 & 권한 부여
초기에 스크립트 실행을 위한 권한을 부여해야 하는데 보통 스크립트를 실행시키면서 권한을 설정합니다.
도구 메뉴의 스크립트 편집기를 선택하여 DailyRecord를 선택한 다음 Play 버튼을 누릅니다.

권한 검토를 누르고 계정을 선택한 후 확인되지 않은 앱이 나오면 고급을 누른 다음 stock(으)로 이동(안전하지 않음)을 누릅니다. 권한 확인이 나올 때 허용을 누르면 됩니다.

일일 기록 스크립트를 스케줄러에 등록

스크립트 에디터의 메뉴에서 시간 버튼을 눌러서 DailyRecord를 원하는 시간에 실행 되도록 등록을 해주면 됩니다

바탕화면에 일일 기록 일정 등록이라는 버튼으로 자동 생성되도록 만들어 놨습니다. 시간이 바뀔 때를 대비하여 위의 방법을 알아 두셔야 합니다.

오전 7시가 되면 일일기록에 자동으로 그날 수익률 정보가 기록되고 메일이 날라옵니다.

다음은 스마트폰에서 접근할 수 있게 웹앱을 설정해야 하는데 스크립트 에디터에서 게시를 누르고  웹 앱으로 배포를 누릅니다.

스마트폰에서 매수/매도 내역을 입력하기 위한 웹앱(Web App) 세팅하기

※ 현재 웹 앱 URL 을 잘 적어 주셨다가 혹시 바뀐다면 종합 시트(A15 셀의 HYPERLINK)에서 수정해 줘야 합니다.

스마트폰에서 매수매도내역 입력하기

2019/1/31

배당금 입력 폼 구현


제가 금융전문가가 아니고 수식과 계산 방법에 잘못된 곳이 있을 수 있습니다.

2019년 7월 2일 = 스크립트에 수식 적용 

수식이 있는 첫 행은 삭제에 주의해 주세요. 그래서 스크립트 내부에 수식을 넣었습니다. 
삭제 했다면 입력 폼을 통한 입력으로 한번 해주시면 될 듯
종합시트이외에는 수정해도 큰 문제가 없을 듯.

2019년 11월 22일

미래에셋 위주로 작성되어 있어 통합증거금의 환전 시트 입력 편의성은 없앴습니다. 주식이 매수 되면 증거금을 mts에서 보시고 수동으로 입력하시기를 바랍니다.

Google Apps Script for Google SpreadSheet 는 JavaScript 로 되어 있으므로 웹페이지를 만든 경험이 있으시면 배우기 쉽습니다. 폼 작성과 입력 부분은 홈페이지 만드는 것과 다르지 않으니 만들기 위한 충분한 정보가 모두 포함 되어 있습니다.


상업적이용을 금합니다

개인적으로 많은 시간을 할애하여  만들어 사용하고 있던 것이고 구글 스프레드 시트와 앱 스크립트 공부에 도움이 되도록 하기 위하여 공개하는 것입니다. 
일반적으로 구글 시트의 경우 상업용으로 개발되지 않는다면 스크립트 소스가 오픈 되어 있습니다.