November 19, 2012 
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:
- $StartChunk to “<tr class=’liveRatesRw’>”
- $EndChunk to “<td class=”>Inverse:</td>”
- $StartingText to “<td class=’rateCell’><a href=’/currencycharts/?from=USD&to=“
Those changes should grab the new table formatting from xe.com
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
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
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
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
FileMaker Demo
This demo database uses a Web Viewer to grab a URL and parse the table data into a set of records and fields.
- Select your home currency
- The Lookup URL will update based on a field script trigger
- Click the Update Currencies button
- The Currency list will be deleted and a new set of currencies imported from the web page.
Currency Exchange Rates Refresh layout
The user feedback screen displays while the Currency URL is being refreshed.
Behind the scenes
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
The main script is ‘Get Exchange Rate Batch of Currencies’
Get Exchange Rate Batch of Currencies
The components of this script are:
- Clear out the existing records
- Open the xe.com URL in a new Window
- Grab a chunk of the resulting HTML text — using a Custom Function: ExtractText ( GetLayoutObjectAttribute ( “CurrencyLookupWebViewer” ; “content”) ; $StartChunk; $EndChunk )
- Parse out the results into records and place the data into individual fields
Add a value list
The list of currencies requires a Value List. This value list is employed in the calculated URL for each currency.
Currencies Value List
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
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
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
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.
HomeBase Software 
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.
Ah Brilliant, thanks very much!
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.
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.
Great tip. I see they also have a temperature API there:
Currency API: http://rate-exchange.appspot.com/currency?from=USD&to=EUR&q=1
Temperature API: http://rate-exchange.appspot.com/temperature?mode=C2F&q=37
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
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.