CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
Ruben
Ruben

Reputation: 11

How to make spreadsheet that converts different currencies to Euro, at a precise date exchange rate? Google Sheets

Hi everyone noob here looking for help,

I am trying to make a spreadsheet with 4 columns: A - Date; B - Multiple Currency Values with ($,€,£); C -Exchange rate; D - B*C

In all rows depending on the symbol portraited in the cell in column B, I want the cell in column C to show the exchange rate of that currency to Euros in the date in the cell in column A, so I can then multiply the value to the exchange rate and get it in Euros.

The formula I am trying to use in Column C sure is wrong but might have something right in it, could you help me debug it please?

=IF(REGEXMATCH($B3,"£"),index(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", $A3), 2, 2), IF(REGEXMATCH($B3,"$"),index(GOOGLEFINANCE("CURRENCY:USDEUR", "price", $A3), 2, 2), IF(REGEXMATCH($B3,"€"),index(GOOGLEFINANCE("CURRENCY:EUREUR", "price", $A3), 2, 2),"")))

Thanks,

Upvotes: 0

Views: 357

Answers (1)

player0
player0

Reputation: 1

format B column as Plain text

enter image description here

use in C2 and drag down:

=IF(REGEXMATCH(B2, "£"), 
 INDEX(GOOGLEFINANCE("CURRENCY:GBPEUR", "price", A2), 2, 2), 
 IF(REGEXMATCH(B2, "\$"), 
 INDEX(GOOGLEFINANCE("CURRENCY:USDEUR", "price", A2), 2, 2), 
 IF(REGEXMATCH(B2, "€"), 1, )))

enter image description here

use in D2:

=ARRAYFORMULA(IFNA(REGEXEXTRACT(B2:B, "\d+.\d+|\d+")*C2:C))

enter image description here

Upvotes: 1

Related Questions