How do I combine new entries with existing data from the database?-CodePudding

I'm trying to make a tool for my trades. I have built several functions where I can add new trades to my portfolio and close trades where trades are moved from my portfolio to my archive. But sometimes I buy assets on different prices, like expecting it to go down a bit, but since I don't know how much I ladder down accumulating on different levels. I would like to add those trades to my sheet as well, however that requires the existing asset to update its amount and purchase price.

Here is an example file.

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

There are two sheets, one to submit data and one database. So basically, my first challenge: it should not write on the last row, but the row where the asset already exist. Second challenge: while some data can be just changed to the new info, some needs to be combined with the existing data (old amount new amount, average purchase price instead of old/new purchase price).

This is the "trademanager" sheet, you enter a trade and it copies it to the portfolio. What I don't want is a new row with this trade, what I do want is to update the already existing asset with this new trade (add amount, average price, and putting in new date, SL en TP info

This is the "porftolio" sheet, with the current situation (duplicate assets) and the desired situation (new and old asset combined with amount added to existing and price averaged between existing and new entry.

This is what I have so far. I know it is something with finding the unique asset and writing to it. But I don't know how to combine it. Or maybe there is a much easier way of course.

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

  var values = [[formSS.getRange("d3").getValue(),
                formSS.getRange("d5").getValue(),
                formSS.getRange("d7").getValue(),
                formSS.getRange("d9").getValue(),
                formSS.getRange("d11").getValue(),
                formSS.getRange("d13").getValue(),
                '=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)))']]
  
  datasheet.getRange(datasheet.getLastRow() 1,1,1,7).setValues(values)
}

var SEARCH_COL_IDX = 1;

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]);
    }
  }
}

CodePudding user response：

I believe your goal is as follows.

You want to retrieve the inputted values from the sheet "Trademanager" and want to put it to the sheet "Portfolio". At this time, you want to search the duplicate values from the column "B" of "Portfolio" and when the inputted value is duplicated with the existing data, you want to put the data by updating.

In this case, how about the following modified script?

Modified script:

function updateTrade() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName("Trademanager");
  var datasheet = ss.getSheetByName("Portfolio");
  var colValues = formSS.getRange("D1:D13").getValues();
  var values = [3, 5, 7, 9, 11, 13].map(e => colValues[e - 1][0]);
  var dataRange = datasheet.getRange("A4:F"   datasheet.getLastRow());
  var obj = dataRange.getValues().reduce((o, r) => {
    o[r[1] == values[1] ? "dupValues" : "newValues"].push(r);
    return o
  }, { newValues: [], dupValues: [] });
  if (obj.dupValues.length > 0) {
    var [, , occ, odd] = obj.dupValues[0];
    var [naa, nbb, ncc, ndd, nee, nff] = values;
    obj.newValues.push([naa, nbb, occ   ncc, ((ndd * ncc)   (odd * occ)) / (occ   ncc), nee, nff]);
  } else {
    obj.newValues.push(values);
  }
  dataRange.clearContent();
  datasheet.getRange(4, 1, obj.newValues.length, obj.newValues[0].length).setValues(obj.newValues);
}

When you run this modified script, the inputted values are retrieved from "Trademanager" and the values are checked with "Portfolio" and put to it to "Portfolio".
The formulas of occ ncc and ((ndd * ncc) (odd * occ)) / (occ ncc) are from your sample Spreadsheet.

Modified script:

References: