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


#1

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 = html.search(sentinel);
  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('http://www.goyax.de/fonds/swisscanto-bvg-3-index-45-r_CH0238046459', '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 = html.search(sentinel1);
  if (index >= 0) {
    html = html.substring(index + sentinel1.length);
    index = html.search(sentinel2);
    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('https://blockchain.info/address/' + walletAddress, '<td id="final_balance"><font color="green"><span data-c="', '>');
}

function getEthereumWalletBalance(walletAddress) {
  return fetchQuote2('https://etherscan.io/address/' + walletAddress, 'ETH Balance:', '<td>');
}

#2

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


#3

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


#4

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:


#5

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.


#6

Yeah:

  • for VT: =GOOGLEFINANCE("VT").
  • for VRWL in SIX: GOOGLEFINANCE("SWX:VWRL")
  • for CSSMI in SIX: GOOGLEFINANCE("SWX:CSSMI")
    etc. Just look the symbol up in https://www.google.com/finance 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.


#7

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)


#8

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


#9

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…


#10

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) .


#11

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


#12

Sure, try =GOOGLEFINANCE("CURRENCY:USDCHF", "average").


#13

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

Do you know what I’m doing wrong?


#14

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


#15

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


#16

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


#17

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

when do i have time…


#18

Maybe when you reach FI… :slight_smile:


#19

By the way, for those who hold cryptocurrencies, GOOGLEFINANCE now also supports the major assets: https://decrypto.net/2018/09/21/how-to-display-cryptocurrency-prices-in-google-sheets/