Exchange Rate [Implementation]

[gabelula]

Hi!

For Transactions.Amount we could have a different currency than the one that was in the contract before. How do we model this? We need a way to add an exchange rate to the transaction.

[timgdavies]

This is a good question. To start thinking around a solution to this....

I'm looking for a few precedents we could build upon:

• IATI Transactions have a value-date to support currency conversion at rates from that day, but does not provide previous currencies or the actual exchange rate used;
• The Financial Products Markup Language provides very detailed schema for representing exchange rates;
• There is discussion of currencies in the Fiscal Data Package here which highlights an interesting edge case in use of "'Unidad de Fomento' (an inflation-adjusted chilean currency used in public tendering)" which might not be captured in current currency codelists.

None of this throws up a good ready-made block of schema we could use for modelling exchange rates, but will keep looking. However, it does highlight we need to consider:

• The exchange rate date;
• The exchange rate used;
• The source currency;
• The destination currency;

Questions we should also look at:

• Do we extend the default amount object with this information? (This would add quite a lot of complexity to simple amounts)
• Do we add an extra alternativeAmounts array, with more detailed amount objects with exchange rates? If so - what are the semantics of these amounts?

@gabelula Can you share an example of the data you currently encounter? E.g. does it have original and converted currency values, dates, rates etc.?

[jpmckinney]

See requirement "R-TransactionCurrency" for what I discovered when preparing the draft Extractives extension.

[timgdavies]

Thanks @jpmckinney. Bringing that across here for ease of reading:

R-TransactionCurrency The existing Value.currency property fulfills this requirement if a single currency is reported. If two currencies are reported, a new Transaction.amountUSD property can be added to fulfill this requirement, whose value is a number. Its definition may be “the value of the transaction in US dollars.” If this property is used, the currency property of the Transaction.amount should be the local currency.

All use cases above for multiple currencies involve the US dollar and a local currency. If there are doubts that the US dollar will maintain its special status in the world economy, a new Transaction.foreignAmount property can be added to fulfill this requirement, whose value is an Value. Its definition may be “the value of the transaction in a foreign currency.” If this property is used, the currency property of the Transaction.amount should be the local currency.

[jpmckinney]

Re: exchange rate date, I'm not clear on its utility. We already have a transaction date. The exchange may have been performed at a different date by a broker, which is often unknowable (for example, when sending direct deposits to the US from Canada, I use a digital broker that quotes a rate, which I accept, which then withdraws my CA$ and deposits the US$, so that the amount deposited is exactly what it should be in the local currency). I think the transaction date is sufficient for the use case of, "Did they get an okay rate?"

As in the draft extension, I think simply reporting the values in the two currencies is preferable to encoding a rate, because:

1. the most likely calculation to perform on amounts is to add them up, and that's simpler if you don't need to multiply by exchange rates and perform rounding, and
2. the actual exchange rates used by different systems go to different levels of precision, and they don't all share the same rounding rules, so you will inevitably be a few cents/dollars off if you are multiplying a lot of numbers by exchange rates, which is bad for the use case of matching numbers across systems (e.g. how much did the company pay versus how much did the government deposit).

[jpmckinney]

Also, having two amounts is really simple schema-wise, without adding complexity to the Amount class.

[bill-anderson]

Copying in the side discussion on JDA list

@timgdavies Would really welcome thoughts on:

• Whether other standards on this list capture exchange rate information, and how they currently do that?
• Any pointers to existing standardisation we should build upon.

@davidmegginson I thought IATI's approach was the right one. Instead of trying to capture exchange rates, just capture the currency, nominal amount, and date. Historical exchange-rate databases exist separately anyway, and there may be some disagreement, so I think you might be overloading the transaction record by including it.

@countculture I agree with Dave. Anything else will potentially be adding assumptions that might not be there in the underlying data.

@bill-anderson +1 to Chris and David. Storing the original currency is the only way to ensure the integrity of the data. But ... Can we consider a user-driven utility that does conversions as an integral part of the standard? IATI has talked about the importance of an easy-to-use conversion tool for years and has never got around to it. This places a big burden on users. Making that utility an essential part of the standard as a whole may be a solution?

@davidmegginson That's a good point, Bill. The challenge for a freely-available currency-conversion API, though, is probably more business and financial sustainability than incompatible APIs, so we might need to do the heavy living outside the standards world. In the meantime, there are commercial services like https://openexchangerates.org (though I don't know how seriously to take the "open" part).

[davidmegginson]

"Heavy living" should have been "heavy lifting" — blame autocorrect.

D

On Thu, 17 Dec 2015, 04:21 Bill Anderson notifications@github.com wrote:

Copying in the side discussion on JDA list

@timgdavies https://github.com/timgdavies Would really welcome thoughts on:

• Whether other standards on this list capture exchange rate information, and how they currently do that?
• Any pointers to existing standardisation we should build upon.

@davidmegginson https://github.com/davidmegginson I thought IATI's approach was the right one. Instead of trying to capture exchange rates, just capture the currency, nominal amount, and date. Historical exchange-rate databases exist separately anyway, and there may be some disagreement, so I think you might be overloading the transaction record by including it.

@countculture https://github.com/countculture I agree with Dave. Anything else will potentially be adding assumptions that might not be there in the underlying data.

@bill-anderson https://github.com/bill-anderson +1 to Chris and David. Storing the original currency is the only way to ensure the integrity of the data. But ... Can we consider a user-driven utility that does conversions as an integral part of the standard? IATI has talked about the importance of an easy-to-use conversion tool for years and has never got around to it. This places a big burden on users. Making that utility an essential part of the standard as a whole may be a solution?

@davidmegginson https://github.com/davidmegginson That's a good point, Bill. The challenge for a freely-available currency-conversion API, though, is probably more business and financial sustainability than incompatible APIs, so we might need to do the heavy living outside the standards world. In the meantime, there are commercial services like https://openexchangerates.org (though I don't know how seriously to take the "open" part).

— Reply to this email directly or view it on GitHub https://github.com/open-contracting/standard/issues/277#issuecomment-165391857 .

[CountCulture]

Also strong back up james re utility of this. What the exchange rate in reality is going to vary depending on when the exchange is made (it may be done automatically by the bank, stored in a dollar account, for example, or hedged against, for example), and when the money is booked (for example our accounts system uses XE.com to do an exchange calculation at time of invoice, and then adjustments are made for when the exchange actually happens). In short, it's a bit of a minefield that has so many variables that won't be able to be put in the data, that I think the core should be recording what the applicable date is (could be transaction date, or maybe invoice date) and the amount and currency used.

[bill-anderson]

In IATI we added a value date used for conversion in addition to the transaction date because there are cases where they are not the same. (I can't remember who the experts were that advised us on this, but I am now not convinced that the two dates are necessary.)

[davidmegginson]

I suspect the difference came from governments wanting to be credited with the exchange rate on the date of announcement, rather than on the date of payment. I agree that it seems painfully overanalysed.

[gabelula]

@timgdavies

The exchange rate date;
The exchange rate used;
The source currency;
The destination currency;

Questions we should also look at:

Do we extend the default amount object with this information? (This would add quite a lot of complexity to simple amounts)
Do we add an extra alternativeAmounts array, with more detailed amount objects with exchange rates? If so - what are the semantics of these amounts?

@gabelula Can you share an example of the data you currently encounter? E.g. does it have original and converted currency values, dates, rates etc.?

This are all quite important questions. Right now we need to decide where to add the exchange rate in the CDMX's case. The issue here is that when signing the contract they negotiate a FIX exchange rate that will work for the future when the transaction happens. We need to know what was the negotiated exchange rate and for which day.

"value": { "amount": 429000, "exchangeRate": 15.30 --opcional "dateExchangeRate": 2015-11-04T00:00:00-05:00 --opcional "currency": "USD", "destinationCurrency": "MXN" },

This is not for the planning or tender stage as those stage is always in MXN. This is for award, contract and implementation stages.

We may not need the "destinationCurrency" really as it is the local currency that we are exchanging everything into.

[timgdavies]

Thanks @gabelula - this is really helpful. I've looked again at FPML and at both UBL and FIBO for inspiration for modelling exchange rates in the ways you describe above, but not found anything.

Below I try to recap current use cases, and the options we have on the table. My preferred option, and the one which I think would meet your needs, is option 1 - but I would welcome other views.

Use cases

We have a range of instances where multiple currencies are required:

• Two currencies are reported for a given contract. For example, a document might give values in both USD and a local currency.
• A contract agrees a fixed exchange rate for payments. For example, a contract is agreed in USD, but with a fixed exchange rate to convert this into MXN when payments are made. Sometimes the second value is declared. Sometimes only the rate is declared (as this will be the rate used
• Values are in multiple currencies, but users want to access the values in a single currency. For example, an application may want to sum up total contract values in USD, even if original contracts were in a range of other values.

Considerations

• Rates should not be reverse engineered from pairs of currency values, as there can be rounding errors that enter in.
• Knowing the date of an exchange rate is useful to understand if a competitive rate was used.

Modelling options

1) Extend value with ‘altAmount’ and ‘altCurrency’ fields

Optional exchangeRate and exchangeRateDate values could also be included, interpreted as the conversion between currency and altCurrency.

This allows the inclusion of a single extra currency along with each value. The actual inclusion of the altAmount could be made optional in cases where the user just wants to specify the exchange rate.

"value": {
   "amount": 6563700,
   "currency":"MXN",
   "altAmount":429000,
   "altCurrency":"USD",
   "exchangeRate":0.065,
   "exchangeRateDate":"2015-11-04T00:00:00-05:00"
}

2) Permit use of {CURRENCY_CODE}Amount fields

For example, USDAmount, MXNAmount, GBPAmount.

This has the advantage of allowing a system to look for all the USDAmount fields directly, without having to work out if the USD is in amount, or altAmount for example.

It also allows multiple additional currencies, as for example in:

"value": {
   "amount": 6563700,
   "currency":"MXN",
   "USDAmount":429000,
   "GBPAmount":295438.27
}

However, modelling exchange rates in this instance is more complicated.

3) Add a currencyValues array

This option provides scope for unlimited additional currencies, but in flattened serialisations would lead to additional sub-tables.

Exchange rates could be included for each currency in this model.

"value": {
   "amount": 6563700,
   "currency":"MXN"
},
"currencyValues":[
    {
        "amount": 429000,
                "currency":"USD",
                "exchangeRate":0.065,
                "exchangeRateDate":"2015-11-04T00:00:00-05:00"
        },
        {
                "amount": 295438.27,
                "currency":"GBP"
         }
]

[gabelula]

Yes. I agree that the modelling option 1) seems the better one:

"value": {
   "amount": 6563700,
   "currency":"MXN",
   "altAmount":429000,
   "altCurrency":"USD",
   "exchangeRate":0.065,
   "exchangeRateDate":"2015-11-04T00:00:00-05:00"
}

Why to use altAmount ? That is an amount that can be calculated using the exchangeRate and amount.

Is the altCurrency the sourceCurrency? I would change to have the currency be 'USD' and other name like destCurrency or something more clear.

"value": {
   "amount": 6563700,
   "currency":"USD",
   "localCurrency":"MXN",
   "exchangeRate":0.065,
   "exchangeRateDate":"2015-11-04T00:00:00-05:00"
}

[timgdavies]

Why to use altAmount ? That is an amount that can be calculated using the exchangeRate and amount.

In the discussion above, the feeling was that specifying the exchange rate was not always required, so there might be situations in which:

• Only exchange rate is given
• Only altAmount is given
• Both exchange rate, and altAmount are given

Is the altCurrency the sourceCurrency? I would change to have the currency be 'USD' and other name like destCurrency or something more clear.

I was trying to avoid adding semantics such as 'local currency', or 'destination currency' which may not be relevant in all cases where multiple currencies are used.

The exchange rate is supposed to be the rate between amount and altAmount - so as you will see in the example above, I reversed the rate to be the MXN-USD rate.

I would suggest the semantics should be:

• amount is the value in the currency in which the contract was signed
• altAmount is the value in a secondary currency
• exchangeRate is the conversion between amount and altAmount

So, in your case, if the contract is signed in USD, but has a fix agreed to MXN, the example would be:

"value": {
   "amount": 429000,
   "currency":"USD",
   "altCurrency":"MXN",
   "exchangeRate":15.30,
   "exchangeRateDate":"2015-11-04T00:00:00-05:00"
}

It would be up to a consuming application in this case to calculate altValue if it required this.

[timgdavies]

Looking at data on the approval of the Red Compartida PPP in Mexico, which sets out projected private sector investment in the project, there is a note that

"All figures are in pesos the year: 2016".

This may point to another use-case for exchange rate information, being able to know the year at which exchange was calculated, even if the rate itself is not given.

The question would be whether structured data version of this has value, or whether just having explanatory note meets most analysis requirements.

[timgdavies]

Continued in #384