0

From Mexico's official currency exchange website, http://dof.gob.mx/index.php, I need to pull the USD rate on the far right of the page into a simple math equation in a SQL Server 2005 query to calculate USD to Mexican Pesos for invoices. Is this possible, and if so how?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
milkmood
  • 1
  • 1
  • 1
  • 4
    You mean screen scrape the rate? You don't and can't from SQL. – gbn May 18 '11 at 13:26
  • You could get the exchange rate XML from an application and send the XML to a SQL Server Stored Procedure. You can get the XML from a place like http://themoneyconverter.com/USD/rss.xml – SQLMason May 18 '11 at 13:30
  • 1
    Actually, get it from here on that site: http://www.dof.gob.mx/indicadores.xml – SQLMason May 18 '11 at 13:33
  • Dan, that may be the closest thing to what I need. So, 1. Is there a way to get SQL to grab that XML file, say in a trigger 2. How do I drop that into a math calculation in a query, or 3. Should I use that to populate another table and then do the math? – milkmood May 18 '11 at 14:24
  • 1
    SQL cannot scrape anything on it's own. It'll only take what's passed to it so you'll need a webservice or webpage to do the scraping and call a SQL proc to save the exchange rate. – Brent D May 18 '11 at 16:54
  • I've never tried this, so I have no idea if it will work, but you could try grabbing the value from the web service from within a SQLCLR assembly. – Jon Seigel Jul 03 '11 at 05:07

1 Answers1

0

It would be much simpler to pull date from this kind of web API service where you can simply calculate up-to-date conversion of USD to MXN:

http://openexchangerates.org/documentation

And then just store all prices either in USD or MXN in the database, and apply conversion when needed.

E.g. in this list of exchange rates all ratios are compared to USD. So USD to MXN ratio is now 13.3744. So to convert dollars to Peso you need to multiply amount in dollars to the exchange rate:

var dollars = 25;
var peso = dollars * 13.3744;

and peso value is:

334.36

I hope that'll help anyone!

mikhail-t
  • 4,103
  • 7
  • 36
  • 56