multi currency/get exchange rate 1

[ttuser4]

hi, i need to allow users to use different currencies (up to ten currencies) to calculate prices for their input.
is there any way i can use data from websites like

http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml

or

http://www.oanda.com/convert/classic?lang=en

i don't need a link to such site; i need to read the current exchange rate for selected currency and calculate value of goods/services for an user.
thanks.

 

[jpadie]

oddly enough i wrote a class to do this only the other day. i have published it here

http://rathercurious.net/archives/281.

it does not use ecb as it does not easily support dates in the past.

there is a slight improvement coming but i have not published it yet. it will simply optimise part of the queries.

for a list of currencies and their ISO codes, check out oanda.com, but if there are only 10 that you support then it should be straightforward.

if you are in the retail trade you might want to add a spread/margin to the rates. typically your bank will so there is no need for you to lose out!

 

[ttuser4]

thanks, i will check it out. i don't even need exchange rates for past days, just current rate when calculation is done (this is not bank/finance business, just manufacturing quotes online/internationally)

 

[jpadie]

then you will want to change the $firstSupportedDay variable to something recent to avoid the overhead of filling the database from 1 Jan 2007.

sqLite is a great solution for this db but if you want a mysql solution let me know. it's only a few lines different.

 

[ttuser4]

yes, i would be more interested in mysql since i am already using it for pricing.

 

[jpadie]

nb change the properties as you need for your mysql info

<?php
class exchangeRates{
	private $fields = array('from', 'to', 'date', 'amount');
	private $table = 'currency';

	private $firstSupportedDay = 1167606000 ; //1st Jan 2007
 
 	private $hostName = '';
	private $dbName = '';
	private $user = '';
	private $pass = '';
	/**
	 * constructor function
	 * @return void
	 */
	public function __construct(){
		try{
			$this->pdo = new PDO("mysql:host={$this->hostName};dbname={$this->dbName}", $this->user, $this->pass);
		} catch (PDOException $e){
			echo "Error!: " . $e->getMessage() . "<br/>";
    		die();
		}
		$this->init();
		$this->today = strtotime('today');
		if (empty($this->date)){
			$this->date = $this->today;
		}
		$this->addRates();
		if (isset($this->statement)) $this->statement= null;
	}
 
	/**
	 * main API for the conversion
	 * @return a [formatted] string for the currency conversion
	 * @param string $from  the ISO code of the currency you want to convert from
	 * @param string $to the ISO code of the currency you want to convert tp
	 * @param float $amount[optional]  the amount of the from currency you wish to convert. defaults to 1
	 * @param int $date[optional] the day for the conversion expressed as a unix time stamp. defaults to today's date.  M
	 * @param bool $format[optional] whether to format the returned information. defaults to false (unformatted
	 */
	public function convert($from, $to, $amount =1 , $date=null, $format=false){
		global $wpdb;
		if(empty($date)){
			$date = $this->today;
		}
		foreach (array($from, $to) as $symbol){
			$sql = "Select xDate as xDate from $this->table where symbol=? and xDate <= ? order by xDate desc limit 5";
			$statement = $this->pdo->prepare($sql);
			if ($statement === false){
				die (print_r($this->pdo->errorinfo(), true));
			}
			$result = $statement->execute(array($symbol, $date));
			if ($result === false){
				die (print_r($result->errorinfo(), true));
			}
			while ($row = $statement->fetchObject()){
				$results[$symbol][] = $row->xDate;
			}
		}
		$dates = array_intersect($results[$from], $results[$to]);
		if (count($dates) == 0){
			die ('We do not have a currency exchange rate pair listed for the requested day nor for any day close thereto');
		}
		//will be the first that has the latest date
		$sql = "select rate, symbol from $this->table where symbol in (?, ?) and xDate=?";
		$statement = $this->pdo->prepare($sql); 
		$statement->execute(array($to, $from, $dates[0]));
		$results = $statement->fetchAll(PDO::FETCH_OBJ);
		//convert to USD
		foreach($results as $pair){
			if ($pair->symbol == $from){
				$this->fromRate = $pair->rate;
			} else {
				$this->toRate = $pair->rate;
			}
		}
		$usd = $amount / $this->fromRate;
		$conversion = $usd * $this->toRate;
		if ($format) {
			return $this->getSymbol($to) .  $this->formatted($conversion);
		} else {
			return $conversion;
		}
	}
 
	/**
	 * dummy function to return the usual currency symbol for an ISO code
	 * @return 
	 * @param object $iso
	 */
	private function getSymbol($iso){
		//will perform lookup of currency symbols.  for the time being return the ISO symbol
		return $iso;
	}
 
	/**
	 * helper method to format the converted rate if required.
	 * @return 
	 * @param object $number
	 */
	private function formatted ($number){
		return number_format($number, $this->decPlaces, $this->decSeparator, $this->kSeparator);
	}
 
	/**
	 * method to set the required information for the formatted method.  
	 * @return void
	 * @param integer $decPlaces[optional]	defaults to 2
	 * @param string $decSeparator[optional]	defaults to . 
	 * @param string $kSeparator[optional]	defaults to ,
	 */
	public function setFormat($decPlaces=2, $decSeparator='.', $kSeparator=','){
		list($this->decPlaces, $this->decSeparator, $this->kSeparator) = func_get_args();
	}
 
	/**
	 * method to ratched the day property by one day
	 * @return void
	 */
	private function incrementDay(){
		$this->day = strtotime('+1 day', $this->day);
	}
 
	/**
	 * method to determine whether we need to get more data.  if we do, go and get it
	 * @return void
	 */
	private function addRates(){
		global $wpdb;
		$query = "select max(xdate) as mD from {$this->table}";
		$s = $this->pdo->query($query);
		$obj = $s->fetchObject();
		$day = $obj->mD;
		unset($s);
		if ($day < $this->firstSupportedDay){
			$day = $this->firstSupportedDay;
		}
		$this->day = $day;
		if ($this->day < $this->today){
			while ($this->day <= $this->today){
				if ($this->day !== $this->firstSupportedDay){ //increment the day to avoid duplication
					$this->incrementDay();
				}
				set_time_limit(30);
				$this->getDailyRates($this->day);
				$this->incrementDay();
			}
			if (!empty($this->parser)){
				xml_parser_free($this->parser);
				unset($this->parser);
			}
		}
	}
 
	/**
	 * method to obtain a day's currency pairs from the yahoo api
	 */
	private function getDailyRates($day){
		$date = date('Ymd', $day);
		$url = "[URL unfurl="true"]http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote;date=$date;currency=true?view=basic&format=xml&callback=currencyConverter.addConversionRates";[/URL]
		$ch = curl_init();
		curl_setopt($ch, CURLOPT_URL, $url);
		curl_setopt($ch, CURLOPT_HEADER, false);
		curl_setopt($ch, CURLOPT_FRESH_CONNECT, true);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
		curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
		$response = curl_exec($ch);
		$tdb = $this->getRatePairs($response);
		$this->writeDB($tdb, $day);
	}
 
	/**
	 * helper method to set up the insert statement for optimised db handling
	 * @return 
	 */
	private function prepareInsertStatement(){
		$sql = "insert into $this->table (xdate, rate, symbol) values (?, ?, ?)";
		$statement =  $this->pdo->prepare($sql);
		if ($statement === false){
			die (print_r($this->pdo->errorinfo(), true));
		}
		$this->insertStatement = $statement;
 
	}
 
	/**
	 * method to write the currency data to the database
	 * 
	 * @return void
	 * @param object $obj  an object holding the currency rate data
	 * @param int $date - unix date of the currency rate data
	 */
	private function writeDB($obj, $date){
		if (empty($obj->symbol)) return;
		if (empty($this->insertStatement )){
			$this->prepareInsertStatement();
		}
		$result = $this->insertStatement->execute(array($date, $obj->price, $obj->symbol));	
		if ($result === false){
			die (print_r($this->insertStatement->errorInfo(), true));
		}
	}
 
	/**
	 * method to parse the incoming data from yahoo api
	 * 
	 * @return void
	 * @param string $xml
	 */
	private function getRatePairs($xml){
		if (empty($this->parser)){
			$this->parser = xml_parser_create();
		    xml_parser_set_option($this->parser, XML_OPTION_CASE_FOLDING, 0);
		    xml_parser_set_option($this->parser, XML_OPTION_SKIP_WHITE, 1);
		}
	    xml_parse_into_struct($this->parser, $xml, $values, $tags);
 
	    // loop through the structures
	    foreach ($tags as $key=>$val) {
	        if ($key == "resource") {
	            $dataRanges = $val;	
	            for ($i=0; $i < count($dataRanges); $i+=2) {
	                $offset = $dataRanges[$i] + 1;
	                $len = $dataRanges[$i + 1] - $offset;
	                $obj = $this->parseFields(array_slice($values, $offset, $len));
					$this->writeDB($obj, $this->day);
	            }
	        } else {
	            continue;
			}
		}
	}
 
	/**
	 * method to parse individual data sets from the xml field
	 * @return object
	 * @param array $fields
	 */
	private function parseFields($fields) {
		$d = array('symbol', 'price');
		$obj = new stdClass();
	    foreach ($fields as $r){
	    	//$item = array();
	    	if ($r['type'] == 'complete'){
	    		if (in_array($r['attributes']['name'], $d)){
	    			$obj->$r['attributes']['name'] = $r['value'];
	    		}
	    	}
	    }
		if (isset($obj->symbol)){
			$obj->symbol = str_replace('=X', '', $obj->symbol);
		}
		return $obj;
	}
 
	/**
	 * method to create the necessary tables
	 * @return 
	 */
	private function init(){
		$query[] = <<<SQL
CREATE TABLE if not exists {$this->table} (
 symbol varchar(4) not null,
 rate float (10,4) not null,
 xdate int (12) not null,
PRIMARY KEY (symbol, xdate) )
SQL;
 
		foreach ($query as $q) {
			$result = $this->pdo->exec($q);
			if ($result === false){
				die (print_r($this->pdo->errorinfo(), true));
			}
		}
	}
}
?>

 

[ttuser4]

thank you!

 

[ttuser4]

i forgot to ask - does it work with php4 or it requires php5?

 

[jpadie]

php5 only. it can be easily converted to php4 but not with PDO. you would have to convert to native mysql and remove the references to public/private.

 

[jpadie]

follows a php4, native mysql, version of the class. i have not tested this, so it really may not work. post back with any syntax errors etc.

<?php

class exchangeRates{
    var $fields = array('from', 'to', 'date', 'amount');
    var $table = 'currency';

    var $firstSupportedDay = 1167606000 ; //1st Jan 2007
 
    var $hostName = '';
    var $dbName = '';
    var $user = '';
    var $pass = '';
    /**
     * constructor function
     * @return void
     */
    function __construct(){
        mysql_connect($this->hostName, $this->user, $this->pass) or $this->bail(__LINE__);
		mysql_select_db($this->dbName) or $this->bail(__LINE__);
        $this->init();
        $this->today = strtotime('today');
        if (empty($this->date)){
            $this->date = $this->today;
        }
        $this->addRates();
        if (isset($this->statement)) $this->statement= null;
    }
 	/**
 	 * for php4 compatibility
 	 */
 	function exchangeRates(){
 		$this->__construct();
 	}
	
    /**
     * main API for the conversion
     * @return a [formatted] string for the currency conversion
     * @param string $from  the ISO code of the currency you want to convert from
     * @param string $to the ISO code of the currency you want to convert tp
     * @param float $amount[optional]  the amount of the from currency you wish to convert. defaults to 1
     * @param int $date[optional] the day for the conversion expressed as a unix time stamp. defaults to today's date.  M
     * @param bool $format[optional] whether to format the returned information. defaults to false (unformatted
     */
    
	function convert($from, $to, $amount, $date=null, $format=false){
		global $wpdb;
		if(empty($date)){
			$date = $this->today;
		}
		$sql = <<<SQL
SELECT t.rate,t.symbol,t.xDate
FROM   currency t
INNER JOIN (SELECT symbol, max(xDate) as maxDate
            FROM   currency
            WHERE  xDate <= %d
            AND    symbol in ('%s', '%s')
            GROUP BY symbol) AS m
ON (m.symbol = t.symbol
AND m.maxdate = t.xDate)
WHERE t.symbol in ('%s', '%s')
SQL;
		$query = sprintf($sql, $date, $from, $to, $from, $to);
		$result = @mysql_query($query) or $this->bail(__LINE__, $query);
		
		while($pair = mysql_fetch_object($result)){
			if ($pair->symbol == $from){
				$this->fromRate = $pair->rate;
			} else {
				$this->toRate = $pair->rate;
			}
		}
		
		//convert to USD
		$usd = $amount / $this->fromRate;
		$conversion = $usd * $this->toRate;
		if ($format) {
			return $this->getSymbol($to) .  $this->formatted($conversion);
		} else {
			return $conversion;
		}
	}
	
	function getSymbol($iso){
		//will perform lookup of currency symbols.  for the time being return the ISO symbol
		return $iso;
	}
	function formatted ($number){
		return number_format($number, $this->decPlaces, $this->decSeparator, $this->kSeparator);
	}
	
	function setFormat($decPlaces=2, $decSeparator='.', $kSeparator=','){
		list($this->decPlaces, $this->decSeparator, $this->kSeparator) = func_get_args();
	}
	function incrementDay(){
		$this->day = strtotime('+1 day', $this->day);
	}

	function addRates(){
		$query = "select max(xdate) as mD from {$this->table}";
		$s = mysql_query($query) or $this->bail(__LINE__, $query);
		$obj = mysql_fetch_object($s);
		$day = $obj->mD;
		unset($s);
		if ($day < $this->firstSupportedDay){
			$day = $this->firstSupportedDay;
		}
		$this->day = $day;
		if ($this->day < $this->today){
			while ($this->day <= $this->today){
				if ($this->day !== $this->firstSupportedDay){ //increment the day to avoid duplication
					$this->incrementDay();
				}
				set_time_limit(30);
				$this->getDailyRates($this->day);
				$this->incrementDay();
			}
			if (!empty($this->parser)){
				xml_parser_free($this->parser);
				unset($this->parser);
			}
		}
	}
	
	function getDailyRates($day){
		$date = date('Ymd', $day);
		$url = "[URL unfurl="true"]http://finance.yahoo.com/webservice/v1/symbols/allcurrencies/quote;date=$date;currency=true?view=basic&format=xml&callback=currencyConverter.addConversionRates";[/URL]
		$ch = curl_init();
		curl_setopt($ch, CURLOPT_URL, $url);
		curl_setopt($ch, CURLOPT_HEADER, false);
		curl_setopt($ch, CURLOPT_FRESH_CONNECT, true);
		curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
		curl_setopt($ch, CURLOPT_CONNECTTIMEOUT, 30);
		$response = curl_exec($ch);
		$tdb = $this->getRatePairs($response);
		$this->writeDB($tdb, $day);
	}
	
	function prepareInsertStatement(){
		$sql = "insert into $this->table (xdate, rate, symbol) values (?, ?, ?)";
		$statement =  $this->pdo->prepare($sql);
		if ($statement === false){
			die (print_r($this->pdo->errorinfo(), true));
		}
		$this->insertStatement = $statement;
		
	}
	
	function writeDB($obj, $date){
		if (empty($obj->symbol)) return;
		$sql = "insert into $this->table (xdate, rate, symbol) values (%d, '%s', '%s')";
		$data = array_map('mysql_real_escape_string', array($date, $obj->price, $obj->symbol) );
		$query = vpsrintf($sql, $data);
		@mysql_query($query) or $this->bail(__LINE__, $query);
	}
	
	function getRatePairs($xml){
		if (empty($this->parser)){
			$this->parser = xml_parser_create();
		    xml_parser_set_option($this->parser, XML_OPTION_CASE_FOLDING, 0);
		    xml_parser_set_option($this->parser, XML_OPTION_SKIP_WHITE, 1);
		}
	    xml_parse_into_struct($this->parser, $xml, $values, $tags);
	   
	    // loop through the structures
	    foreach ($tags as $key=>$val) {
	        if ($key == "resource") {
	            $dataRanges = $val;	
	            for ($i=0; $i < count($dataRanges); $i+=2) {
	                $offset = $dataRanges[$i] + 1;
	                $len = $dataRanges[$i + 1] - $offset;
	                $obj = $this->parseFields(array_slice($values, $offset, $len));
					$this->writeDB($obj, $this->day);
	            }
	        } else {
	            continue;
			}
		}
	}
	
	function parseFields($fields) {
		$d = array('symbol', 'price');
		$obj = new stdClass();
	    foreach ($fields as $r){
	    	//$item = array();
	    	if ($r['type'] == 'complete'){
	    		if (in_array($r['attributes']['name'], $d)){
	    			$obj->$r['attributes']['name'] = $r['value'];
	    		}
	    	}
	    }
		if (isset($obj->symbol)){
			$obj->symbol = str_replace('=X', '', $obj->symbol);
		}
		return $obj;
	}
	
	function init(){
		$query = <<<SQL
CREATE TABLE if not exists $this->table (
 symbol varchar(10) ,
 rate float(10,4) ,
 xdate int(15),
PRIMARY KEY (symbol, xdate))
SQL;
		@mysql_query($query) or $this->bail(__LINE__, $query);
	}
	
	function bail($line, $query = null){
		$error = mysql_error();
		$query = empty($query) ? '' : <<<HTML
<p>Query was:<br/>
<pre>
$query
</pre>
</p>
HTML;	
		echo <<<HTML
<h1>Database Error</h1>
$query
<p>Error occurred at line $line. <br/>
Error message was: <span style="color:red">$error</span></p>
HTML;
		exit();
	}
}
$xR = new exchangeRates();
$xR ->setFormat(2,'.', ',');
echo "100  GBP is " . $xR->convert('EUR', 'GBP', '1399', null, true) ;
?>