[Tools] Google Spreadsheets auto-updating portfolio JSON functions.

[Namworld]

Can't recall who I got the function from, originally for GLBSE data. I've heavily modified it to fetch:

Blockchain.info	Bitcoin Address Balance
MtGox	Weighted average, any currency
BTCT.co	Ticker data, balance and portfolio data
BitFunder	Ticker data, balance and portfolio data
MPEx	Average trading prices
Havelock Investments	Ticker data, balance and portfolio data

Bitcoin address balances from blockchain.info
Portfolio data and average trading price from BTCT.co
Average trading price from MPEx
Havelock
MtGox weighted average, any currency

Can easily be further customized.

Demo of self-updating portfolio with stats and etc:
https://docs.google.com/spreadsheet/pub?key=0AvwwyRGyc1WgdGFKVlAtZVAzOGNqS1Brd05MUlFlT2c

###################################
Go in code editor and put this code in, replacing the variables

Code:

// Namworld's JSON Portfolio Toolkit v0.5
// https://bitcointalk.org/index.php?topic=140427.0

// Variables, set them up to use your account data functions
var BTCT_key = "YourReadOnlyApiKey" // BTCT.co Portfolio via JSON API key (read only)
var BF_key = "YourReadOnlyApiKey" // Bitfunder API Key (read only)
var Havelock_key = "YourReadOnlyApiKey" // Havelock Investment API key. Only give portfolio data permissions! Not orders or anything else.
var cacheTime = 120 // time to cache data in seconds

// Main fetch JSON data function

function fetchCached(url){ // fetch url from cache
    var publicCache = CacheService.getPublicCache()
    var cached = publicCache.get(url)

    if (null == cached) { // If cache is null, fetch url
        var response = UrlFetchApp.fetch(url)
        
        if ('undefined' != typeof(response)) {
            var code = response.getResponseCode()
            if (code == 200) {
              cached = response.getContentText()
              publicCache.put(url, cached, cacheTime)
            }
        }
    }
    
    return cached
    }

function fetchCached_Havelock_Key(url,key){
    var publicCache = CacheService.getPublicCache()
    var cached = publicCache.get(url)

    if (null == cached) { // If cache is null, fetch url
        var payload = {"key": key};
        var options = {"method" : "post","payload" : payload};
        var response = UrlFetchApp.fetch(url,options);
        
        if ('undefined' != typeof(response)) {
            var code = response.getResponseCode()
            if (code == 200) {
              cached = response.getContentText()
              publicCache.put(url, cached, cacheTime)
            }
        }
    }
    
    return cached
    }

// MtGox #########################################################################

function MtGoxCurrency(currency,data,datatype){
// Fetch currency data from MtGOX API
// Ex: =MtGoxCurrency("USD","avg","value") for weighted average
// https://mtgox.com/api/1/BTCUSD/ticker
    var r = fetchCached("https://mtgox.com/api/1/BTC"+ currency +"/ticker")
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r['return'][data][datatype]
    if ('undefined' == typeof(r))
        return 0
    
    if (!isNaN(r)){r = r*1} 
    return r
    }

// Blockchain.info ###############################################################

function BlockchainBalance(btcAddress){
    // Fetch balance from Blockchain.info API
    // EX: =BlockchainBalance("1Namjies9JeJ4ugCzjkAj6dbaWrtjAFKCk")
    // http://blockchain.info/address/1dice9wVtrKZTBbAZqz1XiTmboYyvpD3t?format=json
    var r = fetchCached("http://blockchain.info/address/" + btcAddress + "?format=json&limit=0")
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r['final_balance'] / 100000000
    if ('undefined' == typeof(r))
        return 0
    
    if (!isNaN(r)){r = r*1} 
    return r
    }

// BTCT.co #######################################################################

function BTCTsecurity(assetName){
    // Fetch live security amount from BTCT.co API
    // Ex: =BTCTsecurity("BTC-BOND")
    // https://btct.co/api/act?key=InputYourApiKey
    var r = fetchCached("https://btct.co/api/act?key=" + BTCT_key)
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    if ('undefined' == typeof(r['securities'][assetName]))
        return 0
    r = r['securities'][assetName]['quantity']
    if ('undefined' == typeof(r))
        return 'Unkown Error!'
    
    if (!isNaN(r)){r = r*1}   
    return r
    }

function BTCTbalance(){
    // Fetch your account balance
    // EX: =BTCTbalance()
    // https://btct.co/api/act?key=InputYourApiKey
    var r = fetchCached("https://btct.co/api/act?key=" + BTCT_key)
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r['balance']['BTC']
    if ('undefined' == typeof(r))
        return 0
    
    if (!isNaN(r)){r = r*1}   
    return r
    }

function BTCTasset(assetName,assetData){
    // Fetch public asset ticker data
    // Ex: =BTCTasset("BTC-BOND","7d_avg")
    // https://btct.co/api/ticker/BTC-BOND
    var r = fetchCached("https://btct.co/api/ticker/" + assetName)
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r[assetData]
    if ('undefined' == typeof(r))
        return 0
    if ('--' == r)
        return 0
    
    if (!isNaN(r)){r = r*1}
    return r
    }

function BTCTcontract(assetName,assetData){
    // Fetch public asset contract data
    // Ex: =BTCTcontract("BTC-BOND","Shares Outstanding")
    // https://btct.co/api/assetContract/BTC-BOND
    var r = fetchCached("https://btct.co/api/assetContract/" + assetName)
        if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'   
    r = r[assetData]
    if ('undefined' == typeof(r))
        return 0
    
    if (!isNaN(r)){r = r*1}   
    return r
    }
    
// Bitfunder.com #######################################################################

function BFasset(assetName, assetData){
    // Fetch public asset data from Bitfunder's API
    // Ex: =BFasset("BitPride","last_price")
    // https://api.bitfunder.com/public/market
    var r = fetchCached("https://api.bitfunder.com/public/market")
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r[assetName][assetData]
    if ('undefined' == typeof(r))
        return 0
    
    if (!isNaN(r)){r = r*1} 
    return r
    }
    
function BFportfolio(assetName, assetData){
    // Fetch live security amount from Bitfunder's API
    // Ex: =BFportfolio("BitPride","amount")
    // https://api.bitfunder.com/private/assets?key=InputYourApiKey
    var r = fetchCached("https://api.bitfunder.com/private/assets?key=" + BF_key)
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r["assets"][assetName][assetData]
    if ('undefined' == typeof(r))
        return 0
    
    if (!isNaN(r)){r = r*1}   
    return r
    }

function BFbalance(){
    // Fetch live security amount from Bitfunder's API
    // Ex: =BFbalance()
    // https://api.bitfunder.com/private/assets?key=InputYourApiKey
    var r = fetchCached("https://api.bitfunder.com/private/balance?key=" + BF_key)
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r["account_balance"]
    if ('undefined' == typeof(r))
        return 'Unkown Error!'
    r = r / 100000000    
    if (!isNaN(r)){r = r*1}
    return r
    }
    
// MPEx ##########################################################################

function MPEXaverage(MPSIC,period){
    // Fetch public asset contract data
    // Ex: =MPEXaverage("S.MPOE","1d")
    // http://mpex.co/mpex-vwap.php
    var r = fetchCached("http://mpex.co/mpex-vwap.php")
        if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'   
    r = r[MPSIC][period]["avg"] / 100000000
    if ('undefined' == typeof(r))
        return 0  
        
    if (!isNaN(r)){r = r*1}
    return r
    }
    
// Havelock Investments ##########################################################################

function HavelockTicker(symbol,data){
    // Fetch Havelock ticker
    // Ex: "HIM","last"
    // https://www.havelockinvestments.com/r/tickerfull
    var r = fetchCached("https://www.havelockinvestments.com/r/tickerfull")
        if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'   
    r = r[symbol][data]
    if ('undefined' == typeof(r))
        return 0
        
    if (!isNaN(r)){r = r*1}
    return r
    }

function HavelockPeriod(symbol,period,data){
    // Fetch Havelock averages and over a period data
    // Ex: "HIM","30d","vwap"
    // https://www.havelockinvestments.com/r/tickerfull
    var r = fetchCached("https://www.havelockinvestments.com/r/tickerfull")
        if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'   
    r = r[symbol][period][data]
    if ('undefined' == typeof(r))
        return 0  
        
    if (!isNaN(r)){r = r*1}
    return r
    }
    
function HavelockBalance(){
    // Fetch your Havelock balance
    // Available data : balance, balanceescrow, balanceavailable
    var r = fetchCached_Havelock_Key("https://www.havelockinvestments.com/r/balance",Havelock_key)
        if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'   
    r = r['balance']['balance']
    if ('undefined' == typeof(r))
        return 0
        
    if (!isNaN(r)){r = r*1}
    return r
    }
    
function HavelockPortfolio(symbol,data){
    // Fetch your Havelock balance
    // Ex: "HIM","quantity"
    // Available data : symbol, name, quantity, quantityescrow, lastprice, bookvalue, marketvalue
    // https://www.havelockinvestments.com/r/portfolio
    var r = fetchCached_Havelock_Key("https://www.havelockinvestments.com/r/portfolio",Havelock_key)
        if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r['portfolio']
    if (r == null){return 0}
    for(var i=0;i<r.length;i++){
        if (r[i]['symbol'] == symbol) {r = r[i]}
    }
    r = r[data]
    if ('undefined' == typeof(r))
        return 0   
        
    if (!isNaN(r)){r = r*1}
    return r
    }

Then simply put in your spreadsheet cells, putting one value for each variable for the function, with quotes around the text:

Code:

=functionName("value","value")

[1714935650]

1714935650

[1714935650]

1714935650

[1714935650]

1714935650

[1714935650]

1714935650

[1714935650]

1714935650

[🏰 TradeFortress 🏰]

Do not share your API key or URL with anyone!

I'm not sure but the JS should be executed client side.

[Namworld]

The only API key is for BTCT.co and only allows viewing the portfolio. If you don't want Google to be able to take a peek at your portfolio, don't use it.

[burnside]

That is really cool.

[Namworld]

Bumping these. Possibilities are endless with this. Makes trading a breeze.

[EskimoBob]

Good stuff. Thank you. 

[EskimoBob]

https://btct.co/api/ticker/COGNITIVE

{"ticker":"COGNITIVE","latest":"4@0.33","bid":"0.305","ask":"0.33","24h_low":"0.325","24h_high":"0.33","24h_avg":"0.3292","24h_vol":"6@2.0","7d_low":"0.3099","7d_high":"0.339","7d_avg":"0.33","7d_vol":"33@10.7","30d_low":"0.2401","30d_high":"0.476","30d_avg":"0.36","30d_vol":"605@214.9","total_vol":"639.59290000","type":"STOCK","yes_votes":"9","no_votes":0}

Burnside, can you please add the "last" price only to JSON feed. Only the price.

and same for https://www.litecoinglobal.com/api/ticker/*

 


 

[burnside]

https://btct.co/api/ticker/COGNITIVE

{"ticker":"COGNITIVE","latest":"4@0.33","bid":"0.305","ask":"0.33","24h_low":"0.325","24h_high":"0.33","24h_avg":"0.3292","24h_vol":"6@2.0","7d_low":"0.3099","7d_high":"0.339","7d_avg":"0.33","7d_vol":"33@10.7","30d_low":"0.2401","30d_high":"0.476","30d_avg":"0.36","30d_vol":"605@214.9","total_vol":"639.59290000","type":"STOCK","yes_votes":"9","no_votes":0}

Burnside, can you please add the "last" price only to JSON feed. Only the price.

and same for https://www.litecoinglobal.com/api/ticker/*

I know I posted in the other thread about this, but for those watching this thread, there is now a "last_qty" and "last_price" value in the JSON.

Cheers.

[Bit_Happy]

Very nice work.

[Namworld]

New!

Version 0.3

This one supports Havelock Investments. It will require an API key to be filled.

ONLY GIVE BALANCE/PORTFOLIO ACCESS! DON'T GIVE WITHDRAW OR TRADE ACCESS! GOOGLE WILL HAVE ACCESS TO THIS KEY AS WELL AS ANYONE WHO YOU SHARE YOUR SPREADSHEET WITH! IT SHOULD ONLY HAVE ACCESS TO YOUR ACCOUNT DATA!

[Franktank]

Brilliant!

[funkymunky]

Thank you ever so much

[Eric Muyser]

Awesome Namworld.

How'd you get a Bitfunder API key?

[Franktank]

Awesome Namworld.

How'd you get a Bitfunder API key?

He didn't, that's still in progress. We all gotta keep bugging Ukyo for that feature.

[Namworld]

Actually I was updating it manually not long ago. Now I got an API key. It's still not public tho. As soon as it is, I'll release a new version.

[Franktank]

Actually I was updating it manually not long ago. Now I got an API key. It's still not public tho. As soon as it is, I'll release a new version.

Who did you have to kill to get your key, unbelievable...

[Namworld]

Actually I was updating it manually not long ago. Now I got an API key. It's still not public tho. As soon as it is, I'll release a new version.

Who did you have to kill to get your key, unbelievable...

That just means Ukto is working on the new API and it should be available soon enough. Hence why he made me one key to test.

[Exocyst]

Excellent contribution Namworld, this is so helpful!

To add to your work: I did the easy search/replace to adapt the BTC-TC bits for LTC-GLOBAL, and I added a function to pull the spot price of BTC (in LTC) on vircurex:

Code:

// First, Add this to the list of variables:
var LTC_key = "<insert your public LTC-GLOBAL api key>" // LTC-Global Portfolio via JSON API key (read only)

// LTC-GLOBAL #######################################################################

function LTCsecurity(securityName){
    // Fetch live security amount from litecoinglobal.com API
    // Ex: "CIPHERMINE"
    // https://litecoinglobal.com/api/act?key=InputYourApiKey
    var r = fetchCached("https://litecoinglobal.com/api/act?key=" + LTC_key)
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    if ('undefined' == typeof(r['securities'][securityName]))
        return '0'
    r = r['securities'][securityName]['quantity']
    if ('undefined' == typeof(r))
        return 'Unkown Error!'
    return r
    }

function LTCbalance(){
    // Fetch your account balance
    // https://litecoinglobal.com/api/act?key=InputYourApiKey
    var r = fetchCached("https://litecoinglobal.com/api/act?key=" + LTC_key)
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r['balance']['LTC']
    if ('undefined' == typeof(r))
        return '0'
    return r
    }

function LTCasset(assetName,assetData){
    // Fetch public asset ticker data
    // Ex: "CIPHERMINE","7d_avg"
    // https://litecoinglobal.com/api/ticker/CIPHERMINE
    var r = fetchCached("https://litecoinglobal.com/api/ticker/" + assetName)
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r[assetData]
    if ('undefined' == typeof(r))
        return '0'
    return r
    }

function LTCcontract(assetName,assetData){
    // Fetch public asset contract data
    // Ex: "CIPHERMINE","Shares Outstanding"
    // https://litecoinglobal.com/api/assetContract/CIPHERMINE
    var r = fetchCached("https://litecoinglobal.com/api/assetContract/" + assetName)
        if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'   
    r = r[assetData]
    if ('undefined' == typeof(r))
        return '0'
    return r
    }

// Vircurex ##########################################################################
function LTCUSD(){
    // Fetch Litecoin Price for 1 BTC from vircurex
    var r = fetchCached("https://vircurex.com/api/get_highest_bid.json?base=BTC&alt=LTC")
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r['value']
    if ('undefined' == typeof(r))
        return '0'
    return r
    }

[Exocyst]

Hi Namworld,

For the benefit of fellow alt-coiner's, I added a last price look up function for mcxNOW (which uses XML).

Code:

// mcxNOW #######################################################################

function mcxNOW(currency){
  // Fetch altcoin exchange data from mcxNOW
  // Ex: "XPM" for last XPM price
  // https://mcxnow.com/orders?cur=XPM
  var mcx = fetchCached("https://mcxnow.com/orders?cur="+ currency )
  var data1 = Xml.parse(mcx, true);
  var doc = data1.doc
  var lprice = data1.doc.lprice
  var lastprice = data1.doc.lprice.getText()
  return lastprice
}

[penta]

Ok nevermind figured it out, i hadnt set it up in the script correctly and was using the default
KEY silly me.

great script!
got some of the functions working but for some reason the BTCT balance one seems to be wrong, this is what i use:
=BTCTbalance("https://btct.co/api/act?key=mykey")

wich outputs: 36.18778354, but the real balance is

 "balance":{"BTC":"5.69554250"}

where does the the 36 number come from i wonder