Historical data

[LesterCovax]

[Project-42]

Hi, Hope this can help. I tried in the past using ImportJSON from this API: https://min-api.cryptocompare.com/data/histoday?aggregate=1&e=CCCAGG&fsym=ETH&limit=31&tsym=GBP

But was not able to make it work correctly.

Im sure you can find a way to add that to the code :D

[RJMoise]

I wrote a quick function to save data to a sheet called history, not sure if its exactly what you are talking about or not. Basically I have a 4 hour trigger on my getData and getHistory, so when the data updates its sent to the history page. It only saves data since you started using it, not the whole history of the coin. I'm sure a more talented coder could enhance its abilities. I've attached a pic of my history sheet and what it looks like.

[LesterCovax]

Nice! Yeah, pretty much that. IMO 1/day would be fine but the user could define their trigger. I've already been running into execution time limits with sheets since the overhaul. They annoyingly have limits on items per call and script execution time. I think the path forward is splitting up the custom functions/overall scripts more.

Is that graph on the left just using the wrong axis? Were you trying to map balance/value of each currency over time?

[RJMoise]

I found that every 4 hours was to often as well. Of course the trigger is person specific so it can be whatever is needed. Yeah I think the left graph looks goofy Im not a chart guy by any means and I was trying to visualize each coins gains/losses but I ended up removing it and just leaving the totals chart.

[dbuskariol]

@LeMoise could you share the getHistory() function you wrote? This is exactly what i'm after 😄

[RJMoise]

This is my (probably) poorly written history function. I have a sheet that multiples the coins BTC value by the amount of each coin I hold and places them in a column. Then I just read in those values once a day and record them. I then calculate the change between yesterdays total and todays to get an rough gain/loss percentage per day.

function history(){
var test = (new Date())
  for (var i=1; i<=100; i++) {
  var cell = ssHist.getRange('A'+i)
    if (cell.isBlank()) {
      ssHist.getRange('A'+i).setValue(ssHodl.getRange('D5').getValue());
      ssHist.getRange('B'+i).setValue(ssHodl.getRange('D6').getValue())
      ssHist.getRange('C'+i).setValue(ssHodl.getRange('D7').getValue())
      ssHist.getRange('D'+i).setValue(ssHodl.getRange('D8').getValue())
      ssHist.getRange('E'+i).setValue(ssHodl.getRange('D9').getValue())
      ssHist.getRange('F'+i).setValue(ssHodl.getRange('D10').getValue())
      ssHist.getRange('G'+i).setValue(ssHodl.getRange('D11').getValue())
      ssHist.getRange('H'+i).setValue(ssHodl.getRange('D12').getValue())
      ssHist.getRange('I'+i).setValue(ssHodl.getRange('D13').getValue())
      ssHist.getRange('J'+i).setValue(ssHodl.getRange('D14').getValue())
      ssHist.getRange('K'+i).setValue(ssHodl.getRange('D15').getValue())
      ssHist.getRange('L'+i).setValue(ssHodl.getRange('D16').getValue())
      ssHist.getRange('M'+i).setValue(ssHodl.getRange('D20').getValue())
      ssHist.getRange('N'+i).setValue(ssHodl.getRange('E20').getValue())
      ssHist.getRange('O'+i).setValue(((ssHist.getRange('M'+i).getValue())/(ssHist.getRange('M'+(i-1)).getValue()))-1)
      ssHist.getRange('P'+i).setValue(new Date())
      break;
    }
  }

[JSterling8]

Looks like a really good start. I found a way to clean it up a little bit. In your sheet, have a Date column that increments by 1 day at a time. Somewhere else in the same sheet, have a Todays Date cell with this in it =MATCH(today(),'Crypto Value Over Time'!B1:B), (the name of my sheet is "Crypto Value Over Time", and the date column is B. This will put the current date's row number into a box for you. Right click on todays row index and select "Define Named Range" and in the box that appears on the right set the name to todaysRowNumberCrypto. You can also define named ranges in your sheet that has the current valuations of your tokens. I name mine like vtcCurrentValue. Then bringing that all together you can make a function like this:

function recordDailyCryptoNetWorth() {
  
  var netWorthSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Net Worth");
  var cnwOverTimeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Crypto Value Over Time");
  
  var todaysRowNumber = cnwOverTimeSheet.getRange("todaysRowNumberCrypto").getValue();
  
  cnwOverTimeSheet.getRange(todaysRowNumber, 3).setValue(netWorthSheet.getRange("btcCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 4).setValue(netWorthSheet.getRange("kmdCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 5).setValue(netWorthSheet.getRange("xmrCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 6).setValue(netWorthSheet.getRange("bchCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 7).setValue(netWorthSheet.getRange("vtcCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 8).setValue(netWorthSheet.getRange("venCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 9).setValue(netWorthSheet.getRange("pivxCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 11).setValue(netWorthSheet.getRange("arkCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 12).setValue(netWorthSheet.getRange("omgCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 13).setValue(netWorthSheet.getRange("zecCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 14).setValue(netWorthSheet.getRange("qspCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 15).setValue(netWorthSheet.getRange("ethCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 16).setValue(netWorthSheet.getRange("viaCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 17).setValue(netWorthSheet.getRange("ubqCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 18).setValue(netWorthSheet.getRange("lskCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 19).setValue(netWorthSheet.getRange("expCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 20).setValue(netWorthSheet.getRange("dashCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 21).setValue(netWorthSheet.getRange("dogeCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 22).setValue(netWorthSheet.getRange("hsrCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 23).setValue(netWorthSheet.getRange("ltcCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 24).setValue(netWorthSheet.getRange("qtumCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 26).setValue(netWorthSheet.getRange("xlmCurrentValue").getValue());
  cnwOverTimeSheet.getRange(todaysRowNumber, 27).setValue(netWorthSheet.getRange("stratCurrentValue").getValue());
}

The output sheet looks like this:

The sheet it reads from looks like this:

My rate updating code looks like this:

function updateRates() {
  
  var netWorthSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Net Worth");
  
  var btcUsdRate = getRateForCurrencyId('bitcoin');   
  var kmdUsdRate = getRateForCurrencyId('komodo');
  var xmrUsdRate = getRateForCurrencyId('monero');
  var bchUsdRate = getRateForCurrencyId('bitcoin-cash'); 
  var vtcUsdRate = getRateForCurrencyId('vertcoin');
  var venUsdRate = getRateForCurrencyId('vechain');   
  var pvxUsdRate = getRateForCurrencyId('pivx');   
  var arkUsdRate = getRateForCurrencyId('ark');
  var omgUsdRate = getRateForCurrencyId('omisego');
  var zecUsdRate = getRateForCurrencyId('zcash');
  var qspUsdRate = getRateForCurrencyId('quantstamp');
  var ethUsdRate = getRateForCurrencyId('ethereum');
  var viaUsdRate = getRateForCurrencyId('viacoin');
  var ubqUsdRate = getRateForCurrencyId('ubiq');
  var lskUsdRate = getRateForCurrencyId('lisk');
  var expUsdRate = getRateForCurrencyId('expanse');
  var dashUsdRate = getRateForCurrencyId('dash');
  var dogeUsdRate = getRateForCurrencyId('dogecoin');
  var hsrUsdRate = getRateForCurrencyId('hshare');
  var ltcUsdRate = getRateForCurrencyId('litecoin');
  var qtumUsdRate = getRateForCurrencyId('qtum');
  var xlmUsdRate = getRateForCurrencyId('stellar');
  var stratUsdRate = getRateForCurrencyId('stratis');
  
  
  netWorthSheet.getRange("btcUsdRate").setValue(btcUsdRate);
  netWorthSheet.getRange("kmdUsdRate").setValue(kmdUsdRate);
  netWorthSheet.getRange("xmrUsdRate").setValue(xmrUsdRate);
  netWorthSheet.getRange("bchUsdRate").setValue(bchUsdRate);
  netWorthSheet.getRange("vtcUsdRate").setValue(vtcUsdRate);
  netWorthSheet.getRange("venUsdRate").setValue(venUsdRate);
  netWorthSheet.getRange("pvxUsdRate").setValue(pvxUsdRate);
  netWorthSheet.getRange("arkUsdRate").setValue(arkUsdRate);  
  netWorthSheet.getRange("omgUsdRate").setValue(omgUsdRate);  
  netWorthSheet.getRange("zecUsdRate").setValue(zecUsdRate);  
  netWorthSheet.getRange("qspUsdRate").setValue(qspUsdRate);  
  netWorthSheet.getRange("ethUsdRate").setValue(ethUsdRate);
  netWorthSheet.getRange("viaUsdRate").setValue(viaUsdRate);
  netWorthSheet.getRange("ubqUsdRate").setValue(ubqUsdRate);
  netWorthSheet.getRange("lskUsdRate").setValue(lskUsdRate);
  netWorthSheet.getRange("expUsdRate").setValue(expUsdRate);
  netWorthSheet.getRange("dashUsdRate").setValue(dashUsdRate);
  netWorthSheet.getRange("dogeUsdRate").setValue(dogeUsdRate);
  netWorthSheet.getRange("hsrUsdRate").setValue(hsrUsdRate);
  netWorthSheet.getRange("ltcUsdRate").setValue(ltcUsdRate);
  netWorthSheet.getRange("qtumUsdRate").setValue(qtumUsdRate);
  netWorthSheet.getRange("xlmUsdRate").setValue(xlmUsdRate);
  netWorthSheet.getRange("stratUsdRate").setValue(stratUsdRate);
}


function getRateForCurrencyId(currencyId) {
  
  var url = 'https://api.coinmarketcap.com/v1/ticker/' + currencyId + '/';
  var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
  var json = response.getContentText();
  var data = JSON.parse(json);
  
  return parseFloat(data[0]['price_usd']);
}

Hope this helps.