z

[znort987]

z

[Ben Walsh (beamer)]

Subbed. Should prove very useful. Thanks for sharing.

[sunnankar]

Very helpful. Sent a donation.

Perhaps Difficulty and Expected Difficulty? That would be helpful.

[EskimoBob]

Thank you. This is exactly what I need

[sunnankar]

Thank you. This is exactly what I need

Be sure to donate something to Znort so he can snort. 

[sunnankar]

Expected Difficulty?

Looks like Blockexplorer may be constipated and odd that Blockchain doesn't provide it; gives Error. Blockchain or the constipated Blockexplorer?

[locust]

Expected Difficulty?

Looks like Blockexplorer may be constipated and odd that Blockchain doesn't provide it; gives Error. Blockchain or the constipated Blockexplorer?

I haven't found a JSON RPC on blockchain.info for estimated difficulty,
and blockexplorer is not working right now.

If anyone knows of a site where estimated difficulty is available, or
how theymos calculates it, let me know.

http://blockchain.info/q/getdifficulty

Donation sent  

edit: sorry! Now I saw: estimated!

[Garr255]

Cool, I'll be using this in the future.

Thanks!

[sunnankar]

Cool, I'll be using this in the future.

Thanks!

No freeriding, donate!

[Garr255]

Cool, I'll be using this in the future.

Thanks!

No freeriding, donate!

Of course!

[John (John K.)]

Thank you for giving me this chance to learn Google spreadsheet!  
I've realized that the spreadsheet only recalculates if you edit it, and the ticker data would be outdated by a few hours. After some research, I've done this crude workaround here which theoretically would force the spreadsheet to recalculate quickly.

1. Put the field in your spreadsheet with this code:

Code:

=fetchTickerRefresh(ImportData("https://glbse.com/api/asset/ASSETNAME"&"?workaround="&INT(NOW()*1E3)&REPT(GoogleFinance("AAPL");0)),"fieldName")/100000000

where ASSETNAME is replaced with the asset you want to monitor and fieldName the type of data you want to fetch. (eg: latest_trade for the latest price.)

The workaround fetches the json code to that field, and with the workaround; refreshes it after a short period. (1 min+-)

2.Put this code inside your script:

Code:

function fetchTickerRefresh(input,fieldName
)
{
    var json = input

        
    json = Utilities.jsonParse(json)
    if ('undefined' == typeof(json))
        return 'Malformed JSON data returned by GLBSE for ticker ' + tickerName
        
    json = json[fieldName]
    if ('undefined' == typeof(json))
        return 'Unknown field ' + fieldName + ' for ticker ' + tickerName
          
   
    /*
        Fields are:

        {
            "ask":0
            "bid":0,
            "btc_vol_total":0,
            "latest_trade":0,
            "max":0,
            "min":0,
            "t24havg":0,
            "t24hvol":0,
            "t5davg":0,
            "t5dvol":0,
            "t7davg":0,
        }
    */

    return json
}

The input parameter should be the field where your ImportData code is(e.g.: A52), and the fieldName would be the field you wanted.

Example usage of code:

Code:

Code:

=fetchTickerRefresh(ImportData("https://glbse.com/api/asset/TYGRR.BOND-P"&"?workaround="&INT(NOW()*1E3)&REPT(GoogleFinance("AAPL");0)),"latest_trade")/100000000

This returns this:

Code:
1.048


Sorry for the bluntness in explaining as I'm in a hurry to go out. All credits go to znort987 for starting this project!

Edit: cleaner code

[sunnankar]

I am getting an odd error and it is only on some addresses. Other balances load just fine.

=fetchAddrInfo(D12, "final_balance")/100000000

"error: Address unavailable: http://blockchain.info/rawaddr/D12?limit=0 (line 10)" [D12=a valid bitcoin address]

When I click Run in the Script Editor I get this:

"Invalid JSON string. (line 18)"

Any idea why Google Docs is loading some addresses's balances but not others? Sure makes my spreadsheet look ugly with =sum() resulting in #ERROR!  

Could it be a problem with Blockchain.info? I also get an #ERROR! in another spreadsheet pulling in the difficulty. BlockChain.info has been having some issues lately.

[drlatino999]

I'll have to try this later today, thanks for the creation!

[sunnankar]

If the only problem is staleness, one way is what john did: add a bogus
call to something Google knows must be recalculated often, like Google
finance data.

Anyone written the function that would do this for a wallet address balance?

I also seem to be having a problem with the workaround and being able to reference a cell which has the ticker symbol. I would prefer not to hard code the ticket into the import function.

[John (John K.)]

If the only problem is staleness, one way is what john did: add a bogus
call to something Google knows must be recalculated often, like Google
finance data.

Anyone written the function that would do this for a wallet address balance?

I also seem to be having a problem with the workaround and being able to reference a cell which has the ticker symbol. I would prefer not to hard code the ticket into the import function.

I could do both and probably document them later as its almost 4 am here.

[sunnankar]

I could do both and probably document them later as its almost 4 am here.

That is great. You can get so much done being an early riser. Chirp, chirp! 

[btharper]

This is something I posted  in another thread https://bitcointalk.org/index.php?topic=82076.msg940941#msg940941.

This is what I've been using, hopefully it helps someone. Everything is already returned in bitcoins, not satoshis though.

Code:

//Shrink an array down to at most 3 elements
//Helper function
function get3(info) {
//If you want more or less payouts, change this 3
  while(info.length > 3)
    info.shift();
  return info;
}

//Fetches asset info based on ticker
function getInfo(ticker) {
  return glbseCachedFetch("/api/asset/" + ticker);
}

//Fetches dividend info based on ticker
function getDiv(ticker) {
  return glbseCachedFetch("/api/dividends/asset/" + ticker);
}

//Fetches the price of an asset
//Returns first non-zero price from the list of 24h average, 5 day average, 7 day average, or last trade
function getPrice(ticker) {
  var info = getInfo(ticker);
  return (info.t24havg/100000000) || (info.t5davg/100000000) || (info.t7davg/100000000) || (info.latest_trade/100000000);
}

//Gets the average dividend from passed in array
function getDivArray(info) {
  Logger.log("Into getDivArray");
  if(info === null) return null;
  if(info.length == 0) return 0;
  Logger.log("Not null or zero length");
  var divTotal = 0;
  for(var i = 0; i < info.length; i++) {
    divTotal += info[i].pps;
    Logger.log("Step " + i + "/" + info.length + " " + String(divTotal));
  }
  Logger.log(String(divTotal));
  return divTotal / info.length / 100000000;
}
//Gets average dividend of all payouts
function getAvgDividend(ticker) {
  Logger.log("Into getAvgDividend " + ticker);
  var info = getDiv(ticker);
  Logger.log(info);
  Logger.log("Got Info");
  return getDivArray(info);
}
//Gets Average dividend of last 3 payouts
function get3AvgDividend(ticker) {
  var info = get3(getDiv(ticker));
  return getDivArray(info);
}

//Fetches info from GLBSE, caches when fetched and attempts to use the cache
//Caches info for 30 minutes
function glbseCachedFetch(apiUrl) {
  var publicCache = CacheService.getPublicCache();
  var cached = publicCache.get("http://glbse.com" + apiUrl);
  if(cached !== null && JSON.parse(cached).length > 1 ){
    return JSON.parse(cached);
  } else {
    getSleep();
    //Browser.msgBox("Fetching");
    Logger.log("Fetching");
    var response = UrlFetchApp.fetch("http://glbse.com"+apiUrl);
    //check for status code
    var result = response.getContentText();
    if(response.getResponseCode() < 300)//Only save if good fetch
      publicCache.put("http://glbse.com"+apiUrl, result, 60 * 30);//30 * 60 seconds - 30 minutes
    return JSON.parse(result);
  }
}

//Allows small delay between multiple fetches to lighten the load on GLBSE site
function getSleep() {
  var delay = 50;
  var date = new Date();
  var now = date.getTime();
  var cache = CacheService.getPublicCache();
  var cacheKey = "sleepUntil";
  var timeString = cache.get(cacheKey);
  var time = parseInt(timeString);
  var sleep = time - now;
  
  Logger.log(cacheKey + " " + timeString);
  Logger.log("parseInt " + time);
  Logger.log("sleeping " + sleep);
  
  if((timeString === null) || (timeString == "NaN") || (time === null) || (time == NaN) || (sleep < 0)) {
    cache.put(cacheKey, String(now + delay));
  } else {
    cache.put(cacheKey, String(time + delay));
    Utilities.sleep(sleep);
  }
}
//Days between payouts, based on all payouts
function daysBetween(ticker) {
  var info = getDiv(ticker);
  if(info === null) return null;
  if(info.length < 2) return Infinity;
  var first = info[0];
  var last = info.pop();
  return (last.timestamp - first.timestamp) / info.length / 3600 / 24;
}
//Days between payouts, based on the last 3 payouts
function daysBetween3(ticker) {
  var info = get3(getDiv(ticker));
  if(info === null) return null;
  if(info.length < 2) return Infinity;
  var first = info[0];
  var last = info.pop();
  return (last.timestamp - first.timestamp) / info.length / 3600 / 24;
}

These are the functions I'm using in my sheet, and while I'm not quite ready to show off the whole thing I hope this might be helpful to someone. I'm looking to get these published to the gallergy, but approval takes a while. These can be used regularly in a sheet as a docs formula or as a function on other scripts. These may be used as if under any license or as public domain where available.

Tips to 18ef54UQ3t9ieqU3MebyqMHjCpzKrZzS5N are greatly appreciated if you've got the spare coin and find these useful.

[EskimoBob]

I atempted to häkk your original code to fetch data form MtGox but all I get is "Unknown field sell for ticker eur"

I am not a programmer and this code makes close to no sense to me at all

Code:

function fetchTicker(
    tickerName,     // e.g. :  'EUR'
    fieldName       // e.g. :  'buy'  (see code for full list of args)
)
{
    // Fetch live data from MtGox JSON feed
    var json = UrlFetchApp.fetch("https://mtgox.com/api/0/data/ticker.php?Currency=" + tickerName)
    if ('undefined' == typeof(json))
        return 'No data for ticker ' + tickerName + ' from MtGox'

    json = json.getContentText()
    if (json.length<=0)
        return 'MtGox Returned nothing ' + tickerName

    json = Utilities.jsonParse(json)
    if ('undefined' == typeof(json))
        return 'Malformed JSON data returned by MtGox for ticker ' + tickerName

    json = json[fieldName]
    if ('undefined' == typeof(json))
        return 'Unknown field ' + fieldName + ' for ticker ' + tickerName

    /*
        Fields are:

        {
            "high":0,
            "low":0,
            "avg":0,
            "vwap":0,
            "vol":0,
            "last_all":0,
            "last_local":0,
            "buy":0,
            "sell":0,
        
    */

    return json
}

[EskimoBob]

I got it working with Intersango JSON feed so I am happy for now. 

[jackmaninov]

Not sure if people have a better solution, but here's a function (formatting shamelessly ripped from znort987) to fetch the time weighted prices from bitcoincharts.com (e.g. the 24h USDBTC average). I think these are better to use than MTGox quotes if you're doing P&L calcs.

Code:

function fetchPrice(
    currency,     // currency to quote
    period       // optional report period of (30d, 7d, 24h) defaults to 24h
)
{
  if (arguments.length == 1)
    period='24h'
  
  var json = UrlFetchApp.fetch('http://bitcoincharts.com/t/weighted_prices.json')
  if ('undefined' == typeof(json))
    return 'Error retrieving quote data'
  
  json = json.getContentText()
  if (json.length<=0)
    return 'Quote data was invalid'
  
  json = Utilities.jsonParse(json)
  if ('undefined' == typeof(json))
    return 'Quote data was malformed JSON data'
      
  json = json[currency]
  if ('undefined' == typeof(json))
    return 'Quote of that currency not found'

  json = json[period]
    
  if ('undefined' == typeof(json))
    return 'Invalid quote period'
      
  return parseFloat(json) 
    
}