Re: Currency payment issue

[Ed W <..hidden..>]

Hi

Actually, I just have a script which pulls the rates from XE.com every
morning.  Breaks every year or so, but other than that it's fine

Care to contribute it?

Based on the script provided by Dieter some years back:

crontab has:

0 9 * * * /usr/local/sbin/update_sql_xrates.sh

In that file is the attached script (nippynetworks is the name of my database in case it's not obvious...)

The correct "accounting" way to handle deviations from the xe.com rates
is to post the difference in fx gain/loss

Agreed.    I think we should store the XE.com rate in the db, and another one attached to the invoice.  We can then regenerate the difference wrt gain/loss properly this way.

Err, yeah agree, but it shouldn't be calculated on the fly I think?  Lets just post it

However, yes, that's basically what is missing is a form to take in the second rate and calculate all the fx adjustments.  It should be in the cash transfer page to do this I feel

I'm just right out of time this week.  If I email some screen shots to someone off list would they be prepared to write up the FX transaction entry for me?

Cheers

Ed W

#!/bin/bash

if [ $1 ]; then
        TODAY=$1
else
        TODAY=`date --rfc-3339='date'`
fi

# get exchangerate from XE
USD=`lynx -dump "http://www.xe.com/ucc/convert.cgi?Amount=1&From=USD&To=GBP"; | grep "1 USD =" | awk '{print $4}'`
EUR=`lynx -dump "http://www.xe.com/ucc/convert.cgi?Amount=1&From=EUR&To=GBP"; | grep "1 EUR =" | awk '{print $4}'`
if [ -n $EUR ] ; then
    EXIST=`psql -U sql-ledger -t -c "SELECT * FROM exchangerate WHERE curr = 'EUR' AND transdate = '$TODAY';" nippynetwo
rks`
    if [ -n "$EXIST" ] ; then
        psql -U sql-ledger -q -c "DELETE FROM exchangerate WHERE curr='EUR' AND transdate='$TODAY';" nippynetworks
    fi
#    echo "EUR: $EUR"
    psql -U sql-ledger -q -c "INSERT INTO exchangerate (curr,transdate,buy,sell) VALUES('EUR','$TODAY',$EUR + 0.01,$EUR)
;" nippynetworks
else
    echo "Failed to retrieve EUR rates"
fi

if [ -n $USD ] ; then
    EXIST=`psql -U sql-ledger -t -c "SELECT * FROM exchangerate WHERE curr = 'USD' AND transdate = '$TODAY';" nippynetwo
rks`
    if [ -n "$EXIST" ] ; then
        psql -U sql-ledger -q -c "DELETE FROM exchangerate WHERE curr='USD' AND transdate='$TODAY';" nippynetworks
    fi
#    echo "USD: $USD"
    psql -U sql-ledger -q -c "INSERT INTO exchangerate (curr,transdate,buy,sell) VALUES('USD','$TODAY',$USD + 0.01,$USD)
;" nippynetworks
else
    echo "Failed to retrieve USD rates"
fi