FIFO for Investments Inventory Accounting [Google Sheets - Apps Script]

Fifo.gs

/**
 * Calculates the remaining inventory, current average buy price, total cost and total gains using FIFO (First In First Out) method.
 * The function expects three parameters: transactionsType, transactionQuantities and transactionPrices sorted by date.
 *
 * Inspired by: https://www.allstacksdeveloper.com/2022/09/fifo-stock-portfolio-google-sheets.html
 * 
 * @param {string[] | string} transactionsType - An array or a single string representing transaction types. Each string should be either 'B' for buy or 'S' for sell.
 * @param {number[] | number} transactionQuantities - An array or a single number representing the quantities for each transaction.
 * @param {number[] | number} transactionPrices - An array or a single number representing the prices for each transaction.
 * @throws Will throw an error if transactionPrices and transactionQuantities are not arrays or if their lengths are not equal.
 * @throws Will throw an error if the first transaction type is not 'B' (Buy).
 * @throws Will throw an error if a sell transaction occurs before any buy transaction.
 * @throws Will throw an error if the transaction type is neither 'B' nor 'S'.
 * @returns {number[]} A 4-element array: The remaining inventory (number of units left after all transactions), current average buy price per unit (average cost price of the units left in the inventory), total cost of all transactions, and total gains from all sell transactions.
 */

function FIFO(transactionsType, transactionQuantities, transactionPrices) {
  if (typeof transactionPrices === 'number') {
    transactionsType = [transactionsType];
    transactionPrices = [transactionPrices];
    transactionQuantities = [transactionQuantities];
  }

  if (!Array.isArray(transactionPrices) || !Array.isArray(transactionQuantities) || transactionPrices.length === 0 || transactionQuantities.length === 0) {
    throw new Error('Transaction quantities and prices must have at least one element' );
  }

  if (transactionPrices.length !== transactionQuantities.length) {
    throw new Error('Transaction quantities and prices do not have the same length');
  }

  if (String(transactionsType[0]) !== "B") {
    throw new Error('The first transaction should be a buy: ' + transactionsType[0]);
  }

  const inventory = [];
  let totalGains = 0.;
  let totalCost = 0.;

  for (let index = 0; index < transactionPrices.length; index++) {
    const price = Math.abs(transactionPrices[index]);
    const quantity = Math.abs(transactionQuantities[index]);
    const type = String(transactionsType[index])

    if (type === "B") { // BUY
      inventory.push({price: price, quantity: quantity});
    } else if (type === "S") { // SELL
      if (inventory.length === 0) {
        throw new Error('Sell transaction before a buy transaction');
      }

      let quantityToSell = quantity;
      while (quantityToSell > 0) {
        let inventoryItem = inventory[0];
        if (inventoryItem.quantity <= quantityToSell) {
          quantityToSell -= inventoryItem.quantity;
          totalCost += inventoryItem.quantity * inventoryItem.price;
          totalGains += inventoryItem.quantity * (price - inventoryItem.price);
          inventory.shift();
        } else {
          inventoryItem.quantity -= quantityToSell;
          totalCost += quantityToSell * inventoryItem.price;
          totalGains += quantityToSell * (price - inventoryItem.price);
          quantityToSell = 0;
        }
      }
    } else {
      throw new Error('Wrong transaction type: ' + type);
    }
  }

  const remainingInventory = inventory.reduce((total, item) => total + item.quantity, 0);
  const remainingInventoryCost = inventory.reduce((total, item) => total + (item.quantity * item.price), 0);
  const currentAverageBuyPrice = remainingInventoryCost / Math.max(remainingInventory, 1);

  return [remainingInventory, currentAverageBuyPrice, totalCost, totalGains];
}