Centre of the Universe

Now that we have everything in place, it would be nice to add a little bit of interactivity.

Since the script which generates the graph outputs an image, all we need to do put in place a few combo boxes so that we can select the values we want for the graph, and then all we do is call a Javascript function to change the inner html for the div containing the image with a new image referencing the updated url.

Now we can dynamically change our input values to get the data we want the graph to show.

	



Convert 

	ZAR
	GBP
	USD
	EUR
	AUD
	NZD
	CHF
	

 to 

	ZAR
	GBP
	USD
	EUR
	AUD
	NZD
	CHF
	



Period 

	Last month
	Last 6 months
	Last year
	Last 5 years
	Last 10 years
	

Now, that we can draw a graph of the exchange rate, the info contained within our database will quickly grow outdated if we do not update it with the new daily data.

Fortunately, the European Central Bank comes to the rescue once again. They have an XML feed which contains the latest exchange rate for all the currencies that are listed in the history data. All we need to do is get the contents and then extract the values we need out of it.

The XML feed can be found at http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml.

Using an XML parser, which converts the XML into an object, we can quickly find currencies we want, and then create an insert statement to update our database.

This script would need to be run daily to keep the database fully up-to-date, so one way to do this would be to run the script as a cron job on your server.

$row[ipaddress] = "localhost";
$row[dbusername] = "root";
$row[dbpassword] = "password";
$row[dbname] = "exchange";

$contents = '';
$file = "http://www.ecb.europa.eu/stats/eurofxref/eurofxref-daily.xml";
if($handle = @fopen($file, "r")){
	while (!feof($handle)) {
	   $contents .= fread($handle, 8192);
	}
	fclose($handle);

	if (strlen($contents) > 0){

		$data = simplexml_load_string($contents);
		$currdate = (string)$currdate = $data->Cube->Cube['time'];
		foreach($data->Cube->Cube->Cube as $curr){
			switch ((string)$curr['currency']){
				case 'GBP':
					$gbp = (string)$curr['rate'];
					break;
				case 'USD':
					$usd = (string)$curr['rate'];
					break;
				case 'ZAR':
					$zar = (string)$curr['rate'];
					break;
				case 'NZD':
					$nzd = (string)$curr['rate'];
					break;
				case 'AUD':
						$aud = (string)$curr['rate'];
				case 'CHF':
						$chf = (string)$curr['rate'];
					break;
			}
		}
		$dbconn = mysql_connect($row[ipaddress], $row[dbusername], $row[dbpassword]);
		mysql_select_db($row[dbname], $dbconn);
		echo $gbp;

		$sql = "SELECT * FROM `exchangerates` WHERE `ratedate` = '$currdate'";
		$res = mysql_query($sql, $dbconn);
		if (mysql_num_rows($res) == 0){
			$sql = "INSERT INTO `exchangerates` (`ratedate`, `GBP`, `USD`, `ZAR`, `AUD`, `NZD`, `CHF`) VALUES ('$currdate', '$gbp', '$usd', '$zar', '$aud', '$nzd', '$chf')";
			echo $sql;
			mysql_query($sql, $dbconn);
		}
		mysql_close($dbconn);
	}
}

In the next part of the series, we will be putting the finishing touches on creating the graph

Now that we have the basics in place for creating an exchange rate graph from the last part, we can now use the data we have captured to draw a graph.

We pass to the script the parameters fromcurr, which is the currency to convert from, tocurr, which is the currency to convert to, the valid values being, ZAR, GBP, USD, AUD, NZD and CHF. The last parameter is period which can be month, 6month, year, 5year and 10year.

The first thing we do is process the parameters we have been passed to find out the currencies and periods we need, which we then use to get the exchange rate data for the values we are looking for.
We then calculate the conversion rate for each row in the data we have returned, which will be used to draw the graph.

To draw the graph itself, we use the imagecreate() function to create an image, and then use the inbuilt php image drawing functions to draw the data onto the image we have created.

Essentially all we need to do is iterate though our conversion rates and draw the data point on the graph at the relevant location by applying a scaling factor to the value which will put the values within the pixel range which we desire.

At this point we also draw all necessary labels and guidelines for the chart.

Once we finish drawing the graph, we call imagepng() which causes the script to output the image data directly. We can then call this script from within an <img> tag, for example
<img src=”www.someserver.com/exchangegraph.php?tocurr=GBP&fromcurr=ZAR&period=5year>
continue reading…

Over the next few blog posts I am going to explain how to create an exchange rate graphing script in PHP, which takes the two currencies as input as well as the period to calculate for, and then outputs an image showing the graph of how the exchange rate has varied over time.

One of the biggest problems when trying to display exchange rate data is where to get the data to display, but that is not a major worry, as there are several sources of this information freely available on the web.

The source where I got the historical data is from the European Central Bank, which publishes a list of exchange rates for many currencies against the Euro, with historical data dating back to 1999. The data is available for download here.

Of particular note here is the historical file, which can be downloaded as a CSV, which we are going to use to populate our database.

The first thing we need to do though is to create a MySQL database to store the exchange rate data in. The query to create the table we need is as follows:

CREATE TABLE `exchangerates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ratedate` date DEFAULT NULL,
  `GBP` decimal(10,4) DEFAULT NULL,
  `USD` decimal(10,4) DEFAULT NULL,
  `ZAR` decimal(10,4) DEFAULT NULL,
  `AUD` decimal(10,4) DEFAULT NULL,
  `NZD` decimal(10,4) DEFAULT NULL,
  `CHF` decimal(10,4) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Now that we have a database structure and have the historical exchange rate data, we need a script to populate our database. This is done with this little script.

what this script does is parse the CSV file, and then selects the columns in the CSV corresponding to the currencies we are interested in – we don’t want most of the currencies. The script builds an insert statement for each line and then executes the query against the database, and at the end of it, we have the full historical exchange rate data since 1999 in our database.
continue reading…