1

I am trying to convert money amounts in USD to EUR, but I want to do it in relation to date of transaction taking place due to dyncamic exchange rates.

Idea is to use googlefinance function with index function, along with date function, which will be made of right, mid, and left functions. All of that should be wrapped with arrayformula. Numbers should be rounded using round.

Non-working example:

enter image description here

Don't mind ;, this is instead of , in my google sheets language settings.

Formula: =ARRAYFORMULA(IF(B2:B="";"";ROUND(INDEX(B2:B*GOOGLEFINANCE("CURRENCY:USDEUR";"price";DATE(RIGHT(A2:A;4);MID(A2:A;4;2);LEFT(A2:A;2)));2;2);2)))

Formula (with , instead of ;): =ARRAYFORMULA(IF(B2:B="","",ROUND(INDEX(B2:B*GOOGLEFINANCE("CURRENCY:USDEUR","price",DATE(RIGHT(A2:A,4),MID(A2:A,4,2),LEFT(A2:A,2))),2,2),2)))

Since I'm pretty sure this formula should work in "normal circumstances", I've replicated it leaving out arrayformula, and full ranges (such as B2:B, etc), and it works once I drag down the formula.

Working example: enter image description here

Formula: =IF(F2="";"";ROUND(INDEX(F2*GOOGLEFINANCE("CURRENCY:USDEUR";"price";DATE(RIGHT(E2;4);MID(E2;4;2);LEFT(E2;2)));2;2);2))

Formula (with , instead of ;): =IF(F2="","",ROUND(INDEX(F2*GOOGLEFINANCE("CURRENCY:USDEUR","price",DATE(RIGHT(E2,4),MID(E2,4,2),LEFT(E2,2))),2,2),2))

Does anyone have idea what's going on with the first case, i.e. why it doesn't work? I believe it has to be something with those full ranges (e.g. B2:B, A2:B, etc), but not sure why...

player0
  • 124,011
  • 12
  • 67
  • 124
NadanSHA
  • 77
  • 6
  • share a copy of your sheet – player0 Aug 02 '20 at 12:22
  • https://docs.google.com/spreadsheets/d/1CgpCRyjFTiDspEFLDJtqxZQI-a7SH3ELhQztvGfywLs/edit?usp=sharing There you go. – NadanSHA Aug 02 '20 at 12:31
  • Does this answer your question? [ArrayFormula with GoogleFinance dynamic date](https://stackoverflow.com/questions/59273160/arrayformula-with-googlefinance-dynamic-date) – vilc Sep 20 '22 at 06:42

1 Answers1

1

GOOGLEFINANCE is already an ARRAYFORMULA type formula so try like this:

=ARRAYFORMULA(IF(B2:B="";;ROUND(B2:B*IFNA(VLOOKUP(A2:A+0,9986111111; 
 GOOGLEFINANCE("CURRENCY:USDEUR"; "price"; MIN(A2:A); MAX(A2:A)+1); 2; 1)); 2)))

enter image description here

player0
  • 124,011
  • 12
  • 67
  • 124
  • :) Wiat, how. I only got zeros. You can see here: https://docs.google.com/spreadsheets/d/1CgpCRyjFTiDspEFLDJtqxZQI-a7SH3ELhQztvGfywLs/edit#gid=0 It also says "VLOOKUP only takes 4 arguments, but this is argument 5". – NadanSHA Aug 02 '20 at 12:59
  • you changed semicolon to comma instead of dot in number 0,9986111111 (should be: 0.9986111111 - if you use commas instead of semicolons) see your sheet – player0 Aug 02 '20 at 19:57
  • :O magic. Thank you very much. Btw, if you find time, would you please shortly describe what are you doing, since I really don't understand how formula does what it does. For example, what is IFNA, what is 0.9986111111 (i googled it, i says it is 1438 minutes, which is almost one day, but why :D). Also how does this vlookup works at all, it looks nothing like vlookup i'm used to... :D – NadanSHA Aug 02 '20 at 20:10
  • 1
    if VLOOKUP does not find any value it will produce #N/A error. to remove this error from output we use IFNA around VLOOKUP so if value is not found it will output empty row. 0.9986111111 is indeed time "23:58:00". if you run just GOOGLEFINANCE formula you can notice that first column includes date and also time but your A column consists only from dates. (keep in mind that dates are just formated numbers so date 09.07.2020 = 44021 but 09.07.2020 23:58:00 = 44021.9986111111) so the idea is to add "23:58:00" to your dates in A column and then pair it via VLOOKUP with GOOGLEFINANCE timestamps – player0 Aug 02 '20 at 20:24
  • 1
    This makes sense to me now. Thank you very much! – NadanSHA Aug 02 '20 at 20:27