const spread = new GC.Spread.Sheets.Workbook(document.getElementById('spreadContainer'));
const sheet = spread.getActiveSheet();
spread.options.tabStripVisible = false;
sheet.suspendPaint();
const headers = ['종목명', '매수가(원)', '현재가(원)', '수량', '수익률(%)', '평가손익(원)', '포트비중(%)'];
const colWidths = [120, 110, 110, 65, 88, 115, 88];
headers.forEach((h, c) => {
sheet.getCell(0, c).value(h).backColor('#1565C0').foreColor('#fff').font('bold 12px sans-serif').hAlign(GC.Spread.Sheets.HorizontalAlign.center);
sheet.setColumnWidth(c, colWidths[c]);
});
sheet.setRowHeight(0, 36);
const stocks = [
['삼성전자', 60500, 79800, 50],
['SK하이닉스', 88000, 175000, 20],
['NAVER', 210000, 188000, 15],
['카카오', 55000, 44500, 80],
['LG에너지솔루션', 430000, 398000, 5],
['현대차', 178000, 255000, 30],
['셀트리온', 185000, 210000, 25],
['POSCO홀딩스', 380000, 452000, 10],
];
const totalValue = stocks.reduce((s, [, , cur, qty]) => s + cur * qty, 0);
stocks.forEach(([name, buy, cur, qty], r) => {
const row = r + 1;
const retPct = ((cur - buy) / buy * 100);
const pnl = (cur - buy) * qty;
const weight = (cur * qty / totalValue * 100);
sheet.setValue(row, 0, name);
sheet.setValue(row, 1, buy);
sheet.setValue(row, 2, cur);
sheet.setValue(row, 3, qty);
sheet.setValue(row, 4, parseFloat(retPct.toFixed(2)));
sheet.setValue(row, 5, pnl);
sheet.setValue(row, 6, parseFloat(weight.toFixed(1)));
sheet.getCell(row, 0).hAlign(GC.Spread.Sheets.HorizontalAlign.left);
sheet.getCell(row, 1).formatter('#,##0');
sheet.getCell(row, 2).formatter('#,##0');
sheet.getCell(row, 4).formatter('0.00');
sheet.getCell(row, 5).formatter('#,##0');
sheet.getCell(row, 6).formatter('0.0');
if (r % 2 === 1) for (let c = 0; c < 7; c++) sheet.getCell(row, c).backColor('#F5F5F5');
});
// 요약 행: STDEV 수식으로 포트폴리오 수익률 변동성 자동 계산
const sumRow = stocks.length + 1;
sheet.getCell(sumRow, 0).value('합계 / 변동성(σ)').font('bold 12px sans-serif').backColor('#E3F2FD');
sheet.setValue(sumRow, 5, stocks.reduce((s, [, buy, cur, qty]) => s + (cur - buy) * qty, 0));
sheet.getCell(sumRow, 5).formatter('#,##0').font('bold 12px sans-serif').backColor('#E3F2FD');
sheet.setFormula(sumRow, 4, '=STDEV(E2:E9)');
sheet.getCell(sumRow, 4).formatter('0.00').font('bold 12px sans-serif').backColor('#E3F2FD');
// 3단계 컬러 스케일: 손실(빨강) → 보합(노랑) → 수익(초록)
const SVT = GC.Spread.Sheets.ConditionalFormatting.ScaleValueType;
sheet.conditionalFormats.add3ScaleRule(
SVT.number, -20, '#EF5350',
SVT.number, 0, '#FFF9C4',
SVT.number, 30, '#66BB6A',
[new GC.Spread.Sheets.Range(1, 4, stocks.length, 1)]
);
sheet.resumePaint();