구글독스로 자동 주식 관리 툴을 만들어보자 (5) - 실시간으로 업데이트 되는 내 포트폴리오 만들기
2015. 9. 25. 07:00엑셀이 없어도 손쉽게 접근할 수 있는 구글 독스(a.k.a 구글 드라이브)를 이용해서 실시간 주가 정보를 가져오고 여러가지로 활용할 수 있는 방법을 소개했습니다. 그런데 생각해보니, 가장 기본적인 내용을 건너뛴 것 같은 생각이 들어서, 오늘은 가장 기본적이면서도 많은 분들에게 도움이 될 수 있을만한 내용을 다뤄볼까 합니다.
바로 '실시간으로 업데이트 되는 내 주식 포트폴리오 만들어서 관리하기'입니다.
이제와서 언급하기 민망한 내용이지만, 일단 기본적으로 구글 독스를 이용하기 위해는 구글 계정이 필요합니다. 우리나라는 안드로이드 사용 인구가 많으니 대부분 구글 계정을 가지고 계시겠지만, 혹시 없으시다면 이번 기회에 하나 만들어보시지요. ^-^
구글 드라이브(구글 독스를 저장하는 클라우드 서비스) 바로가기
구글 드라이브에서 '스프레드 시트'를 하나 새로 만들어볼까요?
간단한 정보를 넣을 수 있게 테이블을 꾸며보았습니다.
기본적으로 매수한 정보들을 입력하고, 해당 종목에 대해서 현재가를 실시간으로 가져올 방법을 궁리해보겠습니다. 일전에 소개한 방법들 중에서 원하는 방법을 선택하시면 됩니다.
솔직히 투자하시는 종목이 얼마 안된다면, 구글 파이낸스에서 지원하는 종목 코드를 바로 입력해서 20분 지연 시세를 이용하시는 방법도 있을테고, 투자하는 종목이 많아서 일일히 종목 코드를 찾기도 번거롭고, 20분 지연 시세가 싫고 실시간 시세를 원한다고 하면 네이버나 다음의 정보를 가져와서 이용하는 방법도 있습니다.
[도움되는 과거 시리즈]
구글드라이브(구글 독스)의 스프레드시트를 통해 실시간 주식 정보 가져오기. (feat. 야후 파이낸스)
구글드라이브(구글 독스)의 스프레드시트를 통해 실시간 주식 정보 가져오기 2탄 (feat. 구글 파이낸스)
구글독스로 자동 주식 관리 툴을 만들어보자 (1) - 주식 종목 리스트 다운로드 방법 (코스피, 코스닥, 코넥스)
일단 오늘은 쉽게 실시간 가격과 등락율을 가져올 수 있는 다음 금융 페이지의 정보를 이용해보겠습니다. :)
다음의 전종목 시세 페이지 정보를 이용해서, 코스피와 코스닥 현재가와 등락률을 불러오게 별도의 시트를 만듭니다. 그리고 이 시트의 정보를 참조해 현재가를 불러올 수 있도록 vlookup 함수를 만들어봅니다.
=iferror(vlookup(B2,'다음'!A:C,2,0),iferror(vlookup(B2,'다음'!D:F,2,0),iferror(vlookup(B2,'다음'!G:I,2,0),vlookup(B2,'다음'!J:L,2,0))))
엑셀 함수가 낯선 분들도 계실텐데요. 그냥 단순히 vlookup만 쓰지 않고, 복잡해지는 이유는 다음 금융 페이지에서 불러오는 data가 종목명과 가격 정보가 한 칼럼에 쭉~ 나오는 것이 아니라 절반은 A칼럼에 나오고, 또 나머지 절반은 D칼럼부터 나오는 식으로 나뉘어져 있기 때문입니다. vlookup을 쓸때는 찾으려고 하는 값(여기에서는 종목명)이 제일 첫째 칼럼에 있어야지만 찾을 수가 있는 한계가 있는 함수거든요. ㅠ_ㅠ;;;
그래서 여기서 iferror라는 함수를 사용했는데, 이 함수는 vlookup으로 해당 종목명을 찾을 수 없어 에러가 발생하면 그 다음에 위치한 값을 표시하라는 함수입니다. 그래서 A칼럼에 종목명이 없으면 D칼럼을, 거기도 없으면 G칼럼, 마지막으로 J칼럼까지(코스피 2줄과 코스닥 2줄;;) 찾아서 현재가를 불러올 수 있도록 함수를 만들었습니다. (아래 예제 시트가 있으니 참조하시고, 잘 이해가 안되시는 분들은 질문 남겨주세요^^)
이제 현재 평가액은 현재가와 수량을 곱하면 되고, H칼럼에 위치한 수익률은 현재 평가액과 매수액의 비율을 계산하면 됩니다. 퍼센트로 나타내기 위해서 상단바에 있는 %를 클릭해주면 쉽게 수익률을 표시할 수 있습니다.
여러 종목으로 구성된 포트폴리오라면 비중을 계산해서 보여주는 것도 중요한 정보일 수 있습니다. 계산식은 해당 종목의 평가액을 전체 평가액의 합으로 나눠주는 산술 계산식으로 만들면 됩니다. 비중도 물론 %로 표시되면 보기 편하겠죠?
주로 장기적으로 주식 종목을 보유하시는 분이라면, 보유기간 정보도 필요하겠죠? today()는 오늘 날짜를 보여주는 함수이므로, 오늘 날짜에서 매수일과의 차이를 보여주면 됩니다. 혹시 몇 주, 혹은 몇개월 단위로 보여주고 싶다면 해당 함수식에서 /7을 하거나 /30을 하시면 되겠죠?
평가액 등 단위가 큰 숫자들은 콤마(,)가 없으면 알아보기 어려우니, 필요한 곳을 선택하시고 위의 숫자 서식을 선택하시면 4자리 이상의 숫자에는 콤마(,)가 들어가게 됩니다.
이제는 수익률에 따라서 빨간색이나 파란색을 보여주는 '조건부 서식'을 만들어줍니다. 해당 칼럼을 선택한 뒤, 메뉴에서 서식 > 조건부서식을 선택하면 위와 같이 조건(0을 초과하면)과 서식(빨간색으로 보여줌)을 설정할 수 있습니다. 반대로 0 미만인 경우에는 파란색으로 설정되게 해주면 되겠죠?
예쁘게 색깔도 입혀보고, 가운데 정렬도 해보았습니다. 여러분의 디자인 감각을 마음껏 뽐내보시길... :)
앞선 시리즈에서도 알려드렸듯이, importhtml 함수를 이용해서 다음이나 타 포털에서 제공하는 웹데이터를 긁어와서 이용하는 경우, 이 스프레드시트를 계속 열어두었을때는 값이 업데이트 되지 않습니다. 값을 업데이트하고자 하면 새로고침을 하거나 시트를 닫았다가 열거나, 함수 자체를 지웠다가 붙여넣기를 하는 등, 새로 불러올 수 있도록 해야 실시간의 의미가 있어지니 참조하시기 바랍니다. ^_^
위에 나왔듯이 블로그 등에 공개하기 위해서는 파일 > 웹에 게시 메뉴로 가신 뒤
'임베드'를 클릭하면 아래와 같이 블로그나 홈페이지 등에 붙여 넣을 수 있는 html코드가 나옵니다. 이를 복사하셔서 html 코드를 넣을 수 있는 곳에다가 붙여넣기 하시면 됩니다. :)
파이낸셜프리덤 블로그에 올라오는 새글 편하게 읽는 방법
- 이메일로 받아보기 / Feedly에서 구독 / RSS Feed 구독하기 / 네이버 이웃커넥트
그리고 페이스북에서도 만나요!
이 글이 도움되었다 생각하시는 분은 공감 하트 ♡ 클릭 부탁드려요 :D
'잘 굴리기 > 국내직접투자' 카테고리의 다른 글
| 고배당주의 왕, 배당 챔피언(Dividend Champion)을 아시나요? (6) | 2015.10.12 |
|---|---|
| 구글독스로 자동 주식 관리 툴을 만들어보자 (6) - 고급반. 종합적인 배당주 포트폴리오 만들기 (27) | 2015.10.06 |
| 2015년 9월 WhyBe 배당주 펀드 월간 운용 보고서 (4) | 2015.09.30 |
| 2015년 9월 WhyBe 액티브 펀드 월간 운용보고서 (2) | 2015.09.30 |
| 증권사별 월 적립식 ETF 투자 자동 매수 서비스 모음 (2) | 2015.09.28 |
| 제레미 시겔의 '주식에 장기투자하라'는 우리나라에서도 통할까? (국내 주식 vs 채권 vs 부동산 수익률 비교) (10) | 2015.09.22 |
| 구글독스로 자동 주식 관리 툴을 만들어보자 (4) - 국내 포털 사이트 금융 정보 가져와보자 (실시간 주가, 원자재가격, 기업공시 등) (14) | 2015.09.16 |
| 국내 배당주로 꾸준한 현금흐름을 만들고 싶다면? - 3월/6월/12월 결산 법인에 분산투자하기 (10) | 2015.09.15 |
| 미래에셋이 평가해준 제 포트폴리오 성과분석 (4) | 2015.09.14 |
| 내가 투자하는 기업 공시 실시간으로 받아보자. (1) | 2015.09.11 |