2025.08.25 블룸버그 스타일로 ETF 표준편차 구하기

INPUT  시트의 A2: D2에 종목명, 티커, 기간, 밴드기간을 입력하면 표준편차를 구해주는 구글시트 & 앱스크립트이다. 

볼린저밴드 차트까지 만들려고 했으나 실패했다. 뭐가 꼬인거 같은데 내실력으로 거기까지는 구현하지 못했다. 

모로가도 서울만 가면 된다고 볼린저밴드매매 에서 입력해서 그리면 된다. 다음에 시간이 더되면 해봐야겠다. 이틀동안 붙잡고 있더니 더 헷갈린다. 

 

 

https://docs.google.com/spreadsheets/d/1QPyWWhiALJDFh1BeJSybURxhKRbH-N5HS5JrWwdAuRE/edit?usp=sharing

표준편차매매법

 

const NUM_BINS = 40;

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('σ툴')
    .addItem('템플릿(재)구성', 'setup')
    .addItem('히스토그램 재생성', 'makeHistogramChart')
    .addToUi();
}

function setup() {
  const ss = SpreadsheetApp.getActive();
  const inputS = ss.getSheetByName('Input');
  if (!inputS) throw new Error('Input 시트가 필요합니다.');

  const name   = String(inputS.getRange('A2').getDisplayValue() || '').trim();
  const ticker = String(inputS.getRange('B2').getDisplayValue() || '').trim();
  const period = Number(inputS.getRange('C2').getValue() || 0);
  const bandN  = Number(inputS.getRange('D2').getValue() || 20); // 볼린저 N
  if (!name || !ticker || !period) throw new Error('Input!A2:D2를 채워주세요.');

  const sh = ensureSheet_(ss, name, true);

  // ===== 1) 본표 A:J (G=BB Mid, J=Signal) =====
  sh.getRange('A1:J1').setValues([[
    'Date','Close','Return','MA(N)','BB Low(-2σ)','-1σ','BB Mid(MA)','+1σ','BB High(+2σ)','Signal'
  ]]);

  // A:B (가격)
  sh.getRange('A2').setFormula(
    '=QUERY(GOOGLEFINANCE(Input!B2,"close",TODAY()-Input!C2,TODAY()),"select Col1, Col2 offset 1",0)'
  );

  // C (로그수익률)
  sh.getRange('C2').setFormula('=ARRAYFORMULA({""; IFERROR(LN(B3:B / B2:B), )})');

  // 포맷
  sh.getRange('A:A').setNumberFormat('yyyy-mm-dd');
  sh.getRange('B:B').setNumberFormat('#,##0');
  sh.getRange('C:C').setNumberFormat('0.00%');
  sh.setFrozenRows(1);

  SpreadsheetApp.flush();
  waitForReturnsReady_(sh, 15000);

  // ===== 2) MA & σ (가격 기반 볼린저) D:I =====
  const last = lastDataRow_(sh, 1);
  for (let r = 2; r <= last; r++) {
    if (r < 1 + bandN) { sh.getRange(r, 4, 1, 6).setValues([['','','','','','']]); continue; }
    const a = r - bandN + 1, b = r;
    sh.getRange(r, 4).setFormula(`=AVERAGE(B${a}:B${b})`);                 // D: MA
    sh.getRange(r, 5).setFormula(`=D${r}-2*STDEV.S(B${a}:B${b})`);          // E: -2σ
    sh.getRange(r, 6).setFormula(`=D${r}-STDEV.S(B${a}:B${b})`);            // F: -1σ
    sh.getRange(r, 8).setFormula(`=D${r}+STDEV.S(B${a}:B${b})`);            // H: +1σ
    sh.getRange(r, 9).setFormula(`=D${r}+2*STDEV.S(B${a}:B${b})`);          // I: +2σ
  }
  sh.getRange('D:I').setNumberFormat('#,##0');

  // ===== 3) Mid(G) 배열수식 =====
  sh.getRange('G2').setFormula('=ARRAYFORMULA(IF(D2:D="","",D2:D))'); // Mid = MA

  // ===== 4) Signal (±1σ=F/H 기준) → J열 =====
  sh.getRange('J2').setFormula(
    '=ARRAYFORMULA(IF(B2:B="","",IF((H2:H="")+(F2:F="")>0,"",IF(B2:B>=H2:H,"SELL",IF(B2:B<=F2:F,"BUY","")))))'
  );

  // ===== 5) 요약 통계(가로) M1:T2 =====
  sh.getRange('M1:T1').setValues([[
    '평균(μ)','표준편차(σ)','표본수(N)','bin 폭','−2σ','−1σ','+1σ','+2σ'
  ]]);
  sh.getRange('M2').setFormula('=IFERROR(AVERAGE(FILTER(C2:C,ISNUMBER(C2:C))),)');  // μ(로그수익률)
  sh.getRange('N2').setFormula('=IFERROR(STDEV.S(FILTER(C2:C,ISNUMBER(C2:C))),)');  // σ(로그수익률)
  sh.getRange('O2').setFormula('=IFERROR(COUNT(C2:C),)');
  sh.getRange('P2').setFormula('=IF(OR(M2="",N2=""),"",0.2*N2)');
  sh.getRange('Q2').setFormula('=IF(OR(M2="",N2=""),"",M2-2*N2)');
  sh.getRange('R2').setFormula('=IF(OR(M2="",N2=""),"",M2-N2)');
  sh.getRange('S2').setFormula('=IF(OR(M2="",N2=""),"",M2+N2)');
  sh.getRange('T2').setFormula('=IF(OR(M2="",N2=""),"",M2+2*N2)');
  sh.getRange('M2:N2').setNumberFormat('0.00%');
  sh.getRange('P2:T2').setNumberFormat('0.00%');
  sh.getRange('O2').setNumberFormat('0');
  applySummaryColors_(sh);

  // ===== 6) 히스토그램 원데이터 AA:AG =====
  sh.getRange('AA1:AG1').setValues([[
    'Bin Center(수익률)','히스토그램 건수','정규분포 추정건수','-2σ','-1σ','+1σ','+2σ'
  ]]);
  sh.getRange('AA2').setFormula(
    `=IF($M$2="","",SEQUENCE(${NUM_BINS},1,$M$2-4*$N$2 + $P$2/2,$P$2))`
  );
  const lastBinRow = 1 + NUM_BINS;
  for (let r = 2; r <= lastBinRow; r++) {
    sh.getRange(r, 28).setFormula(
      `=IF($M$2="","",COUNTIFS(C$2:C, ">="&(AA${r}-$P$2/2), C$2:C, "<"&(AA${r}+$P$2/2)))`
    );
    sh.getRange(r, 29).setFormula(
      `=IF($M$2="","",NORM.DIST(AA${r}, $M$2, $N$2, FALSE)*$O$2*$P$2)`
    );
    const maxRange = `$AB$2:$AB$${lastBinRow}`;
    sh.getRange(r, 30).setFormula(`=IF($M$2="","",IF(ABS(AA${r}-$Q$2)<$P$2/2, MAX(${maxRange}), ))`);
    sh.getRange(r, 31).setFormula(`=IF($M$2="","",IF(ABS(AA${r}-$R$2)<$P$2/2, MAX(${maxRange}), ))`);
    sh.getRange(r, 32).setFormula(`=IF($M$2="","",IF(ABS(AA${r}-$S$2)<$P$2/2, MAX(${maxRange}), ))`);
    sh.getRange(r, 33).setFormula(`=IF($M$2="","",IF(ABS(AA${r}-$T$2)<$P$2/2, MAX(${maxRange}), ))`);
  }
  sh.getRange(2, 27, NUM_BINS, 1).setNumberFormat('0.00%');
  sh.getRange(2, 28, NUM_BINS, 6).setNumberFormat('0');

  // ===== 7) (보조표 사용 안 함) =====

  // ===== 8) 조건부서식(J열만) =====
  applySignalFormatting_(sh);

  // ===== 9) 차트 =====
  SpreadsheetApp.flush();
  waitForStatsReady_(sh, 8000);
  removeChartAt_(sh, 13, 4);   // 히스토그램 M4
  makeHistogramChart();
}

// ---- 히스토그램 → M4
function makeHistogramChart() {
  const ss = SpreadsheetApp.getActive();
  const name = ss.getSheetByName('Input').getRange('A2').getDisplayValue().trim();
  const ticker = ss.getSheetByName('Input').getRange('B2').getDisplayValue();
  const sh = ss.getSheetByName(name);
  if (!sh) return;
  if (sh.getRange('M2').getValue() === "" || sh.getRange('O2').getValue() <= 1) return;

  const range = sh.getRange(1, 27, 1 + NUM_BINS, 7); // AA1:AG
  const chart = sh.newChart().asComboChart()
    .addRange(range).setNumHeaders(1)
    .setOption('title', `${name} (${ticker}) 수익률 분포 & 정규추정`)
    .setOption('width', 900)
    .setOption('height', 600)
    .setOption('legend', { position: 'top' })
    .setOption('hAxis', { title: '수익률(%)', format: '0.00%' })
    .setOption('vAxis', { title: '건수' })
    .setOption('series', {
      0: { type: 'bars' },
      1: { type: 'line', curveType: 'function' },
      2: { type: 'line' }, 3: { type: 'line' }, 4: { type: 'line' }, 5: { type: 'line' }
    })
    .setPosition(4, 13, 0, 0)
    .build();
  removeChartAt_(sh, 13, 4);
  sh.insertChart(chart);
}

// ===== 조건부서식(J열만) =====
function applySignalFormatting_(sh) {
  const lastRow = sh.getMaxRows();
  const colJ = sh.getRange(2, 10, lastRow - 1, 1); // J열
  const buyRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$J2="BUY"')
    .setBackground('#D6E4FF').setFontColor('#0B5394')
    .setRanges([colJ]).build();
  const sellRule = SpreadsheetApp.newConditionalFormatRule()
    .whenFormulaSatisfied('=$J2="SELL"')
    .setBackground('#F8D7DA').setFontColor('#A61C1C')
    .setRanges([colJ]).build();
  sh.setConditionalFormatRules([buyRule, sellRule]);
  sh.getRange('J:J').setHorizontalAlignment('center').setFontWeight('bold');
}

// ===== 요약 통계 가독성 =====
function applySummaryColors_(sh) {
  sh.getRange('M1:T1').setBackground('#F5F5F5').setFontWeight('bold');
  sh.getRange('M2:T2').setFontWeight('normal').setFontColor('#222');
  sh.getRange('M2').setBackground('#E3F2FD').setFontWeight('bold').setFontColor('#0D47A1'); // μ
  sh.getRange('N2').setBackground('#FFF3E0').setFontWeight('bold').setFontColor('#E65100'); // σ
  sh.getRange('O2:P2').setBackground('#FAFAFA');
  sh.getRange('Q2').setBackground('#FFEBEE').setFontColor('#B71C1C').setFontWeight('bold'); // −2σ
  sh.getRange('T2').setBackground('#FFEBEE').setFontColor('#B71C1C').setFontWeight('bold'); // +2σ
  sh.getRange('R2:S2').setBackground('#E8F5E9').setFontColor('#1B5E20').setFontWeight('bold'); // −1/+1
}

// ===== 유틸 =====
function ensureSheet_(ss, name, clear) {
  let s = ss.getSheetByName(name);
  if (!s) s = ss.insertSheet(name);
  if (clear) {
    s.clear();
    s.clearFormats();
    s.setConditionalFormatRules([]);
    s.getCharts().forEach(c => s.removeChart(c));
  }
  return s;
}

function removeChartAt_(sheet, anchorCol, anchorRow) {
  sheet.getCharts().forEach(c => {
    const info = c.getContainerInfo();
    if (info.getAnchorColumn() === anchorCol && info.getAnchorRow() === anchorRow) {
      sheet.removeChart(c);
    }
  });
}

function lastDataRow_(sheet, col) {
  const values = sheet.getRange(1, col, sheet.getMaxRows(), 1).getValues();
  for (let i = values.length - 1; i >= 0; i--) {
    if (values[i][0] !== '' && values[i][0] != null) return i + 1;
  }
  return 1;
}

function waitForReturnsReady_(sh, maxMs) {
  const t0 = Date.now();
  while (Date.now() - t0 < maxMs) {
    SpreadsheetApp.flush();
    const last = lastDataRow_(sh, 3);
    const vals = sh.getRange(2, 3, Math.max(0, last - 1), 1).getValues().flat();
    const cnt = vals.filter(v => typeof v === 'number' && !isNaN(v)).length;
    if (cnt > 10) return true;
    Utilities.sleep(300);
  }
  return false;
}

function waitForStatsReady_(sh, maxMs) {
  const t0 = Date.now();
  while (Date.now() - t0 < maxMs) {
    SpreadsheetApp.flush();
    const m = sh.getRange('M2').getValue();
    const n = sh.getRange('N2').getValue();
    const cnt = Number(sh.getRange('O2').getValue());
    if (typeof m === 'number' && typeof n === 'number' && cnt > 1) return true;
    Utilities.sleep(300);
  }
  return false;
}