Currency conversion

Murrx · April 27, 2017, 10:58am

Hi everyone,

As an extension to OIPA’s functionality we would like to add the option to convert values to a specific currency. For example, this would make it possible to do aggregations on transaction in different currencies.

To be able to do this we need an exchange rate dataset. Datasets like this are published by IMF, World Bank and OECD and others. However, we are unsure what dataset is best suited for this purpose, seeing how they seem to differ and secondly we do not want to make use general market rates.

Does the IATI community have an opinion on what dataset would be most accurate to use for this use?

Work has been done by ourselves last year, D-Portal and work on the BD AIMS has been done, but no general solution exists that could work for IATI works.

Suggestions more than welcome.

Regards, Robin

dalepotter · February 19, 2016, 4:43pm

Hi Robin,

Thanks for posting this - this has also been a timely problem for the central tech team too. In October we added 2014 IATI spend $USD aggregations for all publishers on the Dashboard coverage tab.

We spoke to D-Portal about how they are managing conversions and their approach seems to be to scrape daily exchange rates from the IMF website each night, and then to save a local copy in Git. When the data is collected, these are averaged out for the whole month, and that average rate is used for USD (based on the month in the value-date).
(I’ve also asked @Matt for some clarification on the process here, as the IMF link appears to only list currency to SDR rates, rather than currency to USD rates.)

They also have an ‘exchange’ tab on a Google sheet that is used for for currencies which aren’t in the nightly IMF data. This stores exchange rate data on an annual basis.

In terms of the implementation on the Dashboard, we went for an MVP approach, using the annual data in the D-portal exchange tab. The spreadsheet we use, together with logic is available here: https://github.com/IATI/IATI-Stats/tree/master/helpers/currency_conversion In time, we would like to move to a monthly granularity, based on automated inputs of IMF data, although this is not a priority at present.

Would be interesting to hear others perspectives, and do post back with details, in due course, of your implementation approach @Murrx

rolfkleef · February 24, 2016, 11:43am

I’ve started collecting rates from http://openexchangerates.org (daily rates available from 1999 onward), interesting to compare these. I guess an SDR exchange rate makes sense too.

To throw in some of my questions:

Why would “one exchange rate per year” be good enough? It appears to me that some currencies are too volatile to capture in just one rate for a whole year?
What are current approaches to changes of the “base rate” over time? Sometimes you just want to aggregate (for instance) dollar amounts as they occur, sometimes you’d like to express things in “dollars of the year 2000”.

matmaxgeds · February 26, 2016, 1:57pm

Hi,

Our work in Bangladesh has also identified that many donors have their own exchange rates (often set by their central bank / ministry of finance) and therefore when converting projects between currencies, these are the ones that need using. For example, the Netherlands update their EUR to USD rate annually, and this is the rate that should be used for converting the values on their projects - using another rate would be might reduce the value of IATI data as it would no longer match the official values expected by the publisher. This is also often an issue for loans, where the loan agreement might include an official rate of conversion for the life of the loan.

Is there somewhere in IATI that would allow publishers to include this information e.g. an exchange rate field, to go after the currency and value fields for the transactions? This would also massively increase the utility of IATI for monitoring debt data - integration of IATI feeds into CS-DRMS and DFMAS would be a huge win for recipient countries.

Matt

dalepotter · July 1, 2016, 10:42am

Hi @matmaxgeds - thanks for flagging this.

As I don’t think that it is possible to sent an explicit exchange rate for a given transaction, I’ve added a post to describe this in our separate forum for Modifications, Additions, Improvements to the IATI standard. This means that it will be considered for inclusion in future upgrades of the standard.

matmaxgeds · July 1, 2016, 11:18am

Hi @dalepotter, great, thanks - let me know if there is anything I can do to make it happen e.g. rounding up examples, or testing etc. Also registering with the iatisupport site so I am available there too.

Matt

Wendy · January 4, 2017, 11:38am

This issue was originally added to the previous version IATI Standard Upgrade forum. However that forum has itself now itself moved to the IATI Discuss platform so I have recatagorised this topic for consideration for the next IATI Standard upgrade

Herman · February 16, 2017, 9:31am

Exchange rates are very dependent on the use-case. Therefore IATI data users will have different requirements. So I do not think we should try to add exchange-rates to the standard.

markbrough · February 16, 2017, 5:34pm

Hi @Herman

The specific use case here is for loans and debt repayment, where agreements will specify a specific exchange rate. This would be very helpful for making IATI useful for debt management.

At the moment, we can use the value-date to find the prevailing exchange rate to the target currency. It generally does not matter enormously (especially with grants) if there are slight differences in the rate we end up with from the rate at which the funds were actually converted (as a result of using a slightly different date or a different source for the rates). However, with loans this does matter as there is a specific amount that has to be paid back and because it makes a difference to the country’s debt stock. Incidentally, almost all countries have debt management software that could benefit from using this data (and are one of the principal collectors / users of aid data in many countries).

So, making it possible to provide the actual exchange rate from the agreement, where possible, would help satisfy an important use case. Though of course, nobody would be forced to use it.

Herman · February 17, 2017, 8:41am

Hi @markbrough,
I am not sure. The interest rate you mention, if I understand you correctly, is an attribute of the contract and not transaction specific? We run the risk of adding a lot of elements to the transactions, which do not really belong there. Would this be a case for linked open data (IATI <-> open contracts)?

matmaxgeds · February 24, 2017, 11:13am

Hi all,

@Herman yes, for many agreements the exchange rate is at the level of the contract - where would this go in IATI data, as a single project can be covered by several contracts - if contract = commitment, perhaps it can go there? Would that then be linked to all transactions made under that contract - I do not know enough about the nesting etc to be sure?

However, there is another situation. Attached below are a set of screenshots showing how the statement for a loan specifies (or allows calculation of) the exchange rates for the different value dates. In this case, the exchange rate is set per transaction.

The reason I am inclined to push this is that in most aid recipient countries, the debt management office/unit is the most competent user of aid flow data as it tends to be a far stronger legal requirement to track the disbursements as more disbursements means more indebtedness/repayments. If IATI could allow transaction level exchange rates (specifying the sending/loan currency, the rate applied on that value-date and the currency of receipt) then it might pick up a significant number of users. If not, all aid recipient countries will continue to collect this data separately/manually so are likely to continue to find IATI peripheral to their needs.

The ideal endpoint is that CS-DRMS/DEMFAS debt management systems would pull data directly from IATI, eliminating a huge amount of manual data entry and potential for error.

The bigger issue here (and also starting to crop us in traceability etc) is that IATI is not an accounting system e.g. with double entry etc. If it was, then all transactions would have a debit and a credit, a sender and a receiver (with an official receiver/org ID), a send currency, a receipt currency (and therefor exchange rates built in). In this scenario, traceability is much easier to solve and I think this has to be a serious consideration going forwards.

As it is, if a donor reports in e.g. SDR or Japanese Yen, but the recipients receive the funds into a local currency bank account, they typically struggle to make any connection with the amounts shown in IATI data - and again are disinclined to use IATI.

/end ramble!

Matt