본 포스팅에 사용되는 함수 :: IMPORTXML, IMPORTRANGE, IMPORTDATA, ARRAYFORMULA, SUBSTITUE, LEFT, RIGHT, CONCAT, IF, IFERROR, VLOOKUP, GOOGLEFINANCE, TODAY, SUM, SUMPRODUCT, UNIQUE, ISBLANK, INDEX
본 포스팅에 사용되는 Script :: Copy Column
- 종합잔고
백데이터는 이제 충분합니다.
이제 하나의 시트 안에서 모든 자료를 반영할 수 있도록 만들겠습니다.
기본적인 부분은 제가 제공해드리고, 추가적인 부분은 댓글 문의, 혹은 직접 짜셔도 되요~!
종합잔고 시트를 하나 만드시고,
A ~ P열 까지 사용하겠습니다.
A ~ C, E열은 직접 입력하는 열로 하고
D, F ~ P열은 함수를 짭니다.
D3:19 [화폐의 종류]
=IF(B3="","",IFERROR(GOOGLEFINANCE(B3,"currency"),"KRW"))
|
화폐단위를 설정해주는 함수입니다.
GoogleFinance에서 검색이 안되면 모두 KRW로 한다는 함수입니다.
F3:19 [매수액]
=IF(D3="","",IF(D3="KRW",(C3*E3),(C3*E3)*INDEX(GoogleFinance(CONCAT(D3,"KRW"),"close",A3),2,2)))
|
사용화폐가 KRW면 매수단가와 수량을 곱하고,
KRW가 아니면, 해당 화폐를 GoogleFinance에서 매수일 종가 기준 환율로 계산한다는 함수입니다.
서식
G3:19 [현재가]
=IFERROR(IF(D3="","",IFERROR(IFERROR(IFERROR(VLOOKUP(B3,backdata!A:B,2,0),GOOGLEFINANCE(B3,"price")),VLOOKUP(B3,'전자화폐'!A:B,2,0)),VLOOKUP(B3,'주식'!G:I,3,0))),"")
|
화폐의 종류가 비어있으면 그대로 놔두고, 채권정보에서 찾아보고 없으면, GoogleFinance에서 찾아보고 없으면, 전자화폐에서 찾아보고, 없으면 주식에서 찾아본다는 함수입니다.
H3:19 [평가액]
=IF(G3="","",IF(D3="","",IF(D3="KRW",(E3*G3),(E3*G3)*GoogleFinance(CONCAT(D3,"KRW")))))
|
화폐의 종류가 KRW면 현재가 x 수량, KRW가 아니면 GoogleFinance에서 환율 검색 후 반영해서 나타내는 함수입니다.
서식
I3:19 [수익률]
=IFERROR(IF(H3="","",H3/F3-1),"")
|
수익률 구하는 함수입니다.
서식
퍼센트(%)
J3:19 [비중](매수액 기준)
=IF(F3="","",F3/F$20)
|
매수액 기준으로 해당 종목의 비중을 구하는 함수입니다.
서식
퍼센트(%)
K3:19 [보유기간]
=IF(A3="","",(TODAY()-A3))
|
보유기간 구하는 함수입니다.
서식
F20 [매수액]
=SUM(F3:F19)
|
서식
H20 [평가액]
=SUM(H3:H19)
|
서식
I20 [수익률]
=SUMPRODUCT(I3:I19,J3:J19)
|
서식
퍼센트(%)
K20 [수익]
=H20-F20
|
서식
- 전환사채 전환평가
M3:19
=IF(RIGHT(B3,2)="CB",B3,"")
|
종목명 우측 2글자가 CB라면 종목명을 불러오는 함수입니다.
N3:19
=IFERROR(IF(M3="","",VLOOKUP(M3,backdata!A:C,3,0)),"")
|
불러온 종목명을 채권목록에서 찾아서 가져오는 함수입니다.
서식
O3:19
=IFERROR(IF(M3="","",VLOOKUP(SUBSTITUTE(M3,RIGHT(M3,4),""),'주식'!G:I,3,0)),IF(M3="","",VLOOKUP(SUBSTITUTE(M3,RIGHT(M3,3),""),'주식'!G:I,3,0)))
|
불러온 종목명의 주가를 찾아오는 함수입니다.
서식
P3:19
=IFERROR(IF(M3="","",(O3-N3)/N3),"")
|
전환가액과 현재주가대비 수익률입니다.
서식
퍼센트(%)
- 마치며
이로써 기본적인 종합잔고는 완성했습니다.
더 많은 자료를 추가해봤는데, 무한로딩 상태가 되버려서...
아무래도 구글 스프레드시트로는 이자 자동계산, 조기청구 행사 기간, 달력 응용 등등은 아직 무리인 것 같습니다.
로딩이 너무 오래걸려요.
아무쪼록 사용하시는 분들이 더욱 개량해서 잘 사용하시면 좋겠습니다.
공유버젼 URL ::
https://docs.google.com/spreadsheets/d/1QWEv2m0Dcv3eOWKGtfmpEUfIWhHr1zwGBHD60hjPpJs/edit?usp=sharing
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [Prologue]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(1)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(2)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [채권 리스트(3)]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [주식 리스트]
[GoogleSpreadsheet] - 구글 스프레드시트 :: 채권, 주식, 해외주식, 전자화폐 한눈에 보는 종합잔고 [전자화폐]