This recipe synchronizes currency exchange rates on an as needed basis or on a periodic basis. This is extremely useful for enterprises that deal with multiple currencies need to track the exchange rates for the purpose of their commercial documentation involving money. Vtiger by default provides a currency management module, where the present implementation limitations are
- No change of data stored for trend analysis of currency rate fluctuations
- Requires updating of exchange rates manually
The present vtiger currency management module will be modified to realize the additional requirements. Yahoo Finance provides an API which is used for the purpose of this implementation. Other services exist, including the paid for real time rate queries provided by financial information agencies. While Yahoo’s rates are not real time, it is available for free and has a popularly used API.
Implementation and Features
- Independent script that would be executable either by a cron job or by a call from Vtiger currency module.
- Vtiger currency module would need to have an additional button, which when clicked, would call the current rates from Yahoo Finance and update the rates.
- A new exchange rate change table to be added to the database.
- Above features to be packed into a module and made installable via vtiger module manager installable zip file.
Present vTiger system features vtiger menu- Settings
The currency management module of vtiger is accessible from the Settings menu link. Selecting this module brings up the currency management screen where once can manage the list of currencies that are to be configured in the system. In our example, we have setup a typical situation of a enterprise that deals with vendors, suppliers and customers, channel partners from many countries, and in this case, we have selected 21 different currencies. Figure 1 : New Currency setup form 
Figure 2 : Multiple currencies setup for testing 
Figure 3 : Updates to user interface for exchange rate update 
- Scripts modified – Smarty/templates/CurrencyListView.tpl, vtigercron.php
- Scripts added – getxrates.php
- Tables modified – vtiger_currency_info
- Tables added – vtiger_currencies_rates
- Other changes – Permissions on directories, Smarty/templates
Cheat sheet using Yahoo! Finance API
- http://www.gummy-stuff.org/Yahoo-data.htm
- http://allurcode.com/2010/11/08/get-live-exchange-rates-in-php/
A premium real time forex rate service
- http://www.exchangerate-api.com/
Further enhancements
- Historical exchange rates log table storage parameters ( no of days, no of records ). See UI concept in figure
- Link to other / alternative exchange rate sources
- Store rates from more than one source
https://raw.github.com/currencybot/open-exchange-rates/master/latest.json vtiger tables’ details
- vtiger_currencies
- vtiger_currencies_seq
- vtiger_currency
- vtiger_currency_decimal_separator
- vtiger_currency_decimal_separator_seq
- vtiger_currency_grouping_pattern
- vtiger_currency_grouping_pattern_seq
- vtiger_currency_grouping_separator
- vtiger_currency_grouping_separator_seq
- vtiger_currency_info
- vtiger_currency_info_seq
- vtiger_currency_symbol_placement
- vtiger_currency_symbol_placement_seq
[code language=”PHP”]</pre>
<?php
//
//CREATE TABLE ‘vtiger_currencies_rates’ ( ‘Id’ int(11) NOT NULL auto_increment, PRIMARY KEY (‘Id’) ) ENGINE=MyISAM COMMENT=’Stores the daily exchange rates against the default currency for all other currencies found in the vtiger_currency_info table’ CHARACTER SET=’utf8′;
//ALTER TABLE vtiger_currencies_rates ADD (currency_code VARCHAR(10), exchange_rate DECIMAL(12,6), rate_date DATE, rate_time TIME);
//ALTER TABLE vtiger_currency_info ADD xr_date DATETIME;
require_once(“config.php”);
require_once(‘include/logging.php’);
require_once(‘include/utils/CommonUtils.php’);
require_once(‘include/utils/VtlibUtils.php’);
$log = &LoggerManager::getLogger(‘Get Currencies Exchange rates’);
global $adb,$log;
$tdt=date(‘Y-m-d’);
$action = $_REQUEST[‘action’];
if ($action == ‘List’ || $action == ‘list’)
{
$sql_sel=”select * from vtiger_currencies_rates order by id desc “;
$dresult = $adb->pquery($sql_sel, array());
while($row = $adb->fetch_array($dresult))
{
echo $row[‘id’] .’, ‘. $row[‘rate_date’] .’, ‘. $row[‘rate_time’] .’, ‘. $row[‘currency_code’] .’, ‘. $row[‘exchange_rate’] .'<br>’ ;
}
}
else
{
$sql_sel=”select currency_code from vtiger_currency_info where currency_name = ? “;
$dresult = $adb->pquery($sql_sel, array($currency_name));
$from = $adb->query_result($dresult,0,”currency_code”);
$sql_sel=”select currency_code from vtiger_currency_info where currency_name <> ? “;
$result = $adb->pquery($sql_sel, array($currency_name));
while($row = $adb->fetch_array($result))
// loop thru all currencies in the table, obtain the information from Yahoo Finance and insert new records into the table with a datetimestamp of loading
{
$to= $row[‘currency_code’];
$xrate = getYahooXrate($from,$to);
sleep (1);
$xval=$xrate[0];
if ($xrate && $xrate[0] > 0 )
{
$ins_sql= “INSERT INTO vtiger_currencies_rates (currency_code, exchange_rate, rate_date, rate_time) VALUES (‘$to’, $xval, CURDATE(), CURTIME() )”;
$upd_sql= “UPDATE vtiger_currency_info set conversion_rate = ?, xr_date=NOW() where currency_code= ?”;
$uresult= $adb->pquery($upd_sql, array($xval,$to) );
}
else
{
$to .= “-FAILED”;
$ins_sql= “INSERT INTO vtiger_currencies_rates (currency_code, exchange_rate, rate_date, rate_time) VALUES (‘$to’, 0, CURDATE(), CURTIME() ) ;”;
}
$iresult = $adb->pquery($ins_sql, array() );
}
}
function getYahooXrate($cur_from,$cur_to)
{
$yf_url = ‘http://finance.yahoo.com/d/quotes.csv?f=l1d1t1&s=’.$cur_from.$cur_to.’=X’;
$handle = fopen($yf_url, ‘r’);
if ($handle)
{
$result = fgetcsv($handle);
return $result;
fclose($handle);
}
else
{
return false;
}
}
?>
[/code]
Template file Smarty/templates/ CurrencyListView.tpl, near line # 52
This is the orginal code
[code language=”PHP”]
<input class=”crmButton create small” type=”submit” value=”{$MOD.LBL_NEW_CURRENCY}” />[/code]
This has to be like this
[code language=”PHP”]
<a href=”getxrates.php” target=”_blank”>Refresh Rates</a>
<input class=”crmButton create small” onclick=””alert(“Cookbook” type=”submit” value=”value=” />
<input class=”crmButton create small” onclick=””href:” type=”submit” value=”value=” />”>
<input class=”crmButton create small” type=”submit” value=”{$MOD.LBL_NEW_CURRENCY}” /> [/code]
—————————————————————————————————–
Check www.vtigress.com for the latest release of vtDZiner.
Use Promo Code vtDZinerWP and avail a discount of 5% on the sale price of vtDZiner
—————————————————————————————————–