Automation of exchange rates retrieving

[86ds8DR]

FrontAccounting has a nice feature of automatic updates of exchange rates, see here:

http://frontaccounting.com/fawiki/index.php?n=Help.ExchangeRates

I didn't find such a functionality in linet. I want to write a script that will do it. Is it enough to update the table curRates or there are other tables/files I need to update?

[adam2314]

it is a nice feature, will you publish the script afterwards?

i would have used this as a valid source http://www.boi.org.il/he/Markets/Pages/explainxml.aspx

and don't update curRates as we need to have history, only insert new records. the system will get the latest cur rate, that is all you need to do.

[86ds8DR]

I wrote a little Perl script that seems to work.

You can run it with crontab once a day, or with anacrontab if the computer is not 24/7 on.

There are a few values that must be configured at the beginning of the script.

Disclaimer: I'm not responsible for any harm it may do, use on your own risk.

#!/usr/bin/perl -w

##########  CUSTOMIZATION: 

#~ 1. Install missing dependieces for those packages.
#~ E.g., in Debian-based systems all you need is, probably,
#~ apt-get install libdatetime-format-mysql-perl

use strict;
use LWP::Simple;
use POSIX qw(strftime);
use DBI;
use DateTime::Format::MySQL;

#~ 2. change the following values according to your installation:

my $mysql_host = 'localhost';
my $mysql_database = 'linet';
my $mysql_table = 'CA1_curRates';  ### seems to be default in linet3
my $mysql_user = 'root';
my $mysql_password = 'topsecret';

my $currency_num = '01';  # USD
my $currency_sym = 'USD';

#~ The following values are the currency code options:
#~ 01        Dollar              United States
#~ 27        Euro                European Monetary Union
#~ For more currency codes and other info see:
#~ http://www.boi.org.il/en/Markets/Pages/explainxml.aspx

###########  END OF CUSTOMIZATION

# Connect to the linet database
my $dbh = DBI->connect("DBI:mysql:database=$mysql_database", "$mysql_user", "$mysql_password") || die "Could not connect to database $mysql_database: $DBI::errstr";

# Find the date of the latest recorded exchange rate
my $sth = $dbh->prepare("SELECT MAX(date) FROM $mysql_table WHERE currency_id = '$currency_sym'") || die "Select of recent date failed: $DBI::errstr";
$sth->execute();
my @row = $sth->fetchrow_array();
$sth->finish();
my $latest_date;

# if there is no rates at all, start retrieving from January 01 of the current year
if (not defined $row[0]) { 

    $latest_date = DateTime->now(time_zone => 'local')->truncate( to => 'year');
    print "Didn't found any rates in the database, will retrieve starting from".$latest_date->strftime("%e %B %Y")."\n";

} else {

    $latest_date = DateTime::Format::MySQL->parse_datetime($row[0]);

    # Set time to 00:00:00, since only the date is important
    $latest_date = $latest_date->truncate( to => "day" );

    print "latest recorded exchange rate is for".$latest_date->strftime("%e %B %Y")."\n";
}

my $today = DateTime->now(time_zone => 'local');
# Set time to 00:00:00, since only the date is important
$today = $today->truncate( to => "day" );

my $rate;

if ($latest_date < $today) {

    # Retrieve and insert to the database all the missing rates since the latest recorded

    my $date = $latest_date->add( days => 1 );

    while ($date <= $today) {

        if ($date->day_of_week() > 5) {  #  day_of_week returns 1-7, 1=Monday

            print "skipping weekend days (Sat and Sun)...\n"  

        } else {

            print "retrieving $currency_sym rate for ".$date->strftime("%e %B %Y")."...\n";

            my $xml_responce = get("http://www.boi.org.il/currency.xml?rdate=".$date->strftime("%Y%m%d")."&curr=$currency_num");
            die "Couldn't get it!" unless defined $xml_responce;

            if ($xml_responce =~ /No exchange rate published for this date/) {

                if ($date == $today) {
                    print "cannot retrieve today's rate, probably not published yet.\n"
                } else {
                    print "cannot retrieve rate for this date, holiday?\n";
                }

            } elsif ($xml_responce =~ /<RATE>([\d\.]+)<\/RATE/) {

                $rate = $1;
                print "got a rate, 1 $currency_sym = $rate NIS\n";
                print "inserting into database...\n";

                $dbh->do(qq{INSERT INTO $mysql_table (currency_id,date,value) VALUES  ('$currency_sym', '$date->strftime("%Y%m%d %H:%M:%S")', $rate)}) || die "Insert into database failed: $DBI::errstr";

                print "done.\n"

            } else {
                print "Got an unknown responce from BOI, here it is:\n$xml_responce\n";
            }
        }
        $date->add( days => 1 );
        sleep 1;
    }
} else {
    print "We aready have the today's rate, nothing to do.\n";
}
$dbh->disconnect();

[aribhour]

Thanks a lot for your good idea and code contribution.