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

Namworld

hero member

Activity: 745

Merit: 501

Quote from: 18RATTT on September 18, 2013, 06:22:26 PM

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

I can't really help for that.

18RATTT

sr. member

Activity: 282

Merit: 250

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

tyrion70

legendary

Activity: 934

Merit: 1000

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
}

Namworld

hero member

Activity: 745

Merit: 501

Quote from: Peter Lambert on September 14, 2013, 08:00:52 PM

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)

Peter Lambert

hero member

Activity: 756

Merit: 500

It's all fun and games until somebody loses an eye

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

Duffer1

full member

Activity: 227

Merit: 100

Quote from: Peter Lambert on September 14, 2013, 12:57:46 PM

Quote from: Duffer1 on September 14, 2013, 10:16:43 AM

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

hero member

Activity: 756

Merit: 500

It's all fun and games until somebody loses an eye

Quote from: Duffer1 on September 14, 2013, 10:16:43 AM

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

full member

Activity: 227

Merit: 100

Quote from: Namworld on September 12, 2013, 11:16:59 PM

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

hero member

Activity: 756

Merit: 500

It's all fun and games until somebody loses an eye

Quote from: Namworld on September 12, 2013, 11:16:59 PM

Quote from: 18RATTT on September 12, 2013, 08:43:42 PM

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.

Namworld

hero member

Activity: 745

Merit: 501

Quote from: 18RATTT on September 12, 2013, 08:43:42 PM

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.

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

Quote from: Duffer1 on September 12, 2013, 08:02:16 PM

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 Embarrassed

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

hero member

Activity: 756

Merit: 500

It's all fun and games until somebody loses an eye

Quote from: 18RATTT on September 12, 2013, 08:43:42 PM

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

Should this be in project development instead of securities?

18RATTT

sr. member

Activity: 282

Merit: 250

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

Duffer1

full member

Activity: 227

Merit: 100

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

Namworld

hero member

Activity: 745

Merit: 501

Quote from: Duffer1 on September 12, 2013, 04:56:07 PM

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

full member

Activity: 227

Merit: 100

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

hero member

Activity: 745

Merit: 501

Quote from: funkymunky on September 12, 2013, 03:30:42 AM

Quote from: Namworld on September 12, 2013, 02:52:28 AM

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.

funkymunky

sr. member

Activity: 283

Merit: 250

Quote from: Namworld on September 12, 2013, 02:52:28 AM

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

hero member

Activity: 745

Merit: 501

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

SoylentCreek

sr. member

Activity: 266

Merit: 250

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

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
}

penta

member

Activity: 113

Merit: 20

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

Exocyst

sr. member

Activity: 266

Merit: 250

Science!

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
}

Exocyst

sr. member

Activity: 266

Merit: 250

Science!

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 = "" // 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
}

Namworld

hero member

Activity: 745

Merit: 501

Quote from: Franktank on June 04, 2013, 10:31:11 PM

Quote from: Namworld on June 04, 2013, 10:27:07 PM

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.

Franktank

hero member

Activity: 560

Merit: 500

Quote from: Namworld on June 04, 2013, 10:27:07 PM

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

hero member

Activity: 745

Merit: 501

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

hero member

Activity: 560

Merit: 500

Quote from: Eric Muyser on June 04, 2013, 09:39:17 PM

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.

Eric Muyser

full member

Activity: 224

Merit: 100

You can't kill math.

Awesome Namworld.

How'd you get a Bitfunder API key?

funkymunky

sr. member

Activity: 283

Merit: 250

Thank you ever so much

Franktank

hero member

Activity: 560

Merit: 500

Brilliant!

Namworld

hero member

Activity: 745

Merit: 501

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!

Bit_Happy

legendary

Activity: 2114

Merit: 1040

A Great Time to Start Something!

Very nice work.

burnside

legendary

Activity: 1106

Merit: 1006

Lead Blockchain Developer

Quote from: EskimoBob on February 10, 2013, 09:05:18 AM

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

{"ticker":"COGNITIVE","latest":"[email protected]","bid":"0.305","ask":"0.33","24h_low":"0.325","24h_high":"0.33","24h_avg":"0.3292","24h_vol":"[email protected]","7d_low":"0.3099","7d_high":"0.339","7d_avg":"0.33","7d_vol":"[email protected]","30d_low":"0.2401","30d_high":"0.476","30d_avg":"0.36","30d_vol":"[email protected]","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.

EskimoBob

legendary

Activity: 910

Merit: 1000

Quality Printing Services by Federal Reserve Bank

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

{"ticker":"COGNITIVE","latest":"[email protected]","bid":"0.305","ask":"0.33","24h_low":"0.325","24h_high":"0.33","24h_avg":"0.3292","24h_vol":"[email protected]","7d_low":"0.3099","7d_high":"0.339","7d_avg":"0.33","7d_vol":"[email protected]","30d_low":"0.2401","30d_high":"0.476","30d_avg":"0.36","30d_vol":"[email protected]","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/*

EskimoBob

legendary

Activity: 910

Merit: 1000

Quality Printing Services by Federal Reserve Bank

Good stuff. Thank you.

Namworld

hero member

Activity: 745

Merit: 501

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

burnside

legendary

Activity: 1106

Merit: 1006

Lead Blockchain Developer

That is really cool.

Namworld

hero member

Activity: 745

Merit: 501

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.

🏰 TradeFortress 🏰

vip

Activity: 1316

Merit: 1043

👻

Quote

Do not share your API key or URL with anyone!

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

Namworld

hero member

Activity: 745

Merit: 501

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

Topic: [Tools] Google Spreadsheets auto-updating portfolio JSON functions. (Read 7517 times)