CODEWITHSUNDEEP
google-sheetsgoogle-apps-scriptstockcustom-functiongoogle-finance
Liu Ed
Liu Ed

Reputation: 9

Spreadsheet custom function suddenly stop working

I use this to keep track of my stock portfolio, been using it for almost three years now, and recently it stop working, can't figure out why.

function MyPortfolio2(tickers, values , price) 
{
  var total = []
  var sums = {}
  var average = {}
  var total_price
  for(i=0; i < tickers.length; i++)
  {   
    var t = tickers[i].toString()

    if(t != "Cash")
    {
       if(t in sums)
       {
         if(Number(values[i])>0){
          total_price=sums[t]*average[t]+Number(values[i])*Number(price[i])
          average[t]=total_price/(sums[t]+Number(values[i]))
         }
         sums [t] += Number(values[i])
       }
       else
       {
         sums[t] = Number(values[i]) 
         average[t] = Number(price[i])
       }
    }

  }
  for(var ticker in sums)
  {
    if(sums[ticker]>0)
    {
      total.push([ticker, sums[ticker], average[ticker]])
    } 
  }
  return total
}

I have two copies and it both stop working at the same time, but if I return my spreadsheet to a previous version, it would work for about a second before showing errors again.

EDIT1:

Screenshot of my Googlesheets

This is where input my data

sample1

error

sample2

expected output

sample3

It adds up my current stock holding to show what stocks I own and how many shares, also calculate my average buy price.

I have two Googlesheets that has this same exact function, it was the first version of my portfolio tracker sheet, I only open it up to see if it was working of not, unfortunately it wasn't either.

I greatly appreciate the help, I don't really know much about coding, kind of hard for me to find the solution on my own.

EDIT2:

DATA INPUT:

here I enter my transaction data

tickers value price
TSLA 4 200
TSLA 1 400
GOOG 1 100
DIS 1 150
DIS -1 100

RESULT:

here shows my holding and my average price

=MyPortfolio2(tickers, value, price)
TICKER SHARES AVG_PRICE
TSLA 5 240
GOOG 1 100

Upvotes: -3

Views: 107

Answers (1)

doubleunary
doubleunary

Reputation: 19145

Custom functions use Apps Script, and there are daily quotas and limitations. If you exceed a quota or limitation, the function errors out. In addition, there are long-time issues such as Google sheets custom functions stuck in loading.

To avoid those issues, use a plain vanilla spreadsheet formula, like this:

=query(
  query(
    filter(hstack(Transactions!B2:B, Transactions!D2:D, Transactions!D2:D * Transactions!E2:E), Transactions!D2:D),
    "select Col1, sum(Col2), sum(Col3) / sum(Col2) group by Col1", 0
  ),
  "where Col2 <> 0 label Col1 'Ticker', Col2 'Shares', Col3 'Avg price' ", 1
)

If you have this data in the Transactions tab:

date tickers action value price
... TSLA ... 4 200
... TSLA ... 1 400
... GOOG ... 1 100
... DIS ... 1 150
... DIS ... -1 100

...the formula will get this result:

Ticker Shares Avg price
GOOG 1 100
TSLA 5 240

See query(), filter() and Quotas for Google Services.

To vote for fixing the loading issue, click the star icon ☆ in the issue tracker. Please do not comment, i.e., do not post a "me too" or "+1" reply, but just click the star icon, because comments spam everyone's inboxes. Google prioritizes issues with the most stars.

Upvotes: 2

Related Questions