마지막 매수 건 이후 수익률 구하기 @ 구글 스프레드시트로 하는 주식 수익률 관리

네이버 증권 주식 수익률 관리가 없어진 이후 구글 스프레드시트로 투자하고 있는 주식을 관리하기 위하여 만들었는데 필요한 기능을 하나씩 추가하였는데 공개용 버전에도 추가해 두고 블로그에도 어떻게 만들었는지  공부 삼아 필요하신 분들을 위해 공유해봅니다. 
blog.naver.com/yonghan_shin/220992801774

구글 스프레드시트로 하는 주식 수익률 관리

국내 주식관리 스프레드시트에 마지막 매수 건 이후 수익률 열을 추가하였습니다.

요즘 폭락장이 계속되어 -5% , -10% 기준을 세우고 추가 매수를 하려고 하는데 

마지막 주식 매수 이후에 얼마나 떨어졌는지 알기 위해서입니다.  

그리고 어떻게 만들었는지 엑셀 수식도 설명하고요. 

​주식 목록 시트

네이버 매수 후 -8% 먹었네요.  물 탈 때가 아닌가 싶습니다.

​

주식 관리 시트의 AS 열에 위치 되어 있고 열의 이름은 "마지막 매수 이후 수익률" 

 수식은 다음과 같습니다.  

 =E2/( 
   INDEX(매수단가,
     ARRAY_CONSTRAIN(ARRAYFORMULA(
      MATCH(
       ARRAY_CONSTRAIN(ARRAYFORMULA(
         MAX(매수일자*(매수종목코드=AE2)*(매수일자<TODAY()))
       ),1,1) & AE2 , -- 배열수식1
       매수일자&매수종목코드,
       0)
      ),1,1)
     ) -- 배열수식2 의 끝
  )-1

매수 시트

매수 시트의 구성은 다음과 같이 되어 있으며

1) A2부터 마지막 열까지 = 이름 영역 : 매수 일자

2) C2부터 마지막 열까지 = 이름 영역 : 매수 종목코드

3) E2부터 마지막 열까지 = 이름 영역 : 매수단 가로 되어 있습니다.

1. 수익률 구하기

수익률 = E2/ 과거가격 -1

E2는 현재 가격입니다.

 

2. 마지막 매수일의 매수 일자를 구합니다.

과거 매수일자의 수식은 ARRAY_CONSTRAIN(ARRAYFORMULA( 배열수식*),1,1)

배열수식*은 MAX(매수일자*(매수종목코드=AE2)*(매수일자<TODAY())) 으로 배열 수식에 치환하면 값이 구해집니다. 5~7 번 라인에 해당됩니다.

ARRAY_CONSTRAIN(ARRAYFORMULA( MAX(매수일자*(매수종목코드=AE2)*(매수일자<TODAY()))),1,1)

3. 값 검색 = 최종 매수단가가 있는 열 번호

다음은 그 매수 일자의 매수단가를 구하기 위해서 INDEX 및 MATCH 함수로 매수 시트를 검색합니다.

MATCH 함수는 영역에서 정확한 값이 있는 행을 찾는 것이고 INDEX는 영역의 실제 값을 반환합니다.

MATCH(찾을 값 , 찾을 영역 , 0 )

이렇게 하면 찾을 값이 있는 열 번호를 구할 수 있습니다.

그런데 여기서 매수 일자&매수 종목코드를 쌍으로 찾아야 하기 때문에 여기서도 배열 수식을 적용합니다.

​

MATCH( 2번의마지막매수일자 & AE2 , 매수일자&매수종목코드 , 0 )

AE2는 매수종목코드 입니다. 매수일자&매수종목코드는 이름영역1 & 이름영역2 이고요.

​

이름영역이 배열이므로 여기서 배열 수식을 적용 합니다.

2 번에서 ARRAY_CONSTRAIN(ARRAYFORMULA( 배열수식*),1,1) 이라고 했죠.

​

즉, 매수 일자의 매수단가가 있는 열은 3 ~ 10 번에 해당됩니다.

구해진 열 번호를 가지고 이제 실제 값을 찾아야죠. INDEX ( 영역이름, 열번호)

INDEX(매수단가, 3번에서 구해진 값)

해당 부분만 발췌하였으니 수식을 참고하세요.

​

MS 엑셀에서는 ARRAY_CONSTRAIN(ARRAYFORMULA( 배열수식*),1,1) 는 { 배열 수식}으로 대체 가능합니다. 배열 수식을 입력하고 CTRL SHIFT ENTER를 누르면 될 거예요.

​

​

https://docs.google.com/spreadsheets/d/1YC8mTc6GP4M5ay1BfE3TC104AjE3CoYQeJFvuUiR3Jk/edit?usp=sharing