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>');
}
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
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.
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.
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)
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 reading and partipating to this forum, you confirm you have read and agree with the disclaimer presented on http://www.mustachianpost.com/
En lisant et participant à ce forum, tu confirmes avoir lu et être d'accord avec l'avis de dégagement de responsabilité présenté sur http://www.mustachianpost.com/fr/
Durch das Lesen und die Teilnahme an diesem Forum bestätigst du, dass du den auf http://www.mustachianpost.com/de/ dargestellten Haftungsausschluss gelesen hast und damit einverstanden bist.
i just found the 