Manage Mutual Fund Portfolio using Google Sheets

Do you hold a mutual fund portfolio, want to understand a snapshot in a easy to maintain way?  If so, please read on.

1. Create a spreadsheet on Google Spreadsheets.  Name the spreadsheet as you need.
2. Let us create a sheet and name the sheet as "NAV".  This sheet will hold all the latest NAV and will be updated with latest price automatically.  Let us visit this sheet later for automating the price update.
3. Now, let us create a new sheet, call it "Portfolio".  
4. 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
5. Let us shift the tab to "NAV".  In the column A1 enter the formula as
• =IMPORTDATA("https://www.amfiindia.com/spages/NAVAll.txt")
6. This will update the NAV automatically and we will use this data to update information in our portfolio.  This will have following columns now:
• Scheme Code
• ISIN Div Payout / ISIN Growth
• ISIN Div Reinvestment
• Scheme Name
• Net Asset Value
• Date
7. 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.
8. Get your statement from the AMC and update the Unit Balance, Cost - Dividends columns.
9. In the "Portfolio" sheet enter the formula against NAV
• =INDEX(SPLIT(VLOOKUP(A2&"*",NAV!$A$1:$A$20501,1,false),";"),,5)
10. In the "Portfolio" sheet enter the formula against NAV Date
• =INDEX(SPLIT(VLOOKUP(A2&"*",NAV!$A$1:$A$20501,1,false),";"),,6)
11. Value column is the product of Units x NAV
12. Returns is Value-(Cost-dividends)
13. Return % is Returns / (Cost-dividends)
14. AV Cost / Unit is (Cost-dividends) / Unit Balance
15. 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.

Calendar Finance

Do you use Calendar for time management? Do you also track your finance?  Have you thought about managing finance using Calendar!!  Yes, this article helps you do manage your finance using Google Calendar.

Collect Data 

Get credentials to login your bank account.  Most of the banks provide ability to download CSV or Excel format. Some banks provide facility to download OFX, if so, convert them into CSV.

Prepare the Collected Data

Prepare the CSV with the following columns:
1. Subject: Concatenate all the details except Date in this field
2. Start Date: Date of the transaction
3. End Date: Date of the transaction
4. All Day Event: True
5. Private: True

Save the CSV file.

Google Calendar Settings

In order to perform settings, select a calendar (create a new calendar, this is suggested) using the settings.  Next click on the Import/Export -> Import.  In this process Navigate to the file created in the previous step and select the calendar.  Finally click on the "Import" button.

Review the Data

Review the calendar by the date, all the transactions pertain to the date are displayed on the top of the day.