fifo_positions.gs

//Get transactions from customer transaction history and sort them by date oldest to newest
var lastTransactionRow = SpreadsheetApp.getActive().getRangeByName('lastTransactionRow').getValue();
var transactions = SpreadsheetApp.getActive().getSheetByName("History").getRange("B3:J" + (lastTransactionRow - 1)).getValues();
transactions = transactions.sort(compareNthElements(4, basicComparator));
//specify which column contains the account name (arrays are 0 based)
var accountCol = 2;
//tickers may be duplicated across accounts; each key in a Map object must be unique; therefore each account needs it's own map
var account1 = new Map();
var account2 = new Map();
var account3 = new Map();
var account4 = new Map();
var account5 = new Map();
var account6 = new Map();

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

function generatePortfolio() {
  //determine the names of the unique accounts the user has in transaction history
  var uniqueAccounts = transactions.map( function (v) {
    return v[accountCol];
  })
  .filter( function (v,i,a) {
    return a.indexOf(v) === i;
  });
  //loop over the transaction rows to generate a list of tickers and trades from which the positions in the account can be determined
  for (var j = 0; j < transactions.length; j++) {
    let ticker = transactions[j][0].toString();
    let transactionAccount = transactions[j][2].toString();
    let action = transactions[j][8].toString();
    let stockQuant = Number(transactions[j][5]);
    let stockPrice = Number(transactions[j][7]);
    let trade = generateTrade(transactionAccount, stockQuant, stockPrice, action);

//process the buys, drips, and splits which increase the shares for a given ticker and also generally affect the cost basis
    if (action == "Buy" || action.toUpperCase() == "DRIP" || action == "Split") {
      if (transactionAccount == uniqueAccounts[0]) {
        let activeTrades = account1.get(ticker); //does the ticker already exist for the account
        if (activeTrades == null) {
          account1.set(ticker, [trade]); //ticker does not exist in this account so add it and generate the trade details
        } else {
          activeTrades.push(trade); //ticker does exist so add details of this trade
        }
      } else if (transactionAccount == uniqueAccounts[1]) {
        let activeTrades = account2.get(ticker);
        if (activeTrades == null) {
          account2.set(ticker, [trade]);
        } else {
          activeTrades.push(trade);
        }
      } else if (transactionAccount == uniqueAccounts[2]) {
        let activeTrades = account3.get(ticker);
        if (activeTrades == null) {
          account3.set(ticker, [trade]);
        } else {
          activeTrades.push(trade);
        }
      } else if (transactionAccount == uniqueAccounts[3]) {
        let activeTrades = account4.get(ticker);
        if (activeTrades == null) {
          account4.set(ticker, [trade]);
        } else {
          activeTrades.push(trade);
        }
      } else if (transactionAccount == uniqueAccounts[4]) {
        let activeTrades = account5.get(ticker);
        if (activeTrades == null) {
          account5.set(ticker, [trade]);
        } else {
          activeTrades.push(trade);
        }
      } else {
        let activeTrades = account6.get(ticker);
        if (activeTrades == null) {
          account6.set(ticker, [trade]);
        } else {
          activeTrades.push(trade);
        }
      }     
    }

    if (action == "Sell") {
      if (transactionAccount == uniqueAccounts[0]) {
        let activeTrades = account1.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;
            }
            if(activeTrades.length > 0){
              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){
            account1.delete(ticker);
          }
        }
      } else if (transactionAccount == uniqueAccounts[1]) {
        let activeTrades = account2.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;
            }
            if(activeTrades.length > 0){
              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){
            account2.delete(ticker);
          }
        }
      } else if (transactionAccount == uniqueAccounts[2]) {
        let activeTrades = account3.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;
            }
            if(activeTrades.length > 0){
              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){
            account3.delete(ticker);
          }
        }
      } else if (transactionAccount == uniqueAccounts[3]) {
        let activeTrades = account4.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;
            }
            if(activeTrades.length > 0){
              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){
            account4.delete(ticker);
          }
        }
      } else if (transactionAccount == uniqueAccounts[4]) {
        let activeTrades = account5.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;
            }
            if(activeTrades.length > 0){
              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){
            account5.delete(ticker);
          }
        }
      } else {
        let activeTrades = account6.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;
            }
            if(activeTrades.length > 0){
              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){
            account6.delete(ticker);
          }
        }
      }
    }
  }
  var myPositions = [];
  account1.forEach ((value, key) => {
    let shares = 0;
    let totalCost = 0;
    let avgPrice = 0;
    let account = "";

    value.map (trade =>{
      shares += trade.shares;
      account = trade.account;
      totalCost += trade.shares * trade.price;
  });
  avgPrice = totalCost / shares;
  myPositions.push([key, account, shares, avgPrice]);
  });
  account2.forEach ((value, key) => {
    let shares = 0;
    let totalCost = 0;
    let avgPrice = 0;
    let account = "";

    value.map (trade =>{
      shares += trade.shares;
      account = trade.account;
      totalCost += trade.shares * trade.price;
  });
  avgPrice = totalCost / shares;
  myPositions.push([key, account, shares, avgPrice]);
  });
  account3.forEach ((value, key) => {
    let shares = 0;
    let totalCost = 0;
    let avgPrice = 0;
    let account = "";

    value.map (trade =>{
      shares += trade.shares;
      account = trade.account;
      totalCost += trade.shares * trade.price;
  });
  avgPrice = totalCost / shares;
  myPositions.push([key, account, shares, avgPrice]);
  });
  account4.forEach ((value, key) => {
    let shares = 0;
    let totalCost = 0;
    let avgPrice = 0;
    let account = "";

    value.map (trade =>{
      shares += trade.shares;
      account = trade.account;
      totalCost += trade.shares * trade.price;
  });
  avgPrice = totalCost / shares;
  myPositions.push([key, account, shares, avgPrice]);
  });
  account5.forEach ((value, key) => {
    let shares = 0;
    let totalCost = 0;
    let avgPrice = 0;
    let account = "";

    value.map (trade =>{
      shares += trade.shares;
      account = trade.account;
      totalCost += trade.shares * trade.price;
  });
  avgPrice = totalCost / shares;
  myPositions.push([key, account, shares, avgPrice]);
  });
  account6.forEach ((value, key) => {
    let shares = 0;
    let totalCost = 0;
    let avgPrice = 0;
    let account = "";

    value.map (trade =>{
      shares += trade.shares;
      account = trade.account;
      totalCost += trade.shares * trade.price;
  });
  avgPrice = totalCost / shares;
  myPositions.push([key, account, shares, avgPrice]);
  });
  Logger.log(myPositions);
  return myPositions.sort();
}

function basicComparator(first, second) {
    if (first === second) {
        return 0;
    } else if (first < second) {
        return -1;
    } else {
        return 1;
    }
}

function compareNthElements(n, comparatorFunction, reverse) {
    return function(first, second) {
        if (reverse === true) {
            return comparatorFunction(second[n], first[n]);
        } else {
            return comparatorFunction(first[n], second[n]);
        }
    }
}

function refreshPositions() {
  var positionsFormula = '=generatePortfolio()'
  SpreadsheetApp.getActive().getSheetByName('Positions').getRange(3,1).clearContent();

  var positionsCheck = SpreadsheetApp.getActive().getRangeByName("positionsCheck").getValue();

  for(var i = 0; i < 100; i++) {
    if (!positionsCheck) {
      break;
    }
  }
  SpreadsheetApp.getActive().getSheetByName('Positions').getRange(3,1).setFormula(positionsFormula);
}