Sunday, June 16, 2013

yogi_WorkAround (Convoluted) To Use GoogleFinance Function For An Array (for upto specified maximum number) Of Entries

                                          Google Spreadsheet   Post  #1243
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Jun 16, 2013
user rajvivek (http://productforums.google.com/forum/?zx=j9xbgzjzha3t#!category-topic/docs/spreadsheets/BE1WrT2Zllo)
How to combine arrayformula with index?
Hi!

I am using this formula to know the exchange rate (closing) of a currency-pair of any particular date :  =INDEX(GoogleFinance("CURRENCY:USDINR";"close";A2) ; 2 ;2)

A2 = Date

How to re-imagine this formula to to include ArrayFormula so that the above formula does not need to be put in all the cells of B columns (by dragging or copy-pasting) as A column is full of dates and keep updating i.e., A2:A = Date ?

I tried this but it is not working : =ARRAYFORMULA(INDEX(GoogleFinance("CURRENCY:USDINR";"close";A2:A) ; 2 ;2))

The sample sheet is at this link : http://goo.gl/obJqV (Formula to be put in cell D2)

Thanks!
---
Yogi Anand's solution to somewhat similar problem at this post did the trick : https://productforums.google.com/forum/?fromgroups#!topic/docs/yVyfQZ1IHtY

Detailed solution for this problem at his (Yogi's) blog post here.

How to implement that solution into my problem is the real trick.
------------------------------------------------------------------------------------------------------------------------------------------------