Getting Foreign Exchange Rates into FileMaker



2016-10-16 Update: Apparently this parsing code for XE.com broke. XE.com changed the html formatting of their currency table. I have created an updated version (in .fmp12 format) and it has replaced the demo file link below.

Here are the steps I changed:

In the script “Get Exchange Rate Batch of Currencies”
Change these variables (Set Variable)
  • $StartChunk to “<tr class=’liveRatesRw’>”
  • $EndChunk to “<td class=”>Inverse:</td>”
  • $StartingText to “<td class=’rateCell’><a href=’/currencycharts/?from=USD&amp;to=“

Those changes should grab the new table formatting from xe.com

thechunk

If you have already integrated this script in your solution, these changes should be enough to make it work again. If not, re-download the demo file and copy the script directly.

There may be easier ways to do this, but I have set up a few currency translations scripts in the past and found that the calculated URL I was employing kept breaking. The exchange websites would change their URL scheme and my scripts would fail. My suspicion is that these adverting-based websites don’t want automated engines pulling data from them. Then it occurred to me that it might be possible to just scrape the initial table data from their main page. XE.com has been around for a while and seems to have settled into a consistent display, so here’s hoping that this one doesn’t break too soon. The basic technique I am using here has been in use for about a year now and seems to be holding up.

I have tried to make this demo as flexible as possible, displaying any currency that xe.com supports. What follows is an overview of how the demo database is constructed and I conclude with a description of how best to bring this functionality into your own databases.

Download Currency Exchange Rates Demo File

XE.com – The World’s Favorite Currency Site

media_1353021578801.png

According to their marketing they are the world’s favorite currency site. Regardless, they are one of the ones that shows up first in a Google Search for currency exchange rates and so here is hoping they are well financed and plan to be around for the foreseeable future.

XE.com lookup a specific Currency Foreign Exchange Table

media_1353021270953.png

From the main page, it is possible to click on any of the main currencies and drill down to a sub-page that displays exchange rates to that particular currency from a number of other major currencies.

Safari’s – Inspect Element

media_1353021435149.png

To have a look at the HTML that will be parsed, right click somewhere in the table and select Inspect Element. This is using Safari, other browser may have other approaches to this. Most modern browsers will at least have a ‘View Source’ option.

Web Inspector – examine the HTML returned

media_1353021449145.png

FileMaker Demo

media_1353044087089.png

This demo database uses a Web Viewer to grab a URL and parse the table data into a set of records and fields.

  1. Select your home currency
  2. The Lookup URL will update based on a field script trigger
  3. Click the Update Currencies button
  4. The Currency list will be deleted and a new set of currencies imported from the web page.

Currency Exchange Rates Refresh layout

media_1353044158972.png

The user feedback screen displays while the Currency URL is being refreshed.

Behind the scenes

media_1353044759947.png

There are two named Web Viewers. The first one is at the top and is simply for user feedback, as the script runs it displays a spinning wheel to let the viewer know something is happening. The second Web Viewer is doing the work. It goes to the calculated URL and grabs the source HTML. That text is then parsed into the records in the Currency table.

The scripts

media_1353044181452.png

The main script is ‘Get Exchange Rate Batch of Currencies’

Get Exchange Rate Batch of Currencies

media_1353044188201.png

The components of this script are:

  1. Clear out the existing records
  2. Open the xe.com URL in a new Window
  3. Grab a chunk of the resulting HTML text — using a Custom Function: ExtractText ( GetLayoutObjectAttribute ( “CurrencyLookupWebViewer” ; “content”) ; $StartChunk; $EndChunk )
  4. Parse out the results into records and place the data into individual fields

Add a value list

media_1353045095794.png

The list of currencies requires a Value List. This value list is employed in the calculated URL for each currency.

Currencies Value List

media_1353045128581.png

It turns out the URL for each of the individual currencies is just the three letter acronym for the Currency and the Currency name with dashes in between. A Value List of these concatenated names was created. This is important for building the custom URLs for each currency.

Custom Functions

media_1353045275036.png

The demo solution employs two Custom Functions. These text parsings can be done with FileMaker’s built-in abilities, but these Custom Functions make it much clearer what is going on.

ExtractText and ParseData

media_1353045307534.png

Two Custom Functions taken from the excellent collection at www.briandunning.com.

With a copy of FileMaker Pro Advanced, it is possible to copy and paste these Custom Functions into your solution.

Adding this functionality to your solution

media_1353045981404.png

Here is the recommended order for adding this functionality to your own solution:

  • Add the Currencies Value List
  • Copy the two Custom Functions into your solution. You will need a copy of FileMaker Pro Advanced to do this.
  • Create an ExchangeRates table with the fields: Currency, Exchange Rate and Timestamp as well as two global fields: Local Currency and Lookup URL
  • Replicate the two layouts based on the Exchange Rates table, one for the Currency list and one for the Web Viewers
  • Paste in the named two Web Viewers from the demo database
  • Copy the main script in “Get Exchange Rate Batch of Currencies”
  • Look for any broken steps in the script and repair as needed.

There is also a demo script for hard coding a single currency lookup. It is very similar except there is no loop involved, it just looks for a single chunk of text, and pastes it where required.

Advertisements

10 Responses to “Getting Foreign Exchange Rates into FileMaker”

  1. Hi Douglas,

    This is a great help, perfect for my organization and my needs. I was toying around with it on Friday and everything worked, and I was excited to incorporate it into my own database this week. However in trying the demo today, the currencies no longer update. Do you have any idea why this is? The timing can not be worse, I was just about done adding the functionality when I discovered the issue. Thanks so much for your help and the demo in the first place!

    • It seems that xe.com has updated their HTML table and changed the output slightly. Fortunately it is a relatively minor change.

      In the script “Get Exchange Rate Batch of Currencies” change the variable $StartingText to

      <a href='/currencycharts/?from="&$LocalCurrency&"&to=

      Farther down the script, on the Set Field ExchangeRate field, change the calculation to:

      ExtractText ( $RowChunk ; "'>" ; "" )

      I have updated the demo file.

  2. Ah Brilliant, thanks very much!

  3. Hi Douglas,

    I incorporated the changes you suggested and while everything runs as it should, my updates still come up blank. I was also not able to download your updated demo file, it says access denied/do not have permission. But when I incorporated your suggestions into your previous demo I had downloaded, it does the same as mine, in that everything runs smoothly but no data appears. Any further suggestions or other changes you noticed are needed, with xe.com updating their HTML table? Many thanks as always

    • Hi Colin. I fixed the demo download. You should be able to download that now. I just retested it and it seems to work. Please copy the new script out of the demo and see if that works for you.

  4. A simple way to get a single currency pair is using Google. The format for the URL is “http://rate-exchange.appspot.com/currency?from=” & YOUR_CURRENCY_HERE & “&to=” & YOUR_SECOND_CURRENCY_HERE. In reply you get. {“to”: “USD”, “rate”: 1.0474000000000001, “from”: “AUD”} Which is simple to parse. Visit http://rate-exchange.appspot.com to see the formatting details.

  5. Hi that’s a great info . You can also use http://showcurrency.com for live conversion between currencies. it also got what you have been looking for ,contact the web master and they will provide a link for currency converter on your websites

  6. Doug,
    I have an unlocked solution for Rate Exchanges that works with iPad, iPhone and Desktop here.

    http://yurka.net/resources/index.html

    I hope it helps others as your examples have helped me.

    • Very nice little application you have created. It works great and a nice interface. Thanks for sharing Jeff. Also thanks for the tip about using the Yahoo API.

%d bloggers like this: