-3

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
6
  • Works fine for me. Commented Feb 24, 2025 at 9:35
  • Try refesh. And take your time. GAS is not super fast. :) Commented Feb 24, 2025 at 9:38
  • If click on the error, a detailed error message will show up. Could you post that? Commented Feb 24, 2025 at 11:01
  • Try to check your custom function in Google sheet and check the execution log to make sure what's an error. Commented Feb 24, 2025 at 11:05
  • The question is lacking the exact error message you get. Check My Executions for failed executions, and edit the question to include the exact error message you see there. Commented Feb 24, 2025 at 11:55

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.