한빛출판네트워크

주식 투자자의 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분 지연 시세로 표시되고 베타(β)는 서비스되지 않습니다. 시총은 우선주 시총까지 포함한 금액이 표시되므로주의해야 합니다. 참고 목적으로만 사용하는 수치입니다.