Bitcoin Forum
November 17, 2017, 06:06:12 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 7189 times)
Namworld
Hero Member
*****
Offline Offline

Activity: 743



View Profile
February 01, 2013, 03:59:18 AM
 #1

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

Blockchain.infoBitcoin Address Balance
MtGoxWeighted average, any currency
BTCT.coTicker data, balance and portfolio data
BitFunderTicker data, balance and portfolio data
MPExAverage trading prices
Havelock InvestmentsTicker 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")
Advertised sites are not endorsed by the Bitcoin Forum. They may be unsafe, untrustworthy, or illegal in your jurisdiction. Advertise here.
TradeFortress
VIP
Legendary
*
Offline Offline

Activity: 910


View Profile
February 01, 2013, 04:05:01 AM
 #2

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

Activity: 743



View Profile
February 01, 2013, 04:11:17 AM
 #3

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

Activity: 1008



View Profile WWW
February 01, 2013, 05:45:16 AM
 #4

That is really cool.

I'm not a Coinbase fan -- I placed a buy order, they took the funds out of my account, then a week later the price went up and they canceled the buy and closed my account.  You've been warned.  Use a different exchange.
Namworld
Hero Member
*****
Offline Offline

Activity: 743



View Profile
February 09, 2013, 11:34:24 PM
 #5

Bumping these. Possibilities are endless with this. Makes trading a breeze.
EskimoBob
Legendary
*
Offline Offline

Activity: 910


Quality Printing Services by Federal Reserve Bank


View Profile
February 10, 2013, 01:45:20 PM
 #6

Good stuff. Thank you. 

While reading what I wrote, use the most friendliest and relaxing voice in your head.
BTW, Things in BTC bubble universes are getting ugly....
EskimoBob
Legendary
*
Offline Offline

Activity: 910


Quality Printing Services by Federal Reserve Bank


View Profile
February 10, 2013, 02:05:18 PM
 #7


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

 


 

While reading what I wrote, use the most friendliest and relaxing voice in your head.
BTW, Things in BTC bubble universes are getting ugly....
burnside
Legendary
*
Offline Offline

Activity: 1008



View Profile WWW
February 13, 2013, 08:36:03 AM
 #8

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.

I'm not a Coinbase fan -- I placed a buy order, they took the funds out of my account, then a week later the price went up and they canceled the buy and closed my account.  You've been warned.  Use a different exchange.
Bit_Happy
Legendary
*
Offline Offline

Activity: 1638


A Great Time to Start Something!


View Profile
February 13, 2013, 09:07:35 AM
 #9

Very nice work.

Namworld
Hero Member
*****
Offline Offline

Activity: 743



View Profile
March 24, 2013, 01:23:32 AM
 #10

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

Activity: 574


For Science


View Profile WWW
March 24, 2013, 01:28:07 AM
 #11

Brilliant!
funkymunky
Sr. Member
****
Offline Offline

Activity: 283



View Profile
June 03, 2013, 11:07:08 AM
 #12

Thank you ever so much
Eric Muyser
Full Member
***
Offline Offline

Activity: 196


You can't kill math.


View Profile
June 05, 2013, 02:39:17 AM
 #13

Awesome Namworld.

How'd you get a Bitfunder API key?

@EricMuyser | EricMuyser.com | OTC - "Defeat is a state of mind; no one is ever defeated until defeat has been accepted as a reality" - Bruce Lee
Franktank
Hero Member
*****
Offline Offline

Activity: 574


For Science


View Profile WWW
June 05, 2013, 02:43:19 AM
 #14

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

Activity: 743



View Profile
June 05, 2013, 03:27:07 AM
 #15

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

Activity: 574


For Science


View Profile WWW
June 05, 2013, 03:31:11 AM
 #16

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

Activity: 743



View Profile
June 05, 2013, 03:32:23 AM
 #17

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

Activity: 266


Science!


View Profile
August 02, 2013, 03:50:03 AM
 #18

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

Activity: 266


Science!


View Profile
August 10, 2013, 05:05:39 PM
 #19

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

Activity: 86


View Profile
August 15, 2013, 08:06:58 AM
 #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
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!