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&to=“

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

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 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.