How to calculate totals of stock transactions based on dates
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:
Formula in cell K4:
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.
- Select all cells you want to convert to an Excel Table.
- Press CTRL + T to create an Excel Table, a dialog box is displayed.
- Enable the checkbox if the Table has headers.
- 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.
- Press with left mouse button on any cell in the Excel Table.
- Go to tab "Insert" on the ribbon.
- Press with left mouse button on "Pivot Table" button.
- 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. - Press with left mouse button on OK button to create the Pivot Table.
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.
- Press and hold with left mouse button on "Year" field.
- Drag to Report filter area.
- Repeat steps with "Month" field.
The Pivot Table changes to this.
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.
- Drag Date, Transaction and Item fields to Row labels area.
- Press with right mouse button on on a date in the Pivot Table, see image below.
- Press with left mouse button on "Group..." on the menu.
- Select "Months".
- Press with left mouse button on OK button to apply settings.
Values area
- Drag Number to the Values area.
- Press with left mouse button on Number and press with left mouse button on Value field settings.
- Select Sum, see image below.
- Press with left mouse button on OK button.
- Drag Amount to Values area.
Remove Deposit from Transactions
- Press with left mouse button on Buy or Sell in the first column
- Press with left mouse button on the arrow next to Row Labels to display a menu.
- Deselect the ceckbox next to Deposit.
- 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.
How to sum holdings yearly
- Press with right mouse button on on a month.
- Press with left mouse button on "Ungroup..."
- Press with right mouse button on on a date.
- Press with left mouse button on Group..
- Select Years.
- Press with left mouse button on OK button.
How to refresh a Pivot Table
You need to refresh the Pivot Table if you add more values to the Excel Table later on. This is easy to forget.
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]
Below is an excel table containing recurring expenses and corresponding amounts, dates and recurring intervals. An excel table allows you to […]
In a previous related post we calculated the stock portfolio performance using the most current stock prices compared to buying […]
Brad asks: I'm trying to use your formulas to create my own bill reminder sheet. I envision a workbook where […]
This article demonstrates how to display buy and sell signals on an Excel chart based on two moving averages, the […]
This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to […]
By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]
The image above shows an Excel chart of the S&P 500 with buy and sell signals based on a 50 […]
This blog article explains in greater detail how to determine stock portfolio performance based on units of NAV (Net Asset […]
In my previous post, I described how to build a dynamic stock chart that lets you easily adjust the date […]
If you study a stock chart you will discover that sometimes significant trend reversals happen when a stock chart […]
This time I want to demonstrate an alternative way to identify a major trend in the stock market. The previous post […]
In a previous post: How to create a dynamic pivot table and refresh automatically I demonstrated how to refresh a pivot […]
Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]
In this article, I am going to show you how to quickly change Pivot Table data source using a drop-down […]
This article shows you how to refresh a pivot table automatically using a small VBA macro. If you add or delete […]
To be able to use a Pivot Table the source data you have must be arranged in way that a […]
Excel 2013 allows you to count unique distinct values in a pivot table, this article explains how to use a […]
This article demonstrates how to build a calendar in Excel. The calendar is created as a Pivot Table which makes […]
ExcelBeginner asks: I have a small problem that I am not sure on how to solve. I now have a […]
I read this interesting article Quick Trick: Resizing column widths in pivot tables on the Microsoft Excel blog. It is […]
A pivot table allows you to examine data more efficiently, it can summarize large amounts of data very quickly and is very easy to use.
Rodney Schmidt asks: I am a convenience store owner that is looking to make a spreadsheet formula. I want this […]
Table of Contents Introduction to pivot tables Create pivot table Group data Analyze data (pivot table) Compare performance, year to […]
Today I am going to demonstrate how amazing pivot tables are! Take a look at this time sheet. You can […]
This article demonstrates a macro that allows you to rearrange and distribute concatenated values across multiple rows in order to […]
Anura asks: I have a list of credit card transactions showing the name of the cardholder, their Branch and the […]
4 Responses to “How to calculate totals of stock transactions based on dates”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
=YEAR([@Date]) looks like SQL, could you please explain what it does, and how it does it?
Cyril,
It is a cell reference inside the table. [@Date] returns the value from the Date column and same row.
Yes, a year(a1) would be translated into a year([header]) in a table, just wondering if this could be used beyond tables... Hence if the use of @ is limited to tables within excel.
Yes, a year(a1) would be translated into a year([header]) in a table
year([header]) is a reference to the entire (table) column: [header].
just wondering if this could be used beyond tables... Hence if the use of @ is limited to tables within excel.
Yes, @ is limited to tables, as far as I know.