Tracking a stock portfolio in excel (auto update)

Author: Oscar Cronquist Article last updated on August 11, 2022

Question:

I found a question here about tracking a stock portfolio. He would like to automatically create an overview table with a unique stock symbol per row. He also wants the range extended down to include new rows as they become valid.

Example,

Sheet1
Symbol     Type    Shares    Price
F          Buy     100       12
MSFT       Buy     100       25
MSFT       Buy     50        28
F          Buy     100       16
GOOG       Buy     25        550

Sheet2
Symbol               Quantity
F                    200
MSFT                 150
GOOG                 25

Answer:

Sheet1

Sheet2

Create named ranges

Select tab "Formulas" on the ribbon
Press with left mouse button on "New..." button
Type
Symbol

in Name: bar
Type formula:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))

in "Refers to:" bar
Press with left mouse button on Close button

Repeat above instructions with following names and formulas:

Type - =Sheet1!$B$2:INDEX(Sheet1!$B:$B, COUNTA(Sheet1!$B:$B))
Shares - =Sheet1!$C$2:INDEX(Sheet1!$C:$C, COUNTA(Sheet1!$C:$C))
Price - =Sheet1!$D$2:INDEX(Sheet1!$D:$D, COUNTA(Sheet1!$D:$D))

Array formulas

Array formula in cell A2, sheet 2:

=IFERROR(INDEX(Symbol, MATCH(0, COUNTIF($A$1:A1, Symbol), 0)), "") + CTRL + SHIFT + ENTER

Copy cell A2 and paste down as far as needed. This formula creates uniue distinct symbols. Read this post: How to extract a unique distinct list from a column for a formula explanation.

Array formula in cell B2, sheet 2:

=IF(A2<>"", SUMPRODUCT((A2=Symbol)*(Type="Buy")*Shares)-SUMPRODUCT((A2=Symbol)*(Type="Sell")*Shares), "") + Enter

Copy cell B2 and paste down as far as needed.

Get excel file

Tracking a stock portfolio.xlsx
(Excel 2007 -2010 Workbook *.xlsx)

Pivot table - Semi auto update

You can also convert the range in sheet1 to a table and then create a pivot table with data from table. The table includes new rows automatically. Unfortunately the pivot table does not. Press with right mouse button on anywhere on pivot table and press with left mouse button on Refresh when new rows are added.

The question is how to subtract sold shares from bought shares in a pivot table?

Stock portfolio category

Automate net asset value (NAV) calculation on your stock portfolio
Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]

Calculate your stock portfolio performance with Net Asset Value based on units in excel
In a previous related post we calculated the stock portfolio performance using the most current stock prices compared to buying […]

Compare the performance of your stock portfolio to S&P 500 using Excel
By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]

See all articles in 'stock portfolio' category

Functions in this article

COUNTA IFERROR INDEX MATCH COUNTIF IF SUMPRODUCT

More than 1300 Excel formulas

Excel categories

Home page

Latest updated articles.

Excel Functions

More than 300 Excel functions with detailed information including syntax, arguments, return values, and examples for most of the functions used in Excel formulas.

Excel Formulas

More than 1300 formulas organized in subcategories.

Excel Tables

Excel Tables simplifies your work with data, adding or removing data, filtering, totals, sorting, enhance readability using cell formatting, cell references, formulas, and more.

Advanced Filter

Allows you to filter data based on selected value , a given text, or other criteria. It also lets you filter existing data or move filtered values to a new location.

Data Validation

Lets you control what a user can type into a cell. It allows you to specifiy conditions and show a custom message if entered data is not valid.

Drop Down List

Lets the user work more efficiently by showing a list that the user can select a value from. This lets you control what is shown in the list and is faster than typing into a cell.

Named Ranges

Lets you name one or more cells, this makes it easier to find cells using the Name box, read and understand formulas containing names instead of cell references.

Excel Solver

The Excel Solver is a free add-in that uses objective cells, constraints based on formulas on a worksheet to perform what-if analysis and other decision problems like permutations and combinations.

Charts

An Excel feature that lets you visualize data in a graph.

Conditonal Formatting

Format cells or cell values based a condition or criteria, there a multiple built-in Conditional Formatting tools you can use or use a custom-made conditional formatting formula.

Pivot Tables

Lets you quickly summarize vast amounts of data in a very user-friendly way. This powerful Excel feature lets you then analyze, organize and categorize important data efficiently.

VBA

VBA stands for Visual Basic for Applications and is a computer programming language developed by Microsoft, it allows you to automate time-consuming tasks and create custom functions.

Macros

A program or subroutine built in VBA that anyone can create. Use the macro-recorder to quickly create your own VBA macros.

UDFs

UDF stands for User Defined Functions and is custom built functions anyone can create.

8 Responses to “Tracking a stock portfolio in excel (auto update)”

Neil says:

February 6, 2011 at 3:33 am

Thanks for making a more elaborate answer to the question I originally posted on SuperUser. Using INDEX and COUNTA to create an auto updating named range is something I had originally considered.

However, in the end I had to resort to writing a VSTO add-in to get all the behavior I wanted. I kept running into even more complex problems that I couldn't solve in excel natively.

Reply
- Chetan says:
  
  October 22, 2014 at 5:34 pm
  
  Hi Oscar,
  
  I have a similar question, but my problem size is 580,526 products. which i need to consolidate. I used the same formula mentioned above, the computational time is 5 hours and still running. Suggest something else.
  
  Regards,
  Chetan
Bhavik says:

July 25, 2011 at 2:52 pm

Thanks for the blog. nice 1. it's what I was looking for
Bhavik.

Reply
Oscar says:

July 26, 2011 at 7:54 am

Bhavik,

Thank you!

Reply
Duncan says:

August 8, 2012 at 11:45 pm

Just to say thank you for this article, it's exactly what i have been looking for.

Duncan

Reply
Automatically keeping two excel data tables in-sync (w/out VBA) says:

February 14, 2013 at 6:08 am

[...] https://www.get-digital-help.com/2011/01/31/tracking-a-stock-portfolio-in-excel-auto-update/ [...]

Reply
Chetan says:

October 22, 2014 at 5:35 pm

Hi Oscar,
I have a similar question, but my problem size is 580,526 products. which i need to consolidate. I used the same formula mentioned above, the computational time is 5 hours and still running. Suggest something else.

Regards,
Chetan

Reply
Excel udf: Import historical stock prices from yahoo – added features says:

August 6, 2017 at 9:55 am

[…] Tracking a stock portfolio in excel (auto update) […]

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.

Contact Oscar

You can contact me through this contact form