Currency rate autoupdate

Good morning,
I have a number of currencies, in my Multi-Currency module, of Dollibar ver. 12.0.1, used for purchases and sales alike; the base currency is Euro and would like to have the rates automatically updated or synced with an online Forex rate provider or a bank. I have searched all related topics in this forum and searched dolistore for a solution, but to no avail. Hence, I should be grateful you would help me resolve this challenge.
Many thanks.

I am not sure an auto-update feature is available in Dolibarr, i didnt check very well. I have implemented this totally in mysql so we will not have issues when Dolibarr is updated.

Step 1 create a mysql currency table (preferably new database not in the Dolibarr database)
Step 2 auto update the currency table with a cron job every hour or every day as you like
Step 3 Use a mysql event to get last row of data from the currency table and write that data to llx_multicurrency_rate

I use the following code of php, which is called by the cron job.

<?php

// script to import currency data into a mysql table.
// connect to mysql db
// https://fixer.io/documentation
	$dns = 'mysql:host=localhost;dbname=DATABASE-NAME';
	$con = new PDO($dns, 'DATABASE-USER', 'DATABASE-PASSWORD', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8"));
    $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    //read the json file contents
    $jsondata = file_get_contents('http://data.fixer.io/api/latest?access_key=PASTE_API_KEY%20base=EUR&symbols=USD,JPY,GBP,CAD,AUD');
    
    //convert json object to php associative array
    $data = json_decode($jsondata, true);

    $success = $data['success'];
    $timestamp = $data['timestamp'];
    $base = $data['base'];
    $date = $data['date'];
    // Eur base rates from JSON
    $eur_usd = $data['rates']['USD'];
    $eur_jpy = $data['rates']['JPY'];
    $eur_gbp = $data['rates']['GBP'];
    $eur_cad = $data['rates']['CAD'];
    $eur_aud = $data['rates']['AUD'];
    // rebase to inverse JPY
    $jpy_usd = $eur_jpy / $eur_usd;
    $jpy_gbp = $eur_jpy / $eur_gbp;
    $jpy_cad = $eur_jpy / $eur_cad;
    $jpy_aud = $eur_jpy / $eur_aud;
    $jpy_eur = $eur_jpy / 1;

   $q = $con -> prepare("INSERT INTO currency (success, timestamp, base, date, jpy_usd, jpy_gbp, jpy_cad, jpy_aud, jpy_eur, eur_usd, eur_jpy, 
                                                eur_gbp, eur_cad, eur_aud)
                                        VALUES (:success, :timestamp, :base, :date, :jpy_usd, :jpy_gbp, :jpy_cad, :jpy_aud, :jpy_eur, :eur_usd, :eur_jpy, 
                                                :eur_gbp, :eur_cad, :eur_aud)");


$q-> bindValue(":success",$success,PDO::PARAM_STR);
$q-> bindValue(":timestamp", $timestamp,PDO::PARAM_STR);
$q-> bindValue(":base", $base,PDO::PARAM_STR);
$q-> bindValue(":date", $date,PDO::PARAM_STR);
$q-> bindValue(":jpy_usd", $jpy_usd,PDO::PARAM_INT);
$q-> bindValue(":jpy_gbp", $jpy_gbp,PDO::PARAM_INT);
$q-> bindValue(":jpy_cad", $jpy_cad,PDO::PARAM_INT);
$q-> bindValue(":jpy_aud", $jpy_aud,PDO::PARAM_INT);
$q-> bindValue(":jpy_eur", $jpy_eur,PDO::PARAM_INT);
$q-> bindValue(":eur_usd", $eur_usd,PDO::PARAM_INT);
$q-> bindValue(":eur_jpy", $eur_jpy,PDO::PARAM_INT);
$q-> bindValue(":eur_gbp", $eur_gbp,PDO::PARAM_INT);
$q-> bindValue(":eur_cad", $eur_cad,PDO::PARAM_INT);
$q-> bindValue(":eur_aud", $eur_aud,PDO::PARAM_INT);
$q-> execute();

?>
<html>
<head>
<title>Welcome to currency storage</title>
</head>
<body>
<h1>Currency stored into the database</h1>
<p> <?php
echo nl2br ("jpy_usd -> " . $jpy_usd . "\n");
echo nl2br ("jpy_gbp -> " . $jpy_gbp . "\n");
echo nl2br ("jpy_cad -> " . $jpy_cad . "\n");
echo nl2br ("jpy_aud -> " . $jpy_aud . "\n");
echo nl2br ("jpy_eur -> " . $jpy_eur . "\n");
?> </p>
</body>
</html>


The mysql event query should look something like this, actual code depends on how you setup the above:



INSERT INTO llx_multicurrency_rate(date_sync,rate)
SELECT date, eur_usd,  FROM currency
ON DUPLICATE KEY UPDATE
		eur_usd = rate;

I hope this helps you to get some direction how to implement this relatively simple.

1 Like

Hi Erik , nice solution and a smart one. Although I think that could be nice if we could join and develop a function in multicurrency module which could be part of dolibarr and have that functionality in dolibarr available with simply enabling the module. Just my 2c.

Please add a feature request here: https://github.com/Dolibarr/dolibarr/issues if you haven’t already.

Good afternoon Erik,

Thank you for taking the time out to write the code and propose a solution. Regrettably, I have little or no knowledge of coding or implementation of the proposed solution. All I know is a Multi Currency module in a comprehensive business solution environment, such as Dolibarr, is incomplete without a currency rate auto update. I wish I could help in the development of such tool, until I can, I will trust the professional to save the day.

Does anyone here use any of trading platforms? Like, https://www.ratingfx.com/instaforex this one or other? I’m starting a new project soon and need some specifications for it.