구글 파이낸스 수익률 - gugeul painaenseu su-iglyul

주식 투자자의 43%는 코로나19 이후 주식을 시작했고, 이중 92%는 주식 투자를 계속할 예정이라고 합니다. 투자한 종목과 관심 있는 종목의 데이터를 자동으로 불러와서 관리할 수 있다면 얼마나 좋을까요?

여기 국내외 주식 시세 정보를 불러오는 구글 스프레드 시트의 멋진 GOOGLEFINANCE 함수에 대해 알아보겠습니다.

GOOGLEFINANCE로 금융 데이터 불러오기

GOOGLEFINANCE는 매우 강력한 함수입니다. Morningstar, Thomson Reuters, ICE Data Services 등으로부터 제공받는 금융 상품 데이터를 불러올 수 있습니다. 전 세계 증시에 상장된 주식들의 정보뿐 아니라 채권, 환율, 암호화폐에 관한 데이터까지 제공됩니다.

GOOGLEFINANCE 함수를 통해 국내, 해외 주식의 정보를 가져오는 스프레드시트를 만들어보겠습니다.

01 연두색 배경 부분을 완성해보겠습니다. [E4]셀에 수식=GOOGLEFINANCE(JOIN(":",$B4,$C4),E$2)를 입력합니다. 인수 중 티커를 거래소와 코드를 결합한 문자열로, 속성을 [E2]셀의 값인 “PRICE”로 입력해주는 수식입니다.

02 최근 30일 그래프를 넣을 L열을 제외한 [E4:K4]까지 선택하고 Ctrl + R 을 눌러 [E4]셀의 수식을 범위 안에 붙여넣습니다. [F2]셀부터 [K2]셀의 값이 속성 인수로 적용됩니다.

03 [G4]셀의 등락률은 % 수치이므로 소수점 둘째 자릿수까지 표시되도록 [소수점 이하 자릿수 증가]를 클릭하여 숫자 서식을 바꿔줍니다.

04 [L4]셀은 SPARKLINE 함수를 써서 소형 차트를 만들 겁니다. [L4]셀에 수식 =SPARKLINE(GOOGLEFINANCE(JOIN(":",$B4,$C4),"PRICE",TODAY()-30,TODAY()))를 입력합니다. 30일 전부터 현재까지의 종가를 배열로 받아서 SPARKLINE 함수에 넣어주는 수식입니다. SPARKLINE 함수는 셀 안에 간단한 선, 막대 차트를 그려주는 함수로 자세한 설명은 6장의 마지막에서 알아봅니다.

05 아래쪽까지 동일한 수식을 채워 넣기 위해 [E4:L10] 범위를 선택하고 Ctrl + D 를 눌러줍니다

06 해외 주식의 경우 소수점 둘째 자릿수까지 나오도록 [E9:F10], [H9:J10] 범위를 선택하고 [소수점 이하 자릿수 증가]를 클릭하여 숫자 서식을 바꿔줍니다. 이제 언제든 구글 스프레드시트를 열어놓고 제 잔고를 보며 뿌듯해 할 수 있습니다. 저런 수익률이면 좋겠군요

여기서 잠깐!
GOOGLEFINANCE를 쓸 때 고려할 점▶  국내 증시는 20분 지연 시세로 표시됩니다. 현재가 부분은 실시간 주가가 표시되는 국내 포털 사이트 정보를IMPORTXML 등의 함수로 가져오면 실시간 주가로 반영할 수 있겠죠. 수식에 ARRAYFORMULA를 쓰지 않은 것은 GOOGLEFINANCE 함수가 배열 함수라서 ARRAYFORMULA와 함께 사용할 수 없기 때문입니다. B열에 사용한 거래소 코드 입력의 데이터 확인 목록은 [EXCHANGE] 시트에 체크 박스와 FILTER 함수를 이용해 만들어놓았습니다. 궁금한 분들은 참고하시기 바랍니다.

GOOGLEFINANCE 함수로 확인 가능한 정보는 https://www.google.com/finance 에서 모두 조합해서 볼 수 있기 때문에 단순히 정보를 모아서 보기 위해 스프레드시트를 쓰는 것은 좋은 생각이 아닙니다. 하지만 매일매일 갱신되는 정보를 가지고 뭔가 계산을 해야 한다면 스프레드시트에 데이터를 끌고 오는 게 필요하겠죠. 매출채권의 환산손익을 계산하는 시트를 만들어봅시다. 간편한실습을 위해 매출액, 평가액, 환산손익은 미리 수식을 입력해두었습니다.

01 [매출액(G열)은 채권발생일의 환율*외화금액으로 계산됩니다. [F9]셀에 수식
=INDEX(GOOGLEFINANCE("CURRENCY:"&$D9&$C$2,"PRICE",$C9),2,2)를 입력합니다. 환율을 조회하는 티커는 CURRENCY:from통화to통화입니다. USD to KRW인 경우라면 CURRENCY: USDKRW가 됩니다. 현재 환율을 조회하기 위해서는 티커만 입력해도 되지만 과거의 환율이 필요하므로 "PRICE" 속성과 과거 일자($C9)를 시작일로 기재했습니다.

02 기준일의 매출채권 평가액(I열)은 기준일의 환율*외화금액으로 계산합니다. GOOGLEFINANCE함수를 과거 기간에 대해 사용하는 경우에는 항상 헤더가 함께 반환됩니다. 이를 제거하기 위해INDEX 함수로 배열의 2행 2열 위치의 환율값만 가져오겠습니다. [H9]셀에 수식 =INDEX(GOOGLEFINANCE("CURRENCY:"&$D9&$C$2,"PRICE",$C$3),2,2)를 입력합니다. [F9]셀의 수식에서 날짜만 기준일이 적힌 [C3]셀로 바꿔준 것입니다.

03 ARRAYFORMULA가 적용되지 않은 아래 셀들도 동일한 수식으로 채우기 위해 [F9]셀을 선택하고 [F15]셀까지 자동 채우기 핸들을 드래그합니다.

04 [H9]셀을 선택하고 [H15]셀까지 자동 채우기 핸들을 드래그합니다. ARRAYFORMULA가 적용된 G, I, J열은 값이 자동으로 채워집니다. 외화 매출채권의 환산 시트가 완성되었습니다.

GOOGLEFINANCE에서 제공하는 정보의 항목들과 관련 정보들 그리고 사용 시에 유의할 점은다음 페이지를 참조하십시오. 전 세계 증권 거래소의 주식과 주가 지수, 미국과 인도의 뮤츄얼 펀드, 미국의 채권 지수, 그리고 각국 환율(Morningstar)과 암호화폐 시세(Coinbase)를 제공합니다.

GOOGLEFINANCE 함수에서 사용 가능한 속성은 다음과 같습니다. 한국 증시는 20분 지연 시세로 표시되고 베타(β)는 서비스되지 않습니다. 시총은 우선주 시총까지 포함한 금액이 표시되므로주의해야 합니다. 참고 목적으로만 사용하는 수치입니다.

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

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

예전에 엑셀로 만든 주식 수익률 관리를 구글 스프레드시트로 옮겨 봤습니다.Excel 소프트웨어가 필요하고...

blog.naver.com

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

요즘 폭락장이 계속되어 -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

공유하기

게시글 관리

저작자표시 비영리 변경금지

• 카카오스토리
• 트위터
• 페이스북

'컴퓨터 활용 > 구글스프레드시트 응용' 카테고리의 다른 글