Exchange Rates Update

I am an American living in Germany, and I export German stamps to English-speaking markets, mainly to the US, UK and Australia. Once a quarter, I mail the new stamps to my customers with a statement priced in their local currency. This means that I have to keep track of the exchange rates for the Euro, US dollar, UK pound and Australian dollar.

Many years ago, when I started my stamp business, I solved this problem by listening to the radio every day at 6pm, when the daily US dollar fix was issued. I wrote the exchange rate on some scrap paper. It was a mess to keep all these little notes together. The rates for the Australian dollar and the British pound I had to guess when I made my quarterly price list. Today, by using the Internet, MySQL, PHP, KMail, and SuSE 8.2 Linux, all the tracking and conversions are done automatically.

I began by looking on the Internet for a currency exchange list; I found's Currency Update Service. They list 69 different currencies, including gold, silver and platinum. also has an on-line currency history, which goes back about 10 years. A helpful service is the free, six times a week e-mail currency update. This update shows all exchange rates in relation to a base currency of the user's choice. For example, my update shows all 69 currencies in relation to the Euro.

Due to the way that sends out the updates, I sometimes get two with the same date. Most of the double dates occur on Monday, probably because the Sunday update happens early Monday morning. There is no update sent on Saturday.

I receive the currency data in an e-mail in the KMail program. When I receive an e-mail, my computer screen shows the following:

Rates as of 2003.10.14 20:53:31 UTC (GMT). Base currency is EUR.

Currency Unit                   EUR per Unit      Units per EUR
============================	================  ===============
USD United States Dollars	0.852967        	1.17238
GBP United Kingdom Pounds 	1.42584                 0.701340

At this point, I need to input the information into a MySQL database. When I built the database, I set up three tables, one for each currency. Each table looks like this:

mysql> describe Money_USA;
 | Field | Type        | Null | Key | Default    | Extra |
| date   | date        |        |        | 0000-00-00 |       |
| euro  | float(8,6) |        |        | 0.000000   |       |
| other | float(8,6) |        |        | 0.000000   |       |
3 rows in set (0.02 sec)

Getting the currency data from the daily e-mail into the database is a two-step process, and both steps can be done inside KMail's filters. First, I grep to pull out two lines from the e-mail, the date of the exchange rate (first line) and the exchange rate itself (second line). I have to do this three times, once for each currency I track. Here are the two filter rules I use to pull the data out of the e-mail:

grep "Rates as of" >> /var/lib/mysql/money/USA.txt
grep "USD" >> /var/lib/mysql/money/USA.txt

At this point I have three files, one for each currency. Each file consists of two lines, the date line and the rate line. Here is what the file USA.txt looks like:

Rates as of 2003.10.15 20:33:31 UTC (GMT). Base currency is EUR.
USD United States Dollars        0.859128              1.16397

MySQL cannot read the data in the format. Therefore, I wrote a short bash script to change each file into MySQL format. The second KMail rule runs this script. It has to be in the directory /user/bin, otherwise KMail cannot find it. Here is the MySQL-readable file USA1.txt:

2003.10.15            0.859128              1.16397

But to get MySQL to read the files my bash script makes, they have to be in the database's money directory. Actually, the command load data local makes it possible to input mass data easily. The MySQL programmers turned off this command, however, because they judged it to be a security risk. Supposedly it is possible to turn the command back on, but I was not able to get it to work. Therefore, I had to hack around it. I found that I can make load data work when the input file is inside the same dir as the mysql table is. My dir path is /var/lib/mysql/money/.

To make my solution work, I had to change the directory permissions from root only to global (666). To be honest, this is a bad hack and you should not do it if your system is open to the Internet. My database system is not, only my wife and I use it. It would be nice if MySQL or SuSE would make the load data local command simpler for the system owner to activate. Here is my bash script, euro_update.program:

######## euro_update.program ####################
#! /bin/bash
cat /var/lib/mysql/money/AUD.txt | awk '/Rates/{D=$4 ; next}
{n=NF; print D  "\t" $(n-1) "\t" $(n) } ' >
cat /var/lib/mysql/money/GBP.txt | awk '/Rates/{D=$4 ; next}
{n=NF; print D  "\t" $(n-1) "\t" $(n) } ' >
cat /var/lib/mysql/money/USA.txt | awk '/Rates/{D=$4 ; next}
{n=NF; print D  "\t" $(n-1) "\t" $(n) } ' >

rm /var/lib/mysql/money/AUD.txt
rm /var/lib/mysql/money/GBP.txt
rm /var/lib/mysql/money/USA.txt
mysql -h localhost -u XXXXX -pXXXXXXXX <

rm /var/lib/mysql/money/AUD1.txt
rm /var/lib/mysql/money/GBP1.txt
rm /var/lib/mysql/money/USA1.txt

The script actually boils down to only two lines of important code. The first important line is:

cat /var/lib/mysql/money/USA.txt |
awk '/Rates/{D=$4 ; next} {n=NF; print D  "\t" $(n-1) "\t" $(n) }
' > /var/lib/mysql/money/USA1.txt

This line pipes the file USD.txt through the awk command and creates a new file, USA1.txt. grep works only on whole lines, awk works on each word in the line. There are three things I want awk to do: find the date, find the two exchange rates and print all three on one line with tabs between (this is the MySQL input format) in a new file.

The awk command is composed of two parts, the search pattern and the action. The command can have both parts or only one. The search pattern is shown by /XX/ and the action is inside {}.

'/Rates/{D=$4 ; next}

In my script I have awk look for the pattern Rates. When found, awk counts four words over (D=$4) and puts that word into memory. The date is always the fourth word on the line starting with Rates. Then, I tell awk to go to the next record with the command next.

{n=NF; print D "\t" $(n-1) "\t" $(n) }'

Because the next command does not have a pattern, awk does the action on the whole line. First, it counts the number of words (n=NF) in the line. It then is ready to print to the new file. Now it prints D, which is the date, and then a tab. The rates are always the last and next to last words on the line, so I count backwards--$(n-1)--to print the first rate, then another tab and the last rate.

The next important line is:

mysql -h localhost -u XXXXX -pXXXXXXXX <

This is the line that pulls the data into the tables. MySQL's batch file processing ability is helpful here. Short functions can be written as batch files and then used repeatedly. To get batch file processing to work, three steps have to be considered; only two of them are mentioned in the manual.

First, the < symbol tells MySQL to run this file. Second, the batch file, in my case euro.bat, must be executable, at least 755. Third, when the password is inputed as -p XXXXX, MySQL does not recognize it. The password has to be given in the format -pXXXXX, without a blank between the -p and the password. If there is a blank, MySQL stops and demands a password.

Here is the MySQL batch file, euro.bat:

######### euro.bat ################
use money;
load data infile "AUD1.txt" into table Money_AUD;
load data infile "GBP1.txt" into table Money_GBP;
load data infile "USA1.txt" into table Money_USA;

At this point, all data are in a MySQL table. Now we need to get them into a readable, usable format. To do so, we used the MySQL command select * from Money_AUD;. This is not convenient, however, so I used HTML and PHP to make a form that Netscape can display. I avoided using Java because I need to let my wife view the data. She uses only command-line programs, so I had to make a file that Lynx or Links can read. Figure 1 shows the last 14 days of data that I have.

Figure 1. MySQL Data Viewed in Lynx

There are two ways of looking at the exchange rate, so I made two screens. The main screen shows the currencies I picked in relation to 1 Euro. The second screen shows the Euro in relation to 1 USD, 1L and 1 AUD.

In addition to current rate data, I have stored data back to the middle of 2002. Therefore, I added another two screens that can be accessed from the main screen. One shows all the raw data and one shows the monthly averages for 2002 and 2003 (see Figure 2).

Figure 2. Finding Data from Previous Years

Finally, in order to build a history of the exchange rates from all the stored data, I wrote the following PHP script, currency.php. It allows you to figure the value of your money quite accurately, without the need for a crystal ball.

################ currency.php ################
<!DOCTYPE public "-//w3c//dtd html 4.01 transitional//en"
  <title>DataBase -- Currency Report</title>
<body  bgcolor="#ffffff">

  $db = mysql_connect("XXX", "YYYYYY", "ZZZZZZZ");

Currency Report <br> Last 14 Days <br>
Other = Euro

<table width="100%" >
<tr valign="middle" bgcolor="#dedebb">
<td align="center"><B>
<A href="currency_other.php">Euro = Other</a>
<A href="reports.php">Reports Menu</a>
<A href="2002.php">2002 Raw data</A>
<A href="2002agv.php">2002 AVG data</A>
<A href="2003.php">2003 Raw data</A>
<A href="2003avg.php">2003 AVG data</A>
<A href="xindex.php">Main Menu</a>
<font size="+1">
$result_AUD = mysql_query("select * from Money_AUD order by date
desc", $db);
$result_GBP = mysql_query("select * from Money_GBP order by date
desc", $db);
$result_USA = mysql_query("select * from Money_USA order by date
desc", $db);

echo "<table border=1> \n";

printf("<td align=\"center\">DATE</td><td align=\"center\">1 AUD
 <td align=\"center\">1 &pound; =</td> <td align=\"center\">1 US$
=</td> ");


while ($i <> 0) {
     $myrow_AUD = mysql_fetch_array($result_AUD);
     $myrow_GBP = mysql_fetch_array($result_GBP);
     $myrow_USA = mysql_fetch_array($result_USA);
$c= (--$i%2? 'yellow':'white');
printf("<tr bgcolor=\"$c\"><td align=\"center\">%s</td><td
<td align=\"center\">%2.4f</td> <td
align=\"center\">%2.4f</td></tr>", $myrow_AUD["date"],
$myrow_AUD["euro"], $myrow_GBP["euro"], $myrow_USA["euro"]);
echo "</table>\";




Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.


Anonymous's picture

It would be nice if MySQL or SuSE would make the load data local command simpler for the system owner to activate.
For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege

Have you tried using the /tmp directory, which should be readable by all? Put the infile there and you can keep your database directory permissions untouched.

Re: Using MySQL for Exchange Rate Updates

Anonymous's picture

It's really nice to hear about a business person solving his own computing problems (however simple) in a way that goes beyond common/average knowledge of data processing. Having said that, I have some doubts about this article:

1. Why is this article even here? After all it describes simple string parsing and inserting of results into what is widelly mistaken for an RDBMS. All of this is computers 101 after all. I had an impression that The LinuxJournal was not in competition with

2. What's so big about MySQL in this particular case?
Why even use MySQL for this task? Considering the autor's table design, he could have saved a lot of time and computer resources byusing plain text files - 365 lines per file per year, a number of records which hardly requires a database to search through, especially if data is appended in chronological order and each currency is stored in its own table (file). Overall, I regard this setup a waste of time and resources.

If however someone has this unquenchable urge to use an RDBMS to store a few lines of text in, then (like another poster here suggested) why not go for a real RDBMS, which MySQL is certainly not. Not yet. My personal choice for the task would be, due to it's minimal size, small footprint and rock solid performance.

Re: Using MySQL for Exchange Rate Updates

billn's picture

Because MySQL, however simplistic, offers SQL functionality for the right price, and runs on anything. It's a good learning tool, and this example provides a basic fundamental building block example that others can learn from.

Not everyone is a power user. Recognize those who aspire to be.

Re: Using MySQL for Exchange Rate Updates

Anonymous's picture

How does this mesh with's terms of use which state:

You agree to follow all applicable laws and regulations when using this website. Furthermore, you agree that you shall not:
i. embed or import any financial data provided by us into any information services (whether or not web-based), data files or application software, including without limitation accounting and payroll systems, except as except as specifically permitted in writing by

Re: Using MySQL for Exchange Rate Updates

billn's picture

"any financial data provided by us into any information services"

That's the kicker right there. You can't use their data to run a profit, or non-profit, service to other people. He's compiling the data for his private use, and not offering that compilation to other people.

The rule of thumb when using anyone else's data is that Fair Use likely covers private, personal use of the data.

Re: Using MySQL for Exchange Rate Updates

Anonymous's picture

Seems like a lot of extra programming here. If you are already
using php to access the MySQL database, why not just pipe
the e-mail to a php script to do all the parsing and update
the MySQL database as well; and junk all the tmp files and
awk scripts, and MySQL batch files, etc.......

Re: Using MySQL for Exchange Rate Updates

Anonymous's picture

And what about not using MySQL at all?

Why use a "database" that claims to be a database but it's not more than a Microsoft Access on steroids without bugs, but still, not a real dabase?

You'd better go for PostgreSQL :)

Using Postgres?

Anonymous's picture

I've downloaded the Postgres book, and read it. I've also downloaded, and purchased, other books/articles on Postgres. And while I'd like to learn Postgres as my first database, since it is considered a "real" database, how can I? My uses would be for web, information storage, and other fairly lightweight uses. Should those uses become heavy weight later, I'd like to avoid porting from MySQL to Postgres. But when I look for books/docs on using Postgres/Perl with Apache, or for building web sites that use databases, everything I find is MySQL and PHP. There are some Perl books out there, and I have them, but except for one of the Postgres' team's book, and a few other general books (and a really old Postgres book with Perl and/or web), there's nothing. The vast majority of documentation is for MySQL.

MySQL has the buzz. They have the marketing muscle. They have an adoption rate in enterprise, who's curve must look similar to the adoption rate of GNU/Linux. They are partnering with many enterprise companies, and they are growing in their SQL and ACID compliance. Where is Postgres? Did they fall off the planet? Are they making so much money that they don't need or want publicity/buzz? What is their market share, if that's the case?

Unix admins scoffed at Linux a couple of years ago. They aren't scoffing anymore, they're on the unemployment line instead, unless they're lucky. MySQL has the buzz that Linux had a couple of years ago. With buzz, come developers, partners, adoption, growth, excellence.

Postgres may have been superior a couple of years ago. Maybe even a year ago. Maybe even now. But not for much longer. They don't have the attention. Postgres is/will be the next BSD. I wish I were wrong, but I called Linux correctly 3 years ago, and I believe I'm right now. Why learn a dying technology? Why not learn a growing, improving, and well backed technology, with available source code, one that appears to be mimicking GNU/Linux in adoption and eyeballs?

Prove me wrong. Provide the links to the docs/books that equal the docs/books on MySQL, especially when it comes to integrating MySQL into web sites, and which are current, and up -to-date.

Recommending Postgres just because you use it, when most people and businesses are moving to MySQL, is self-serving, and not adding to the discussion. Firebird, Postgres, and other databases will continue to exist, because the source code is available. Just like FreeBSD, NetBSD, and other O/S's will continue to exist. Just because they are there doesn't mean people will actually use them. Or should use them.

Re: Using MySQL for Exchange Rate Updates

Anonymous's picture

Why use a "database" that claims to be a database but it's not more than a Microsoft Access on steroids without bugs, but still, not a real dabase?
Why do people spout this FUD? The reason that there are so many different databases available is because people have different needs. MySQL is a real database and it is robust and feature-rich enough for the "big boys" like Yahoo!, Sabre Holdings, Cox Communications, and the Associated Press.

Re: Using MySQL for Exchange Rate Updates

Anonymous's picture

Good job tho. Solved the problem yourself. I like that.

Re: Using MySQL for Exchange Rate Updates

Anonymous's picture

To insert textfiles from shell to MySQL, check out 'mysqlimport'

Re: Using MySQL for Exchange Rate Updates

Anonymous's picture

sorry, but bad database design; you use 1 table for 1 currency -- better something like
from_amount, from_currency, to_ammount, to_currency, date
1 EUR 1.23 USD 1.1.1980

so you have no trouble if you become interested to other currencies.