Convert currency using particular date

Question

Level 2

231 points

Convert currency using particular date

Hi, I wish to convert a cell that contains a USD value to another currency, AUD value. I'd like to use the date in another cell in the row to be the date that the conversion is calculated, if possible. I played around with the CURRENCY formula, but can't get it to work.

In the screenshot, the source cell for conversion is F2, looking to calculate the AUD value into G2, with the date of conversion being A2.

F2 and G2 are currently simply formatted as 'number'.

Is this possible somehow? (I've seen it done in Google sheets, pulling data from a server, but not sure if it is possible to do it on a fixed date, or only the current or most recent date.) Thanks

MacBook Pro (15-inch Mid 2012), macOS Sierra (10.12.4), SSD, Optical Drive swapped for HD

Posted on Oct 31, 2017 1:15 AM

Reply

Answer 1

SGIII

Level 8

37,562 points

Nov 1, 2017 8:48 AM in response to JDfunky

It's worth studying how Apple approached the problem in the 'My Stocks' template (at File > New in your menu).

The formula inserts a "-" for the errors on the weekend days. Then a filter on "text is not -" hides those rows.

When the filter is active (the box in the panel at the right is checked by default) the table and chart look like this:

This approach, of course, will work equally well for CURRENCYH()

SG

Reply

Answer 2

JDfunky Author

Level 2

231 points

Dec 28, 2017 4:50 AM in response to SGIII

Thanks SG, so essentially the workaround is just a way to cover the error and display a neat cell, but still there is no way to get an actual result, i.e. calculate a transaction that occurred on a Saturday or Sunday.

Reply

Answer 3

SGIII

Level 8

37,562 points

Dec 28, 2017 5:15 AM in response to JDfunky

Currency markets are usually closed on Saturday and Sunday, right?

SG

Reply

Answer 4

JDfunky Author

Level 2

231 points

Jan 1, 2018 4:04 AM in response to SGIII

SGIII wrote:

Currency markets are usually closed on Saturday and Sunday, right?

No. Unlike stocks, they trade 24/7, every day of the year.

Reply

Answer 5

SGIII

Level 8

37,562 points

Jan 1, 2018 4:54 AM in response to JDfunky

Are you sure about that?

https://www.investopedia.com/ask/answers/05/forex24hoursaday.asp

SG

Reply

Answer 6

JDfunky Author

Level 2

231 points

Nov 1, 2017 5:41 AM in response to JDfunky

Ok so I've worked out I need to use the CURRENCYH function. I've got it working, except for dates that are weekends, I get an error for those. Can it really be that the function can't get data for the weekend days? Even if it's based on Yahoo data for stocks which only provides 5 days of the week, wouldn't it just go back to the Friday before and use that close? Amazing if so!

Reply

Answer 7

stfflspl

Level 3

593 points

Jan 1, 2018 5:42 AM in response to SGIII

SG, from the link you provided:

"The market is open 24 hours a day from 5pm EST on Sunday until 4pm EST Friday."

And there is the overlap in time zones from Tokyo over Singapore, London, Chicago.

However, these overlaps might not be covered by Yahoo.

Paul.

Reply

Answer 8

SGIII

Level 8

37,562 points

Jan 1, 2018 6:01 AM in response to stfflspl

Hi Paul,

Partial overlaps only, unless one somehow wants to argue that the world has moved beyond a 24-hour clock and there is more than one international date line.🙂

No weekend quotes on Yahoo! That is quite understandable, and the behavior I would expect. In any case, it's a simple matter to use a filter to hide the dates where =CURRENCYH does not return a meaningful result, as in the 'My Stocks' template, as explained in my previous reply.

SG

Reply

Answer 9

JDfunky Author

Level 2

231 points

Jan 1, 2018 2:56 PM in response to SGIII

It's not forex currency, it's crypto currency that I trade, markets never close. These trades in question were ones that I placed myself, on Saturdays or Sundays, hence why I need a closing price to calculate the USD equivalent on that day. It would appear that Yahoo have not yet caught up.

My current workaround is to manually go through the sheet and insert "[date cell reference] -1" if a Saturday trade, "[date cell reference]-2" if a Sunday trade, to use the Friday data. Hopefully the tax department will be happy enough with this estimation for now, and eventually Yahoo will provide weekend data as more and more people request it.

Reply