주식을 진지하게 임하면서 자연스럽게 내가 매매한 종목들에 대한 정리가 필요했다.
처음 주식을 공부할 때처럼 구글, 네이버, 유튜브를 뒤지기 시작했고 구글 시트와 구글 파이낸스 함수를 활용하여 나만의 포트폴리오를 만들기로 했다. 처음에는 '가계부와 많이 다르겠어?' 하며 쉽게 생각했는데 확실히 주식 포트폴리오는 단순한 자산 포트폴리오와 많이 달랐다. 게다가 내가 만들고자 하는 것은 매매일지가 아니기 때문에 조금 더 공을 들여보기로 했다.
인터넷에 돌아다니는 온갖 고수들의 포트폴리오를 보고 또 보면서 현재 4개월째 정착 중인 내 포트폴리오.
그중에서도 Analysis sheet이다. 모든 데이터는 함수로 연동되어있고, 매일 저녁 미국 장이 열리면 실제 주식 종목 창이 움직이듯 수치가 바뀐다. 비록 20분의 지연 시간이 있지만, 전체 종목에 대한 그날의 수익률, 배당률, 고점 대비 변화 등 한 시트에서 흩어져 있는 정보를 볼 수 있다. 각 항목별 조건부 서식을 통해 직관적으로 수익/손실을 바로 확인할 수 있도록 했기 때문에 이번 포트폴리오 버전에 매우 만족하고 있다.
이것이 가능한 것은 엑셀 함수와 구글에서 제공하는 구글 파이낸스(Google finance) 함수를 적절하게 사용했기 때문이다. 오늘은 일반적인 엑셀 함수보다 구글 파이낸스 함수를 우선적으로 정리해볼까 한다.
Google Finance
구글 시트는 엑셀과 거의 비슷하지만 온라인으로 여러 정보를 실시간으로 불러올 수 있는 점이 가장 다른 점이 아닐까 생각한다. 그 중 가장 쉽게 사용할 수 있는 것이 이 구글 파이낸스 함수이지 않을까. 함수에 대한 정보만 잘 활용한다면 구글 파이낸스 사이트에서 제공되는 수많은 정보 중 나에게 맞는 정보만 선별하여 언제든 사용할 수 있다.
구글 파이낸스 함수의 기본값은 이렇게 생겼다.
=GoogleFinance("ticker","속성")
: 해당 종목(ticker)에 대한 속성 값을 보여준다
구글 파이낸스에서 불러올 수 있는 주식 중 한국/미국 주식은 다음과 같다
코스피 상장사 (KRX)
코스닥 상장사 (KOSDAQ)
국내 상장 ETF (KRX)
미국 NYSE 상장사 (NYSE)
미국 나스닥 상장사 (NASDAQ)
미국 상장 ETF (개별 티커, 별도 상장사)
그리고 국가별, 상장사별 구분되어 있는 주식도 구별할 수 있다. 예를 들어, 주식 티커가 같은 종목이 있다면 함수값을 불러오는 과정에서 2개 이상의 정보가 충돌을 일으킨다.
내가 원하는 종목의 티커와 상장사를 확인하고 싶을 때는 구글 검색창에 해당 종목명이나 회사명을 검색하면 쉽게 확인할 수 있다.
구글 파이낸스 함수의 장점은 실시간 데이터를 반영하는 것이다. 그렇다면 어떤 데이터를 실시간으로 확인할 수 있을까.
실시간 데이터를 반영할 수 있는 속성 중 가장 자주 쓰이는 항목은 다음과 같다.
"name" 종목의 전체 이름
"price" 실시간 가격 정보(20분 지연 값)
"priceopen" 개장 시점 가격
"high" 현재 날짜의 최고가
"low" 현재 날짜의 최저가
"volume" 현재 날짜의 거래량
"marketcap" 주식의 시가 총액
"tradetime" 마지막 거래 시간
"datadelay" 실시간 데이터의 지연 정도
"volumeavg" 일일 평균 거래량
"pe" 가격 대 수익률
"eps" 주당 순이익
"high52" 52주 최고가
"low52" 52주 최저가
"change" 전 거래일 마감 이후의 가격 변동
"beta" 베타 값
"changepct" 전 거래일 마감 이후 주식 가격 백분율 변동
"closeyest" 전일 종가
"shares" 발행 주식 수
이밖에도 특정일 기준의 금액, 거래량, 기업 자산 가치, 뮤추얼펀드 수익 관련 데이터 등의 속성 값을 활용하면 다양한 정보를 함수 하나만으로 손쉽게 불러올 수 있다.
엑셀 함수처럼 각 함수마다 적절한 주식 티커와 속성 값을 설정하여 나만의 포트폴리오를 구성할 수 있다. 이제 실제로 내가 사용하는 포트폴리오를 예시로 가장 많이 사용할 듯한 5가지 구글 파이낸스 함수를 보면서 어떻게 수식이 응용되는지 보면 훨씬 이해하기 쉬울 것이다.
1. Price
- 설명: 실시간 가격 정보(최대 20분 지연 값)
- 예시
=GoogleFinance($C27,"PRICE")
구글 파이낸스 기본 함수에서 티커 값을 입력해야 하는 자리에 마이크로소프트 티커 값이 입력된 셀($C27)이 선택되어 있다. 그리고 속성에는 실시간 가격 정보를 나타내는 명령어인 "Price"를 입력하였고, 이를 실행하면 매일 이 셀(F27)에는 마이크로소프트의 실제 주가 변동되는 금액이 나타날 것이다.
그리고 위 예시 이미지처럼 현재가를 기입한 F행의 값은 모두 C행에 입력된 각 주식 종목의 티커 값을 기준으로 실시간 가격 정보를 불러오고 있는 것을 볼 수 있다.
2. changepct
- 설명: 전 거래일 마감 이후 주식 가격 백분율 변동
- 예시
=GoogleFinance($C33,"changepct")
간략한 설명만 보았을 때는 어렵게 느껴질 수 있지만, 이렇게 생각해보면 쉽게 이해할 수 있다.
'어제보다 가격이 5% 정도 떨어지면 추가 매수를 하고 싶은데' 하는 생각이 들 때가 있다. 한 종목만 뚝심 있게 투자하는 것이 아니라면 전일 종가 대비 현재 금액이 어느 정도 변화를 나타내는지 쉽게 알 수 없다. 이럴 때 "changepct" 속성을 활용한다면 전일 대비 당일 실시간 변화를 눈으로 쉽게 확인할 수 있는 것이다.
쉽게 말해 어제보다 쌀 때 줍고 싶은데 '주을까 말까' 고민될 때 써먹으면 좋을 속성이라고 볼 수 있다.
3. low52
- 설명: 52주 최저가
- 예시
=GoogleFinance($C36,"low52")
4. high52
- 설명: 52주 최고가
- 예시
=GoogleFinance($C36,"high52")
많은 사람들이 현재 주가가 저평가되었는지, 고평가 되어 있는지 확인할 때 52주 최고/최저가를 대입하여 설명하는 것을 쉽게 볼 수 있다. 속성 이름도 직관적이랄까. 이 속성은 현재부터 과거 1년 사이에 변동된 주가 중 가장 낮을 때(low52)와 가장 높을 때(high52)를 쉽게 나타내 주는 속성이다.
이를 특히 잘 활용했을 때는 코로나로 전 세계 주식이 직격타를 맞은 3월 말 이후, 일부 섹터 주식들이 빠르게 회복을 하며 특정 회사는 코로나 이전 최고가를 수시로 갱신하기도 했다. 이때 이 속성을 활용하여 주가 상승 비교를 하기도 했다.
5. name
- 설명: 해당 종목의 풀네임
- 예시
=GoogleFinance($C50,"name")
이 속성은 단일 종목 주식에는 크게 쓸 일은 없는 것 같다. 다만, ETF의 경우에는 상품명이 길다 보니 많은 사람들이 티커를 이름처럼 외우고 있는데, 간혹 상품명 전체를 알아두면 좋을 때가 있다. ETF 상품명에는 운용사와 ETF의 특징을 그대로 담고 있기 때문이다. 종종 티커가 겹치는 ETF는 오류가 날 경우 상품명과 상장사를 확인하여 정확히 함수를 매칭 하는 것이 좋다.
Google Finance 응용 편
크게 사용할 일은 없지만 함수를 활용했을 때 유용했던 속성도 함께 소개해볼까 한다.
간단한 티커 변경만으로도 구글 시트에서 시장 지수와 인덱스 지수 변화도 실시간으로 확인할 수 있다. 상장되어 있는 회사들이 모두 주식 시장의 흐름과 항상 동일하게 움직인다고 단정할 수는 없다. 하지만 큰 흐름으로 본다면 지역별, 분야별 시장의 추세나 분위기 정도는 파악하는데 도움이 될 때가 많다. 본인의 경우에는 너무 피곤해서 전 종목을 확인하기 힘들 때, 그날의 시장과 인덱스 변화를 살펴보는 데 사용하고 있다.
그전에 알아두어야 할 시장/인덱스 지수(미국)의 티커이다.
◆ 시장(미국)
다우존스 INDEXDJX:.DJI
S&P500 INDEXSP:.INX
나스닥 INDEXNASDAQ:.INIC
◆ 인덱스(미국)
에너지 INDEXSP:SP500-10
소재 INDEXSP:SP500-15
산업 INDEXSP:SP500-20
자유소비 INDEXSP:SP500-25
필수소비 INDEXSP:SP500-30
헬스케어 INDEXSP:SP500-35
금융 INDEXSP:SP500-40
정보기술 INDEXSP:SP500-45
통신 INDEXSP:SP500-50
유틸리티 INDEXSP:SP500-55
리츠 INDEXSP:SP500-60
1. 시장지수 당일 수치
- 설명: 당일 수치 값은 지수별 가격을 나타내므로 "price" 함수 사용
- 예시
=GoogleFinance("INDEXDJX:.DJI","price")
2. 시장지수 당일 변화
- 설명: 당일 변화 비율은 전일 종가 대비 변화 값이므로 "changepct" 함수 사용
- 예시
=GoogleFinance("INDEXDJX:.DJI","changepct")
구글 파이낸스 함수 티커 자리에 시장지수 티커를 정확하게 입력해주면 수치와 변화는 쉽게 데이터를 불러올 수 있다.
미국 주식은 우리와 시차를 두고 운영되기 때문에 가끔은 너무 피곤해서 보는 것마저 귀찮을 때가 있다. 그래도 아예 관심을 끊자니 궁금하기도 하고, 걱정이 되기도 한다. 그럴 때는 우선 구글 파이낸스의 시장지수 함수를 사용한 표만 확인한다. 어느 한 시장지수의 변화가 크다면 그때 대장주 격인 회사들의 변화를 살펴보는 방식으로 활용할 때 유용하다.
3. 인덱스 당일 변화
- 설명: 당일 변화 비율은 전일 종가 대비 변화 값이므로 "changepct" 함수 사용
- 예시
=GoogleFinance("INDEXSP:SP500-10","changepct")
인덱스는 상장된 주식회사의 분야별 분류라고 보면 쉽게 이해할 수 있다. 살짝 우리나라 주식시장과 기준이 다른 경우가 있지만 회사 특성이 대부분 그대로 반영되어 있어서 내가 투자하는 회사가 어떤 분야에 속해 있는지 쉽게 구별할 수 있다. 만약 여러 인덱스 분야에 투자를 하고 있다면 시장이 상승세라고 하더라도 일부 회사 주가는 하락세를 보일 때가 있다. 회사의 개별적인 이슈가 있지 않다면, 해당 회사가 속한 분야 자체의 이슈가 문제가 될 수도 있다.
코로나로 인해 소비가 위축되고, 관광 수요가 거의 사라지다시피 소멸했고, 그로 인해 석유 소비도 급감하였다. 반면, 외출이 줄어들면서 재택근무가 활성화되고 집 안에서의 여가 생활이 늘면서 IT, 통신, 플랫폼 관련 수요는 폭증했다. 최저점을 찍던 주식 시장은 이런 현상과 맞물려 IT, 플랫폼의 대장격인 회사와 신생회사들이 주가를 영차영차 끌어올려 시장지수의 상승을 견인했다. 이때 시장지수가 아닌 인덱스 별 흐름의 변화를 보면 정보기술, 헬스케어의 상승 흐름에 반해 소비재, 산업, 에너지 분야의 주가는 늘 하락세를 면치 못했다.
아마 시장지수와 인덱스를 별도로 보지 않았다면 이해하지 못했을 변화였다. '나스닥은 하루가 멀다 하고 오르는데 내가 매수한 주식은 이렇게 힘이 없을까, 오늘 S&P500이 상승했다고 하는데, 내가 매수한 주식은 왜 또 떨어진걸까.' 라는 의문과 함께 종목을 매수한 회사에 대한 정보만 찾아 헤맸을 수도 있다. 코로나 시기가 특수한 경우이기도 하지만 이런 시기일수록 더 다양한 지표를 살피면서 공부를 해야 한다고 생각했고, 이를 위해서 구글 파이낸스 함수를 응용하여 귀차니즘을 극복하기도 했다.
그리고 덧붙여,
간간이 등장하는 IFERROR 함수는 실시간 데이터 불러오기나 연결해놓은 함숫값에 오류가 발생할 경우, 오류 대신 내가 지정한 값으로 반환하기 위해 걸어둔 함수이다.
예를 들어, 아래 그림처럼 IFERROR 함수를 활용하여 입력했다고 보면,
=IFERROR(GoogleFinance($C27,"PRICE"),"")
∵ 함수 해석
=IFERROR( →만약 (A)값에 에러가 발생하면 (B)값으로 처리한다
GoogleFinance($C27,"PRICE"), (A :구글 파이낸스 함수로 불러온 MSFT의 현재 주가)
"") (B : 아무 값도 나타내지 않음)
마이크로소프트 실시간 주가를 불러오기 위한 함수를 입력했는데 실시간으로 주가를 불러오는데 오류가 생길 경우 해당 값이 노출되지 않도록 하는 함수이다.
간혹 구글 파이낸스 자체 로딩에 오류가 있어서 구글 시트 전체에 오류가 발생할 때도 있지만, 보통 IFERROR 함수에서 오류가 날 경우에는 함수에 오타가 있거나, 티커가 들어가야 할 자리에 엉뚱한 셀이 선택되어 있는 경우가 많다. 티커가 제대로 입력이 되었음에도 오류값으로 아무 데이터도 추출되지 않은 적이 있는데 이때는 '상장사:티커'로 정확하게 입력하자 바로 해결되기도 했다.
이처럼 쉽게 함수 오류를 찾기 위한 장치로 IFERROR 함수를 늘 사용하고 있다.
종목이 추가될수록, 사용하는 실시간 함수가 많아질수록 구글 시트 로딩이 느려지기 때문에 현재는 이렇게 간단하게 사용하고 있다. 이전에는 배당금도 Importhtml 함수를 사용해서 ycharts나 nasdaq에서 불러오기도 했지만, 시트 자체가 너무 무거워져서 현재는 사용하지 않는 중이다. 일단 배당금이 주가처럼 수시로 바뀌는 정보가 아니기 때문에 이렇게까지 실시간 반영이 필요 없는 것도 한몫했다.
앞으로 이 시트가 어떻게 바뀔지는 모르겠지만, 앞으로도 계속 뜯어고치면서 관리하기 쉬우면서도 명확한 분석을 이끌어낼 수 있는 포트폴리오로 발전시키고 싶다.
다음에는 엑셀 함수와 관련된 내용도 한 번 정리해서 주식 포트폴리오를 구상 중인 분들에게 도움이 되었으면 좋겠다.
'FIRE 01. 투자 > 10. 자료' 카테고리의 다른 글
| 주식) 실적 발표와 관련된 용어 정리 (1) | 2020.10.31 |
|---|---|
| 자산관리앱) 뱅크샐러드 / 가계부 작성부터 자산, 소비 분석까지 (0) | 2020.10.17 |
| Trend survey of Gen Z(Fall 2020) (0) | 2020.10.12 |
| 2020 CNBC 파괴적 혁신기업 리스트 50 (0) | 2020.09.27 |
| Z세대의 트렌드 변화 (Spring 2020) (1) | 2020.06.24 |
