CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgs-vlookupgoogle-finance
valentin.mu
valentin.mu

Reputation: 93

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.

Upvotes: 6

Views: 2408

Answers (3)

vilc
vilc

Reputation: 333

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.

Upvotes: 1

player0
player0

Reputation: 1

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

Upvotes: 2

user11982798
user11982798

Reputation: 1908

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))

Upvotes: -1

Related Questions