연습장

사진첩(러시아)

function openBuyForm(){
  SpreadsheetApp.getUi()
    .showModalDialog(HtmlService.createHtmlOutputFromFile("buyForm")
    .setWidth(400).setHeight(520), "매수 입력");
}
 
 
 
function getNaverStockName(ticker){
  try{
    const url = "https://m.stock.naver.com/api/stock/" + ticker + "/basic";
 
    const res = UrlFetchApp.fetch(url, {
      muteHttpExceptions: true,
      headers: {
        "User-Agent": "Mozilla/5.0",
        "Referer": "https://m.stock.naver.com/"
      }
    });
 
    const data = JSON.parse(res.getContentText());
    return data.stockName || "";
  }catch(e){
    return "";
  }
}
 
function saveBuy(d){
  const sh = SpreadsheetApp.getActive().getSheetByName("매수");
  const r = sh.getLastRow()+1;
 
  // 종목명 찾기
  const name = findStockNameFromAll(d.ticker);
 
  // 날짜 형식 통일
  const formattedDate = formatDateYMD(d.date);
 
  const row = [
    formattedDate,
    d.acc,
    name,
    "'" + d.ticker,
    d.market,
    d.price,
    d.qty,
    d.total,
    d.target,
    d.stopp,
    "",
    d.type,
    d.reason,
    ""
  ];
 
  sh.getRange(r,1,1,row.length).setValues([row]);
 
  // 비중 계산 (기존 로직)
  const lastDataRow = sh.getLastRow();
  if(lastDataRow >= 5){
    const hRange = sh.getRange(5,8,lastDataRow-4,1);
    const hValues = hRange.getValues().flat().map(v => Number(v)||0);
    const totalH = hValues.reduce((a,b)=>a+b,0);
    if(totalH>0){
      for(let i=0; i<hValues.length; i++){
        const ratio = (hValues[i]/totalH)*100;
        sh.getRange(5+i,11).setValue(Math.round(ratio*10)/10);
      }
    } else {
      for(let i=0; i<hValues.length; i++){
        sh.getRange(5+i,11).setValue(0);
      }
    }
  }
 
  // ✅ 상태 시트 업데이트
  updateStatusSheet();
}
 
 
 
 
function formatDateYMD(dateStr){
  const d = new Date(dateStr);
  const y = d.getFullYear();
  const m = ('0'+(d.getMonth()+1)).slice(-2);
  const day = ('0'+d.getDate()).slice(-2);
  return `${y}-${m}-${day}`;
}
 
 
 
 
/* 🔎 구분별 종목 검색 */
function searchStock(keyword, type){
  const ss = SpreadsheetApp.getActive();
  let sh;
 
  if(type === "ETF"){
    sh = ss.getSheetByName("ETF");
  }else{
    sh = ss.getSheetByName("주식종목");
  }
 
  const data = sh.getRange(2,1,sh.getLastRow()-1,2).getValues();
 
  return data
    .filter(r => r[1].toString().includes(keyword))
    .slice(0, 20);
}
 
 
function findStockName(ticker, type){
  const ss = SpreadsheetApp.getActive();
  const sh = (type === "ETF")
      ? ss.getSheetByName("ETF")
      : ss.getSheetByName("주식종목");
 
  const data = sh.getRange(2,1,sh.getLastRow()-1,2).getValues();
 
  // 숫자/문자/앞자리 0 전부 제거 → 6자리 통일
  const t = String(ticker).replace(/[^0-9A-Z]/gi,'').padStart(6,'0');
 
  for(let i=0;i<data.length;i++){
    const code = String(data[i][0]).replace(/[^0-9A-Z]/gi,'').padStart(6,'0');
    if(code === t) return data[i][1];
  }
  return "";
}
 
function onEdit(e){
  const sh = e.source.getActiveSheet();
  if(sh.getName() !== "매수") return;
  if(e.range.getA1Notation() !== "C1") return;
  if(e.value !== "TRUE") return;
 
  // 1️⃣ A3 오늘 날짜 입력
  sh.getRange("A3").setFormula("=TODAY()");
 
  // 2️⃣ 티커 가져오기
  const tickerRaw = sh.getRange("D3").getValue();
  if(!tickerRaw){
    sh.getRange("C1").setValue(false);
    return;
  }
 
  // 3️⃣ 종목명 찾기
  const name = findStockNameFromAll(tickerRaw);
  sh.getRange("C3").setValue(name);
 
  // 4️⃣ A3:N3 데이터 4번째 줄 이후 마지막 행 다음에 저장
  const lastRow = sh.getLastRow();
  const saveRow = Math.max(lastRow + 1, 5); // 최소 5행부터 저장
  const data = sh.getRange("A3:N3").getValues()[0];
  sh.getRange(saveRow, 1, 1, data.length).setValues([data]);
 
  // 5️⃣ H3, I3, J3만 남기고 나머지 초기화
  const keepIndexes = [8,9,10]; // H=8, I=9, J=10
  for(let col=1; col<=14; col++){
    if(!keepIndexes.includes(col)){
      sh.getRange(3,col).clearContent();
    }
  }
 
  // 6️⃣ H5 이후 모든 행 비중 계산
  const lastDataRow = sh.getLastRow();
  if(lastDataRow >= 5){
    const hRange = sh.getRange(5,8,lastDataRow-4,1); // H5~마지막 행
    const hValues = hRange.getValues().flat().map(v => Number(v)||0);
    const totalH = hValues.reduce((a,b)=>a+b,0);
 
    if(totalH>0){
      for(let i=0; i<hValues.length; i++){
        const ratio = (hValues[i]/totalH)*100;
        sh.getRange(5+i,11).setValue(Math.round(ratio*10)/10); // 소수점 첫째 자리
      }
    } else {
      for(let i=0; i<hValues.length; i++){
        sh.getRange(5+i,11).setValue(0);
      }
    }
  }
 
  // 7️⃣ 상태 시트 업데이트 추가 ✅
  updateStatusSheet();
 
  // 8️⃣ 체크박스 초기화
  sh.getRange("C1").setValue(false);
}
 
 
 
 
 
function findStockNameFromAll(ticker){
  const ss = SpreadsheetApp.getActive();
  const sheets = ["주식종목","ETF"];
  const t = String(ticker).replace(/[^0-9A-Z]/gi,'').padStart(6,'0');
 
  for(const s of sheets){
    const sh = ss.getSheetByName(s);
    const data = sh.getRange(2,1,sh.getLastRow()-1,2).getValues();
 
    for(let i=0;i<data.length;i++){
      const code = String(data[i][0]).replace(/[^0-9A-Z]/gi,'').padStart(6,'0');
      if(code === t) return data[i][1];
    }
  }
  return "";
}
 
 
function updateStatusSheet(){
  const ss = SpreadsheetApp.getActive();
  const buySheet  = ss.getSheetByName("매수");
  const sellSheet = ss.getSheetByName("매도");
  const status    = ss.getSheetByName("상태");
  if(!buySheet || !sellSheet || !status) return;
 
  const buyLast  = buySheet.getLastRow();
  const sellLast = sellSheet.getLastRow();
  if(buyLast < 5) return;
 
  const buyData  = buySheet.getRange(5,1,buyLast-4,14).getValues();
  const sellData = sellLast >= 5
        ? sellSheet.getRange(5,1,sellLast-4,7).getValues()
        : [];
 
  // 상태 초기화 (A~K)
  if(status.getLastRow() >= 2){
    status.getRange(2,1,status.getLastRow()-1,11).clearContent();
  }
 
  const map = {};
 
  // =========================
  // 매수 반영
  // =========================
  buyData.forEach(r=>{
    const acc    = r[1];
    const name   = r[2];
    const ticker = String(r[3]).replace("'","");
    const qty    = Number(r[6]);
    const total  = Number(r[7]);
    const type   = r[11];
    if(!acc || !ticker || !qty) return;
 
    const key = acc+"|"+ticker;
 
    if(!map[key]){
      map[key] = {acc,name,ticker,qty:0,total:0,type};
    }
    map[key].qty   += qty;
    map[key].total += total;
  });
 
  // =========================
  // 매도 반영 (수량 차감)
  // =========================
  sellData.forEach(r=>{
    const acc    = r[1];
    const name   = r[2];
    const ticker = String(r[3]).replace("'","");
    const qty    = Number(r[6]);
    if(!acc || !ticker || !qty) return;
 
    const key = acc+"|"+ticker;
 
    if(!map[key]){
      map[key] = {acc,name,ticker,qty:0,total:0,type:""};
    }
    map[key].qty -= qty;   // ⭐ 핵심
  });
 
  const out = [];
 
  Object.values(map).forEach(v=>{
    if(v.qty <= 0) return; // 전량 매도면 상태에서 제거
 
    const avg = v.qty ? Math.round(v.total / v.qty) : "";
    const accShort = v.acc === "위탁종합" ? "위" : v.acc === "ISA" ? "I" : v.acc;
 
    out.push([
      accShort,   // A
      v.name,     // B
      "",         // C 손실률
      "",         // D 매도여부
      "",         // E 현재가
      "",         // F 시간
      avg,        // G 평균매수가
      "",         // H 최고가
      v.type,     // I 구분
      v.ticker,   // J 티커
      v.qty       // ⭐ K 수량
    ]);
  });
 
  if(out.length){
    status.getRange(2,1,out.length,11).setValues(out);
  }
}
 
 
 
 
function getNaverRealtimePrice(ticker){
  try{
    const url = "https://polling.finance.naver.com/api/realtime/domestic/stock/" + ticker;
 
    const res = UrlFetchApp.fetch(url, {
      headers: {
        "User-Agent": "Mozilla/5.0",
        "Referer": "https://finance.naver.com"
      },
      muteHttpExceptions: true
    });
 
    const json = JSON.parse(res.getContentText());
 
    if(!json.datas || json.datas.length === 0) return null;
 
    const price = json.datas[0].closePrice;
    return Number(price.replace(/,/g,""));
  }catch(e){
    return null;
  }
}
 
 
 
function updateStatusRealtime(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("상태");
  if(!sh) return;
 
  const lastRow = sh.getLastRow();
  if(lastRow < 2) return;
 
  const tickers = sh.getRange(2,10,lastRow-1,1).getValues().flat(); // J열
  const avgs    = sh.getRange(2,7,lastRow-1,1).getValues().flat();  // G열
 
  for(let i=0;i<tickers.length;i++){
    const t = tickers[i];
    if(!t) continue;
 
    const price = getNaverRealtimePrice(t);
    if(price === null) continue;
 
    const avg = Number(avgs[i]);
    const loss = avg ? Math.round((price-avg)/avg*1000)/10 : "";
 
    sh.getRange(i+2,5).setValue(price);      // E열 현재가
    sh.getRange(i+2,3).setValue(loss);       // C열 손실률
    sh.getRange(i+2,6).setValue(new Date()); // F열 업데이트시간
  }
}
 
 
function buildStatusSheet(){
  const ss = SpreadsheetApp.getActive();
  const buy = ss.getSheetByName("매수");
  let st = ss.getSheetByName("상태");
  if(!st){
    st = ss.insertSheet("상태");
    st.appendRow(["계좌","종목명","손실률","매도여부","현재가","업데이트시간","평균매수가","최고가","구분","티커"]);
  }
 
  const data = buy.getRange(5,1,buy.getLastRow()-4,12).getValues();
 
  const map = {};
 
  data.forEach(r=>{
    const acc = r[1];
    const name = r[2];
    const ticker = r[3];
    const price = Number(String(r[5]).replace(/,/g,""));
    const qty   = Number(r[6]);
    const type  = r[11];
    if(!ticker || !qty) return;
 
    const key = acc+"_"+ticker;
    if(!map[key]){
      map[key] = {acc,name,ticker,type,sum:0,qty:0};
    }
    map[key].sum += price*qty;
    map[key].qty += qty;
  });
 
  st.getRange(2,1,st.getLastRow(),10).clearContent();
 
  let row=2;
  Object.values(map).forEach(o=>{
    const avg = Math.round(o.sum/o.qty);
    const accShort = o.acc=="위탁종합" ? "위" : (o.acc=="ISA"?"I":o.acc);
 
    st.getRange(row,1,1,10).setValues([[
      accShort,o.name,"","", "", "", avg,"",o.type,o.ticker
    ]]);
    row++;
  });
}
 
function getNaverPrice(t){
  try{
    const url = "https://polling.finance.naver.com/api/realtime/domestic/stock/"+t;
    const res = UrlFetchApp.fetch(url,{muteHttpExceptions:true});
    const json = JSON.parse(res.getContentText());
    if(!json.datas || !json.datas.length) return null;
    return Number(json.datas[0].closePrice.replace(/,/g,""));
  }catch(e){
    return null;
  }
}
 
 
function updateStatusPrice(){
  const sh = SpreadsheetApp.getActive().getSheetByName("상태");
  const last = sh.getLastRow();
  if(last<2) return;
 
  for(let i=2;i<=last;i++){
    const t = sh.getRange(i,10).getValue();
    const avg = sh.getRange(i,7).getValue();
    if(!t || !avg) continue;
 
    const price = getNaverPrice(t);
    if(!price) continue;
 
    const loss = Math.round((price-avg)/avg*1000)/10;
 
    sh.getRange(i,5).setValue(price);
    sh.getRange(i,3).setValue(loss);
    sh.getRange(i,6).setValue(new Date());
  }
}
 
 
function updateStatusRealtimePrice() {
  const sh = SpreadsheetApp.getActive().getSheetByName("상태");
  const lastRow = sh.getLastRow();
  if (lastRow < 2) return;
 
  const tickers = sh.getRange("J2:J" + lastRow).getValues();
  const priceRange = sh.getRange("E2:E" + lastRow);   // 현재가
  const lossRange  = sh.getRange("C2:C" + lastRow);   // 손실률
  const avgRange   = sh.getRange("G2:G" + lastRow);   // 평균매수가
  const highRange  = sh.getRange("H2:H" + lastRow);   // 최고가
  const timeRange  = sh.getRange("F2:F" + lastRow);   // 업데이트시간
 
  let prices = priceRange.getValues();
  let losses = lossRange.getValues();
  let avgs   = avgRange.getValues();
  let highs  = highRange.getValues();
  let times  = timeRange.getValues();
 
  for (let i = 0; i < tickers.length; i++) {
    const t = tickers[i][0];
    if (!t) continue;
 
    try {
      const url = "https://polling.finance.naver.com/api/realtime/domestic/stock/" + t;
      const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
      const data = JSON.parse(res.getContentText());
      if (!data || !data.datas || data.datas.length === 0) continue;
 
      const row = data.datas[0];
 
      const price = Number(row.closePrice.replace(/,/g, ""));
      const todayHigh = Number(row.highPrice.replace(/,/g, ""));
      const naverTimeStr = parseNaverTime(data.time);
 
 
      if (price) prices[i][0] = price;
      if (naverTimeStr) times[i][0] = naverTimeStr;
 
      // 손실률
      if (price && avgs[i][0]) {
        losses[i][0] = Math.round(((price - avgs[i][0]) / avgs[i][0]) * 1000) / 10;
      }
 
      // 최고가
      if (!highs[i][0] || todayHigh > highs[i][0]) {
        highs[i][0] = todayHigh;
      }
 
      // 업데이트 시간
 
 
    } catch (e) {
      Logger.log("FAIL : " + t);
    }
  }
 
  priceRange.setValues(prices);
  lossRange.setValues(losses);
  highRange.setValues(highs);
  timeRange.setValues(times);
}
 
function parseNaverTime(t){
  if(!t || t.length !== 14) return "";
 
  const h  = t.substr(8,2);
  const mi = t.substr(10,2);
  const s  = t.substr(12,2);
 
  return `${h}:${mi}:${s}`;
}
 
 
function openSellForm(){
  SpreadsheetApp.getUi()
    .showModalDialog(
      HtmlService.createHtmlOutputFromFile("sellForm")
        .setWidth(420)
        .setHeight(620),
      "매도 입력"
    );
}
 
 
function getSellCandidates(){
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("상태");
  const lastRow = sh.getLastRow();
  if(lastRow < 2) return [];
 
  // A:계좌 B:종목명 G:평균매수가 J:티커 K:수량
  const data = sh.getRange(2,1,lastRow-1,11).getValues();
  const result = [];
 
  data.forEach(r=>{
    const acc    = r[0];   // 위 / I
    const name   = r[1];
    const avg    = r[6];
    const ticker = r[9];   // J열
    const qty    = r[10];  // K열 ⭐
 
    if(name && ticker && qty){
      const accFull = acc === "위" ? "위탁종합" : acc === "I" ? "ISA" : acc;
      result.push([accFull, name, ticker, avg, qty]);
    }
  });
 
  return result;
}
 
 
 
function saveSell(d){
  const sh = SpreadsheetApp.getActive().getSheetByName("매도");
  const r = Math.max(sh.getLastRow()+1, 5);
 
  const profitRate = ((d.sell - d.buy) / d.buy * 100).toFixed(1);
  const profitAmt = (d.sell - d.buy) * d.qty;
 
  const row = [
    d.date,
    d.acc,
    d.name,
    "'" + d.ticker,
    d.buy,
    d.sell,
    d.qty,
    profitRate,
    profitAmt,
    d.reason,
    d.buyReview,
    d.sellReview,
    d.lesson
  ];
 
  sh.getRange(r,1,1,row.length).setValues([row]);
 
  updateStatusSheet(); 
 
}
  • playground/playground.txt
  • Last modified: 5 weeks ago
  • by likewind