Google-sheets – Keeping track of prices, recording every change above some threshold

google sheets

An updated price comes in to a cell each minute "googlefinance()".
In a second cell we start with a reference price which is typed in manually. The initial value is equal to the first price that comes in on the price feed.

As time goes on, if the a new updated price varies from the reference price by more or less than 0.5%; We want to replace the reference price cell with the most recent updated price and then add the most recent updated price to a new cell in a column that records all of the changes to the reference price.

Is it possible to do this with a custom spreadsheet function?

Best Answer

You can't do that with a function. You need a script.

Here is an example to get you started. It assumes updated price in A1, reference price in B1, and historical record in column C. After entering this script in Script Editor, add a trigger (Menu > Resources) to run it every minute.

The code is more or less self-explanatory. It assumes you are using this particular sheet just to track the price, so there is no other data to worry about. The command insertRowAfter adds a row every time. Eventually you are going to hit a hard limit on the number of cells in a sheet, so it's best to delete all other columns (and perhaps modify the script so that only one column is used). 

function checkPrice() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Price Tracker");
  var updatedPrice = sheet.getRange('A1').getValue();
  var referencePrice = sheet.getRange('B1').getValue();
  if (Math.abs(updatedPrice-referencePrice) > 0.005*referencePrice) {
    sheet.getRange('B1').setValue(updatedPrice);
    var lastRow = sheet.getLastRow();
    sheet.getRange(lastRow + 1, 3).setValue(updatedPrice);
    sheet.insertRowAfter(lastRow + 1);
  }
}
Related Topic