Starting from a table like:

Stock   Action  Shares
AAPL    BUY 100
AAPL    BUY 200
MSFT    BUY 100
MSFT    BUY 200
MSFT    SELL    -100


Stock   Price
AAPL    105.5
MSFT    221.2

I'd like to get a table like:

Stock   Shares  Price   Value
AAPL    300 105.5    $31,650 
MSFT    200 221.2    $44,240 

Just a basic portfolio value report.

something like this

Pivot table is great at aggregating the shares, but I can't find a way to add the value calculation. I set up a data model and linked symbol on the two tables, then, if I start with the price table I can put the total shares next to it but when I put the price in, I can only choose sum of price ->

Looks like this

It feels like I'm close.


ok, I got a solution, its maybe way harder than it needs to be but here it goes.

  • Bring the tables into power pivot and create the connection on symbol
  • In powerpivot
    • Add a 'CurrShares' column to the price table, set the formula to =[Sum of Shares]
    • Add another new column, set formula to =[Price]*[CurrShares]
  • Back in the spreadsheet go to data->existing connections, select Tables tab, pick the table and import as a table

Ends up like this

thanks for help from this question

|improve this answer|||||

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.