2025.08.24 구글시트 + 앱스스크립트로 만드는 볼린저밴드 알림 서비스

핵심 요약

• 전략: 중간선(SMA20) 아래에서 분할매수 → 상단 밴드 터치 시(종가 ≥ 상단) 부분청산
• 데이터: GOOGLEFINANCE로 종가 일봉 수집
• 지표/신호: BB(20,2), %B, BandWidth, Squeeze(120일 하위 20%), 사용자 전략 신호
• 알림: 이메일, 텔레그램(선택)
• UX: 시트에 파생 컬럼, 헤더 툴팁, 조건부서식(파랑=매수 후보, 빨강=매도 후보)

---

무엇을 만들까요? 하루에 한 번 자동으로 각 종목의 일봉을 가져와 볼린저밴드 신호를 계산하고,

• Log 시트에 기록
• 이메일/텔레그램으로 알림
• D_시트엔 BB_MID/UP/LO, %B, BW, SQ를 값으로 기록하고, 파랑/빨강으로 한눈에 보이게 표시합니다.

---

준비물

• 구글 계정(드라이브/시트/앱스스크립트 사용)
• (옵션) 텔레그램 봇 토큰·채팅ID

---

전략 개요 (왜 이렇게?)

• 분할매수: 평균회귀 관점에서 중간선 아래(%B<0.5) 구간을 매수 존으로 삼아 T1/T2/T3로 나눠 분할 접근
• 부분청산: 상단 밴드 터치(종가≥상단) 시 과열/추세 연장 구간에서 일부 익절해 변동성 위험을 낮춤
• 스퀴즈: BW가 최근 120일 중 하위 20%면 SQ=1로 표기(좁은 밴드→방향성 확대 가능성)

---

시트 구조

• Settings: Ticker, Window, Mult, LookbackDays, Notify, Active
  • 예: KRX:453850, 20, 2.0, 220, BOTH, TRUE
• D_티커: 각 종목별 데이터 표시용 시트(Date, Close, BB_MID, BB_UP, BB_LO, PCTB, BW, SQ)
  • 파랑 = 매수 후보(PCTB<0.5)
  • 빨강 = 매도/부분청산 후보(Close≥BB_UP)
• Log: 알림/신호 기록(Timestamp, Date, Ticker, Signal, Close, Upper, Lower)

---

설치 (10분 컷)

1) 시트 생성 & 코드 붙여넣기

1. 새 구글시트 생성 → 확장 프로그램 → Apps Script 열기
2. 아래 전체 코드를 통째로 붙여넣고 저장(Ctrl/Cmd+S)
3. 상단 ▶ 실행으로 setup() 1회 실행(권한 허용)

2) Settings 시트 채우기

• A2에 티커(예: KRX:453850 또는 453850:KS) 입력
• Notify는 EMAIL/TELEGRAM/BOTH 중 선택, Active=TRUE

3) 텔레그램(선택)

• @BotFather로 봇 생성 후 토큰 복사 → 프로젝트 설정 → 스크립트 속성에
  • TELEGRAM_BOT_TOKEN = (복사한 토큰)
  • TELEGRAM_CHAT_ID = (개인/그룹 chat id, 모르면 아래 방식으로 찾아 저장)
• DM에서 봇 Start + “hello” 전송 → 코드의 findAndSaveChatId()(앞서 제공)로 자동 저장하거나, getUpdates로 확인
• 보안: 토큰 노출 시 /revoke → /token으로 재발급

4) 트리거(자동 실행)

• 앱스스크립트 좌측 트리거 → 추가
  • 함수: runDaily
  • 시간 기반: 매일, 16:10 (Asia/Seoul) 등 장마감 이후로 설정

5) 한 번 수동 테스트

• 시트 메뉴 BB Alert → Run Now 실행
• D_티커 시트에서 파랑/빨강 표시 확인
• Log 시트에 최신 신호 생성 확인
• (옵션) 텔레그램/메일 도착 확인

---

컬럼 해석(한눈에)

• PCTB < 0.5 = 파랑: 중간선 아래 → 분할매수 후보
• Close ≥ BB_UP = 빨강: 상단 터치 → 부분청산 후보
• BW: 밴드폭(중앙 대비). 낮으면 스퀴즈, 높으면 변동성 확대
• SQ=1: 최근 120일 중 특히 좁은 밴드

---

자주 묻는 질문(FAQ)

• 왜 신호가 안 찍히죠?
  오늘 기준 새 신호가 없을 수 있습니다. 과거 신호를 보려면 백필 함수를 추가해 사용하세요.
• 열너비/서식이 바뀝니다
  본 코드에선 서식 보존 모드를 적용했습니다. (자동 너비 조정 OFF)
• KRX 티커가 안 나옵니다
  GOOGLEFINANCE에서 지원하는 심볼 표기(KRX:453850 vs 453850:KS)를 시도하세요.

---

커스터마이즈 아이디어

• 부분청산 비율: CFG.sellPartialPct (기본 0.5=50%)
• 윈도/배수: Settings에서 종목별로 Window=20, Mult=2.0 조정
• 거래량 필터: 상단 터치 매도를 20일 평균 대비 거래량 ≥ 1.2배일 때만 발동하도록 조건 추가 가능
• 고가(High) 기준 터치: GoogleFinance로 고가 추가 후 r1.high >= up1 로직으로 변경 가능

---

면책

본 글은 교육용 예시이며, 투자 권유가 아닙니다. 모든 투자 결정과 책임은 투자자 본인에게 있습니다.

/***************
 * Google Sheets + Apps Script
 * Bollinger Band Alert Service (Daily)
 * - Reads tickers from "Settings" sheet
 * - Uses GOOGLEFINANCE to pull Close (daily)
 * - Computes BB(20,2), %B, BandWidth, squeeze(rolling 120d, p20)
 * - Strategy: buy below midline via %B buckets; sell (partial) on touching upper band (Close ≥ BB_UP)
 * - Sends alerts (email / Telegram) once per date+signal+ticker
 * - Preserves column widths & formatting in D_* sheets; adds header tooltips + conditional formats
 ***************/

const CFG = {
  settingsSheet: 'Settings',
  logSheet: 'Log',
  dataPrefix: 'D_',           // per-ticker data sheet name prefix
  defaultWindow: 20,
  defaultMult: 2.0,
  defaultLookback: 220,
  squeezeRoll: 120,
  squeezeQuantile: 0.20,
  sellPartialPct: 0.5,        // 상단 터치 시 권장 부분청산 비율(안내용)
  preserveFormatting: true,   // 열너비/서식 유지 모드
  notifyFrom: Session.getActiveUser().getEmail() || 'bb-alert@no-reply',
};

/* ================== Entry Points ================== */
function setup() {
  const ss = SpreadsheetApp.getActive();
  if (!ss.getSheetByName(CFG.settingsSheet)) {
    const s = ss.insertSheet(CFG.settingsSheet);
    s.getRange('A1:F1').setValues([['Ticker','Window','Mult','LookbackDays','Notify','Active']]);
    s.getRange('A2:F2').setValues([['SPY', CFG.defaultWindow, CFG.defaultMult, CFG.defaultLookback, 'EMAIL', true]]);
  }
  if (!ss.getSheetByName(CFG.logSheet)) {
    const l = ss.insertSheet(CFG.logSheet);
    l.getRange('A1:G1').setValues([['Timestamp','Date','Ticker','Signal','Close','Upper','Lower']]);
  }
  SpreadsheetApp.getUi().createMenu('BB Alert')
    .addItem('Run Now', 'runDaily')
    .addToUi();
}

function runDaily() {
  const cfgs = readSettings_();
  cfgs.forEach(cfg => {
    if (!cfg.active) return;
    try {
      const {ticker, lookback} = cfg;
      const dataSheet = ensureDataSheet_(ticker, lookback);
      SpreadsheetApp.flush();
      Utilities.sleep(1200); // GOOGLEFINANCE 반영 지연 보호(권장)

      const rows = readPriceTable_(dataSheet);
      if (rows.length < cfg.window + 5) return;

      const closes = rows.map(r => r.close);
      const bb = computeBB_(closes, cfg.window, cfg.mult);
      const pctB = computePctB_(closes, bb.lower, bb.upper);
      const bw = computeBW_(bb.middle, bb.upper, bb.lower);
      const squeezeMask = rollingSqueeze_(bw, CFG.squeezeRoll, CFG.squeezeQuantile);

      // 파생 컬럼 가시화 (D_시트 C~H) — 서식/열너비 유지
      writeDerived_(dataSheet, rows, bb, pctB, bw, squeezeMask);

      // UX: 헤더 노트 + 조건부서식 (매수=파랑, 매도=빨강)
      applySheetUX_(dataSheet);

      // 신호 탐지 (사용자 전략 포함)
      const signals = detectSignals_(rows, bb, squeezeMask, pctB);
      if (signals.length) {
        const latest = signals[signals.length - 1]; // 가장 최근 신호만 처리
        if (!alreadyLogged_(latest)) {
          const msg = makeMessage_(latest, cfg, pctB, bw);
          if (cfg.notify === 'EMAIL' || cfg.notify === 'BOTH') sendEmail_(msg);
          if (cfg.notify === 'TELEGRAM' || cfg.notify === 'BOTH') sendTelegram_(msg);
          logSignal_(latest);
        }
      }
    } catch (e) {
      Logger.log(`Error ${cfg.ticker}: ${e}`);
    }
  });
}

/* ================== Derived Columns (write to sheet) ================== */
function writeDerived_(sheet, rows, bb, pctB, bw, squeezeMask) {
  // 헤더 텍스트만 갱신 (서식 보존)
  sheet.getRange(1, 1, 1, 8).setValues([['Date','Close','BB_MID','BB_UP','BB_LO','PCTB','BW','SQ']]);

  // 값 쓰기 (A:B, C:H)
  const n = rows.length;
  const ab = rows.map(r => [r.date, r.close]);
  sheet.getRange(2, 1, n, 2).setValues(ab);

  const ch = new Array(n).fill(null).map((_, i) => ([
    bb.middle[i], bb.upper[i], bb.lower[i],
    pctB[i], bw[i], squeezeMask[i] ? 1 : 0
  ].map(v => (v === null || v === undefined || Number.isNaN(v)) ? '' : v)));
  sheet.getRange(2, 3, n, 6).setValues(ch);

  if (!CFG.preserveFormatting) {
    sheet.autoResizeColumns(1, 8);
  }
}

/* ================== UX: 헤더 툴팁 + 조건부서식 ================== */
function applyHeaderTooltips_(sheet) {
  const notes = [[
    '일자(일봉). 모든 신호는 종가 기준',
    '종가. 상단 터치(종가 ≥ BB_UP) 시 부분청산',
    '볼린저 중심선(SMA 20)',
    '볼린저 상단(MID + 2σ)',
    '볼린저 하단(MID − 2σ)',
    'PCTB=(Close−LO)/(UP−LO): 0=하단, 0.5=중간, 1=상단 (0.5 미만=분할매수 존)',
    'BW=(UP−LO)/MID: 낮을수록 스퀴즈',
    '스퀴즈 플래그(120일 하위 20% 이하면 1)'
  ]];
  sheet.getRange(1, 1, 1, 8).setNotes(notes);
  if (sheet.getFrozenRows() < 1) sheet.setFrozenRows(1);
}

function applyConditionalFormats_(sheet) {
  const lastRow = Math.max(2, sheet.getLastRow());
  const dataRange = sheet.getRange(2, 1, lastRow - 1, 8); // A2:H

  // PCTB<0.5 = 파랑(매수 후보)
  const ruleBuy = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$F2<0.5')
    .setBackground('#E8F0FE')
    .setRanges([dataRange])
    .build();

  // Close≥BB_UP = 빨강(매도/부분청산 후보)
  const ruleSell = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$B2>=$D2')
    .setBackground('#FDECEA')
    .setRanges([dataRange])
    .build();

  sheet.setConditionalFormatRules([ruleBuy, ruleSell]); // 빨강이 뒤에 있어 우선 적용
}

function applySheetUX_(sheet) {
  applyHeaderTooltips_(sheet);
  applyConditionalFormats_(sheet);
}

/* ================== Settings / Data ================== */
function readSettings_() {
  const s = SpreadsheetApp.getActive().getSheetByName(CFG.settingsSheet);
  if (!s) throw new Error('Settings sheet missing');
  const vals = s.getRange(2,1,Math.max(1,s.getLastRow()-1),6).getValues();
  const list = vals
    .filter(r => r[0])
    .map(r => ({
      ticker: String(r[0]).trim(),
      window: Number(r[1] || CFG.defaultWindow),
      mult: Number(r[2] || CFG.defaultMult),
      lookback: Number(r[3] || CFG.defaultLookback),
      notify: String(r[4] || 'EMAIL').toUpperCase(),
      active: String(r[5]).toLowerCase() !== 'false'
    }));
  return list;
}

/* 서식 보존: clear() 대신 clearContent(), autoResize 생략 */
function ensureDataSheet_(ticker, lookback) {
  const ss = SpreadsheetApp.getActive();
  const name = (CFG.dataPrefix + sanitize_(ticker)).slice(0,99);
  let sh = ss.getSheetByName(name);

  if (!sh) {
    sh = ss.insertSheet(name);
  } else {
    const lastRow = sh.getMaxRows();
    const clearRows = Math.max(0, lastRow - 1);
    if (clearRows > 0) {
      sh.getRange(2, 1, clearRows, 8).clearContent();
    }
  }

  const formula =
    `=ARRAYFORMULA(QUERY(GOOGLEFINANCE("${ticker}","close",TODAY()-${lookback},TODAY(),"DAILY"),"select Col1,Col2 where Col2 is not null",0))`;
  sh.getRange('A2').setFormula(formula);

  if (!CFG.preserveFormatting) {
    sh.autoResizeColumns(1, 2);
  }
  return sh;
}

function readPriceTable_(sheet) {
  const last = sheet.getLastRow();
  if (last < 3) return [];
  const vals = sheet.getRange(2,1,last-1,2).getValues();
  return vals
    .filter(r => r[0] && !isNaN(r[1]))
    .map(r => ({date: new Date(r[0]), close: Number(r[1])}));
}

/* ================== BB Math ================== */
function computeBB_(closeArr, window, mult) {
  const n = closeArr.length;
  const mid = Array(n).fill(null);
  const up  = Array(n).fill(null);
  const lo  = Array(n).fill(null);
  for (let i = 0; i < n; i++) {
    if (i+1 < window) continue;
    const w = closeArr.slice(i+1-window, i+1);
    const mean = avg_(w);
    const std = stdev_(w);
    mid[i] = mean;
    up[i]  = mean + mult * std;
    lo[i]  = mean - mult * std;
  }
  return {middle: mid, upper: up, lower: lo};
}
function computePctB_(closeArr, lo, up) {
  return closeArr.map((c,i)=> (up[i]&&lo[i]) ? (c - lo[i]) / (up[i]-lo[i]) : null);
}
function computeBW_(mid, up, lo) {
  return mid.map((m,i)=> (m&&up[i]&&lo[i]) ? (up[i]-lo[i]) / m : null);
}
function rollingSqueeze_(bw, roll, q) {
  const n = bw.length;
  const out = Array(n).fill(false);
  for (let i = 0; i < n; i++) {
    if (i+1 < roll) continue;
    const w = bw.slice(i+1-roll, i+1).filter(x => x !== null);
    if (w.length < 5) continue;
    const thr = percentile_(w, q);
    out[i] = bw[i] !== null && bw[i] <= thr;
  }
  return out;
}

/* ================== Signal Logic (with user strategy) ================== */
function detectSignals_(rows, bb, squeezeMask, pctB) {
  const sigs = [];
  for (let i = 1; i < rows.length; i++) {
    const r0 = rows[i-1], r1 = rows[i];
    const up0 = bb.upper[i-1], lo0 = bb.lower[i-1], mid0 = bb.middle[i-1];
    const up1 = bb.upper[i],   lo1 = bb.lower[i],   mid1 = bb.middle[i];
    if ([up0,lo0,mid0,up1,lo1,mid1].some(v => v === null)) continue;

    const pB0 = pctB?.[i-1], pB1 = pctB?.[i];

    const add = (code, text) => sigs.push({
      date: toYmd_(r1.date),
      ticker: '',
      code, text,
      close: r1.close, up: up1, lo: lo1
    });

    /* 기본 BB 신호(필요시 유지) */
    // if (r0.close <= up0 && r1.close > up1) add('BB_Breakout_Up','상단 밴드 돌파 종가'); // 상단 터치와 중복 가능
    if (r0.close >= lo0 && r1.close < lo1) add('BB_Breakout_Down','하단 밴드 이탈 종가');
    if (r0.close >  up0 && r1.close <= up1) add('BB_Reentry_Sell','상단 밖→안 복귀');
    if (r0.close <  lo0 && r1.close >= lo1) add('BB_Reentry_Buy','하단 밖→안 복귀');
    if (r0.close <= mid0 && r1.close >  mid1) add('BB_Mid_Cross_Up','중심선 상향 재돌파');
    if (r0.close >= mid0 && r1.close <  mid1) add('BB_Mid_Cross_Down','중심선 하향 재돌파');

    if (squeezeMask[i-1] && !squeezeMask[i] && r1.close > up1) add('BB_Sq_Release_Up','스퀴즈 해제 + 상단 돌파');
    if (squeezeMask[i-1] && !squeezeMask[i] && r1.close < lo1) add('BB_Sq_Release_Down','스퀴즈 해제 + 하단 이탈');

    /* 사용자 전략: 분할매수 (%B 버킷) */
    if (r0.close >= mid0 && r1.close < mid1) add('STRAT_Buy_CrossDownMid','전략매수: 중간선 하향 돌파');
    if (pB0 != null && pB1 != null) {
      if (pB0 >= 0.50 && pB1 < 0.50 && pB1 >= 0.35) add('STRAT_Buy_T1','전략매수 T1: %B 0.35~0.50 (중간선 아래)');
      if (pB0 >= 0.35 && pB1 < 0.35 && pB1 >= 0.20) add('STRAT_Buy_T2','전략매수 T2: %B 0.20~0.35');
      if (pB0 >= 0.20 && pB1 < 0.20)                add('STRAT_Buy_T3','전략매수 T3: %B < 0.20 (하단 인근)');
    }

    /* 사용자 전략: 상단 터치 시 매도(부분청산) — 종가 기준 */
    if (r0.close < up0 && r1.close >= up1) {
      add('STRAT_Sell_TouchUpper', `전략매도: 상단밴드 터치(부분청산 권장 ${Math.round(CFG.sellPartialPct*100)}%)`);
    }
  }
  const t = getActiveTickerContext_();
  return sigs.map(s => ({...s, ticker: t}));
}

/* ================== Notify / Log ================== */
function makeMessage_(sig, cfg, pctB, bw) {
  const lastPctB = lastValid_(pctB);
  const lastBW = lastValid_(bw);
  const lines = [
    `📣 ${cfg.ticker} | ${sig.date}`,
    `• Signal: ${sig.text} (${sig.code})`,
    `• Close=${fmt(sig.close)}  Upper=${fmt(sig.up)}  Lower=${fmt(sig.lo)}`,
    `• %B=${fmt(lastPctB)}  BW=${fmt(lastBW)}`,
    `• BB(${cfg.window}, ${cfg.mult})`
  ];
  return lines.join('\n');
}
function sendEmail_(msg) {
  MailApp.sendEmail({
    to: Session.getActiveUser().getEmail(),
    subject: 'BB Alert',
    htmlBody: `<pre>${escapeHtml_(msg)}</pre>`
  });
}
function sendTelegram_(msg) {
  const props = PropertiesService.getScriptProperties();
  const token = props.getProperty('TELEGRAM_BOT_TOKEN');
  const chatId = props.getProperty('TELEGRAM_CHAT_ID');
  if (!token || !chatId) return;
  const url = `https://api.telegram.org/bot${token}/sendMessage`;
  UrlFetchApp.fetch(url, {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({chat_id: chatId, text: msg})
  });
}
function logSignal_(sig) {
  const sh = SpreadsheetApp.getActive().getSheetByName(CFG.logSheet);
  sh.appendRow([new Date(), sig.date, sig.ticker, sig.code, sig.close, sig.up, sig.lo]);
}
function alreadyLogged_(sig) {
  const sh = SpreadsheetApp.getActive().getSheetByName(CFG.logSheet);
  const rng = sh.getRange(2,1,Math.max(0,sh.getLastRow()-1),7).getValues();
  return rng.some(r =>
    toYmd_(r[1]) === sig.date &&
    String(r[2]) === sig.ticker &&
    String(r[3]) === sig.code
  );
}

/* ================== Helpers ================== */
function sanitize_(s){ return String(s).replace(/[^A-Za-z0-9_:-]/g,'_'); }
function toYmd_(d){ return Utilities.formatDate(new Date(d),'Asia/Seoul','yyyy-MM-dd'); }
function avg_(a){ return a.reduce((x,y)=>x+y,0)/a.length; }
function stdev_(a){
  const m = avg_(a);
  const v = avg_(a.map(x => Math.pow(x-m,2)));
  return Math.sqrt(v);
}
function percentile_(arr, p){
  const a = [...arr].sort((x,y)=>x-y);
  const idx = Math.max(0, Math.min(a.length-1, Math.floor(p * (a.length-1))));
  return a[idx];
}
function lastValid_(arr) {
  for (let i = arr.length-1; i >= 0; i--) if (arr[i] !== null) return arr[i];
  return null;
}
function fmt(x){ return (x===null || x===undefined) ? '-' : Number(x).toFixed(4); }
function escapeHtml_(s){ return String(s).replace(/[&<>"']/g, m => ({'&':'&amp;','<':'&lt;','>':'&gt;','"':'&quot;',"'":'&#39;'}[m])); }

// Stores active ticker while detectSignals_ runs
function getActiveTickerContext_(){ return globalThis.__activeTicker || ''; }
function setActiveTickerContext_(t){ globalThis.__activeTicker = t; }

// Override ensureDataSheet_ to push ticker into context around read (small trick)
const _ensureDataSheet = ensureDataSheet_;
ensureDataSheet_ = function(ticker, lookback){
  setActiveTickerContext_(ticker);
  return _ensureDataSheet(ticker, lookback);
};