Bitcoin Forum
November 21, 2017, 07:45:01 PM *
News: Latest stable version of Bitcoin Core: 0.15.1  [Torrent].
 
   Home   Help Search Donate Login Register  

Warning: Moderators do not remove likely scams. You must use your own brain: caveat emptor. Watch out for Ponzi schemes. Do not invest more than you can afford to lose.

Pages: « 1 [2]  All
  Print  
Author Topic: [Tools] Google Spreadsheets auto-updating portfolio JSON functions.  (Read 7199 times)
SoylentCreek
Sr. Member
****
Offline Offline

Activity: 266



View Profile
August 28, 2013, 02:08:28 PM
 #21

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
    }

Was I helpful or insightful?  Feel free to say thanks! 1PuoasR1dYtNq9yYNJj9NreDAfLEzc3Vpe
1511293501
Hero Member
*
Offline Offline

Posts: 1511293501

View Profile Personal Message (Offline)

Ignore
1511293501
Reply with quote  #2

1511293501
Report to moderator
Join ICO Now A blockchain platform for effective freelancing
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
1511293501
Hero Member
*
Offline Offline

Posts: 1511293501

View Profile Personal Message (Offline)

Ignore
1511293501
Reply with quote  #2

1511293501
Report to moderator
Namworld
Hero Member
*****
Offline Offline

Activity: 743



View Profile
September 12, 2013, 07:52:28 AM
 #22

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

Activity: 283



View Profile
September 12, 2013, 08:30:42 AM
 #23

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
*****
Offline Offline

Activity: 743



View Profile
September 12, 2013, 08:53:30 AM
 #24

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
Full Member
***
Offline Offline

Activity: 227



View Profile
September 12, 2013, 09:56:07 PM
 #25

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 Smiley
Namworld
Hero Member
*****
Offline Offline

Activity: 743



View Profile
September 12, 2013, 10:33:05 PM
 #26

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 Smiley

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
***
Offline Offline

Activity: 227



View Profile
September 13, 2013, 01:02:16 AM
 #27

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 Smiley
18RATTT
Sr. Member
****
Offline Offline

Activity: 282



View Profile
September 13, 2013, 01:43:42 AM
 #28

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
Hero Member
*****
Offline Offline

Activity: 756

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


View Profile
September 13, 2013, 02:02:48 AM
 #29

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?

Use CoinBR to trade bitcoin stocks: CoinBR.com

The best place for betting with bitcoin: BitBet.us
Namworld
Hero Member
*****
Offline Offline

Activity: 743



View Profile
September 13, 2013, 04:16:59 AM
 #30

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  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
*****
Offline Offline

Activity: 756

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


View Profile
September 13, 2013, 01:33:37 PM
 #31

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.

Use CoinBR to trade bitcoin stocks: CoinBR.com

The best place for betting with bitcoin: BitBet.us
Duffer1
Full Member
***
Offline Offline

Activity: 227



View Profile
September 14, 2013, 03:16:43 PM
 #32

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 Smiley

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
*****
Offline Offline

Activity: 756

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


View Profile
September 14, 2013, 05:57:46 PM
 #33


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?

Use CoinBR to trade bitcoin stocks: CoinBR.com

The best place for betting with bitcoin: BitBet.us
Duffer1
Full Member
***
Offline Offline

Activity: 227



View Profile
September 14, 2013, 08:02:28 PM
 #34


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
*****
Offline Offline

Activity: 756

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


View Profile
September 15, 2013, 01:00:52 AM
 #35

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

Use CoinBR to trade bitcoin stocks: CoinBR.com

The best place for betting with bitcoin: BitBet.us
Namworld
Hero Member
*****
Offline Offline

Activity: 743



View Profile
September 15, 2013, 01:51:57 AM
 #36

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
Legendary
*
Offline Offline

Activity: 934



View Profile
September 15, 2013, 05:33:15 PM
 #37

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
Sr. Member
****
Offline Offline

Activity: 282



View Profile
September 18, 2013, 11:22:26 PM
 #38

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

Namworld
Hero Member
*****
Offline Offline

Activity: 743



View Profile
September 18, 2013, 11:51:38 PM
 #39

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

I can't really help for that.
Pages: « 1 [2]  All
  Print  
 
Jump to:  

Sponsored by , a Bitcoin-accepting VPN.
Powered by MySQL Powered by PHP Powered by SMF 1.1.19 | SMF © 2006-2009, Simple Machines Valid XHTML 1.0! Valid CSS!