Search code examples
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupgoogle-finance

ArrayFormula with GoogleFinance dynamic date

First of all, i'm not a powerful sheets user :)

I'm trying to use GOOGLEFINANCE to calculate amounts in multiple currencies. I use this formula:

=IF($A2;
IF( 
$C2:C; 
$C2:C;
IF(
  $D2:D; 
  $D2:D*INDEX(GoogleFinance("CURRENCY:USDUAH";"close";$A2);2;2);
  $E2:E*INDEX(GoogleFinance("CURRENCY:EURUAH";"close";$A2);2;2)
));
0)

A-column contains dates, C,D,E - amounts in 3 different currencies. IFs are just to prioritize columns :)

The formula works well but i need to "extend" it each time i add row - to increment $A2 -> $A3 to get rate for specified date.

I try to use ArrayFormula but it turns out it keeps reference to $A2 so i get same rate irrelevant from date specified in A-cells.

I have created sample sheet to illustrate: https://docs.google.com/spreadsheets/d/1K2TbGIWl7JacYKiWgwwmJfelxJ-7fa9F9obp5XswW18/edit?usp=sharing

I have allowed editing by anyone, so if you decide to edit - please don't remove anything :) also you can drop your username in sticky row(above your proposed solution)

  1. Is there a way to apply ArrayFormula to this to make it work?
  2. Maybe you can provide more readable solution to nested IFs.
Solution

  • try:

    =ARRAYFORMULA(IF(A2:A<>""; 
 IF(C2:C<>""; C2:C; 
 IF(D2:D<>""; VLOOKUP(TO_TEXT(A2:A); 
 TO_TEXT(QUERY(GOOGLEFINANCE("CURRENCY:USDUAH"; 
 "close"; MIN(A:A); MAX(A:A)+1);
 "offset 1 format Col1'dd.mm.yy'"; 0)); 2; 0)*1; 
 VLOOKUP(TO_TEXT(A2:A); 
 TO_TEXT(QUERY(GOOGLEFINANCE("CURRENCY:EURUAH"; 
 "close"; MIN(A:A); MAX(A:A)+1);
 "offset 1 format Col1'dd.mm.yy'"; 0)); 2; 0)*1)); ))

    0