Google Sheets macro to fetch up-to-date Swisscanto Index 45R quote


Like many of you, I keep my financial overview in a Google Sheets doc. Yesterday both me and my wife finally invested on Swisscanto, so I figured I’d quickly build a macro to ensure that the spreadsheet always reflects the latest values, without needing my intervention. If you want to use it, just go to Tools/Script Editor and replace the contents there with the following code:

function fetchQuote1(url, sentinel) {
  var html = UrlFetchApp.fetch(url).getContentText();
  var index =;
  if (index >= 0) {
    html = html.substring(index + sentinel.length);
    return parseFloat(html.replace(',', '.'));
  throw "Failed to fetch/parse data from " + url;

function getSwissCantoIndex45RQuote() {
  return fetchQuote1('', 'cknahmepreis">');

assuming you have 100 units of Index 45R, you’ll then be able to put something =100*getSwissCantoIndex45RQuote() on your cells to reflect the current market value.

For altcoins, the same thing can be achieved:

function fetchQuote2(url, sentinel1, sentinel2) {
  var html = UrlFetchApp.fetch(url).getContentText();
  var index =;
  if (index >= 0) {
    html = html.substring(index + sentinel1.length);
    index =;
    if (index >= 0) {
      html = html.substring(index + sentinel2.length);
      return parseFloat(html.replace(',', '.'));
  throw "Failed to fetch/parse data from " + url;

function getBitcoinWalletBalance(walletAddress) {
  return fetchQuote2('' + walletAddress, '<td id="final_balance"><font color="green"><span data-c="', '>');

function getEthereumWalletBalance(walletAddress) {
  return fetchQuote2('' + walletAddress, 'ETH Balance:', '<td>');


this is so cool!
anybody knows how to do this for US / IE based funds? like Vanguard VT, VWRD, etc? would be sooo cool!


I think Yahoo and Google have public APIs for that, but I haven’t tried.


Well done Moebius!

And I confirm that for publicly traded stocks/funds, @nugget you can use the Google finance function in Google spreadsheets. Just type : =GOOGLEFINANCE(“NYSEARCA:VT”, “price”) in the Google Spreadsheet :wink:


Maybe i’ts time for some of the IT guys here to make a guide on how to access all this data^^
i remember trying once, but i didnt succeed. yes, for me it is not trivial.



    etc. Just look the symbol up in and pass that to the GOOGLEFINANCE function.

The reason I did this as a macro is because Google Finance doesn’t know about this Swisscanto index.


A slightly trickier use case is to get an historical quote (remember you can use this for currency pairs, for instance). Since in that case the function returns a table, you need to navigate that like this: =INDEX(GOOGLEFINANCE("CURRENCY:USDCHF", "close", DATE(2016,12,31)), 2, 2)


ok i love this, thank you for pointing out how easy this is!


so i tried to make some sheet according to my portfolio (but for 2016, so values are available)

but the GOOGLEFINANCE function erratically returns “N/A”,

does anybody know why this does not work? or for a big part not work?

/edit: i seriously changed nothing, and 10 mins later all numbers are there:

lets see what happens next^^

/edit2: numbers seem to vanish again. very strange…


Maybe connection error due to too many request at the same time? To be honest, I don’t know how it behaves with that many data points to retrieve - I only fetch half a dozen prices (I hardcode historic prices, since those aren’t going to change) .


By the way, do you know how to get currencies exchange rates in Google Sheets?




Hi @Mobius, I’vre tried this in my sheets and it seems to not work:

Do you know what I’m doing wrong?


Not sure, the exact same formula works fine here :-/


Ok, I figured it out. I had to change “Locale” in “Spreadsheet settings” from Poland to United States. Now it works. :slight_smile:


yes, you can also check a box that says “always use english formulas”
also mind any date-dependent function, in terms of location.


muhahaha :smiling_imp: i just found the yahoo finance package for python.

when do i have time…


Maybe when you reach FI… :slight_smile:


By the way, for those who hold cryptocurrencies, GOOGLEFINANCE now also supports the major assets: