How to add currency exchange rates to spreadsheet in Numbers application

Question

Level 1

4 points

How to add currency exchange rates to spreadsheet in Numbers application

Trying to run this useful function, I enter all the values correctly - the PLN 985 code, the dollar code 840 and I receive the message "Argument 1 is invalid" (PLN). I carefully enter punctuation marks, I know that they are important when entering codes. Where can you find any practical and useful instructions for installing currency exchange currencies in the Numbers app? These pasted below look easy, but they do not work for some reason. I am asking for advice how to make this function work.

Add currency exchange rates to your spreadsheet

You can use the CURRENCY formula to pull data about currency exchange rates from the Internet and use them in your spreadsheet. When you add the formula, you must define these attributes:

Currency-1: This is the currency code for the currency from which you’re converting. Use quotes around your string.
Currency-2: This is the currency code for the currency to which you’re converting. Use quotes around your string.

For your third attribute, Numbers automatically suggests “price." If you enter 0 or omit this item entirely, the cell shows the exchange rate of currency-1 to currency-2, expressed as currency-2.

Mac OS X (10.5.8)

Posted on May 14, 2018 4:57 AM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

36,578 points

May 14, 2018 1:31 PM in response to Iwona

CURRENCY used the codes in Yahoo! Finance, not numeric codes. If you have the codes directly in the formula then you need to surround them by quotation marks (you can also put the codes in cells, and refer to those cells in the formula, in which case you don't need ""):

So for USD/PLN you can use:

=CURRENCY("PLN","USD")

For PLN/USD you can use:

=CURRENCY("USD","PLN")

Substitute ; for , in the formulas if your region uses , as a decimal separator.

SG

Reply

Answer 2

Iwona Author

Level 1

4 points

May 16, 2018 1:35 AM in response to SGIII

Hi SGill, thank you for your advice very much, it worked, but on only one cell, not on the whole column. It happened probably because I am working with one more value such us the day of exchange. Transactions I am entering took place on different days and this fact has to be taken into consideration somehow. Enclosed please find screenshots of a table that I am trying to fix. I will appreciate assistance in solving this problem. Best regards, Iwona R-K

Reply

Answer 3

Iwona Author

Level 1

4 points

May 16, 2018 5:49 AM in response to SGIII

Hi SGill, Thank you for quick response. I have changed the code as you suggested, but the problem remains. It applies change in only one row, not in the entire column.

I would like to have value changed in cell D3 by the code applied in column D (and other values entered below in similar manner on different dates in various amounts). Please look at the screenshot below. Does the code need some coordination with dates of transactions...?

Thank you for your patience and assistance once again...! Best regards, Iwona R-K

Reply

Answer 4

Iwona Author

Level 1

4 points

May 17, 2018 3:19 AM in response to SGIII

Hello SGill, Your formula works, conversion happens, but in a strange manner. It seems that the dates on which conversions happened were not taken into consideration. It is impossible that the same amount of money had the same value in 2016, 2017 and 2018. I have an impression that conversion code has to be somehow coordinated with dates of conversions. I hope that there is a way to do it. 🙂 Best regards, Iwona R-K

Reply

Answer 5

SGIII

Level 8

36,578 points

May 17, 2018 8:11 AM in response to Iwona

For "historical" rates you need to use CURRENCYH instead of CURRENCY, something like this:

In C1, filled down:

=CURRENCYH("USD","PLN",0,A2)

In D1, filled down:

=B2/C2

The red triangle means there is no FX rate for that day. You'll probably want to use the rate from the prior date in those cases.

SG

Reply

Answer 6

Iwona Author

Level 1

4 points

May 14, 2018 1:40 PM in response to SGIII

Hi SGill, your advice worked nicely, the code converts currencies without error. I still do not know how to apply the formula to make it work from the header down the column, so all the values entered in the cells of column are converted by the formula. I would truly appreciate assistance in solving this problem.

Reply

Answer 7

SGIII

Level 8

36,578 points

May 16, 2018 1:16 AM in response to Iwona

Add a column. In that column have a formula that multiplies the value in the original column by the exchange rate.

For example:

In A2 and cells below it: the original currency amounts

In B1 or any other cell: the exchange rate formula per above using CURRENCY

In C2, filled down the column: =A2*$B$1

SG

Reply

Answer 8

SGIII

Level 8

36,578 points

May 16, 2018 6:34 AM in response to Iwona

I can't tell where you are putting the formula. It needs goes in C2 or D2. Then fill the formula down its column. The idea is for the PLN value in column B to be multiplied by the exchange rate in cell $C$1.

SG