6

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.
player0
  • 124,011
  • 12
  • 67
  • 124
valentin.mu
  • 93
  • 1
  • 8

3 Answers3

2

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • cool! looks like it works, but it's really hard to understand what it does :) one more follow-up: can i make it multiply googlefinance result on corresponding currency cell value? (i have updated **dragging** column formula) – valentin.mu Dec 11 '19 at 18:23
  • sure, use: `=ARRAYFORMULA(IF(A2:A<>""; IF(C2:C<>""; C2:C; IF(D2:D<>""; 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; E2:E*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)); ))` – player0 Dec 11 '19 at 18:59
0

There is a new simpler and more flexible method now since the introduction of LAMBDA and its helper functions in Google Sheets in August 2022.

Assuming dates in A2:A, and amounts in UAH, USD, EUR in C2:C, D2:D, E2:E respectively, then the following formula will work, e.g. in cell F2:

=MAP(A2:A;C2:C;D2:D;E2:E;
    LAMBDA(date;uah;usd;eur;
        IFS(
        uah;uah;
        usd;usd*INDEX(GOOGLEFINANCE("currency:usduah";"price";date);2;2);
        eur;eur*INDEX(GOOGLEFINANCE("currency:euruah";"price";date);2;2);
        ISBLANK(date);)))

The trick here is that MAP(LAMBDA) calculates the specified formula for each row of the input array separately (effect similar to manually expanding the formula over the whole range), whereas ARRAYFORMULA passes the whole array as an argument to the formula (GOOGLEFINANCE is special and doesn't work intuitively with such input).

This general method with MAP(LAMBDA) can now be used to pass any arguments to GOOGLEFINANCE in a way one would otherwise expect to do with ARRAYFORMULA.

vilc
  • 183
  • 1
  • 8
-1

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))
user11982798
  • 1,878
  • 1
  • 6
  • 8