FOREX – using the Google Rates API in Oracle

By Kenny Miller, Principal Consultant for Rocela

I thought I’d share a simple but useful database function I’ve developed. I had a requirement to determine foreign exchange spot rates in real-time for the GBP (United Kingdom Pound). The European Central Bank provides a live exchange rates feed for the Euro, and the Federal Reserve provides the same for the US Dollar (both in XML format, available over HTTP). Unfortunately the Bank of England doesn’t provide an equivalent feed for GBP.

Google do however provide a free exchange rates API, where they source rates under licence from Citibank. The API works by passing currency details in the parameters to a web page.

For a GBP to USD example, try the following in the address bar of your favourite browser:

 http://www.google.com/ig/calculator?hl=en&q=1GBP=?USD

This will return something similar to the following:

 {lhs: "1 British pound",rhs: "1.5548 U.S. dollars",error: "",icc: true}

This is all that is returned – there are no HTML tags. It’s easy to write a function to “scrape” out the rate from the text.

CREATE or REPLACE FUNCTION xxget_google_rate (p_from_currency varchar2,
                                              p_to_currency varchar2)
  RETURN number IS

  -- Variable declarations.

  t_page_table    utl_http.html_pieces;
  l_url           varchar2(100);
  l_fx_rate       number;    

BEGIN

  -- Build the URL to the Google API.

  l_url := 'http://www.google.com/ig/calculator?hl=en&q=1' ||
             p_from_currency || '=?' || p_to_currency;

  -- Make a HTTP call to the API           

  t_page_table := utl_http.request_pieces(l_url);

  -- The rate is returned on the first line, so scrape out the rate. There is
  -- no HTML returned. The following is an example of the text returned:
  -- {lhs: "1 British pound",rhs: "1.5625 U.S. dollars",error: "",icc: true}

  l_fx_rate := to_number(substr(t_page_table(1),
                 instr(t_page_table(1), 'rhs: "')+6,
                 instr(substr(t_page_table(1),
                 instr(t_page_table(1), 'rhs: "')+6), ' ', 1, 1)-1));

  -- Return the rate.

  RETURN l_fx_rate;

EXCEPTION WHEN others THEN

  -- Return -999 to indicate that a rate could not be determined.

  RETURN -999;

END xxget_google_rate;
/

That’s all folks, except to draw your attention to Google’s disclaimer for the API:

Currency Conversion Disclaimer

Google cannot guarantee the accuracy of the exchange rates used by the calculator. You should confirm current rates before making any transactions that could be affected by changes in the exchange rates. Foreign currency rates provided by Citibank N.A. and displayed under license. Rates are for information purposes only and are subject to change without notice. Rates for actual transactions may vary, and Citibank is not offering to enter into any transaction at any rate displayed.

Advertisements

2 Responses to FOREX – using the Google Rates API in Oracle

  1. Shastri says:

    Can someone send the complete javascript code that returns the string from the URL http://www.google.com/ig/calculator?hl=en&q=1GBP=?USD.

    That is I want to capture the value({lhs: “1 British pound”,rhs: “1.6069 U.S. dollars”,error: “”,icc: true}) returned from the URL inside a textbox

    • Rocela says:

      Although the Google API returns a JSON string, our example shows how to query the API using an Oracle PL/SQL function. We don’t use any Javascript in our solution.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: