Did you know that you can use a pivot table to summarize portfolio holdings at any point in time? If you trade securities or work as an accountant this blog post is for you.

I am going to demonstrate how to sum portfolio holdings, here is a picture of  the transactions table. The table contains "made-up" fake data.

sum security trades

I have added two columns to this table, Year and Month. This make it possible to filter the pivot table by month and year.

Formula in cell J4:

=YEAR([@Date])

Formula in cell K4:

=Month([@Date])

Insert a pivot table

  1. Click on any cell in the table
  2. Go to tab "Insert"
  3. Click "Pivot table" button
  4. Click OK

Set up pivot table

Select any cell in the pivot table.

Report filter

  1. Click and hold Year field
  2. Drag to Report filter area
  3. Repeat above steps with Month
    sum security trades - pivot table1

Row labels

  1. Drag Date, Transaction and Item to Row labels area
    sum security trades - pivot table2
  2. Right click on dates
  3. Click Group
    sum security trades - pivot table3
  4. Select months
  5. Click OK

Values

  1. Drag Number to Values area
  2. Click Number and click on Value field settings
  3. Select Sum
    sum security trades - pivot table4
  4. Click OK
  5. Drag Amount to Values area
    sum security trades - pivot table5

Remove Deposit from Transactions

  1. Click Buy or Sell in the first column
  2. Click arrow next to Row Labels
    sum security trades - pivot table6
  3. Deselect Deposit
  4. Click OK

The result

Now you can examine all transactions month by month.

sum security trades - pivot table7

How to sum holdings yearly

  1. Right click on a month
  2. Click Ungroup...
  3. Right click on a date
  4. Click Group..
  5. Select Years
  6. Click OK

Download excel *.xlsx file

Sum security holdings monthly and yearly in a pivot table.xlsm