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

[SoylentCreek]

A made a modification that will let you pull the public asset data for any security from Bitfunder.  You still cannot retrieve your balance or total shares for your account, since that is tied in with your individual API.  Still, it's one less thing that you will have to manually type into your spreadsheet.

Code:

function BFasset(assetName,assetData){
    // Fetch public asset ticker data
    // Ex: "ActiveMining","last_price"
    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'
    return r
    }

[Namworld]

I've just published a new version with BitFunder functions to retrieve balance, portfolio data and ticker data.

[funkymunky]

I've just published a new version with BitFunder functions to retrieve balance, portfolio data and ticker data.

Thank you, I appreciate the work you've put in.
Is it the same one as per your OP?

[Namworld]

I've just published a new version with BitFunder functions to retrieve balance, portfolio data and ticker data.

Thank you, I appreciate the work you've put in.
Is it the same one as per your OP?

Yes, same script I use in the example portfolio spreadsheet.

[Duffer1]

Awesome work man.  Could you post a screenshot of your example self-updating spreadsheet with functions showing (to help out some of us thicker users).  Pretty please

[Namworld]

Awesome work man.  Could you post a screenshot of your example self-updating spreadsheet with functions showing (to help out some of us thicker users).  Pretty please

A screenshot would only show one function at a time.

Format is =functionName("value","value")

So if you look through function list, you have

Code:

function BTCTasset(assetName,assetData){
    // Fetch public asset ticker data
    // Ex: =BTCTasset("BTC-BOND","7d_avg")
    // https://btct.co/api/ticker/BTC-BOND

So you'd put something like =BTCTasset("BTC-BOND","7d_avg") into the cell to get the 7 day average. You can put whatever asset it is you need info from or check one of the asset ticer page like https://btct.co/api/ticker/BTC-BOND to see available variables.

[Duffer1]

For BTC-e currency pairs:

Currency Pairs (change the fetchCached link from ltc_btc to whatever you need): btc_usd, btc_rur, btc_eur, ltc_btc, ltc_usd, ltc_rur, ltc_eur, nmc_btc, nmc_usd, nvc_btc, nvc_usd, usd_rur, eur_usd, trc_btc, ppc_btc, ftc_btc (I haven't tried them all)

Variables (enter =BTCe("variable here") in the cell): high, low, avg, vol, vol_cur, last, buy, sell, updated, server_time
 

Code:

function BTCe(assetData){
    // example:  =BTCe("last") 
    var r = fetchCached("https://btc-e.com/api/2/ltc_btc/ticker")
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r['ticker'][assetData]
    if ('undefined' == typeof(r))
        return '0'
    if ('--' == r)
        return 0
    
    if (!isNaN(r)){r = r*1}
    return r
    }

Thanks Namworld

[18RATTT]

im having a problem with BlockchainBalance, it was fine when i use example's btc address.

Code:

=BlockchainBalance("18RATTTptdbmR4TgfK3HR9pswHgbmjp2hg")

the cell return #ERROR!  error: Argument too large: value (line 23, file "Code")

[Peter Lambert]

im having a problem with BlockchainBalance, it was fine when i use example's btc address.

Code:

=BlockchainBalance("18RATTTptdbmR4TgfK3HR9pswHgbmjp2hg")

the cell return #ERROR!  error: Argument too large: value (line 23, file "Code")

I got the same error for one of my addresses, but I used another address and it worked fine.  

Should this be in project development instead of securities?

[Namworld]

im having a problem with BlockchainBalance, it was fine when i use example's btc address.

Code:

=BlockchainBalance("18RATTTptdbmR4TgfK3HR9pswHgbmjp2hg")

the cell return #ERROR!  error: Argument too large: value (line 23, file "Code")

I've fixed it by adding "&limit=0" to the url. There's a LOT of TX id on said address and I guess Google doesn't like pages of JSON data with thousands of lines.

~~~~~~~~~~~~~~~~~~~~~~

Ah ok.  Also quick question.  I've tried to bastardize a new function (copy/pasta from btctasset) to get last price of LTC from BTC-e, but all it returns is zero.

Code:

function BTCe(assetData){
    // LTC last price
    var r = fetchCached("https://btc-e.com/api/2/ltc_btc/ticker")
    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
    }

Any thoughts?  How far off the mark am I? lol  

r = r[assetData] just fetches the variable you input right off the first level. All variables are within "ticker". Try changing that part for:
r = r['ticker'][assetData]

[Peter Lambert]

im having a problem with BlockchainBalance, it was fine when i use example's btc address.

Code:

=BlockchainBalance("18RATTTptdbmR4TgfK3HR9pswHgbmjp2hg")

the cell return #ERROR!  error: Argument too large: value (line 23, file "Code")

I've fixed it by adding "&limit=0" to the url. There's a LOT of TX id on said address and I guess Google doesn't like pages of JSON data with thousands of lines.

Just to be clear, we add the limit to the url in the BlockchainBalance function, like this? (this is somewhere around line 67)

Code:

var r = fetchCached("http://blockchain.info/address/" + btcAddress + "?format=json&limit=0")
    

Seems to work for me now.

[Duffer1]

r = r[assetData] just fetches the variable you input right off the first level. All variables are within "ticker". Try changing that part for:
r = r['ticker'][assetData]

Works perfect, thank you

Also made one for Bitstamp price (for some reason this one only works if you don't have ['ticker'] before [assetData])

Code:

// Bitstamp ######################################
function Bitstamp(assetData){
    // Ex: =Bitstamp("last") high, low, volume, bid, ask
    var r = fetchCached("https://www.bitstamp.net/api/ticker/")
    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
    }

[Peter Lambert]

Also made one for Bitstamp price (for some reason this one only works if you don't have ['ticker'] before [assetData])

Looks like they have the last price, but not a daily average. Is there a site which provides a daily average, like bitcoincharts or something?

[Duffer1]

Also made one for Bitstamp price (for some reason this one only works if you don't have ['ticker'] before [assetData])

Looks like they have the last price, but not a daily average. Is there a site which provides a daily average, like bitcoincharts or something?

I found this script somewhere around here (sorry I forgot where) for bitcoincharts.

Code:

// Bitcoincharts.com - Weighted Prices

var url = "http://api.bitcoincharts.com/v1/weighted_prices.json";
var response = UrlFetchApp.fetch(url);
var json = response.getContentText();
var bcharts_data = JSON.parse(json);

function bit24hr(inNum){
  var x = bcharts_data["USD"]["24h"];
  if (typeof inNum != "number") {  // check to make sure input is a number
    throw "input must be a number";  // throw an exception with the error message
  }
  var y = inNum * x
  return y;  
}

function bit7d(inNum){
  var x = bcharts_data["USD"]["7d"];
  if (typeof inNum != "number") {  // check to make sure input is a number
    throw "input must be a number";  // throw an exception with the error message
  }
  var y = inNum * x
  return y;  
}

function bit30d(inNum){
  var x = bcharts_data["USD"]["30d"];
  if (typeof inNum != "number") {  // check to make sure input is a number
    throw "input must be a number";  // throw an exception with the error message
  }
  var y = inNum * x
  return y;  
}

[Peter Lambert]

So is there a way to get the google spreadsheet to update all the values?

[Namworld]

So is there a way to get the google spreadsheet to update all the values?

Yes, saving script again. (You can change for how long the data is kept in cache from the top too)

[tyrion70]

Hey,

Thanks for the great work!

I've added a function for bitonic.nl (instant buy in Holland with iDeal and sell with SEPA throughout Europe)

Also I've added time variables to all functions which I use to refresh. I have a field in my gsheet with a date (can be anything). When I update it all the functions update to.

Cheers,

Code:

// Bitonic ######################################
function Bitonic(type,time){
    // Ex: =Bitonic("buy") buy, sell
    // https://bitonic.nl/json/sell?part=offer&check=btc&btc=1
    // https://bitonic.nl/json/?part=rate_convert&check=btc&btc=1
    var t = ""
    if (type=='buy') {
        t = "https://bitonic.nl/json/?part=rate_convert&check=btc&btc=1"       
    } else {
        t = "https://bitonic.nl/json/sell?part=offer&check=btc&btc=1"
    }
    var r = fetchCached(t)
    
    if ('undefined' == typeof(r))
        return 'No data!'
    r = Utilities.jsonParse(r)
    if ('undefined' == typeof(r))
        return 'Malformed JSON!'
    r = r['euros_formatted']
    if ('undefined' == typeof(r))
        return '0'
    
    if (!isNaN(r)){r = r*1}
    return r
    }

[18RATTT]

having another problem, when i embedded the gdocs into my website (iframe), the functions stuck at "thinking...."

[Namworld]

having another problem, when i embedded the gdocs into my website (iframe), the functions stuck at "thinking...."

I can't really help for that.