Google Sheets Script Reduce Function With Stock Tickers, Quantity, Price

[travisbklein]

thank you so much for any help, I’m new here and really appreciate it!

I have a list of all my stock trades with 3 columns of use, many duplicates of TICKER so that’s why I want to use the “reduce” function which does work but only to reduce using TICKER and QUANTITY, can’t figure out how to get my script to do PRICE also.

TICKER QUANTITY PRICE

GOOG------3---------$200

my script here works great to reduce down the TICKER QUANTITY so it consolidates one line for each ticker and shows the added values of QUANTITY for each TICKER / row

function MyPortfolio(symbols, quantity, price) {
  const sums = symbols.reduce((map, ticker, i ) => {
    ticker = ticker.toString();
    if (map[ticker]) {
      map[ticker] += Number(quantity[i]);
    } else {
      map[ticker] = Number(quantity[i]);
    }
    return map;
  }, {});

  return Object.entries(sums);
}

I want my script to also do the PRICE and have “pseudo” scripted it, the logic is correct but I know I can’t do map[ticker].price I’m just not sure how to reference the price.

Here is my “code”

function MyPortfolio(symbols, quantity, price) {
  const sums = symbols.reduce((map, ticker, i ) => {
    ticker = ticker.toString();
    if (map[ticker].quantity) {
	  map[ticker].quantity += Number(quantity[i]);	  
    } else {
      map[ticker].quantity = Number(quantity[i]);
	  map[ticker].price = Number(price[i])  * Number(map[ticker].quantity);
    }
    return map;
  }, {});
  return Object.entries(sums);
}

[colinthornton]

Assuming the above image is your input, what’s the expected output as a JS object? Just for reference that would be the quickest to understand your goal.

[bbsmooth]

Also, seeing as you are setting map to an empty object initially, the following line is going to throw an error immediately:

if (map[ticker].quantity) 

Are the three inputs to the MyPortfolio all arrays, one for the symbols, one for the quantities, and one for the prices? I’m guessing they are but you named the last two as singular so it threw me for a moment. I would definitely name them as plurals (quantities and prices).

UPDATE: OK, I think I got this working the way you want. You’ve got some issues in the if/else statements, I’ve already alluded to one. The error messages your code generates should give you a good idea of what the errors are. If you need help fixing them let us know.

[travisbklein]

sorry I should have done that before. Here is the expected output.

but also I would need to add logic to check for when quantity == 0 and then reset the price array so when there are no shares owned, the price gets reset to zero. I would just add an if statement to check for that map[ticker] == 0

on the 2nd transaction ROKU gets zeroed out there are no shares owned, so that is what I’m referring to.

@bbsmooth yes that line was written to show my intention, that is how I would do it using metatables in lua. But I’m not sure how to write it to get it working in this case for my google sheet.

They are arrays yes

yes you are right I should rename the variables I’m just used to doing quantity in pairs quantities

[travisbklein]

I’m wondering if you could tell me what you did to get it working for you, thank you so much!