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