## Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel

### Introduction

In this post I am creating a spreadsheet that will calculate stock portfolio performance. To do this I am calculating net asset value (NAV).

Net asset value is the value of each stock and the account balance summed, calculated each day as the stock prices fluctuate.

When a deposition or withdrawal is made you add or subtract NAV units to your stock portfolio. More about deposits, withdrawals and NAV units in an upcoming post.

All other transactions affects your stock portfolio performance and is added or subtracted from your account balance.

- Stock prices
- Dividends
- Commissions
- Interest payed or earned

In this excel tutorial I will only buy or sell stocks and calculate each stock's market value (column J) each day. Dates are in column A.

### Transactions sheet

Here is the "Transactions" sheet. It contains some random stock data. Here I calculate stock portfolio market value each day.

Formula in H7:

Formula in I7:

### NAV calculation sheet

In this sheet I calculate stock portfolio market value for each day. Excel queries yahoo and copies close price for each stock into E7 and down. Market value is calculated in F7 and down and the total is calculated in C4. The total is then copied into sheet "Transactions".

Formula in F3:

Formula in C3:

Formula in C4:

Formula in B7:

Copy cell and paste down as far as needed.

Formula in C7:

Copy cell and paste down as far as needed.

Formula in D7:

Copy cell and paste down as far as needed.

Formula in F7:

Copy cell and paste down as far as needed.

### Web Query sheet

I created a web query in cell A5 on sheet "Web query".

http://table.finance.yahoo.com/table.csv?a=["m","m"]&b=["d","d"]&c=["y","y"]&d=["m","m"]&e=["d","d"]&f=["y","y"]&s=["ticker", "ticker"]&y=0&g=d&ignore=.csv

*Parameters*

y = cell P2

m = cell P3

d = cell P4

ticker = cell P5

Formula in P1:

Formula in P2:

Formula in P3:

Formula in P4:

### VBA code:

Sub Calc_nav() Application.ScreenUpdating = False Application.DisplayAlerts = False Dim rng As Range Dim price As Range Dim dest As Range Dim mnt As Range Dim i As Integer Dim qryTblStocks As QueryTable 'Iterate rows i = Worksheets("Nav calc").Range("F2") Set mnt = Worksheets("Transactions").Range("J7") Do While i <= Worksheets("Nav calc").Range("F3") Worksheets("NAV calc").Range("C2").Value = i 'Iterate stock symbols Set rng = Worksheets("NAV calc").Range("C7") Set price = Worksheets("NAV calc").Range("E7") Set dest = Worksheets("Web query").Range("P5") Do While rng <> "" dest.Value = rng.Value 'Refresh web query Set qryTblStocks = ThisWorkbook.Worksheets("Web query").QueryTables(1) With qryTblStocks .Refresh BackgroundQuery:=False End With 'Text to columns Sheets("Web query").Select Range("A5:A6").Select Selection.Texttocolumns Destination:=Range("B5"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _ Semicolon:=False, Comma:=True, Space:=False, Other:=False, FieldInfo _ :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), _ Array(6, 1), Array(7, 1)), TrailingMinusNumbers:=True 'Copy close value Web query F6 to NAV calc E7 price.Value = Worksheets("Web query").Range("F6") Sheets("Web query").Select Range("A5:A6").Select Selection.ClearContents 'Iterate to next cells Set rng = rng.Offset(1, 0) Set price = price.Offset(1, 0) Loop mnt.Value = Worksheets("NAV calc").Range("C4") Application.ScreenUpdating = True Sheets("Transactions").Select Application.ScreenUpdating = False Set mnt = mnt.Offset(1, 0) i = i + 1 Loop End Sub

### How to use this spreadsheet

Download excel spreadsheet: Track your stock portfolio performance.xls Remember enabling macros.

Click "update" button on transactions sheet. Watch market values being calculated in column J.

Here is a picture of transactions sheet when NAV have been calculated.

### Final notes

Make sure Transactions sheet is sorted by date.

### Future improvements

Instead of making multiple web queries to yahoo, a better strategy would be to identify the maximum date range and then do one query for each stock in portfolio. This would also speed things up considerably.

### Recommended reading:

### 26 Responses to “Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel”

### Leave a Reply

**How to add a formula to your comment:**

<code>your formula</code>

**How to add VBA code to your comment:**

[vb 1="vbnet" language=","]

VBA code

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org

Add picture link to comment.

**Contact Oscar**

You can contact me through this webpage

How are you tracking option buys and sells, also how are you dealing with short positions?

Matt,

This excel sheet downloads price quotes from yahoo and it doesn´t track neither shorts nor options.

Can the sheet be used for non US stock?

Buzz,

Yes, if the stock has a ticker symbol in yahoo.

[...] stock portfolio performance. I am going to use Net Asset Value based on units in this post.Automate net asset value (NAV) calculation on your stock portfolio (vba)Calculate your stock portfolio performance with Net Asset Value based on unitsDownload dataWe need [...]

Very helpful sheet but I have 2 issues I have been having trouble with. Hopefully you can lend some expertise, which would be greatly appreciated.

1 - My trades have either 1 or 2 exits, so trades can take 1 or 2 lines. I was able to write

Exit 1 - =IF(D9>0,(IF((C9="L"),(G9-E9),IF((C9="SS"),(E9-G9)))),(IF((C9="L"),(G10-E9),IF((C9="SS"),(E9-G10)))))

Exit 2 - =(IF((C10="L"),(G11-E10),IF((C10="SS"),(E10-G11))))

Where C=L/SS (long or short), D=Quantity, E=Entry Price, F=Exit Date, G=Exit Price.

I need to calculate the amount invested every day. There will be new positions, partial exits, full exits to be calculated, based on the entry date (Column A) and exit dates (Column F) while the amount invested in each trade will be based on entry size (Column D) minus exited size (Column H) and entry price (Column E) plus or minus the previous amount invested.

2 - Is there an easier way to write the formula calculating Exit 1 and Exit 2 so that I don't have to manually see if the trade had 1 or 2 exits and paste the correct formula, but allow it to determine if its a 1 or 2 line trade and use the correct part of the formula? Thanks.

Hi Oscar,

You have a great excel sheet here. I have been working with it for few days to make it work with Indian stocks (I also experimented using Google API as they seemed more reliable and simple for Indian stocks).

I've been hitting roadblocks which I have been able to surpass, but I have a doubt now which I am unable to get the answer of.

This is in reference to the column NAV (K in the excel sheet shown above):

If I understand correctly, you are calculating NAV and NAV units progressively as the person had purchased/sold stocks.

So the "NAV" and "NAV Shares" is corresponding to the point of time when the transaction was made.

And the column "Total Stock Value" is corresponding to its current value.

However, I see that "NAV" formula has a reference to the "total stock value" column too while determining the NAV. That doesn't seem right to me. It means that NAV and NAV shares will be different for a transaction each time I run the "Update".

Can you please clarify?

Thanks,

Mukesh

Mukesh,

However, I see that "NAV" formula has a reference to the "total stock value" column too while determining the NAV.Yes, Net asset value is the value of each stock and the account balance summed, calculated each day as the stock prices fluctuate.

That doesn't seem right to me. It means that NAV and NAV shares will be different for a transaction each time I run the "Update".That is not happening here. The values are the same each time I press the "Update" button.

Thanks Oscar. Yes, NAV will be different each day for each stock. I was misinterpreting earlier.

However "NAV shares" shouldn't be different each day. Right?

For some reason the 'update' is not working in the excel sheet right now here, but this is the formula in the cell#M24

=M7-(F8="Withdrawal")*(-H8/((I8+J8-H8)/M7))+(F8="Deposit")*(H8/((I8+J8-H8)/M7))

You see this has a reference to J8, and that would mean that "NAV shares" would be different each day. Isn't it?

PS: I was actually using your other sheet in http://www.get-digital-help.com/2010/03/31/calculate-your-stock-portfolio-performance-with-net-asset-value-based-on-units-in-excel/

Somehow posted here as the excel snapshot looked same.

Sorry, the cell I was referring to was M8

You would see that here the NAV units would not change on refresh as the rows are not for "Deposit" or "Withdrawal". However the cell M24 has Deposit, and I think that would come out different each day.

Mukesh,

However "NAV shares" shouldn't be different each day. Right?From http://www.fool.com/foolfaq/foolfaq0056.htm:

When you deposit or withdraw cash, you treat it as though you are buying or selling the NAV units -- that is, you add or subtract the appropriate number of units.

=M7-(F8="Withdrawal")*(-H8/((I8+J8-H8)/M7))+(F8="Deposit")*(H8/((I8+J8-H8)/M7))You see this has a reference to J8, and that would mean that "NAV shares" would be different each day. Isn't it?

It would change if it is a withdrawal or a Deposit that specific day.

Hi Oscar,

Thanks for the reply. Sorry for writing in late.

I am sorry but I am still not convinced. I feel that with this method value in the "NAV shares" column for a row corresponding to 21 june 2009 (or any other old date) would be different on 13 Mar 2013 and 14 Mar 2013, which I think is not desirable (even after reading the article that you gave reference of).

Is it possible that we connect up on chat somewhere to clarify this? Please let me know. My email id mghatiya AT gmail

Thanks,

Mukesh

Mukesh,

I am sorry but I am still not convinced. I feel that with this method value in the "NAV shares" column for a row corresponding to 21 june 2009 (or any other old date) would be different on 13 Mar 2013 and 14 Mar 2013, which I think is not desirable (even after reading the article that you gave reference of).I don´t have those dates in my workbook or what workbook are you refering to?

Those are some dates I just cooked up.

Mukesh

Ok, I don´t know how to describe this in greater detail. Read the article again and contact the author: http://www.fool.com/foolfaq/foolfaq0056.htm

Sure. I was just trying to say that market price of today should not affect the NAV that I computed in past (for transaction that I did then).

Anyway, sorry that I have been unable to communicate it well.

I'll probably read up more.

Thanks for your time to look into it.

Sorry, I meant to write "I have NOT been able to communicate it well" :)

Hi,

In your sheet, there is only one "Deposit" in the beginning and none after that. Hence the "0" row in your "NAV Calc" sheet comes in last. However, I have deposit in between too. Because of that the "0" row is coming in between.

In your VBA code you have condition for

Now because of this the loop stops as soon as the "0 row" is hit.

To solve this, I thought I would name that row as "blank" and say loop can continue if the "blank" is hit. However, I just figured that there is no "continue" in VBA. I tried GoTo, but it is not working for me. I have no background of VBA, so unable to see what can be done. Can you guide a bit there?

Cheers,

John

John,

Upload an example file

Hi Oscar,

I like this Spreadsheet and I'm trying to use it to track with IRA account which consist of both Mutual Funds and Stocks.

I haven't been able to get it to work to update Mutual Funds off Yahoo.

Is there something special that needs to be done to allow for the combination of Mutual Funs and Stocks?

Thanks

Anil R.

Here is an example using both stocks and a mutual fund.

Track-your-stock-portfolio-performance-mutual-funds.xls

Hi Oscar,

I'm trying to figure out how you calculated the "Total stock Value" in column "J".

It seems like it is based on the end-of-day Stock Price; but I played around with the end-of-day Stock Price for "CAT" of $56.99 on 2/1/2009 and cannot come up to your number, $18,764.00

Can you help me understand your calculations in Column "J"?

Thanks.

Anil R.

It seems like it is based on the end-of-day Stock Price; but I played around with the end-of-day Stock Price for "CAT" of $56.99 on 2/1/2009 and cannot come up to your number, $18,764.00The closing price for "CAT"

1/2/2009was 46.91.I think you got the date wrong.

Sorry about that Oscar. You are right. I got the date wrong. "My bad"

Cheers!

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

Hi Oscar,

I was looking through the file, and I was wondering where the file was calling the data form yahoo? I looked in the macros but couldn't find a web query.