Software:
Google Sheets
This is slightly off-topic in this blog, but since this subject has seriously baffled me, I decided to document and share the solution.
Getting a currency conversion rate in into a Google Sheets expression is fairly simple:
The expression:
GoogleFinance("CURRENCY:USDILS")Simply returns the current conversion rate, in this case between US Dollars – “USD” to Israeli Shekel – “ILS”, expressed as the term “USDILS”.
So what’s the problem?
When using the GoogleFinance function to retrieve a historic conversion value like so:
GoogleFinance("CURRENCY:USDILS","price","1/1/2012")the function doesn’t return the numeric value of the price, but instead, creates a table (see image below):
This seems like a nice feature.. so what’s the problem?
If you need to use the conversion rate within a larger scope calculation, the return value will be the string “Date” instead of a numeric value representing the conversion rate, and that will cause an error:
330*index(GoogleFinance("CURRENCY:USDILS","price","1/1/2012"),2,2)
So.. what’s the solution?
The solution is to use the index function to retrieve the wanted value from the table returned by GoogleFinance.
In this case index(weird_surprising_tabel,2,2) returns the wanted value:
330*index(GoogleFinance("CURRENCY:USDILS","price","1/1/2012"),2,2)
That’s it.
Hope you’ll find this useful! 🙂
P.S.
I do use Google Sheets for subjects directly related to CG work, and I should definitely bring some examples of that here in this blog.
