## Tracking a stock portfolio in excel (auto update)

**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:**

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:**

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

Introduction In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am […]

In a previous related post we calculated the stock portfolio performance using the most current stock prices compared to buying […]

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

### Functions in this article

More than 1300 Excel formulas

## Excel categories

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

### 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.

**Contact Oscar**

You can contact me through this contact form

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.

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

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

Bhavik.

Bhavik,

Thank you!

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

Duncan

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

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

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