Author: Oscar Cronquist Article last updated on November 15, 2019

sum security trades

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 dummy data.

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

Formula in cell J4:

=YEAR([@Date])

Formula in cell K4:

=Month([@Date])

Create an Excel Table

I highly recommend that you create an Excel Table, it makes life easier for you if you need to add more data to your table. This saves you time because you don't need to update the cell references, only perform a refresh the Pivot Table.

  1.  Select all cells you want to convert to an Excel Table.
  2. Press CTRL + T to create an Excel Table, a dialog box is displayed.
    Image: Excel Table - dialog box
  3. Enable the checkbox if the Table has headers.
  4. Press with left mouse button on OK button.

Excel applies cell formatting to indicate that it is now an Excel Table, you can change the Table style if you want something more subtle.

Insert a Pivot Table

A Pivot Table allows you to quickly create totals based on conditions like items, dates, months, years and so on. It is one of the greatest features in Excel, in my opinion.

  1. Press with left mouse button on any cell in the Excel Table.
  2. Go to tab "Insert" on the ribbon.
  3. Press with left mouse button on "Pivot Table" button.
    How to calculate totals of stock transactions based on dates Pivot Table
  4. The dialog box lets you choose the data source but the default Table is based on the cell you selected in step 1.
    You also have the option to decide where you want the Pivot Table located, a new worksheet or an existing worksheet and the location on an existing worksheet.
  5. Press with left mouse button on OK button to create the Pivot Table.

How to calculate totals of stock transactions based on dates Pivot Table1

This image above shows the empty Pivot Table that we now need to configure.

How to set up the Pivot Table

Select any cell in the Pivot Table if you don't see the task pane on the right, see image above. This will open the task pane which is necessary in order to manipulate the Pivot Table.

Report filter

The report filter is going to allow you to use different date ranges like years and months which is very handy in this scenario where we want to consolidate data based on stock transactions.

  1. Press and hold with left mouse button on "Year" field.
  2. Drag to Report filter area.
  3. Repeat steps with "Month" field.
    sum security trades - pivot table1

The Pivot Table changes to this.

How to calculate totals of stock transactions based on dates Pivot Table report filter

The Drop Down lists lets you quickly choose year and months to be included in the Pivot Table.

Row labels

The fields you drag to the row labels area will show up vertically in the Pivot Table.

  1. Drag Date, Transaction and Item fields to Row labels area.
    sum security trades - pivot table2
  2. Press with right mouse button on on a date in the Pivot Table, see image below.
    How to calculate totals of stock transactions based on dates group row labels
  3. Press with left mouse button on "Group..." on the menu.
    sum security trades - pivot table3
  4. Select "Months".
  5. Press with left mouse button on OK button to apply settings.

Values area

  1. Drag Number to the Values area.
  2. Press with left mouse button on Number and press with left mouse button on Value field settings.
    How to calculate totals of stock transactions based on dates value field settings
  3. Select Sum, see image below.
    sum security trades - pivot table4
  4. Press with left mouse button on OK button.
  5. Drag Amount to Values area.
    sum security trades - pivot table5

Remove Deposit from Transactions

  1. Press with left mouse button on Buy or Sell in the first column
  2. Press with left mouse button on the arrow next to Row Labels to display a menu.
    sum security trades - pivot table6
  3. Deselect the ceckbox next to Deposit.
  4. Press with left mouse button on OK button.

The result

Now you can examine all transactions summed month by month or if you prefer yearly.

sum security trades - pivot table7

How to sum holdings yearly

  1. Press with right mouse button on on a month.
  2. Press with left mouse button on "Ungroup..."
  3. Press with right mouse button on on a date.
  4. Press with left mouse button on Group..
  5. Select Years.
  6. Press with left mouse button on OK button.

How to refresh a Pivot Table

How to calculate totals of stock transactions based on dates refresh

You need to refresh the Pivot Table if you add more values to the Excel Table later on. This is easy to forget.

Get the Excel file


Sum-security-holdings-monthly-and-yearly-in-a-pivot-table.xlsx

Related articles

test
Remove blank cells
In this blog post I will provide two solutions on how to remove blank cells and a solution on how […]