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 XE.com's Currency Update Service. They list 69 different currencies, including gold, silver and platinum. XE.com 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 XE.com 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 XE.com 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) } ' >
/var/lib/mysql/money/AUD1.txt
cat /var/lib/mysql/money/GBP.txt | awk '/Rates/{D=$4 ; next}
{n=NF; print D "\t" $(n-1) "\t" $(n) } ' >
/var/lib/mysql/money/GBP1.txt
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
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 <
/var/lib/mysql/money/euro.bat
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 < /var/lib/mysql/money/euro.bat
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; exit ###############################
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"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>DataBase -- Currency Report</title>
</head>
<body bgcolor="#ffffff">
<?php
$db = mysql_connect("XXX", "YYYYYY", "ZZZZZZZ");
mysql_select_db("money",$db);
?>
<center>
Currency Report <br> Last 14 Days <br>
Other = Euro
<BR>
<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>
</B>
</table>
<center>
<br>
<font size="+1">
<?php
$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>
<td align=\"center\">1 £ =</td> <td align=\"center\">1 US$
=</td> ");
$i=15;
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
align=\"center\">%2.4f</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>\";
?>
</font>
</center>
</body>
</html>
###############################################
