如何将新交易条目与数据库中现有资产数据合并更新？

核心逻辑调整 #

修改原有updateTrade函数，在写入数据前先匹配Portfolio表中是否存在相同资产，根据匹配结果执行更新或新增操作，具体逻辑如下：

• 读取Trademanager表提交的新交易数据
• 遍历Portfolio表所有数据行，以B列资产名称作为唯一标识匹配
• 匹配到对应资产行：
  • 总持仓量 = 原有持仓量 + 新提交交易量
  • 加权平均成本价 = (原有持仓量 * 原有成本价 + 新提交交易量 * 新提交买入价) / 总持仓量
  • 直接更新交易日期、SL、TP字段为新提交的值
• 未匹配到对应资产：按原有逻辑在最后一行新增数据

修改后可直接使用的代码 #

// 资产匹配列索引，B列对应索引为1，无需修改
const SEARCH_COL_IDX = 1;

function updateTrade() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const formSS = ss.getSheetByName("Trademanager");
  const datasheet = ss.getSheetByName("Portfolio");

  // 读取新提交的交易数据
  const newTrade = {
    date: formSS.getRange("D3").getValue(),
    asset: formSS.getRange("D5").getValue(),
    volume: formSS.getRange("D7").getValue(),
    price: formSS.getRange("D9").getValue(),
    sl: formSS.getRange("D11").getValue(),
    tp: formSS.getRange("D13").getValue()
  };

  // 读取Portfolio所有数据
  const portfolioData = datasheet.getDataRange().getValues();
  let matchedRowIndex = -1;
  let oldVolume = 0;
  let oldPrice = 0;

  // 遍历匹配资产，默认第一行为表头，所以从i=1开始遍历，无表头则改为i=0
  for (let i = 1; i < portfolioData.length; i++) {
    if (portfolioData[i][SEARCH_COL_IDX] === newTrade.asset) {
      matchedRowIndex = i;
      oldVolume = portfolioData[i][2]; // C列是原有持仓量
      oldPrice = portfolioData[i][3]; // D列是原有成本价
      break;
    }
  }

  // 计算合并后的值
  let finalVolume, finalPrice;
  if (matchedRowIndex > -1) {
    finalVolume = oldVolume + newTrade.volume;
    finalPrice = (oldVolume * oldPrice + newTrade.volume * newTrade.price) / finalVolume;
  } else {
    finalVolume = newTrade.volume;
    finalPrice = newTrade.price;
  }

  const finalValues = [
    newTrade.date,
    newTrade.asset,
    finalVolume,
    finalPrice,
    newTrade.sl,
    newTrade.tp,
    '=if(R[0]C[-3]=1,0,(((R[0]C[-1]/R[0]C[-3]-1)*100)/((R[0]C[-3]/R[0]C[-2]-1)*100)))'
  ];

  // 写入数据
  if (matchedRowIndex > -1) {
    // 匹配到资产，更新对应行，行号为数组索引+1
    datasheet.getRange(matchedRowIndex + 1, 1, 1, 7).setValues([finalValues]);
  } else {
    // 未匹配到，新增行
    datasheet.getRange(datasheet.getLastRow() + 1, 1, 1, 7).setValues([finalValues]);
  }
}

// 原有搜索函数无需修改
function searchStr(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName("Trademanager");

  var str = formSS.getRange("d17").getValue();
  Logger.log(str);

  var values = ss.getSheetByName("Portfolio").getDataRange().getValues();
  for (var i = 0; i < values.length; i++) {
    var row = values[i];
    if(row[SEARCH_COL_IDX] == str) {
      formSS.getRange("d3").setValue(row[0]);
      formSS.getRange("d5").setValue(row[1]);
      formSS.getRange("d7").setValue(row[2]);
      formSS.getRange("d9").setValue(row[3]);
      formSS.getRange("d11").setValue(row[4]);
      formSS.getRange("d13").setValue(row[5]);
      formSS.getRange("d25").setValue(row[2]);
    }
  }
}

注意事项 #

1. 确保Portfolio表的B列资产名称没有拼写、大小写、空格差异，否则会被识别为不同资产
2. 如果你的Portfolio表没有表头，将遍历循环的起始值i = 1改为i = 0即可
3. 加权成本价计算采用实际投入总金额除以总持仓量的标准算法，符合交易统计规则

内容的提问来源于stack exchange，提问作者Osman P.