그동안 주식은 크게 관심을 가지고 있지 않았는데요. 주변의 지인들이 주식을 하라는 권유가 많아 100만원을 투자하여 주식을 시작해 봤습니다. 관심을 가지니 보이는게 많아 정리도 하고 공부도 할겸 구글시트로 주식을 관리해 보았습니다.

Transaction 시트

12시에 만나요의 박시동/이광수님의 조언에 따라 주식거래 내용을 기록하기 위해 구글시트에 입력하였습니다. 이후에 이 데이터를 기반으로 Dashboard가 구성되었기에 일부 컬럼들이 추가되었습니다.

종목코드 : Dashboard의 종목상세에서 매핑하기 위한 Key값입니다.
섹터 : Dashboard에서 섹터별 분석을 위해 추가하였지만, 현재는 사용하고 있지 않습니다.
투자자 : 하나의 계좌에서 관리되지만 와이프 돈도 투자되었기에 구분할 수 있도록 하였습니다.

Dashboard 시트

매수/매도 이력을 구글시트에 입력하다 보니 이 데이터를 사용하여 대시보드를 구성해 보고 싶어졌습니다. 증권사 App으로 조회해도 되지만, 한눈에 모든 종목을 관찰하기 위해 하나의 시트에 다양한 정보를 조회할 수 있도록 하였습니다. 대부분 GOOGLEFINANCE 함수를 사용하기 때문에 최대 20분의 오차가 발생할 수 있습니다. 표와 차트로 구분하여 화면을 구성하였습니다.

표
- 요약 : 전체 종목에 대한 투자금, 평가금을 조회합니다.
- 지수 : 지수추종 ETF에 투자하고 있기 때문에 관련된 지수를 조회합니다.
- 바로가기 : 증권사, 전자공시시스템, 유튜브 등의 링크입니다.
- 종목상세 : 현재 투자하고 있는 종목별 상세 정보를 제공합니다. 이 값을 기반으로 텔레그램 알람이 발송됩니다. 현재가의 변경에 따라 조건부 서식을 충족하면 빨간색 배경색으로 변경됩니다.
차트
- 원금 : 투자한 원금의 비율
- 총수익금 : 수익에 대한 비율
- 수익율 : 어떤 종목이 투자 대비 수익이 높은지 조회합니다.
- 가격 이벤트 : 코스피 지수에 따라 어떤 거래를 했는지 이력을 조회합니다.
- 종목별 차트 : 종목별 가격 흐름을 조회합니다.

구글 시트에서 표를 사용하면 데이터를 구조화하여 사용할 수 있으며, 함수 사용시 셀주소가 아니라 컬럼명을 사용할 수 있습니다.
각 표에서 사용된 항목별 주요 함수들은 다음과 같습니다. 대문자로 된 함수들은 구글시트 함수이며, 카멜표기법으로 작성된 함수들은 Apps Script로 작성한 함수입니다.

요약
- 총 투자금액 : =SUM(종목상세[원금])
- 총 평가금액 : =SUM(종목상세[평가금])
- 총 손익 : =B4-B3
- 총 수익율 : =B5/B3
- 일간 수익 : =sum(종목상세[일 수익금])
지수
- 코스피 : =GOOGLEFINANCE("KOSPI")
- 코스닥 : =VALUE(IMPORTXML("https://finance.naver.com/sise/", "//span[@id='KOSDAQ_now']"))
- 금 1돈 : =GOOGLEFINANCE("GLD","price") * GOOGLEFINANCE("CURRENCY:USDKRW") / 0.093 / 31.1035 * 3.75
- 환율 : =ROUND(GOOGLEFINANCE("CURRENCY:USDKRW"), 0)
종목상세
- 보유량 : =SUMIFS(Tx거래이력[수량],Tx거래이력[종목코드],종목상세[종목코드],Tx거래이력[거래],"구매")-SUMIFS(Tx거래이력[수량],Tx거래이력[종목코드],종목상세[종목코드],Tx거래이력[거래],"판매")
- 총수익율 : =종목상세[총 수익금] / 종목상세[원금]
- 총수익금 : =종목상세[평가금] - 종목상세[원금]
- 평가금 : =종목상세[보유량] * 종목상세[현재가]
- 원금 : =IF(Settings!$E$7 = "원금", calcCostBasis(B31), calcNetInvestment(B31))
- 평가손익 : =종목상세[현재가] - 종목상세[주당 평균가]
- 주당평균가 : =종목상세[원금] / 종목상세[보유량]
- 현재가 : =GOOGLEFINANCE(종목상세[거래소] & ":" & 종목상세[종목코드])
- 고가 : =GOOGLEFINANCE(종목상세[거래소]&":"&종목상세[종목코드],"HIGH")
- 저가 : =GOOGLEFINANCE(종목상세[거래소]&":"&종목상세[종목코드],"LOW")
- 1달 최고가 : =MAX(MAX(INDEX(GOOGLEFINANCE(C31&":"&B31,"high",TODAY()-30,TODAY()),,2)), GOOGLEFINANCE(C31&":"&B31,"high"))
- 52주 최고가 : =MAX( MAX( INDEX( GOOGLEFINANCE(C31&":"&B31,"high",TODAY()-365,TODAY()),,2)), GOOGLEFINANCE(C31&":"&B31,"high") )
- 손절가 : =ROUND(종목상세[주당 평균가] * (100 - Settings!$E$5) / 100)
- 익절가 : =ROUND(종목상세[1달 최고가] * (100 - Settings!$E$6) / 100)
- 7일 가격 : =SPARKLINE(GOOGLEFINANCE(종목상세[거래소]&":"&종목상세[종목코드], "PRICE", TODAY()-7, TODAY()))
- 전일대비 : =GOOGLEFINANCE(종목상세[거래소]&":"&종목상세[종목코드],"CHANGE")
- 일수익율 : =GOOGLEFINANCE(종목상세[거래소]&":"&종목상세[종목코드],"CHANGEPCT")/100
- 일수익금 : =(GOOGLEFINANCE(C31&":"&B31,"price") - GOOGLEFINANCE(C31&":"&B31,"closeyest")) * F31
- 7일 거래량 : =SPARKLINE(GOOGLEFINANCE(종목상세[거래소]&":"&종목상세[종목코드],"VOLUME", TODAY()-7, TODAY()))
- 일 거래량 : =GOOGLEFINANCE(종목상세[거래소]&":"&종목상세[종목코드],"VOLUME")
- PER / PBR : =getNaverStockValuation(B31, "trailing")

Settings 시트

Dashboard 시트와 Apps Script 등에서 사용되는 설정값을 하나의 시트에 모아두었습니다.

Settings : 조회기간, 익절/손절 계산, 알람 설정
목표가격 알림 : 매수/매도 시점을 알림받기 위해 원하는 가격과 조건을 입력하면 텔레그램으로 알려줍니다.

KOSPI Data 시트

제가 투자하고 있던 종목들이 코스피 지수와 밀접하게 관련이 있어 코스피 지수의 변동을 차트로 구성하기 위한 데이터입니다. 더불어 Transaction 시트를 통해 매수/매도 이력도 함께 표시되도록 하였습니다.

// A열 - 날짜, 코스피지수
=ARRAYFORMULA(
   QUERY(
    {
      TEXT(INDEX(GOOGLEFINANCE("KRX:KOSPI","price",TODAY()-Settings!$E$3,TODAY(),"DAILY"),,1),"MM-dd"),
      INT(INDEX(GOOGLEFINANCE("KRX:KOSPI","price",TODAY()-Settings!$E$3,TODAY(),"DAILY"),,2));
  
      TEXT(TODAY(),"MM-dd"),
      INT(GOOGLEFINANCE("KRX:KOSPI"))
    },
    "select Col1, Col2 where Col2 is not null",
    0
  )
 )

// C열 - 구매
=ARRAYFORMULA(IF(A2:A="","",
 IF(COUNTIFS(Transactions!A:A, A2:A, Transactions!F:F, "구매")>0,
    B2:B,
    NA()
 )
))

// D열 - 판매
=ARRAYFORMULA(IF(A2:A="","",
 IF(COUNTIFS(Transactions!A:A, A2:A, Transactions!F:F, "판매")>0,
    B2:B,
    NA()
 )
))

// E열 - 레이블
=ARRAYFORMULA(
  IF(A2:A="", "", 
    IF((NOT(ISNA(C2:C))) + (NOT(ISNA(D2:D))), 
      "·" & B2:B & 
      IF(NOT(ISNA(D2:D)), " ·" & COUNTIFS(Transactions!A:A, A2:A, Transactions!F:F, "판매") & "판매", "") & 
      IF(NOT(ISNA(C2:C)), " ·" & COUNTIFS(Transactions!A:A, A2:A, Transactions!F:F, "구매") & "구매", ""), 
      "#N/A"
    )
  )
)

Chart Data 시트

Dashboard 시트의 종목별 차트를 위한 데이터입니다. 특정 기간동안의 가격 흐름은 빨간색 선으로 표시하고, 내가 구매한 가격의 평균은 파란색 점선으로 표시합니다.

// A, B열 - 날짜, 가격
= ARRAYFORMULA(
   QUERY(
    {
      TEXT(INDEX(GOOGLEFINANCE("KRX:"&B$1,"price",TODAY()-Settings!$E$4,TODAY(),"DAILY"),,1),"MM-dd"),
      INT(INDEX(GOOGLEFINANCE("KRX:"&B$1,"price",TODAY()-Settings!$E$4,TODAY(),"DAILY"),,2));
  
      TEXT(TODAY(),"MM-dd"),
      INT(GOOGLEFINANCE("KRX:"&B$1))
    },
    "select Col1, Col2 where Col2 is not null",
    0
  )
 )

// C열 - 내가격
=IFERROR(ROUND(xlookup(B$1,종목상세[종목코드], 종목상세[원금]) / xlookup(B$1,종목상세[종목코드], 종목상세[보유량]), 0), 0)

News 시트

뉴스를 찾아보기 귀찮아서 종목과 관련된 뉴스를 5개씩 뽑아서 보여줍니다.

function getNaverNews(keyword) {
  const url = "https://openapi.naver.com/v1/search/news.json?query=" + encodeURIComponent(keyword) + "&display=5&sort=date";  

  const response = UrlFetchApp.fetch(url, {
    method: "get",
    headers: {
      "X-Naver-Client-Id": NAVER_CLIENT_ID,
      "X-Naver-Client-Secret": NAVER_CLIENT_SECRET
    }
  });

  const data = JSON.parse(response.getContentText());
  const result = [];

  data.items.forEach(item => {
    const title = decodeHtml(item.title.replace(/<[^>]+>/g, "")); // HTML 태그 제거
    const link = item.link;
    const dateObj = new Date(item.pubDate);
    const formattedDate = Utilities.formatDate(dateObj, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm");

    result.push([title, link, formattedDate]);
  });

  return result;
}

Apps Script

GOOGLEFINANCE 함수만으로는 Dashboard를 구성할 수 없기 때문에 Apps Script를 사용하였습니다.

Google Apps Script : https://developers.google.com/apps-script?hl=ko
구글 앱 스크립트 활용 : https://wikidocs.net/278848

각 gs (Google Script) 들은 다음과 같은 역할을 합니다.

Kiwoom.gs : 키움 증권 API를 사용하여 실시간 종목 정보를 조회합니다. 키움증권 API는 고정 IP에서만 사용가능하므로, 집에서 PC에서 직접 실행시는 정상 동작했지만 트리거로 사용시 고정 IP를 특정할 수 없어서 별도의 프록시 서버가 필요합니다.
Naver.gs : GOOGLEFINANCE에서 누락된 종목 정보나, 제공하지 않는 정보는 네이버 증권을 통해 수집하고 있습니다. 또한 뉴스 정보는 네이버 Open API를 사용하였습니다.
Telegram.gs : 텔레그램 API를 사용하여 익절/손절/목표가 등에 대한 알람 메시지를 발송합니다.
Utils.gs : 공통 유틸성 함수를 모아두었습니다.
Constant.gs : 시트/셀 정보를 정의하고, 텔레그램/네이버/토스 사용을 위한 token 등이 저장됩니다. 민감한 정보는 설정의 스크립트 속성에 저장하는 것이 권장되지만, 혼자 사용하고 있기 때문에 하드코딩 되었습니다.
Dashboard.gs : Dashboard의 원금과 실투자금액은 계산이 복잡하여 별도의 함수로 계산하고 있습니다.

주식 Menu

Apps Script의 함수를 쉽게 제어하기 위한 메뉴를 추가하였습니다.

대시보드 업데이트 : 열려있는 구글시트의 데이터가 변경되는 시점은 특정할 수 없기 때문에 주기적으로 구글시트를 업데이트하기 위한 기능입니다.
캐시 초기화 : 가끔 GOOGLEFINANCE 가 정상적인 값을 못 가져올 경우가 있어서, 비정상 상태에서는 캐시를 초기화합니다.
트리거 재시작/중지 : 트리거를 통해 익절/손절/목표가 등을 점검하고 있습니다. 해당 기능들을 일시적으로 중지하거나 재시작하기 위한 메뉴입니다.

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("📈주식")
    .addItem("대시보드 업데이트", "refreshSheet")
    .addSeparator()    
    .addItem("캐시 초기화 (오동작 시)", "clearCache")
    .addSeparator()
    .addItem("트리거 재시작", "startTrigger")
    .addItem("트기거 중지", "stopTrigger")
    .addToUi();
}

구글 시트로 주식 관리

Transaction 시트

Dashboard 시트

Settings 시트

KOSPI Data 시트

Chart Data 시트

News 시트

Apps Script

주식 Menu

댓글 남기기응답 취소

Transaction 시트

Dashboard 시트

Settings 시트

KOSPI Data 시트

Chart Data 시트

News 시트

Apps Script

주식 Menu

Related posts

Related posts

댓글 남기기응답 취소