1

I downloaded some data from PayPal and they report certain transactions in the destination currency (PHP), but I need to get the currency I sent it in (USD), however, because these are historical transactions, I need to get the currency conversion based on the day of the payment and be able to fill the formula down.

My sheet looks something like this, but with more rows.

A B C D
1 DATE GROSS AMOUNT PAID PAYPAL FEE
2 1-01-21 18000.00 230.00
3 2-05-21 17000.00 220.00
4 3-09-21 18300.00 260.00

What I have tried is this putting this into E2:

=GOOGLEFINANCE("Currency:PHPUSD", "price", A2)

So, I was just trying to get the PHP to USD conversion rate for that day (1-01-21) However, this is what it prints out for me:

       Date                Close
    1/8/2021 23:58:00   0.02080035

And it is impossible to copy down because every time I enter the formula, it adds the Date and Close columns. If I try to fill down from the original formula which resides in the Date field as seen above, it puts the error:

Error
Array result was not expanded because it would overwrite data in V7.

1 Answer 1

0

Use

=INDEX(GOOGLEFINANCE("Currency:PHPUSD", "price", A2),1)

or

=ArrayConstrain(GOOGLEFINANCE("Currency:PHPUSD", "price", A2),1,1)

1
  • Thanks for the answer. If I use the ArrayConstrain option I get "Unkown Function Array Constrain" If I use the INDEX answer I get the same issue where it prints out DATE and CLOSE. Here, I made a test sheet: docs.google.com/spreadsheets/d/…
    – CRAIG
    Jan 25, 2022 at 19:08

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.