0

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.

DTX
  • 192
  • 1
  • 12

2 Answers2

0

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.
  }
}
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
0

There is a way to achieve it without Apps Script. Hope this will help those looking for the answer after 2023.

GOOGLEFINANCE API supports fetching historical data. Here's how the updated formula would look like, assuming you have the date you want to get the conversion rate for in the cell C3:

IF(B2 = "EUR"; A2; A2*INDEX(GOOGLEFINANCE("CURRENCY:" & B2 &"EUR", "price", C3), 2, 2))

From the GOOGLEFINANCE documentation:

Real-time results will be returned as a value within a single cell. Historical data, even for a single day, will be returned as an expanded array with column headers.

INDEX is used to get rid of the column headers based on this answer by Ben Liebrand.

FURDUY A
  • 1