0

I have a simple pivot table, but I'm using Calculated Field values.

The raw data used for the pivot table is like a transaction log, as shown below:

DATE STOCK QTY PRICE TOTAL
03/2023 AAPL 1 100 100
03/2023 AAPL 2 120 240
03/2023 GOOG 2 60 120
03/2023 GOOG 3 80 240

The sheet also has the current price of a ticker with the GOOGLEFINANCE(..) function, as shown below:

TICKER PRICE
AAPL 158.28
GOOG 103.06

The pivot table groups the transactions by the ticker name, sums up the TOTAL per transaction QTY * PRICE, and shows the current value QTY * 'CURRENT PRICE'. The current price is a lookup to the current price table expressed as =VLOOKUP(STOCK,$K$7:$L$10,2, FALSE)*SUM(QTY) in the pivot table as a calculated field.

However, the Grand Total of the CURR POSITION column shows a wrong value. It shows $3,044.75 instead of $1,150.88. It should sum the VLOOKUP value, but it shows a different value.

How can I fix this?

Sample sheet for better visualization here

And prints bellow:

print of the pivot table print of the CURR POSITION calculated field

1
  • Totals would likely be better at the top or somewhere with easy access imo. For ex, if you have 100 stocks, how will you be able to quickly glance at overall performance? Scroll down 100 rows? Commented Mar 29, 2023 at 5:10

1 Answer 1

1
+50

You can get the desired result by using a query() formula instead of a pivot table, like this:

=let( 
  transactions, B6:E16, 
  tickers, offset(transactions, 0, 0, , 1), 
  quantity, offset(transactions, 0, 1, , 1), 
  position_, lambda(t, q, q * vlookup(t, K7:L10, columns(K7:L10), false)), 
  totals_, lambda(col, if(sum(col), to_dollars(sum(col)), "Grand Total")), 
  table, query( 
    { transactions, map(tickers, quantity, position_) }, 
    "select Col1, sum(Col4), sum(Col5) 
     where Col1 is not null 
     group by Col1 
     label sum(Col4) 'Invested', sum(Col5) 'Current position' ", 
    1 
  ), 
  { table; bycol(table, totals_) } 
)

Another workaround would be to re-calculate the totals in the pivot table like this:

=let( 
  pivot, query( { G6:I13 }, "where Col1 is not null and Col1 <> 'Grand Total' ", 1), 
  totals_, lambda(col, if(sum(col), to_dollars(sum(col)), "Grand Total")), 
  { pivot; bycol(pivot, totals_) } 
)

See query(), let(), query(), bycol() and the sample spreadsheet.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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