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:
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:
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:
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:
Hope you’ll find this useful! 🙂
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.