ArrayFormula with GoogleFinance dynamic date

3
votes

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.
2
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupgoogle-finance
What about changing $A2 to $A2:A? - Scott Craner
share a copy of your sheet with example of desired output - player0
@ScottCraner it passes range to googlefinance, not sure it'll work as it expects single date. - valentin.mu
@player0 updated question with link to sheet - valentin.mu

2 Answers

2
votes

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

0
votes

Try This One:

=arrayformula(
   IF(query(arrayformula(if(A2:A="",False,True)), 
      "Select * where Col1=True"),
      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))