Home / 잘 굴리기/국내직접투자 / 구글독스로 자동 주식 관리 툴을 만들어보자 (5) – 실시간으로 업데이트 되는 내 포트폴리오 만들기

구글독스로 자동 주식 관리 툴을 만들어보자 (5) – 실시간으로 업데이트 되는 내 포트폴리오 만들기

엑셀이 없어도 손쉽게 접근할 수 있는 구글 독스(a.k.a 구글 드라이브)를 이용해서 실시간 주가 정보를 가져오고 여러가지로 활용할 수 있는 방법을 소개했습니다. 그런데 생각해보니, 가장 기본적인 내용을 건너뛴 것 같은 생각이 들어서, 오늘은 가장 기본적이면서도 많은 분들에게 도움이 될 수 있을만한 내용을 다뤄볼까 합니다.

바로 ‘실시간으로 업데이트 되는 내 주식 포트폴리오 만들어서 관리하기’입니다.

이제와서 언급하기 민망한 내용이지만, 일단 기본적으로 구글 독스를 이용하기 위해는 구글 계정이 필요합니다. 우리나라는 안드로이드 사용 인구가 많으니 대부분 구글 계정을 가지고 계시겠지만, 혹시 없으시다면 이번 기회에 하나 만들어보시지요. ^-^

 

구글 드라이브(구글 독스를 저장하는 클라우드 서비스) 바로가기

 

구글 드라이브에서 ‘스프레드 시트’를 하나 새로 만들어볼까요?

간단한 정보를 넣을 수 있게 테이블을 꾸며보았습니다.

기본적으로 매수한 정보들을 입력하고, 해당 종목에 대해서 현재가를 실시간으로 가져올 방법을 궁리해보겠습니다. 일전에 소개한 방법들 중에서 원하는 방법을 선택하시면 됩니다.

 

솔직히 투자하시는 종목이 얼마 안된다면, 구글 파이낸스에서 지원하는 종목 코드를 바로 입력해서 20분 지연 시세를 이용하시는 방법도 있을테고, 투자하는 종목이 많아서 일일히 종목 코드를 찾기도 번거롭고, 20분 지연 시세가 싫고 실시간 시세를 원한다고 하면 네이버나 다음의 정보를 가져와서 이용하는 방법도 있습니다.

 

 

일단 오늘은 쉽게 실시간 가격과 등락율을 가져올 수 있는 다음 금융 페이지의 정보를 이용해보겠습니다. 🙂

 

 

다음의 전종목 시세 페이지 정보를 이용해서, 코스피와 코스닥 현재가와 등락률을 불러오게 별도의 시트를 만듭니다. 그리고 이 시트의 정보를 참조해 현재가를 불러올 수 있도록 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 코드를 넣을 수 있는 곳에다가 붙여넣기 하시면 됩니다. 🙂

 

주식 포트폴리오 만들기 예제 시트 바로가기

Check Also

구글 스프레드시트 활용팁 (11) – 중국 상해 주식 정보 불러오기

최근 구글 스프레드시트 활용팁 시리즈는 여러분의 문제점을 해결해드리고 있습니다. 이번에는 봄바람님께서 googlefinance 함수에서 중국 상해 주식은 …

10 comments

  1. 유용한 정보 감사드려요….저도 회사에서 주식 사이트가 막혀있어 관리가 안되어
    잘 사용할 것 같은데요.importhtml함수를 이용해서 네이버에 수십페이지에 있는 자료를 받고 나서 새로 고침을 하게 되면 네이버에서 받은 자료들이 새로 업데이는 되는게 맞나요..?

    • 네. 제가 알기로는 그래요. 🙂 그런데 저는 수시로 가격 변화를 확인하는 용도는 아니라서 직접 확인해보지는 않았어요…^^;

  2. 주식 포트폴리오 만들기 예제 “다음”시트 첫번째 함수에 마이너한 오타가 있어서 수정이 필요할 것같습니다. ^^

    현재 수식 : importhtml(“http://finance.d aum.net/quote/all.daum?type=S&stype=P”,”table”,2
    오타 : finance.daum.net (daum에서 d와a사이에 빈칸이 있어요^^)

    • 구글 스프레드시트 권한을 그냥 오픈해놨더니 시트가 엉망이 되었네요 ^^;;;; 괜찮아 보이는 예전 버전으로 복원해두었습니다. 알려주셔서 감사해요!

  3. 요게 혹시 동일 종목을 분할로 추매하는 형식으로 하면 한 종목의 여러 건의 매수건들을 종합관리는 못하나요?ㅎㅎㅎ A 종목을 3월에 10주 5월에 10주 7월에 10주씩 다른 가격으로 샀다면, A종목을 평균단가를 내서 수익률을 기록해주는 그런거요 ㅎㅎ

  4. 님 글들에 감탄하면 감사히 보고 있습니다. 혹시 질문하나 드려도 될지요..

    시트를 활용하려고 보니

    “오류
    VLOOKUP 평가에서 ‘종목명’ 값을 찾을 수 없습니다.”

    라는 멘트가 뜨고 현재가가 뜨지 않고 ‘다음’ 시트 누르면 “로딩중” 이라고 만 나옵니다
    혹시 방법이 있을까요?

    • 오늘 로그인 하니 로딩완료 되어 있네요. 우문하여 죄송합니다. ^-^

      • 우문이라뇨! 사실 요즘 구글 로딩이 좀 많이 느리긴 하더라구요. 그런데 제 계정에 올린 파일들이다보니까.. 제가 import 함수를 많이 써서 느려지나 싶어서 새로 계정을 파서 만들어봤더니, 좀 나은것 같기도 하고.. 알쏭달쏭입니다. 아무튼 구글에게 조금 인내심을 보여주세요. ^^;

    • 요즘 로딩이 좀 느린 몇몇 파일에 대해서 새 계정으로 옮기면 좀 나으려나 싶어서 이사를 계획중입니다. ㅎㅎ

댓글 남기기