Styjun

Looking to manage the mutual fund portfolio, and automatically update the price so the portfolio always shows the correct information.

I have tried to use Google sheet functions like

INDEX
SPLIT
VLOOKUP
IMPORTDATA

=IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt")
=INDEX(SPLIT(VLOOKUP(A2&"*",NAV!$A$1:$A$20501,1,false),";"),,5)

The outcome of this is to create a portfolio with an automated price update.

Create a spreadsheet on Google Spreadsheets. Name the spreadsheet as you need.

Let us create a sheet and name the sheet as "NAV". This sheet will hold all the latest NAV and will be updated with the latest price automatically. Let us visit this sheet later for automating the price update.

Now, let us create a new sheet, call it "Portfolio".

Create columns in the 1st row as follows:

AMFI Scheme ID
Account Number
AMC
Scheme Name 
Unit Balance 
Cost - Dividends 
Value 
Returns 
Return (%) 
AV Cost / Unit 
NAV 
NAV Date

Let us shift the tab to "NAV". In the column, A1 enter the formula as

=IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt")

This will update the NAV automatically and we will use this data to update information in our portfolio. This will have the following columns now:

Scheme Code
ISIN Div Payout / ISIN Growth
ISIN Div Reinvestment
Scheme Name
Net Asset Value
Date

Now find the scheme you have invested in from the NAV tab and get the Scheme Code and copy to AMFI Scheme ID column on "Portfolio" sheet. Similarly, you can copy the Scheme Name.

Get your statement from the AMC and update the Unit Balance, Cost - Dividends columns.

In the "Portfolio" sheet enter the formula against NAV

=INDEX(SPLIT(VLOOKUP(A2&"*",NAV!$A$1:$A$20501,1,false),";"),,5)

In the "Portfolio" sheet enter the formula against NAV Date

=INDEX(SPLIT(VLOOKUP(A2&"*",NAV!$A$1:$A$20501,1,false),";"),,6)

• Value column is the product of Units x NAV




• Returns are Value-(Cost-dividends)




• Return % is Returns / (Cost-dividends)




• AV Cost / Unit is (Cost-dividends) / Unit Balance

Now create a summary line in the bottom for Cost-Dividends, Value, Returns and Return %

Voila! Your portfolio will be updated automatically when you open the spreadsheet. Just ensure you update unit balance and cost-dividends for accurate computations.