0

I was hoping to use array formula in conjunction with Google Finance to convert currencies on specific dates. I've been using a google form to enter basic expense information and the form adds a new row each time, so I can't simply copy and paste the formula throughout the sheet. ArrayFormula seems to fix that issue, but I haven't gotten it to work yet. I don't have any errors either. The only problem is that it only returns the result for the first date and currency pair and then prints it down the list of expenses incorrectly.

=ARRAYFORMULA(if(B2:B,query(GOOGLEFINANCE("CURRENCY:"&G2:G&$H$1,"price",B2:B),"SELECT Col1, Col2 label Col1 '', Col2 ''"),""))

In my formula Column B is the Date, Column G is the Currency and H1 is EUR. The query function was used to remove the date and close values that you get automatically from Google Finance. I'm sorry I don't have more experience with this to do it myself and I appreciate any help on the matter.

I've also linked a sheet here for your reference. It will make better sense there. https://docs.google.com/spreadsheets/d/1HNZvza9jR8gYk3QlVMgx2nfN4B40oDhyOYVthwQ4HzE/edit?usp=sharing

Best, Galeano

Improve this question
2
  • Look at ArrayFormula with GoogleFinance dynamic date - converting/applying it to your data layout will take some time and experimentation (if my time spent is anything to go by). But it is worth the effort because it is consistent with the problem that you have, and it is an efficient, elegant solution (as usual) by @player0.
    – Tedinoz
    Apr 19, 2020 at 3:45
  • Thank you very much, @Tedinoz! It did take me a while, but I figured that formula out for my particular issue. You directed me to the perfect place. Thank you to @player0 too! Take care!
    – Galeano
    May 6, 2020 at 14:01

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.