Google App Script: Convert Currency, but keep value from moment of conversion

Question

I've built myself a rather fancy finance tracking spreadsheet, but have run into the following problem:

I track my expenses with two values: amount and currency.

Then, in the next column, I use GOOGLEFINANCE() to convert those values to my native currency (Euro).

My formula looks like this: IF(B2 = "EUR"; A2; A2*GOOGLEFINANCE("CURRENCY:" & B2 &"EUR"))

If this row's currency (B column) is EUR, don't do any conversion and just use the value from the A column.

If B is not EUR, take the currency in B and convert the monetary value in A from that currency into EUR.

The problem is that this is constantly updating. In the moment this is okay, but if I go back to it in a few years some values might be very different from the original exchange rate.

What I would like to do is to fill out the field with today's exchange rate and leave the value like that (i.e. static, not dynamic).

Does anyone have any experience with this? I guess it'd also be a common problem for building stock-price record sheets.

Ed Nelson Ed Nelson · Accepted Answer · 2017-07-30T20:31:11

I also have a lot of finance tracking sheets and you are right, freezing a returned value is a common need. The below script is set to run onEdit of cell B2. (You could change it for column B). It get cell C2 using offset. It gets the display value of the formula result of your formula calculation and overwrites the formula with the value. Since the formula is gone, it won't recalculate.

function onEdit(e) {
  if(e.range.getA1Notation()=="B2"){
    freeze(e.range.getA1Notation());
  }}
function freeze(c){
  var ss =SpreadsheetApp.getActiveSpreadsheet()
  var s=ss.getActiveSheet()
  var activeCell =c 
  var oc =SpreadsheetApp.getActiveRange().offset(0, 1).getA1Notation()
  var val=s.getRange(oc).getDisplayValue()
  s.getRange(oc).setValue(val);
}

Here is an improved version to check the sheet and handle all of column B, not just B2. Change the formula in B2 to this and copy down:

=IF(or(B2 = "EUR",B2=""), A2, A2*GOOGLEFINANCE("CURRENCY:" & B2 &"EUR"))

Then change onEdit to this. Change the sheet name as needed:

function onEdit(e){
  var sheet = e.source.getActiveSheet().getName()
  var c = e.range.getA1Notation()
  var col=e.range.getColumn()
  var lcval=e.value.toLowerCase()//to lower case to ignore case.
  if(lcval==""){return}
  if(sheet=="Sheet1" && col==2 && lcval != "eur"){ //Change sheet name as needed.
    freeze(c)//call freeze wuth active cell address.
  }
}

Google App Script: Convert Currency, but keep value from moment of conversion

1 Answers