[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

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