0

I have a google sheet wherein there are 2 sheets named "Position" and "History" respectively.

I have a custom function that takes data in the "History" sheet and arrange that data in the "Position" sheet to create a portfolio of stocks with the FIFO (First in first out) method in the ticker, share and purchase column.

Now, the problem is, everything works fine until there are fewer data in the "History" sheet, and when data is increased in the "History" sheet, the "Exceeded maximum execution time (line 0)" error occurs.

So, How to fine-tune that code to full fill the task, if not what is the possible workaround.

Sample sheet link: Click to open sample sheet

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


function generateFifo(security, actions, quantity, price){
let portfolio = new Map();
for(var i = 0; i<security.length; i++){

let ticker = security[i].toString();
let action = actions[i].toString();
let stockQuant = Number(quantity[I]);
let stockPrice = Number(price[I]);
let trade = generateTrade(stockQuant, stockPrice, action);

if(action == "Buy" || action.toUpperCase() == "DRIP"){
let activeTrades = portfolio.get(ticker);
if(activeTrades == null){
portfolio.set(ticker, [trade]);
} else {
activeTrades.push(trade);
}
}

if(action.toUpperCase() == "SPLIT"){
let activeTrades = portfolio.get(ticker);
let ratio = quantity[i].toString().split(":");
activeTrades = activeTrades.map((trade) =>{
trade.shares = trade.shares * ratio[0];
trade.price = trade.price / ratio[0];
trade.shares = trade.shares * ratio[1];
trade.price = trade.price / ratio[1];
 });
 }

if(action == "Sell"){
let activeTrades = portfolio.get(ticker);
let precision = 8;
if (activeTrades != null){
let sharesToSell = Number(Number(trade.shares).toFixed(precision));
while (sharesToSell > 0){
  sharesToSell = Number(Number(sharesToSell).toFixed(precision));
  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){
  portfolio.delete(ticker);
  }
 }
 }
}
 return portfolio;
 }




     function myPositions(security, actions, quantity, price){
    let portfolio = generateFifo(security, actions, quantity, price);

   let returnArry = [];
   portfolio.forEach((value, key) => {
    let shares = 0;
   let totalCost = 0;
   let avgPrice = 0;
  value.map(trade => {
   shares += trade.shares;
  totalCost += trade.shares * trade.price;
 });

  avgPrice = totalCost / shares;
 returnArry.push([key, shares, avgPrice]);
 });

return returnArry;
}

The output should come as this image: Click to open image

Hope I am clear to you, please help.

1

1 Answer 1

1

Google Sheets custom function have a 30 seconds execution time limit. If the time execution of your custom function depends on a growing data source it's very likely that it eventually will fail. To prevent this you have to change your approach.

You might use an installable trigger together with a control statement to automatically refresh the results when they should be updated.

References

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.