The Lounge - CodeProject

ZurdoDev30-Oct-17 3:27

30-Oct-17 3:27

Does anyone have any tips/recommendations for dealing with currency rates. We'll likely end up using an API, depending on what I can find, but just looking to see what experience others have.

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Currency Rates

OriginalGriff30-Oct-17 3:44

OriginalGriff

30-Oct-17 3:44

Don't trust the ones at airports, or in bars?

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!

Re: Currency Rates

CPallini30-Oct-17 4:16

CPallini

30-Oct-17 4:16

Would you like to fill out our survey?

Re: Currency Rates

OriginalGriff30-Oct-17 5:00

OriginalGriff

30-Oct-17 5:00

FAKU?

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!

Re: Currency Rates

Richard MacCutchan30-Oct-17 4:14

Richard MacCutchan

30-Oct-17 4:14

Always use an API; currency rates can change minute by minute.

Re: Currency Rates

Jacquers30-Oct-17 4:49

Jacquers

30-Oct-17 4:49

To add to Richard MacCutchan's reply - store the history of the currency rate as well.

Re: Currency Rates

ZurdoDev30-Oct-17 5:05

ZurdoDev

30-Oct-17 5:05

I saw one particular API that actually has the history as well.

Thanks, good advice.

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Currency Rates

Herbie Mountjoy2-Nov-17 4:05

Herbie Mountjoy

2-Nov-17 4:05

I like the XE.com api.

We're philosophical about power outages here. A.C. come, A.C. go.

Re: Currency Rates

R. Giskard Reventlov30-Oct-17 5:29

R. Giskard Reventlov

30-Oct-17 5:29

This is pretty old but still works...

Yahoo Market data - another take.[^]

Re: Currency Rates

ZurdoDev30-Oct-17 5:39

ZurdoDev

30-Oct-17 5:39

K, Thanks.

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Currency Rates

Nagy Vilmos30-Oct-17 5:53

Nagy Vilmos

30-Oct-17 5:53

What are the rates for?
Indicative or charging?

If you're going to use a rate from an API, make sure that you know the margin to apply. A lot of the time you'll get a spot price, aka mid price. You never trade at spot always at the buy/sell which will be off by a small margin, sometimes it's percentage, others use fixed points.
Pretend GBP-EUR rate is 1.10. A good margin would be +/-4 bp 1.06 / 1.14.

Also take into account reciprocal rates. from the above GBP-EUR would be 0.909090909.... However you apply it you get rounding errors.

FX Rates are bloody awful. If you want rates for anything other than indicative, you need the correct buy / rates and you should always just divide in the rate to reverse directions.

tl;dr;
FX is Evil.

veni bibi saltavi

Re: Currency Rates

ZurdoDev30-Oct-17 6:03

ZurdoDev

30-Oct-17 6:03

Interesting. Thanks.

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Currency Rates

Jeremy Falcon30-Oct-17 8:15

Jeremy Falcon

30-Oct-17 8:15

I don't really have much to offer that Naggy hasn't addressed. But I'll say this... it is possible to get the rate data without a broker's margin, but for that kind of feed you'll need to pay some moola or avoid using a broker for the data altogether and stick with Google or Yahoo et al and just accept the lag those APIs will give you.

Also, if you're doing this for an online business, I'd suggest always, always storing prices in a base currency (even for past orders) and also the currency of the sale along with the exchange rate at that point in history. It'll make your life a lot easier if you only have to think about one currency (preferably the currency you'll be paying taxes in for the company) and deal with exchanges on a case-by-case basis.

Not sure who you'll be processing payments through, but services like PayPal will handle this stuff for you btw. It's a pretty common problem...

ConvertCurrency API Operation - PayPal Developer[^]

Jeremy Falcon

Re: Currency Rates

ScottotheUnwise31-Oct-17 2:47

ScottotheUnwise

31-Oct-17 2:47

I am using something I found from

http://www.mssqltips.com/sqlservertip/3272/example-using-web-services-with-sql-server-integration-services/

[

"https://openexchangerates.org/api/latest.json?app_id=04f370f1bf4a4ea7a386ade58f07d0e5"

]

myConnection.DownloadFile(Dts.Connections["OpenExchCurrency.json"].ConnectionString, true);

Have SQL 2012 so had to implement reading the JSON file format as a flat file, pretty simplistic it just ignores the first six lines of the file and starts with (example data)

"AED": 3.672761,
"AFN": 67.383551,

Once I read that into a table, parse (known 3 character positional for currency code, ":" to "," for value) for the four currencies I care for for the day. Added an AVG30Days and an AVGPeriod calculation. Then as someone else pointed out, I used a web based CSV request to get and add history back as far as my data goes. from before I had this option.

Re: Currency Rates

ZurdoDev31-Oct-17 2:54

ZurdoDev

31-Oct-17 2:54

Great. Thank you. Thumbs Up | :thumbsup:

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Currency Rates

ormonds31-Oct-17 11:00

ormonds

31-Oct-17 11:00

Probably too simple but I use a call to google:

{
  URI = "http://finance.google.com/finance/converter?a=1&from=NZD&to=" + Currencies[Counter];
  System.Net.WebRequest req = System.Net.WebRequest.Create(URI);
  try
  {
    System.Net.WebResponse resp = req.GetResponse();
    StreamReader sr = new StreamReader(resp.GetResponseStream());
    tmp = sr.ReadToEnd().Trim();
    sr.Dispose();
  }

and then parse the result.

Re: Currency Rates

ZurdoDev31-Oct-17 14:28

ZurdoDev

31-Oct-17 14:28

Cool, thanks.

There are two kinds of people in the world: those who can extrapolate from incomplete data.

There are only 10 types of people in the world, those who understand binary and those who don't.

Re: Currency Rates

Robert/Not The Pirate1-Nov-17 21:30

Robert/Not The Pirate

1-Nov-17 21:30

currencylayer API | Free, Reliable Currency Converter API[^]

Re: Currency Rates

Member 110156841-Nov-17 21:42

Member 11015684

1-Nov-17 21:42

I use the https://www.oanda.com/fx-for-business/exchange-rates-api API which works just great for my needs - tracking USD <-> GBP every minute 24/7

Re: Currency Rates

Gerry Schmitz2-Nov-17 14:22

Gerry Schmitz

2-Nov-17 14:22

Maintain separate accounts.

Only convert on-the-fly when reporting; "actual" conversion when laundering.

Maintain a list of daily conversion rates for reporting.

"(I) am amazed to see myself here rather than there ... now rather than then".
― Blaise Pascal

Just been filling out a "store survey" online

OriginalGriff30-Oct-17 2:07

OriginalGriff

30-Oct-17 2:07

And I got to one section ... Survey.jpg
I'm reasonably sure they didn't mean to show the "Randomizing section" to punters, but I do wish they had chosen different letters. Kinda shows what they think of me, really... Laugh | :laugh:

Bad command or file name. Bad, bad command! Sit! Stay! Staaaay...
AntiTwitter: @DalekDave is now a follower!

Re: Just been filling out a "store survey" online

megaadam30-Oct-17 2:41

megaadam

30-Oct-17 2:41

A key to a good customer relations is sincerity.

... such stuff as dreams are made on

CCC

The pompey29-Oct-17 23:39

The pompey

29-Oct-17 23:39

James fan is a Bad Singer with Sting and loses a thousand (8)