주식 이메일 알람 - 구글 스프레드시트 스크립트

 홈트레이딩 시스템(HTS)이 보급된 이후로 주식거래에 대한 접근성은 나날이 높아져갔고, 이제는 스마트폰에서 거래가 가능한 MTS도 보급화 되고 있다. 하지만 개인투자자들의 투자자본의 회전율과 수익율간의 상관관계는 오히려 마이너스의 방향인 경우가 많다.

 물론 주식을 거래하는데 있어 하나의 방법인 단타, 초단타의 경우 쉽게 끊을 수 없는 매력을 지닌 것은 인정하는 바이나, 개인적으로도 장기투자를 선호하고, HTS를 보고있자면 수명을 갉아먹는 것 같아 어떻게하면 최대한 떨어져있을 수 있는 방법을 강구하기 시작했다.

 증권사들이 제공하는 스탑로스(stoploss) 기능도 좋지만 HTS를 켜둬야하는 증권사를 이용하고 있었고, 뭔가 막연한 불안감과 함께 적용 수수료율도 제대로 공시되어있지 않아 다른 기능이 없나 계속 찾아보았다.

 스마트폰용 안드로이드나 아이폰에서의 앱, 어플로 제작된 주식거래 프로그램에도 해당 주가에 도달할 시에 알람을 해주는 기능이 있었지만 핸드폰에 주식 관련기능은 다음이나 네이버의 증권 서비스로 한정하려했기에 탈락.

 컴퓨터 프로그램으로도 있었지만 컴퓨터를 키고 있어야했기에 아예 신경을 끄고 주가가 설정지점에 도달하는 경우에만 알고 싶었던 욕망을 충족시키는 것에는 부족함을 느꼈다.

 한국에서는 철수했지만 미국에서는 아직 존재하는 야후 파이낸스의 stock alerts 기능을 사용해보았다. 하지만 달러기준이라 그런건지 제대로 작동을 안하는 상황.

 포기하고 있다가 문득 파이낸셜프리덤 블로그에서 빈누님의 글을 보고 구글 스프레드시트로 구글 파이낸스의 데이터를 불러올 수 있음을 알았다. 그렇다면 방법이 있지 않을까라는 생각하다. 검색을 해보기 시작했다. 그리고 역경은 시작되었다.(...)

 

 일단 완성은 하였으니, 먼저 공개를 하고 비전공자의 고난과 역경, 주의점을 후술하도록 하겠다.

주식 메일 알람 - 구글 스프레드시트 with Script 

(Stock Email alerts - Google spreadsheet with Script)

구글에 로그인한 뒤 위의 링크에서

사본만들기를 통해 자신의 구글 드라이브에 가져다가 사용하시면 된다.

사용시 수정해야할 부분은 우선 크게 두부분으로 나뉜다.

1. 스프레드시트 상에서 주식명, 이상, 이하 부분과 메일주소

2. 도구-스크립트 편집기로 들어가서 디버그-실행 테스트, 트리거, 권한설정

우선 1번부터 설명을..

1. 스프레드시트 상에서 주식명, 이상, 이하 부분.

1-1. 주식명

링크를 타고 들어가보면 위와 같이 만들어 놓은 예제가 있다.

노란색, 붉은색, 푸른색 셀만 수정을 하면된다. 

시트이름은 주식알람으로.. 바꾸고 싶으면 스크립트와 함께 수정하면된다.

우선 노란색셀의 주식명은 두번째 시트인 DATA에 2016.4.12일 까지의 코스피, 코스닥 상장기업들의 데이터와 코드가 저장되어있기에 주식명을 치면 자동으로 코드가 입력된다. 단 정확히 쳐야한다. 다음이나 네이버 등등 증권서비스에서 정확한 명칭으로 확인하고 기입하자.

네이버라고 치면 안나온다. NAVER다. 카카오는 CACAO라고 쳐도 안나온다...

정확히 쳐도 안나온다면 코드에 직접 기입하면된다.. 증권코드에 코스피는 KRX: 를 붙여야하고, 코스닥은 KOSDAQ: 을 붙여야한다.

코드가 000000 이렇게 6자리가 완전히 기입되는게 핵심이다. 여기까지됬으면 현재가도 자동출력된다.

1-2. 이상, 이하

자신이 설정한 가격 이상이나 이하로 움직이는 경우 알림, 주식 알람을 받고자 만들었고, 최대한 시트안에서 수정이 편리하게 만들고자 하였다. 그래서 붉은색과 푸른색 셀안에서 하나를 택해 원하는 가격을 적어주면 된다.

위의 이미지에는 아모레퍼시픽의 경우, 현재가가 만원 이상일때 알람이 오도록 설정한 것이다.

네이버는 현재가가 70만원 이하로 떨어질 경우로 설정되있는 것.

이상과 이하의 기본값은 -,과 0으로 되있는데 

사용하지 않는 부분에는 이상의 경우 -를, 이하의 경우 0을 채워두자.

이하라고 적어진 부분을 직접가보면 옆에 화살표로 숨겨져있는 부분을 열 수 있다. 딱히 건드릴 필요는 없다.

1-3. 메일주소

이메일주소 바로 아래 칸을 보면 노란색의 셀에 예시 이메일이 들어있다. 사본을 만들어서 사용시에 받을 이메일로 고쳐주면 된다.

2. 도구-스크립트 편집기로 들어가서 디버그-실행, 트리거, 권한설정

2-1. 디버그-실행, 

도구 - 스크립트 편집기로 들어가면 코드.js인 초기값에 

function myFunction() { 이런게 적어져있는데 전부 지우고 아래 코드를 가져다 붙인다.

이전 버전에서 업그레이드되서 이메일주소도 시트내에서 수정가능하므로  

스크립트 편집기에서 디버그-실행을 해보면서 문제가 없는지 확인하고 저장한다.

실행하다보면 권한 승인이 필요하다고 하는데 승인을 해준다.

그리고 메일로 결과가 도착하여 실행이 잘되는것을 확인했으면

이제 트리거를 설정할 차례다.

function StockAlerts() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName("주식알람");  //해당시트 이름으로 수정

  var range = sheet.getRange("A1:J6");   

  

  var value0 = SpreadsheetApp.getActiveSheet().getRange(2, 10).getValues();

  

  var value1 = SpreadsheetApp.getActiveSheet().getRange(2, 1).getValues();

  var value2 = SpreadsheetApp.getActiveSheet().getRange(3, 1).getValues();

  var value3 = SpreadsheetApp.getActiveSheet().getRange(4, 1).getValues();

  var value4 = SpreadsheetApp.getActiveSheet().getRange(5, 1).getValues();

  var value5 = SpreadsheetApp.getActiveSheet().getRange(6, 1).getValues();

 

  

  if(sheet.getRange(2,8).getValue()>0) sendEmail1(value1)

  if(sheet.getRange(3,8).getValue()>0) sendEmail2(value2)

  if(sheet.getRange(4,8).getValue()>0) sendEmail3(value3)

  if(sheet.getRange(5,8).getValue()>0) sendEmail4(value4)

  if(sheet.getRange(6,8).getValue()>0) sendEmail5(value5)

  

  

  

function sendEmail1(value1){

  var value0 = SpreadsheetApp.getActiveSheet().getRange(2, 10).getValues();

  var recipient = value0;  

  var subject = "목표주가 도달";

  var body = value1 + " :" + " 설정가격에 도달하였습니다. 계좌를 확인해주세요."

  MailApp.sendEmail(recipient, subject, body);

};

  

  function sendEmail2(value2){

  var value0 = SpreadsheetApp.getActiveSheet().getRange(2, 10).getValues();

  var recipient = value0;  

  var subject = "목표주가 도달";

  var body = value2 + " :" + " 설정가격에 도달하였습니다. 계좌를 확인해주세요."

  MailApp.sendEmail(recipient, subject, body);

};

  function sendEmail3(value3){

  var value0 = SpreadsheetApp.getActiveSheet().getRange(2, 10).getValues();

  var recipient = value0;   

  var subject = "목표주가 도달";

  var body = value3 + " :" + " 설정가격에 도달하였습니다. 계좌를 확인해주세요."

  MailApp.sendEmail(recipient, subject, body);

};

  

  function sendEmail4(value4){

  var value0 = SpreadsheetApp.getActiveSheet().getRange(2, 10).getValues();

  var recipient = value0;    

  var subject = "목표주가 도달";

  var body = value4 + " :" + " 설정가격에 도달하였습니다. 계좌를 확인해주세요."

  MailApp.sendEmail(recipient, subject, body);

};

  

  function sendEmail5(value5){

  var value0 = SpreadsheetApp.getActiveSheet().getRange(2, 10).getValues();

  var recipient = value0;   

  var subject = "목표주가 도달";

  var body = value5 + " :" + " 설정가격에 도달하였습니다. 계좌를 확인해주세요."

  MailApp.sendEmail(recipient, subject, body);

};

  

};

2-2. 스크립트 수정 - 트리거, 권한 설정

아래처럼 리소스 - 모든 트리거 보기로 가준다.

처음에는 트리거가 설정되어 있지 않습니다. 여기를 클릭하여 트리거를 추가하세요. 라고 나와있는 것 클릭.

    아래와 같은 식으로 설정한뒤 저장해준다. 

시간 타이머를 클릭해보면 분타이머 이렇게도 가능한데 비추다. 테스트할 때나 사용해볼 수 있지만 구글에서 메일 발송제한이 있기에 넉넉하게 잡아준다. 이 부분은 뒤에서 설명하도록하고, 

정 마음이 답답하면 계정을 주식알람 전용으로 새로 만들어서 설정하면 되겠다. 시트의 칸을 더 줄이고 스크립트도 5개항목 정도로 줄여서 구글의 메일 발송제한을 건드리지 않는다면 더 촘촘한 알림이 가능할 것이다. 보통 24시간내 500통의 제한인 것 같은데, 더 적은 횟수에서도 제한이 걸리기도 하나보다.

알람 항목을 20개정도로 늘려서 계속 테스트하다보니 일일 발송제한에 걸려서 줄인 것이 현재.

주식 알람 전용계정을 만들어 활용하기위해 이메일 주소 수정도 쉽게 업그레이드했으므로 원한다면

여러 계정을 통해 잦은 빈도의 알람도 가능. 취향에 따라가자.

지나왔던 고난과 역경, 주의점

보통 관심은 있었지만 자바스크립트(javascript)정도를 이름만 들어봤지 전혀 다룰줄 몰랐던 비전공자였기에, 주식 이메일 알림? 주식 이메일 알람? Stock Alerts? 를 완성하기까지에는 시행착오가 너무 많았다.

처음 생활코딩(https://opentutorials.org/course/1)에 가서 강의를 들어보다가 좀이 쑤셔서 예제를 찾아보기 시작했다. 스프레드시트에서 이메일 예약발송이 눈에 띄였다. 하지만 필요로하는 것은 특정값을 충족할때 자동 이메일 발송이므로 부족했다.

설상가상 한국에서 구글스크립트에 관한 활동은 거의 없는 듯 자료가 부족했다.

그러다 stackoverflow.com에서 google apps script : google sheet issue : “auto e-mail IF cell value > X” 라는 질문과 답변을 보았고, 답변에서의 예제를 적용해 첫 시험작이 완성되었다. 

function SendEmail() {
  var ui = SpreadsheetApp.getUi();
  var file = SpreadsheetApp.getActive();
  var sheet = file.getSheetByName("Sheet1");  //Change as needed

  if(sheet.getRange(5,1).getValue()>10){      //change row and column in get range to match what you need
    MailApp.sendEmail("xxxxxcxxx@gmail.com", "subject", "message");
  } 

}

위가 그 예제였다. 디버깅과 실행에서 잘 작동해 끝나나 싶었다. 하지만 여기서 문제는 실제 테스팅을 하면서 계속 SpreadsheetApp.getui() 가 호출되지 않는 오류가 나타났다.

그냥 실행을 하거나 시트 내 값이 변경되는 등의 트리거로는 제대로 실행이 되는데, 시간을 사용하는 트리거에서는 계속 오류가 발생.

알고보니 시간 트리거로는 getui()가 실행이 안된다는 것이었다... 

별의별 생각을 다해봤다. 예약메일로 구글 폼을 보내서 시트에 기재되게 해서 실행을 해볼까하다가 뭔짓을 하는건가 해서 후순위로 시도를 밀어두고, 생각했다. 트리거를 스크립트로 넣어봐도 getui()자체를 외부에서 호출할 수 없으니 도루묵.. 인것같아 고민하다가 아예 를 var ui = SpreadsheetApp.getUi(); 지워봤다. 

정상작동.. 

역시 야매로 짜다보니.. 코드에 대한 이해가 없어 이런 삽질을 한 것이다. 

하지만 또다시 고비가 있었다. 역시 스크립트 조건문에 대한 이해가 부족해 if 의 거짓인 값에 순차적으로 스크립트를 연결하는 법을 몰랐다. 적당히 이어 붙여봤는데 제대로 작동하는 줄 알았다가 첫번째 항목이 실행이 안되면 연이어 아무런 알람이 오질 않았다. 두번째 항목부터 알람이 와야하는데 묵묵부답. 

지친 몸으로 웹을 떠돌다 갑자기 해결책을 찾았다. 날아라 키위새의 키위새님이 한달쯤전에 스크립트를 이미 만들어뒀던 것이었다!

(구글 드라이브 스프레드시트를 이용하여 주식 포트폴리오 관리하기 - 2. 자동 이메일 알림 스크립트 만들기)

키위새님에게 감사의 마음을 전한다. 키위새님이 아니었으면 아마 지금도 전..

키위새님의 스크립트를 기반으로 다시 수정에 들어갔다.

키위새님의 경우는 스크립트 자체에서 수치를 비교하고 설정해야하는 부분이 많았기에

시트내에서 대부분을 조작할 수 있는 환경을 목표로 스크립트를 짰다.

(지금와서 보니 이메일 수정부분도 시트내에서 한번에 수정가능하게 될 것같다. 업데이트는 차후로..)

항목별 메일 발송, 알람이 정상적으로 순탄히 작동했다. 드디어 끝이 나나 싶었지만

이번엔 구글의 이메일 일일 호출제한으로 오류가 떴다. :(

항목을 20개로 늘려 테스트했던게 화근인가.

테스트를 하느라 계속 호출을 했기는한데 대체 어느 기준에 해당하는지를 모르겠다. 발송과 호출 자체가 다르게 카운팅되는건지 여전히 하루 이메일 호출 일일제한이 어느정도인지는 미지수.

그래서 아예 알람 항목을 하나만 스크립트로 놔두고 계정을 여럿 만들어 알림을 촘촘하게 만드는 것도 방법인 것 같다.

스크립트 내에 수정할 부분이 크게 이메일 주소와 트리거니, 이메일 주소만 다시 시트내에서 수정가능하도록 업데이트하면 쉽게 가능해질 부분이다.

(16.4.13 이메일주소 간편수정 업데이트)

마지막으로 다시 빈누님과 mongoose36, 키위새님에게 감사를 :)