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   Jun 16, 2013
user rajvivek (!category-topic/docs/spreadsheets/BE1WrT2Zllo)
How to combine arrayformula with index?

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 : (Formula to be put in cell D2)

Yogi Anand's solution to somewhat similar problem at this post did the trick :!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.