## 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
- Click "New..." button
- Type
Symbol
in Name: bar

- Type formula:
=Sheet1!$A$2:INDEX(Sheet1!$A:$A, COUNTA(Sheet1!$A:$A))
in "Refers to:" bar

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

**Download 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. Right click anywhere on pivot table and click Refresh when new rows are added.

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

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

[...] in Excel, Finance on Feb.08, 2011. Email This article to a Friend This is follow up post to: Tracking a stock portfolio in excel (auto update) In this post we are going to calculate cost basis and returns. The calculations are simplified, [...]

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

[...] http://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