# Currency rate autoupdate

**URL:** <https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367>\
**Category:** Using my Dolibarr\
**Tags:** Cron, Scheduled-Jobs\
**Created:** [July 30, 2020, 7:16am UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367 "2020-07-30T07:16:26Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hamid](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/h/5fc32e/32.png) [@Hamid](https://www.dolibarr.org/forum/u/Hamid)\
**Post date:** [July 30, 2020, 7:16am UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/1 "2020-07-30T07:16:26Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![erikvanberkum](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/erikvanberkum/32/2709_2.png) [@erikvanberkum](https://www.dolibarr.org/forum/u/erikvanberkum)\
**Post date:** [August 5, 2020, 7:11am UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/2 "2020-08-05T07:11:52Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![saxa](https://www.dolibarr.org/forum/user_avatar/www.dolibarr.org/saxa/32/2795_2.png) [@saxa](https://www.dolibarr.org/forum/u/saxa)\
**Post date:** [August 5, 2020, 8:55pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/3 "2020-08-05T20:55:48Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![egils](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/e/a88e57/32.png) [@egils](https://www.dolibarr.org/forum/u/egils)\
**Post date:** [August 6, 2020, 10:19am UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/4 "2020-08-06T10:19:29Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Hamid](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/h/5fc32e/32.png) [@Hamid](https://www.dolibarr.org/forum/u/Hamid)\
**Post date:** [August 6, 2020, 12:43pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/5 "2020-08-06T12:43:59Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![andymaccallan](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/a/b2d939/32.png) [@andymaccallan](https://www.dolibarr.org/forum/u/andymaccallan)\
**Post date:** [September 14, 2020, 10:48am UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/6 "2020-09-14T10:48:19Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![AlbertInio](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/a/bbce88/32.png) [@AlbertInio](https://www.dolibarr.org/forum/u/AlbertInio)\
**Post date:** [March 14, 2023, 7:15pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/7 "2023-03-14T19:15:38Z")

</div>

Hi Everybody,  
I look for that problem.  
My solution was to deploy a small docker image on my stack for automatically updating the currency.  
The code (Typescript) is here [GitHub - ismogroup/dolirate: Small tool for automatically update currency rates in Dolibarr](https://github.com/ismogroup/dolirate)  
The main idea is:  
1- query the dolibarr database for retrieving the base currency and the used currencies  
2- make a request to api•apilayer•com/exchangerates\_data (250 free requests / month) for retrieving the rates  
3- INSERT INTO llx\_multicurrency\_rate

the main logic is here:

> <https://github.com/ismogroup/dolirate/blob/main/src/dolirate.ts>

Why Typescript, because the logic is integrated in a more complex NodeJS environment .  
Hope it can help someone to build a better solution. But yet it is a sufficient solution for me.

---

<div class="post-metadata">

**Author:** ![alfim](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/a/ecb155/32.png) [@alfim](https://www.dolibarr.org/forum/u/alfim)\
**Post date:** [January 15, 2024, 7:49pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/8 "2024-01-15T19:49:02Z")

</div>

> [@AlbertInio](#):
>
> GitHub - ismogroup/dolirate: Small tool for automatically update currency rates in Dolibarr

Hello. How can we add this update module as an add-on to the dolibarr infrastructure on a shared hosting running under a standard subdomain?

---

<div class="post-metadata">

**Author:** ![jonbendtsen](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/j/977dab/32.png) [@jonbendtsen](https://www.dolibarr.org/forum/u/jonbendtsen)\
**Post date:** [January 16, 2024, 5:28pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/9 "2024-01-16T17:28:21Z")

</div>

> [@alfim](#):
>
> Hello. How can we add this update module as an add-on to the dolibarr infrastructure on a shared hosting running under a standard subdomain?

You can not, because it is not a Dolibarr module, it is a stand alone tool that serves up it’s own webserver.

_IF_ you ran your Dolibarr inside a container (I use this image [Docker](https://hub.docker.com/r/tuxgasy/dolibarr)) then perhaps you could run this tool inside the same container.

You would probably have a real hard time running it on a remote server because most likely your Dolibarr database would not be reachable over the internet due to security concerns.

| variable | usage | default |
| --- | --- | --- |
| MYSQL\_HOST | fqdn of mysql server | mysql |
| MYSQL\_PORT | port of the mysql server | 3306 |
| DOLI\_DB\_USER | the user with access to the Dolibarr database | mysql |
| DOLI\_DB\_PASSWORD | the password associated with the user | mysql |
| DOLI\_DB\_PREFIX | the table prefix | llx\_ |
| DOLI\_DB\_NAME | the name of the database | dolismo |
| API\_LAYER\_KEY | api key for [Exchange Rates Data API - APILayer](https://apilayer.com/marketplace/exchangerates_data-api) | tobefilled |

---

<div class="post-metadata">

**Author:** ![jonbendtsen](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/j/977dab/32.png) [@jonbendtsen](https://www.dolibarr.org/forum/u/jonbendtsen)\
**Post date:** [January 16, 2024, 5:31pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/10 "2024-01-16T17:31:43Z")

</div>

Actually thinking more about it, I think that if this ran inside the same container as Dolibarr, then I suppose that a CRON job could reach it - or someone could make a module for Dolibarr that would trigger it.

---

<div class="post-metadata">

**Author:** ![alfim](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/a/ecb155/32.png) [@alfim](https://www.dolibarr.org/forum/u/alfim)\
**Post date:** [January 30, 2024, 2:47pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/11 "2024-01-30T14:47:16Z")

</div>

The server with Plesk panel installed directly belongs to me. There are different domain names within the server. I created a subdomain in one of these domains. Docker and Git plugins are also installed on the Plesk panel server. I can do it if you can briefly describe to me how to add it.

For example.

There is a domain name called [erp.example.com](http://erp.example.com) and dolibarr is actively running here. If you could briefly describe what and how I can do on the server control panel, I can do it. Thanks so much in advance.

---

<div class="post-metadata">

**Author:** ![alfim](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/a/ecb155/32.png) [@alfim](https://www.dolibarr.org/forum/u/alfim)\
**Post date:** [January 30, 2024, 2:48pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/12 "2024-01-30T14:48:23Z")

</div>

I think it would definitely be great if you developed it as a module.

---

<div class="post-metadata">

**Author:** ![jonbendtsen](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/j/977dab/32.png) [@jonbendtsen](https://www.dolibarr.org/forum/u/jonbendtsen)\
**Post date:** [January 30, 2024, 5:38pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/13 "2024-01-30T17:38:17Z")

</div>

> [@alfim](#):
>
> The server with Plesk panel installed directly belongs to me. There are different domain names within the server. I created a subdomain in one of these domains. Docker and Git plugins are also installed on the Plesk panel server. I can do it if you can briefly describe to me how to add it.

sorry, I don’t use plesk, but I am sure that you could run this tool on your plesk server

---

<div class="post-metadata">

**Author:** ![jonbendtsen](https://www.dolibarr.org/forum/letter_avatar_proxy/v4/letter/j/977dab/32.png) [@jonbendtsen](https://www.dolibarr.org/forum/u/jonbendtsen)\
**Post date:** [January 30, 2024, 5:39pm UTC](https://www.dolibarr.org/forum/t/currency-rate-autoupdate/19367/14 "2024-01-30T17:39:11Z")

</div>

> [@alfim](#):
>
> I think it would definitely be great if you developed it as a module.

who you?

I am not even a PHP developer, and I have my own stuff in Dolibarr I want to fix or create