Import data from the Sharesight API to Google Sheets by making them shake hands with OAuth 2.0

0
244

I am guessing this post will have a very limited audience, but hey, it might be useful to someone.

Sharesight is an Australia/New Zealand-centric cloud based software that manages share portfolio tax reporting. It also supports tax residents of Canada. It is fantastic for anyone from these countries who invests or trades in shares, LICs, ETFs and anything else that is listed. I remember trying to maintain CGT (and dividend income) records before Sharesight and it was quite the nightmare, although I suspect my accountant isn’t thrilled that my CGT reporting is done with a button press.

Liking a good tinker I set out some time ago to interface with Sharesight’s API via Google Sheets’ Google Apps Script and live feed information from my portfolio into Google Sheets. I am not much of a programmer so I just kind of hacked it together over a weekend. I am sure the code is lacking elegance but it works. It is also built as several functions which could be combined into one. On this plus side this will help amateurs who want to further tinker with it.

One might ask why I would want to import Sharesight data into Google Sheets, well it gives me a little more flexibility in tracking holdings and can be amalgamated with other data to create more holistic asset views, or even more specific ones.

For the API to work you have to contact Sharesight and request they enable it. See https://portfolio.sharesight.com/api (opens in new tab) for the email address. Once enabled you can access your API Client ID and Client Secret from the Account Settings API page.

Security note: This code will use your Client Secret. As you might have guessed from the name it should be kept a secret. The sheet in which you use the code should not be shared with anyone. If someone gets a hold of your Client Secret they could use the API to make changes to your Sharesight account, so treat it like you would a password.

Update – I made some minor changes to the code based on feedback from Kylor at Sharesight. Thanks Kylor

So without further ado, here’s the code.


var ClientID = "ENTER_CLIENTID_FROM_SHARESIGHT_HERE";
var ClientSecret = "ENTER_CLIENTSECRET_FROM_SHARESIGHT_HERE";

/* Note to readers, your portfolio number is the number shown in the address
bar when you have Sharesight open. It is five or six digits. This code
can handle combined portfolios, or consolidated views as Sharesight refers
to them. See below comment */

var PortfolioNumber = "ENTER_PORTFOLIO_NUMBER_FROM_SHARESIGHT_HERE";


/* set isConsolidated to true if this is a consolidated view. That means 
if are looking at several portfolios combined (see ?consolidated=) in 
your address bar. */

var isConsolidated = false; 


/* The following code checks if a token has been retrieved from Sharesight
in the last 30 minutes (how long the tokens remain valid for). If one
hasn't, it requests a token using the client ID an client secret that
we stored at the beginning of the code. */

function addtoproperties(SStoken) {
  var scriptProperties = PropertiesService.getScriptProperties();
  scriptProperties.setProperty('SStoken', SStoken);
  var d = new Date();
  var timeStamp = d.getTime();
  scriptProperties.setProperty('issuedtime', timeStamp);
  var store = scriptProperties.getProperties();
  return;
}

function gettoken() {
  var authdetails = {
    "grant_type": "client_credentials",
    "client_id": ClientID,
    "client_secret": ClientSecret
  };
  var options = {
    'method': 'post',
    'payload': authdetails
  };
  var scriptProperties = PropertiesService.getScriptProperties();
  var issuetime = scriptProperties.getProperty('issuedtime');
  if (issuetime !== null) {
    var TokenExpiry = (Number(issuetime) + 1800);
  } else {
    var TokenExpiry = 0;
  }
  var d = new Date();
  var timeStamp = d.getTime();
  if (timeStamp > TokenExpiry) {
    var response = UrlFetchApp.fetch("https://api.sharesight.com/oauth2/token", options);
    var data = JSON.parse(response.getContentText());
    var token = data.access_token;
    addtoproperties(token)
  } else {
    var token = scriptProperties.getProperty('SStoken');
  }
  return token
}

//This next section requests the JSON data from Sharesight
function getSScombinedportfolio() {
  var token = gettoken();
  var header = {
    'Authorization': 'Bearer ' + token
  }
  var options = {
    'method': 'get',
    'headers': header
  };

  var url = "https://api.sharesight.com/api/v2/portfolios/" + PortfolioNumber + "/valuation.json?consolidated=" + (isConsolidated ? 'true' : 'false');
  
  var response = UrlFetchApp.fetch(url, options);
  var data = JSON.parse(response.getContentText());
  return data
}

/* And this code parses the JSON into an array with three rows containing
Symbol, Quantity and Value. */
function parsedSSdata() {
  var dataSet = getSScombinedportfolio();
  var rows = [],
    data;
  for (i = 0; i < dataSet.holdings.length; i++) {
    data = dataSet.holdings[i];
    rows.push({
      'symbol': data.symbol,
      'quantity': data.quantity,
      'value': data.value
    });
  }
  return rows
}

/* The following section handles when the portfolio number is actually a
 consolidated view of more than one portfolio. It sums any holdings with
the same symbol and then sorts them largest to smallest. */
function summedSSdatawithKeys() {
  var data = parsedSSdata();
  var sum = [];
  data.forEach(function(o) {
    var existing = sum.filter(function(i) {
      return i.symbol === o.symbol
    })[0];
    if (!existing) sum.push(o);
    else existing.quantity += o.quantity, existing.value += o.value;
  });
  // Sort largest value to lowest value
  sum.sort(function(a, b) {
    return b.value - a.value;
  });
  return sum;
}

/* And this final section is the function you can call to import the
data into a sheet. If no option is specified it will show symbol (ticker),
quantity and value. Or you can specify either quantity or value.
E.g. =SStotals("value") */
function SStotals(options) {
  var dataSet = summedSSdatawithKeys();
  var output = [];
  switch (options) {
    case 'value':
      for (var i = 0; i < dataSet.length; ++i) {
        var data = dataSet[i];
        output.push([data.symbol, data.value]);
      }
      break;

    case 'quantity':
      for (var i = 0; i < dataSet.length; ++i) {
        var data = dataSet[i];
        output.push([data.symbol, data.quantity]);
      }
      break;

    default:
      for (var i = 0; i < dataSet.length; ++i) {
        var data = dataSet[i];
        output.push([data.symbol, data.quantity, data.value]);
      }
      break;
  }
  return output;
}