Saving a history of muFunds data automatically

Save the daily values of your fund or pension plan automatically, so you don't lose them after an update, using a time-triggered Google Apps Script function.

This guide uses muFunds, a free Google Sheets add-on that imports your asset data from Morningstar and other sources. It assumes you’ve already installed muFunds and started using it in your spreadsheet.

The goal is to create a Google Apps Script function that saves the Net Asset Value (NAV) of your funds or pension plans, so you build up a history of values for comparison or backtesting in your own spreadsheet. Then you’ll create a time-driven trigger that runs this function periodically (for example, every day).

We’ll use a spreadsheet with two sheets and a single fund: one sheet called Investments with the fund and its data (including the NAV from muFunds), and another called History where past NAVs are saved. This is easy to extend to several funds, as explained below.

Saving function

First, write the Google Apps Script function that copies the NAV and its date from Investments to History. Open the script editor from the menu bar (Tools > Script editor).

Here is a good starting point. It reads a single fund (NAV from cell F2, date from cell H2) and appends a new row to History with the parsed date, the fund’s ISIN and its NAV. Adapt it as you like and test it manually from the menu bar (Run > Run function > saveNavHistory):

function saveNavHistory() {
  // Name of origin sheet (where current NAVs are)
  var sheet1 = "Investments";

  // Name of destination sheet (where historical NAVs will be saved)
  var sheet2 = "History";

  // Array of objects for each asset:
  // - id: asset ISIN or ID
  // - navCell: cell with NAV
  // - dateCell: cell with date
  var funds = [
    { isin: "LU0996182563", navCell: "F2", dateCell: "H2" },
    // ...
  ];

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s1 = ss.getSheetByName(sheet1);
  var s2 = ss.getSheetByName(sheet2);

  funds.forEach(function(fund) {
    var date = s1.getRange(fund.dateCell).getValue();
    var parsedDate = new Date(Date.parse(date));
    var lastDate = s2.getRange(s2.getLastRow(), 1).getValue();
    // Avoid inserting a NAV if we already have it
    if(!(lastDate instanceof Date) || lastDate.getTime() !== parsedDate.getTime()) {
      var nav = s1.getRange(fund.navCell).getValue();
      s2.appendRow([ parsedDate, fund.isin, nav ]);
    }
  });
}

Setting up the time-driven trigger

Once the function is ready and tested, set up a time-based trigger to run it automatically. From the menu bar, go to Edit > Current project’s triggers. A new tab opens.

Create a new trigger with Add Trigger in the lower right corner of the page. Choose your function (saveNavHistory) and select Time-driven as the event source. Funds and pension plans update their NAV at most once a day, so a Day timer is usually enough. Pick a time of day if you have a preference. The result looks like this:

Press Save and you’re done. Your function will now run and save your NAVs every day.