Tuesday, August 6, 2013

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:
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,

5 comments:

  1. Hi Kishan,

    I'm not sure if this solution will help and I don't know if I explained correctly. Please see the example spreadsheet:
    https://docs.google.com/spreadsheet/ccc?key=0AnEYa1POf3VhdGt4aWk0MTIxVTlvRm56dmpFZ21Yd0E&usp=sharing

    1. I want to place the current exchange rate on the spreadsheet for reference. (Cell B1)
    2. I want to freeze the amounts that display on Column D. For example, Apple price (C7) is multiplied by the current rate (B1). The result, R$4.60 (D7) needs to be frozen and so on for the other lines.
    3. The Reais amount (in column d) needs to be frozen with the rate of the day I input the amount in column C.

    Does that make sense? Thanks ahead for the help!

    ReplyDelete
  2. Hi,

    how can I use IMPORTXML() instead of GOOGLEFINANCE()?

    ReplyDelete
  3. I am wondering how to replace GOOGLEFINANCE with another function?

    ReplyDelete
  4. I think a better solution is to use the date parameter to GOOGLEFINANCE to look up a value as of a specific date. See documentation for details - https://support.google.com/docs/answer/3093281?hl=en

    ReplyDelete
  5. Note that when GOOGLEFINANCE retrieves historical results it will return an array with column headers and the date. If you want a singular value, use INDEX to extract the second row, second column, for example:
    =INDEX(GoogleFinance("GOOG","price",DATE(2020,1,1)),2,2)

    ReplyDelete