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 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".
https://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
Get excel spreadsheet: Track your stock portfolio performance.xls Remember enabling macros.
Press with left mouse button on "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:
Compare your stock portfolio with S&P500 in excel
Stock portfolio category
In a previous related post we calculated the stock portfolio performance using the most current stock prices compared to buying […]
Question: I found a question here about tracking a stock portfolio. He would like to automatically create an overview table […]
By comparing your stock portfolio performance to index S&P500 you know if the time you spent on analyzing companies paid […]
Excel categories
31 Responses to “Automate net asset value (NAV) calculation on your stock portfolio”
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.
How are you tracking option buys and sells, also how are you dealing with short positions?
Matt,
This excel sheet gets price quotes from yahoo and it tracks 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 units Transfer data We 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 https://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 https://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: https://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.00
The closing price for "CAT" 1/2/2009 was 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.
[…] Automate net asset value (NAV) calculation on your stock portfolio (vba) in excel […]
Hi Oscar,
I just opened your file and opened it in Excel 2016 and got this error as soon as I hit button Update:
Run Time error.
Unable to open:
https://table.finance.yahoo.com/table.csv..
Cannot locate the Internet Server
Then, I pressed with left mouse button on debug and it gave me this error in Yellow:
.Refresh BackgroundQuery:=False
Thanks for your Help!
Stephane
Stephane
Yahoo made significant changes to their US website in mid May 2017, you need a workaround to transfer quotes data.
Read here: https://www.xlautomation.com.au/free-spreadsheets/yahoo-historical-price-extract
Thank you Oscar. However I tried to integrate with your actual VBA without success. I am not a developer, so it is possible to send an updated Excel version with this new connection integrated?
Thanks in advance
Regards
Stephane
For some weird reason the NAV sheets gets all the number of shares 0 from row 22? I've spent hours searching, can't find an error?