Google AppScript Mutual Fund NAV parser

Google Apps Script is a scripting platform for applications in the G Suite environment of applications. It is remarkably powerful as it has the capability to extend the functionality of ALL Google Apps applications, or all applications in the GSuite. Being built on JavaScript, the syntax and functionality is remarkably similar to that of JavaScript. Any regular JavaScript developer with a flair for utilising the Google Apps APIs will find it very easy to do a great deal more than what Google Apps provides to the end user by default.

A simple example would be to extend the functionality of Google sheets. Sheets as an application already provides developers with APIs that allows a developer to consume them. However, using Apps Script, creating additional functionality along with menus, their icons and dialog windows can be done – pretty easily. A similar analogy can be the VBSript of Microsoft Office Applications that was used to extend the Office apps.

For more open source applications check this link

This is a simple Apps Script parser of the AMFI Mutual Fund NAVs. It will give you a clear picture how you can quickly read from a file, process the information and then display the same in Google Sheets that users can then see and act upon.

Why create a Mutual Fund NAV parser anyway?

There are several reasons. The biggest among these is the fact that it is a cool thing to do and that’s it.

However, there is another point to it.

While it is true that tracking a Mutual Fund NAV is a single Google click away, tracking their relative performance for a day or a week is nearly impossible. Unless you take the help of some of the specialised solutions that are there in the market. Some of these are free. Most don’t offer the entire list of Mutual Funds that you can invest in (in the Indian Markets) in a single report/page.

Take MoneyControl’s page for instance. It does provide information, but does it really help? Especially if you want to check ALL the schemes of a particular AMC and compare that with ALL the schemes of two other?

There are Excel based solutions that take the help of VBScript to fetch the data from the Internet, and provide the information to you for analysis. But there is an issue with this. One, it is dependent on systems that allow native installations of Microsoft Office. VBScript does not really work well on Linux systems. I am yet to try these files by running on Wine. But in any case there are two crucial problems.

1. You can’t expect the average non Windows user with Financial domain knowledge or a significant interest or portfolio to also have sufficient time and interest to install Wine and then install Microsoft Office on top of it in a Linux environment.
2. There is no guarantee that it will work. Worse, these files are often available only post payment.

The greatest advantage of AppScript

The biggest advantage is that this is entirely based on the cloud. That removes any platform dependency at the user level. The user can work on Android, MacOS, Windows, etc. As long as you have access to Google Spreadsheets you are good to go.

Of course, the code is flexible and open source, so that allows developers to further improve upon this base.

High Level Approach

Well, the approach is deceptively simple.

AMFI is the nodal association of all Mutual Funds in India. They also publish the NAVs of all the funds registered in a text file that they publish online. This file is updated on a daily basis.

I have simply read the file based on the input date and the up to date NAV for all funds are printed in the Google sheet.

Here is how the code works

First open your Google Sheets document, name it anyway you like. Now go to Tools – Script Editor. This is where the code in AppScript will go.

My default function myFunction() basically drives it all.

My objective is to read this file – https://www.amfiindia.com/spages/NAVOpen.txt?t=22112019

The date part (t) is variable and accept ddmmyyyy as input.

In the beginning I have defined all the constants that can exist in this project. Some of them may not be needed right now, so feel free to experiment with them. But the important ones are here.

Step 1: Set the headers information for easily assigning values to array

/*

setting the headers information for easily assigning values to array

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

  var _SCHEMECODE   = 0;
  var _ISIN         = 1;
  var _REINVESTMENT = 2;
  var _SCHEMENAME   = 3;
  var _NAV          = 4;
  var _DATE         = 5;

Step 2: Now set the row and column index where you can going to start printing the cells

The separator is not being used as of now. Except in the logger, ie the Console equivalent for AppScript.

  var startRow  = 2;
  var startCol  = 2;
  var separator = ' | ';

Get the current spreadsheet and assign it to a variable. This is what we will refer to when we process the output. Also, create a url variable where the user can input the date in the required format.

  var sheet = SpreadsheetApp.getActiveSheet();
  var url = 'https://www.amfiindia.com/spages/NAVOpen.txt?t=';
  url += sheet.getRange(1,1).getValue();
  var html = UrlFetchApp.fetch(url).getContentText();

The first cell value is the user input. The value is appended to the url and we fetch from the dynamically generated url.

Now set some default iteration variables so that we can substring the entire text from start to finish.

  var htmlLength = html.length;
  var rowStart   = 0;
  var start      = 0;
  var counter    = 0;
  var rowEnd     = html.length;

  var _arr       = {};

  var remainingText = html.slice(start, html.length);

Step 3: Get the Sheet header coordinates and set them. I have deliberately kept them dynamic. If you want to print only the values, you can remove this part of the code.

  sheet.getRange(rowStart+1, 2).setValue(url);

  sheet.getRange(startRow + counter,startCol + _SCHEMECODE).setValue("SCHEME CODE");

  sheet.getRange(startRow + counter,startCol + _ISIN).setValue("ISIN");

  sheet.getRange(startRow + counter,startCol + _REINVESTMENT).setValue("REINVESTMENT");

  sheet.getRange(startRow + counter,startCol + _SCHEMENAME).setValue("SCHEMENAME");

  sheet.getRange(startRow + counter,startCol + _NAV).setValue("NAV");

  sheet.getRange(startRow + counter,startCol + _DATE).setValue("DATE");

If you open the file and check the line with the least number of possible characters, you will find it to be around 120 characters. You can capture this in a variable by iterating the text once. But I have avoided the same because that adds another almost unnecessary iteration – considering the fact that it already takes up about a couple of minutes to fetch the data.

Start a loop covering the entire text till the last text segment is at least 120 characters in length if you check the document outputed with url = ‘url’, the minimum segment length is at least 120+ therefore, if there is any existing line with a length less than 120, it means we have covered all the relevant rows.

Step 4: Set the start and end search strings in regex. this pair gives us each row the advantage in using regex is largely to reduce the amount of code required to set the acceptable range of rows. You can take the help of this website for making this easier. https://regex101.com

while(remainingText.length > 120){

 
    rowStart = remainingText.search('[0-9]{6};');
    rowEnd   = remainingText.search('2019|2018|2017|2016|2015|2014|2013|2012|2011|2010|2009')+4;
    var _row = remainingText.substring(rowStart, rowEnd);
    // split it up
    _arr = _row.split(";");

     sheet.getRange(1 + startRow + counter,startCol + _SCHEMECODE).setValue(_arr[_SCHEMECODE]);

    sheet.getRange(1 + startRow + counter,startCol + _ISIN).setValue(_arr[_ISIN]);

    sheet.getRange(1 + startRow + counter,startCol + _REINVESTMENT).setValue(_arr[_REINVESTMENT]);

    sheet.getRange(1 + startRow + counter,startCol + _SCHEMENAME).setValue(_arr[_SCHEMENAME]);

    sheet.getRange(1 + startRow + counter,startCol + _NAV).setValue(_arr[_NAV]);

    sheet.getRange(1 + startRow + counter,startCol + _DATE).setValue(_arr[_DATE]);
   start = rowEnd; counter +=1;
   remainingText = returnRestofText(remainingText, start);

Once we read a portion of text and find the pattern, we get a new index. The place where the pattern ends is the start of our new search. So we simply assign start to the previous rowEnd.

returnRestofText function is to provide some abstraction. I could have gone with the default substring directly but I feel I will require this function later as I extend the functionality and set rules in here directly.

function returnRestofText( _str, _start){
  var _text = _str.substring(_start);
  return _text;
};

Step 5: If you notice carefully, it calls the SpreadsheetApp method getUI().

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Fetch Data')
      .addItem('Fetch all rows','myFunction')
      .addToUi();
};

This is then used to call the createMenu function with the parent text ‘Fetch Data’ along with sub menu ‘Fetch all rows’. Additionally it binds the function to the sub menu item. This allows the menu to be added to the sheet as it is loaded. To register it, you can simple select this onOpen function from the menu bar and click on the Run button. Once you give the necessary permissions to the script, you should be able to see this menu.

That should do it.

When you wish to get the latest (T-1) day’s NAV for all Mutual Funds, all you need to do is enter the date in ddmmyyyy format and click on the Fetch all rows menu option.

You can download, fork the entire code from this link.

Disclaimer: The above code is made available for educational purposes and without any guarantee. You are advised to not use this for commercial purposes and accept to use this for your personal use alone.