주식 관리를 위한 구글 스프레드시트
(2021.01 업데이트 – 환차손, 매매차익 업데이트)
(2021.03 업데이트 – 손익실현 후 출금 금액 표현 추가)
(2022.01 업데이트 – 손익실현 표현 단순화 작업. DASH보드 자동화 작업. 양도세 추가(참고용). 환차손 추가(참고용))
일반의 초보 투자자들에게 구글 스프레드시트 (Google spreadsheets)는 주식 관리를 하기에 가장 적합한 툴입니다. googlefinance 함수를 사용하여 손쉽게 실시간으로 국내외주식 정보를 불러 올 수 있기 때문이죠.
저처럼 국내주식(연금)과 미국주식을 투자하시는 분들, 혹은 여러개의 계좌로 주식을 하시는 분들에게도 유용한 시트가 될 것 같습니다. 제가 운용하는 계좌는 아래와 같습니다. 샘플을 보시는데 참고하세요.
- 개인투자 계좌 (국내주식, 해외주식 혼용)
- 연금저축 계좌1 (채권형)
- 연금저축 계좌2 (주식형)
- IRP 계좌
순서
Sheet 구성
제가 활용하는 구글스프레드시트는 총 7개의 Sheet로 이루어져 있습니다. 7개의 Sheet를 모두 활용해야 정상 작동 합니다. 노란색 셀로 된 부분만 본인의 포트폴리오대로 수정하면 됩니다.
- Dashboard
- 투자일지
- 입출금일지
- 배당일지
- 월별자산
- 월별손익
- 리밸런싱
Sheet1. Dashboard
Sheet1은 Dashboard입니다.
2.투자일지, 3.입출금일지, 3.배당일지를 작성하면 위 화면처럼 각 계좌에대한 각종 정보를 보여줍니다. 저의 경우 총 4개의 계좌를 운용하기 때문에 4개의 Dashboard가 존재하며 별도로 연금계좌(채권형, 주식형, IRP)를 하나의 Sheet로 볼 수 있게 통합한 연금계좌통합 Sheet가 존재합니다. 연금계좌통합 Sheet는 예시일뿐이며 복잡하다고 생각이 들면 각 계좌별 Dashboard만 꾸리시면 됩니다.
Dashboard에서는 손익을 계산할 때에 원화로 환산된 금액을 기반으로 손익이 계산 됩니다. 즉, 아래 투자일지에서도 설명하겠지만, USD로 구매된 주식은 각각 환율에 맞게 원화로 구매 금액을 입력하게끔 되어 있습니다.
① 계좌 정보 입력
- 목적: 대분류 (예시-연금)
- 계좌: 계좌명 (예시-연금1, 연금2, IRP, 개인투자 등)
② 계좌 연간 납입액 설정(연금저축계좌만 해당)
- 납입목표: 연간 납입액 목표 (연금저축계좌+IRP 포함하여 연간 700만원까지 세액공제 대상에 포함됨)
- 납입액: 해당연도에 계좌에 납입한 금액
- 공제액: 연간소득액에 따라 납입액의 13.2% 혹은 16.5% 세액공제 가능 (개인연금 세액공제 혜택 바로가기)
(20.12 업데이트) – 환차손
또한 환차손 항목을 차변에 넣어 환차손이 얼마인지 직접적으로 확인 할 수 있게 만들었습니다. 수익률은 당연히 환차손이 반영되어 계산됩니다. (오로지 미국주식+한국주식 혼용 Sheet에만 적용되었습니다.)
(21.01 업데이트) – 매매차익 계산
주식의 매수 수량은 자동 계산되지만 아쉽게도 매수금액은 직접 입력해야 합니다. 매수금액을 직접 입력함으로써 각각 항목의 매매차익이 계산될 수 있게 했습니다. 매도시, 선입선출로 매도 차액을 입력만 하면 Dash board에 자동으로 표현되게 업데이트 되었습니다.
(21.03 업데이트) – 손익실현 후 출금 금액 추가
‘입출금’ 탭에서 ‘손익실현(출금)’ 항목을 추가하였습니다. 기존에 쓰시던 분은 이 항목만 복사해서 쓰시면 됩니다. 대시보드의 수식역시 그대로 복사해서 쓰시면 됩니다.
출금을 최근 처음해보면서 수익률 계산에 오류가 발생하는 걸 인지해서 추가하였습니다.
Sheet2. 투자일지
Sheet2는 투자일지 입니다.
국내외주식 매매거래일지라고 생각하시면 됩니다. 한국주식의 경우 매매시점의 원화(KRW)를 단가탭에 입력하시면 체결금액이 자동 계산됩니다. 미국주식의 경우 단가에 USD를 입력하며 체결금액 또한 USD로 계산됩니다. 하지만 원화(KRW)로 환산 될 때, 매매 시점의 환율이 제각기 다르므로 USD로 거래한 주식의 경우 원화 체결금액도 같이 입력하게끔 구현하였습니다. 원화 체결금액을 입력하시면 적용된 환율은 자동 계산 됩니다.
USD로 체결된 주식의 그당시 환율이 중요한 이유는 Dashboard에서 손익이 계산될 때 환차손을 반영하기 위함입니다.
① 투자 정보 입력
- 일자: 주식 매매 일자
- 환율: USD로 주식 매매 시 적용된 환율(매수금액 입력시 환율 자동 반영)
- 목적, 계좌: Sheet1의 Dashboard와 동일한 목적, 계좌 입력
- 매수/매도: 매수/매도 구분 입력
- 통화: KRW or USD 자동 반영
- 종목코드: 종목명 입력시 자동 반영
- 종목명: 종목명
- 거래수: 매수시 +, 매도시 –
- 체결단가: KRW, USD에 맞춰 입력
- 체결금액: 자동입력
- TOTAL(KRW): 모든 손익 계산은 KRW로 계산 되기 때문에 USD로 매매한 금액을 KRW로 환산.
② TOTAL(KRW) – 부연설명
- USD 거래 시, 매매 시점의 거래금액을 직접 입력해야 환율탭의 환율값이 자동 반영됨. 이렇게 계산된 환율값은 Sheet3. 입출금일지의 해당 주식의 매수출금시의 환율값으로 적용됨.
Sheet3. 입출금일지
Sheet3은 입출금일지 입니다.
투자일지를 작성하시고 나면 즉시 혹은 D+2, 3 영업일에 계좌에서 실제 입출금이 발생됩니다. 이를 통해 예수금이 계산되고 수익률에 반영됩니다. USD로 정산되는 모든 항목은 그 시점의 환율을 반영하시면 됩니다.
① 입출금 정보 입력
- 정산일자: 주식을 매매 하고 계좌에서 실제 입출금이 발생하는 시점(국내주식의 경우 D+2영업일, 미국주식의 경우 D+3영업일, IRP의 경우 즉시)
- 발생일자: 주식매매 시점
- 환율: USD로 주식 매매시, 매매시점의 환율 반영
- 목적, 계좌, 통화: 기설명
- KRW, USD: 입금건은 IN(+), 출금건은 OUT(-)에 입력
- TOTAL(KRW): 기설명
② 비고
- 투자원금: 기타수익을 제외하고 입금한 원금
- 매수출금: 매수 후 D+2일 or D+3일에 실현 된 출금 (기타수수료 포함), 단 계좌에서 출금시에는 ‘손익실현(출금)’ 탭 적용
- 매도입금: 매도 후 D+2일 or D+3일에 실현 된 입금 (기타수수료 포함)
- 기타입출금: 원금, 매매금, 배당금, 환전금액을 제외한 모든 입출금
- 배당금: 배당금 (기타수수료 포함)
- 환전: 환전 (기타수수료 포함)
- 손익실현(출금): 계좌에서 출금시 입력
Sheet4. 배당일지
Sheet4는 배당일지 입니다.
입출금일지에도 입력되는 배당을 따로 보기 위해 추출한 Sheet 입니다. 입력 방법은 입출금일지와 동일합니다. Dashboard와 호환되는 Sheet이기 때문에 필수로 입력해주셔야 합니다.
Sheet5. 월별자산
Sheet5는 월별자산 입니다.
월말기준으로 보유 주식의 가치와 예수금을 평가하여 월별손익을 계산하는데 사용됩니다.
※ Sheet5~7은 Dashboard에 직접적으로 반영되는 수치는 아닙니다.
① 계좌 정보 입력
- 기설명
② 월별 정보 입력
- 월말(영업일기준) 날짜를 입력하면 당시의 보유주식 수량, 단가, 환율이 입력됩니다.
③ 월말 자산 확인
- 월말 기준 보유 주식 수와 예수금이 포함된 자산을 확인 할 수 있습니다.
Sheet6. 월별손익
Sheet6은 월별손익 입니다.
앞서 입력한 모든 Sheet에서 월별 자산, 월별 투자금을 계산해서 수익률로 표현합니다. 이미 모든 정보는 입력되었기에 일자와 월별자산만 입력하면 됩니다.
① 월말 기준의 환율, 벤치마킹지수를 나타냅니다.
② 계좌명 입력
③ Sheet5의 월말자산 입력
④ 계산된 수익률을 그래프로 표현합니다.
Sheet7. 리밸런싱
Sheet7은 리밸런싱 입니다.
기존에 설정된 포트폴리오의 종목 비율을 유지하며 투자하는데 도움을 주는 시트입니다. 혹은 기존 설정의 포트폴리오 비율을 바꿨을 때 매매 주식 수를 계산해줍니다.
① 항목별로 계좌 정보를 입력합니다.
② 추가납입은 현재 각 계좌의 예수금으로 설정되어 있으나, 원하는 금액으로 강제입력해도 됩니다.
③ 리밸런싱을 수행하고 나서의 예수금 정보를 나타냅니다. ( – ) 금액으로 나타난다면 리밸런싱을 하기 위한 투자금이 부족하다는 의미입니다.
구글 스프레드시트 공유
위에서 설명드린 Jhonber.com의 관리시트는 아래 링크에서 보기 가능합니다.
구글 스프레드시트로 접속되면 반드시 파일 -> 사본만들기 로 본인의 계정에 사본을 만든 뒤 작업하세요.
공유: Jhonber.com 투자관리시트(Google spreadsheets) 바로가기
※ jhonber.com 투자관리시트는 슬기로운 자본주의생활법의 서대리님 관리시트를 기반으로 합니다. USD 거래를 위한 일부 sheet 수정 및 Dashboard가 수정되었습니다.
※ 매수 및 매도로 시세차익 혹은 손실이 발생된 종목은 Dashboard에 표시되는 평균단가가 앱에서 보여주는 단가와 다를 수 있습니다. 이는 손실 혹은 이익이 반영된 단가여서 그렇습니다. 시트가 완벽하지 않은만큼 업데이트되면 다시 공지하겠습니다.
안녕하세요, 정말 훌륭한 국내외 주식관리 시트인것 같습니다. 공유해주셔서 감사합니다! ^^
한가지 질문이 있습니다.
Apple 이나 테슬라 주식은 중간에 액면분할을 했는데요, 이럴 경우는 데이터 입력을 어떻게 하면 되는지요?
안녕하세요~
주식을 한지 채 1년이 안되다보니 아직 액면분할건이 한번도 없어서, 생각해보지 못했네요!
그리고 이 시트가 아직 미국주식 관련해서는 완벽하지 않아서 보완할 점이 많이 있습니다.
액면분할 관련해서 문득 떠오른 아이디어로는,
1. 액면분할이 되면 매수 총액은 변동없이 주수만 변하게 되어 단가가 낮아질텐데요
2. Dashboard 에서 총액만 입력하게끔 되어 있는데, 총액과 주수까지 강제로 입력을 해야 할 것 같습니다.
3. 그외 ‘투자일지’ 시트에는 수정할게 없어보입니다. 어차피 dashboard에서는 ‘투자일지’의 수량과 총액을 불러올 뿐이기 때문입니다.
아이디어일 뿐인데, 임시방편으로는 이방법이 제일 심플해 보입니다.
빠른 답변 감사합니다! ^^
사본 만들기했습니다. 좋은 자료 공유 감사합니다. 성투하세요 ^^
좋은 자료 공유 감사합니다. 딱 원했던 방향의 스프레드시트입니다.^^ 어떻게 만들어야 하나 고민하던 찰나에 단비를 만났네요~~
완벽한 것 같아요~ 감탄하며 잘쓰겠습니다!!
매수 가격을 원으로 만들려면 어떻게 하나요? 한국 주식같은 경우 매수가격이 자동으로 달러로 바뀌는데.
안녕하세요
가장 쉬운 방법으로는 한국주식이 입력된 행을 그대로 복사해서 쓰시면 됩니다.
다른 방법으로는, 구글스프레드 시트 메뉴에 서식->숫자->통화->원화 선택하시면 됩니다.
‘통화’항목에 USD 또는 KRW를 인식해서 자동으로 통화 인식이 되게하는 방법은 있나요 ?
안녕하세요
조건부서식으로 가능할것같은데요, 한번 해보고 가능하면 수정한 뒤에 알려드릴께요!
항상 감사합니다! 새해 복 많이 받으세요:)
혹시 매일 잔고를 자동기록하는게 가능할까요 현재 총금액계산이아니라 현재 총금액을 차례로 매일 한번씩 기록하는거요 1달후면 30열 기록하게되는거요
안녕하세요~
월단위로는 기록 가능하게 해놨는데요, (‘월별자산’ 시트)
일단위로는 구현하지 않았습니다.
불현듯 떠오르는 생각으론 날짜만 입력하면 그날의 잔고를 기록할 순 있을 것같습니다.
요즘바빠서 수식 수정을 많이 못하고있는데 시도 해보겠습니다 ㅜㅜ
Googlefinance 함수 결과값을 달러 값이 아닌 원화 값으로 나타내는 법 좀 알려주실 수 있을까요?
1.계좌:개인투자 탭 P열에서 googlefinance(티커,”price”) 함수를 사용하니 애플은 달러로 표시되고 삼성전자우는 원화로 표시되시는데 제가 따라하면 삼성전자 가격이 1610 즉 달러로 표시됩니다. 위 댓글을 보고 서식을 원화로 선택하니 숫자는 달러인데 앞에 표시만 KRW 1610로 표시됩니다. 어떻게 해야 미국주식은 달러로 결과값을 내고 한국주식은 원화로 결과값을 낼 수 있는지 궁금합니다.
안녕하세요!
앗 이상하네요. =GOOGLEFINANCE(C36,”price”) 함수에서 표현되는 값은 그 주식값의 ‘가격’만을 표현합니다. 즉 원화든 달러든 숫자는 동일한값을 표현하는거죠. 자동으로 환율을 적용해 원화로 변화시켜준다던지 혹은 반대로 달러를 원화로 환산시켜주는 기능은 없습니다. 제 생각엔 삼성전자 티커가 잘못입력된게 아닐까요?
유용한 자료 감사합니다 🙂 한가지 여쭤보고 싶은게 있는데요~
혹시 종목코드에 없는 항목은 임의로 입력하면 1계좌:개인투자 시트 Symbol코드로 불러와지는게 맞을까요?
미국 주식이나 ETF 제 계좌에 맞게 추가를 했는데 코드가 끌려오지 않네요; 답변 주시면 넘 감사하겠습니다!!
안녕하세요!
앗 제가 설명을 누락했네요. 국내주식은 모두 상장된 대부분의 종목을 자동으로 불러오지만, 미국주식은 제가 투자하는 것만 불러오게 해놨습니다.
심볼을 직접 입력하시거나, 숨겨진 시트중에 ‘종목’ 시트를 불러오셔서 본인의 심볼과 종목 이름을 등록하시면 됩니다!
안녕하세요! 좋은 자료 덕분에 투자일지 작성중입니다. 일단 자료 공유 너무 감사드립니다.
그런데 혹시 입출금현황은 증권사에서 거래내역 다운받아 하는 것 외에 좀 더 효율적인 방법 있을까요?
과거 데이터까지 모두 입력해야 하는데… 데이터양이 좀 많아 거래내역 RAW파일로 작업하면 가공이 꽤 필요하더라고요… 좀 간단하게 데이터 추출하고 기재할 수 있는 방법…조언 받을 수 있을까요?
안녕하세요! 음 사실 과거의 데이터를 모두 기록하는게 쉬운일은 아닙니다.
증권사의 RAW파일을 자동으로 업로드 할 수 있으면 좋겠지만 각 증권사의 포멧이 상이해서 일괄적으로 적용하기 힘든 부분이 있습니다.
문득 떠오르는 생각으로는 아래 방법이 가장 수월할 것 같아요!
1. 증권사 데이터를 다운받습니다.
2. 존버스프레드시트에서 입출금 탭에서 인식하는 항목중 중요하게 계산되는 항목은 ‘투자원금’ ‘매수출금’ ‘매도입금’ ‘손익실현(출금)’ 탭 입니다.
3. 증권사 데이터에서 위 항목을 필터로 추출해서 각 항목의 총 합계만 입력합니다.
4. 나머지 항목들도 최대한 증권사 데이터에서 추출한 뒤에 합계만 입력합니다.
이렇게 하면 하나하나 기록하지 않아도 과거의 데이터를 계산하는데에는 문제 없을 것 같습니다.
저는 처음부터해서 과거의 데이터를 입력해보지 않았는데, 아무래도 지금으로썬 이 방법이 최선일 것 같습니다.
좋은 자료같아 공유 부탁드려도 될까요?
공유 설정에 오류가 있었나봐요. 재설정 하였습니다.
파일->사본만들기
하셔서 본인의 드라이브에 복사하시면 됩니다.
감사합니다.
안녕하세요 주식을 처음 시작하며 어떻게 종목 관리를 할까 엄두가 안났는데 이렇게 좋은 자료를 공유해 주셔서 감사합니다.
한가지 궁금한 것이, 투자일지 설명 부분에 종목명을 입력하면 종목 코드가 자동으로 반영 된다 하셨는데 함수가 적용이 안되는거 같아서요. 종목코드를 직접 입력해야 하는 건가요?
안녕하세요~
시트중 ‘종목코드’가 있습니다. 종목명을 입력하면 이 시트에서 코드를 불러오게 되어 있습니다.
그래서 국내코드같은 경우는 대부분 다 들어있지만 미국주식같은 경우는 제가 매매하는 종목만 들어가 있습니다.
입력이 안되는 종목은 ‘종목코드’ 시트에 들어가셔서 수동으로 입력해주시기 바랍니다.
감사합니다~
안녕하세요, 펀드같은경우에는 종목코드를 제가 따로 업데이트 해도 구글파이낸스에서 검색이 안되서 안되더라구요.. 혹시 방법이 있을까요 ㅠㅠ??
안녕하세요~ 구글파이낸스에서 검색가능한 종목은 국내 상장 주식(ETF포함)만 가능합니다. 펀드는 실시간 시세를 제공하지 않습니다 ㅠ
좋은 시트 공유해 주셔서 감사합니다.
질문이 있습니다. Sector에서 “Developed”가 무슨 의미인가요..?
안녕하세요~ 섹터는 사실 크게 의미가 없습니다. 자동분류되는 것도 아니고 기준도 따로 없으니까요. Developed 라고 적어놓은것은 단순히 미국주식(선진국)이기 때문에 그렇게 적어놓았습니다.
와… 정말 대단합니다… 지금까지 본것 중 최고입니다…
제가 코인에도 투자를 시작했는데 혹시 코인 시트도 적용이 되는 건가요??
아니면 코인 시트 작성하신건 없으신지요??
안녕하세요! 저도 소액으로 코인을 재미삼아 하긴 하지만, 코인시트는 불필요할 것으로 생각됩니다. 자주 거래 해야 하는(?) 코인 특성상 시트가 오히려 불편할 것 같아요. 구글파이낸스에 매우 제한적인 코인만 연동이 되는 걸로 알고있고 한국 가격이 아닌 글로벌 시세가 연동이 되서 국내거래소를 이용하는 저희같은 사람들에겐 불편할 것 같습니다.
아… 그렇군요…. 저는 장투 스타일이라 제가 딱 원하는 스타일이라 코인 거래에도 적용하면 좋겠다는 생각을 하고있었던 터라…ㅎㅎ 그리고 요즘은 국내 거래소도 다 연동이 가능해서 질문 드려봤습니다..^^ 주식에는 유용하게 이용토록 하겠습니다… 감사합니다^^
안녕하세요? 우선 좋은 자료 공유해 주셔서 감사합니다. 내용 채우다 보니 궁금한게 있어서요. 투자일지 시트에서 맨 우측에 ‘total’ 열이 있는데요,, 미국주식같은 경우 수기로 원화체결금액을 넣으라고 하셨는데,, 이 말은 체결하고 2~3일 뒤에 실제 입출금 일어날 때 원화금액을 넣으라는 말씀이신가요?? 입출금 시트에도 원화로 금액 넣는 부분이 있는데 이 값과 같은 값이 되는 건 아닌지 해서요.. 두번째는 요 파일을 처음 활용할 때 기존에 갖고 있는 주식을 어떻게 입력하면 좋을까요? 가령 투자일지 시트에서 ‘total’ 열 같은 값을 어떻게 넣을지 좀 애매합니다. 바쁘시더라도 좋은 의견 부탁 드립니다. 고맙습니다. ^^
안녕하세요~ 투자일지 시트에서 입력하는 원화금액은 입출금시의 원화금액과 다릅니다. 투자일지에서의 원화금액은 매매체결 당시의 환율만 반영된 금액입니다. 입출금 시트에서는 세액과 수수료가 공제된 금액이 입력됩니다. 저는 미래에셋증권을 이용하는데 매매체결시에 원화금액이 떠서 쉽게 입력하고 있는데 다른 증권사는 어떤지 잘 모르겠네요! 만약 사용하시는 증권사에서 매매체결 당시의 환율만 제공한다면 변환하셔서 사용해도 무리 없을 것 같습니다 ㅎㅎ
수정권한 요청 드렸습니다. 감사합니다.
안녕하세요? 공유해주신 파일 잘 활용하고 있습니다. 쓰다 보니 문득 궁금한 게 생겼서요. 3번. 입출금 시트에서 기존에 입력된 예시를 보면 ‘환전’할 때 항상 두개 열을 쌍으로 입력하셨던데요,, 그러니까 원화로 출금되고 달러로 입금되는 형식으로요.. 이걸 한줄로 한꺼번에 쓰면 안될까요? 다른 수식상에 장치가 있나 싶어서 여쭤봅니다. 선한 공유 감사 드립니다 ^^
안녕하세요~ 저도 최대한 간단하게 만들고싶었지만 요건 도무지 아이디어가 떠오르지 않아 두줄로 입력하고 있습니다 ㅜ
안녕하세요? 배당일지는 수동으로 일일히 입력을 해 주어야 하나요?
안녕하세요~ 네 배당일지는 수동으로 입력해야 합니다.
종목이 많으면 일일이 입력 하는게 힘들긴 합니다 ㅎㅎ;