Google Spreadsheet How To Freeze Dynamic Values

Question:

( by raphaefeli )

Hello,

I'm creating a spreadsheet with costs related to Brazilian Reais and I need to freeze the day's exchange rate.

Right now I'm doing this:

1. =GoogleFinance("CURRENCY:USDBRL") - This gives me today's exchange rate. (2.30). 

2. Today, I want to divide an amount, for example, 100 by the exchange rate which gives me USD 43.47.  

3. Let's say, tomorrow I divide another 100 but the exchange is 2.45, giving me USD 40.81.

I want to freeze these amounts so that I know how much I'm paying for an item based on a day's exchange rate and not have these amounts changed when the rate changes and so on... Does anyone know if this is possible?

Thanks!

Solution:

Have a look at the following animated screenshot:

I have the following formula in Cell A1:

=GoogleFinance("CURRENCY:USDBRL")

And when I insert the following formula in Cell A4:
=GOOGLEFINANCE("CURRENCY:USDBRL") 

The custom function "freeze()" will check if the formula has "=GOOGLEFINANCE" (that is all capital letters) then it will take the value of it and it will again put it back but it will remove the formula. So the value will be freezed.

Have a look at the following code:

///////////////////////////////////////

function onEdit(e) {
  freeze();
}

function freeze() {
  Utilities.sleep(1500);
  var activeCell = SpreadsheetApp.getActiveSheet().getActiveCell();
  var formula = activeCell.getFormula();
  if( formula.slice( 0 , 14 - formula.length ) == "=GOOGLEFINANCE" ) {
    var temp = activeCell.getValue();
    activeCell.setValue(temp);
  }

}

///////////////////////////////////////

put the above code in your script editor, and then you can use it directly in your Google Spreadsheet.


And If you are not much familiar with scripts then check out the following link:
http://igoogledrive.blogspot.com/2012/08/how-to-write-script-in-google.html 

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:

http://igoogledrive.blogspot.com/p/paid-projects.html

If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: http://igoogledrive.blogspot.com/2012/09/donate.html 

Thanks,

Kishan.

iGoogleDrive