Google Sheet股票持仓管理脚本调试求助 卖出功能失效排查

核心错误点 #

脚本卖出功能完全失效是由两处语法错误和一处逻辑漏洞导致：

• 持仓份额读取错误：卖出逻辑中直接将交易对象转为数值，得到的结果为NaN，后续所有份额判断逻辑全部失效
  原错误代码：
  itemToSell.shares = Number(Number(itemToSell).toFixed(precision));
  修正为：
  itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision));
• 份额扣减笔误：卖出扣减逻辑中引用了不存在的shares变量，运行到此处会直接抛出引用错误，终止执行
  原错误代码：
  sharesToSell -= itemToSell / shares;
  修正为：
  sharesToSell -= itemToSell.shares;
• 死循环漏洞：卖出循环未处理待卖出份额大于总持仓的情况，持仓清空后会进入无限循环，需要增加跳出判断。

可选优化：拆股逻辑修复 #

原有拆股逻辑运算错误，且map方法无返回值会导致持仓数组被清空，修正后的拆股逻辑可正常处理拆股场景。

完整修正后代码 #

function generateTrade(stockQuant, stockPrice, action){
  return {
    shares: stockQuant,
    price: stockPrice,
    action: action
  };
}

function generateFifo(security, actions, quantity, price){
  let portfolio = new Map ();
  for(var i = 0; i<security.length; i++){

    let ticker = security[i].toString();
    let action = actions[i].toString();
    let stockQuant = Number(quantity[i]);
    let stockPrice = Number(price[i]);
    let trade = generateTrade(stockQuant, stockPrice, action);


    if(action == "Buy" || action.toUpperCase() == "DRIP"){
      let activeTrades = portfolio.get(ticker);
      if(activeTrades == null){
        portfolio.set(ticker, [trade]);
      } else {
        activeTrades.push(trade);
      }
    }


    if (action.toUpperCase() == "SPLIT"){
      let activeTrades = portfolio.get(ticker);
      if(!activeTrades) continue;
      let ratio = quantity[i].toString().split(":").map(Number);
      // 拆股比例x:y，代表每y股拆为x股
      const shareMultiplier = ratio[0]/ratio[1];
      activeTrades = activeTrades.map((trade) =>{
        trade.shares = Number((trade.shares * shareMultiplier).toFixed(5));
        trade.price = Number((trade.price / shareMultiplier).toFixed(5));
        return trade;
      });
      portfolio.set(ticker, activeTrades);
    }


    if(action == "Sell"){
      let activeTrades = portfolio.get(ticker);
      let precision = 5;
      if (activeTrades != null){
        let sharesToSell = Number(Number(trade.shares).toFixed(precision));
        while (sharesToSell > 0){
          sharesToSell = Number(Number(sharesToSell).toFixed(precision));
          if (activeTrades.length === 0 ){
            // 持仓已清空，剩余待卖出份额不足，跳出循环
            break;
          }
          let itemToSell = activeTrades[0];
          itemToSell.shares = Number(Number(itemToSell.shares).toFixed(precision));
          if(itemToSell.shares == sharesToSell){
            sharesToSell = 0;
            activeTrades.splice(0,1);
          }
          else if(itemToSell.shares < sharesToSell) {
            sharesToSell -= itemToSell.shares;
            activeTrades.splice(0,1);
          }
          else {
            itemToSell.shares -= sharesToSell;
            sharesToSell = 0;
          }
        }
        if (activeTrades.length == 0){
          portfolio.delete(ticker);
        } else {
          portfolio.set(ticker, activeTrades);
        }
      }
    }
  }
  return portfolio;
}

function myPositions(security, actions, quantity,price){
  let portfolio = generateFifo(security, actions, quantity, price);
  let returnArry = [];
  portfolio.forEach((value, key) => {
    let shares = 0;
    let totalCost = 0;
    let avgPrice = 0;

    value.forEach(trade => {
      shares += trade.shares;
      totalCost += trade.shares * trade.price;
    });
    avgPrice = totalCost / shares;
    returnArry.push([key, shares, avgPrice]);
  });
  return returnArry;
}

内容的提问来源于stack exchange，提问作者Kaiser Dragon