I had used android app for tracking my Mutual Fund investments. I can also track the Funds in MyCams app.
Today I decided to build my own tracker using Libreoffice Spreadsheet.
AMFI updates NAVs of all Mutual Funds from India every day and one text file is available for previous day's NAVs.
I opened a Blank Sheet and clicked on Sheet/Link_to_external_Data_Source and entered the URL.and hit Enter.
The text file got loaded on the sheet.
Next I added another sheet and entered Scheme_Code, Fund Name, Date of purchase, NAV, Units allotted in the columns and added columns for current NAV and Date.
Then I used VLOOKUP function to get the values of currurent NAV and Date for the first entry in row 2 as follows:
For NAV =VLOOKUP(A2,$Sheet1.A$1:F$18034,5, )
For Date =VLOOKUP(A2,$Sheet1.A$1:F$18034,6, )
VLOOKUP function looks for the row having the value in A2 cell on Sheet1and picks up the value in column 5 for NAV and column 6 for Date,
Simple isn't it?
Sheet1 Data can be updated every day and Sheet2 containing the Data for my funds gets updated accordingly.
You may like to use the Date obtained through the above VLOOKUP function for further calculation by subtracting the Date of investment to get the total days of investment and the output of Date difference may produce error. To solve this do the following Settings:
Libreoffice Tools/Options/Libreoffice_Calc/Formula
Detailed Calculation Settings
Custom (conversion of text to numbers and more)
Details
Convert also locale dependent
Reference syntax for string reference Calc A1 | Excel A1
