Erpnext: Better Management of Currency Exchange Rates

[Olawale1]

Hi ,

For foreign currency transactions, ERPNext fetches the last exchange rate defined (prior to posting date) regardless of how old it is. let's say posting date is 17th July and the last exchange rate before then is 13th January, the system automatically uses this rate (13th January). This is however not best practice as exchange rates could have fluctuated significantly within the period

Ideally, the system should throw an error message requesting that an exchange rate be entered for the posting date (i.e. 17th July in the example above). If however there are multiple exchange rates existing on the system for 17th July, the latest one can be automatically selected. This principle should actually apply to all foreign currency transaction documents

Thanks

[umairsy]

Hi,

If you wish to have latest exchange rates auto-fetched, then ensure that no Currency Exchange record exists between these currencies.

[Olawale1]

Hi @umairsy

Trust you're doing great. Not very sure what you mean but my point is that it should be mandatory to enter an exchange rate for the exact date (Posting Date) of the transaction. This is the standard practice in ERP systems like SAP

Please let me know if further clarification is required

Thanks

[tundebabzy]

Perhaps we can implement this as such:

1. If the document has never been saved:
   
   
   
   • If there already exists an exchange rate for the date of transaction, we fetch the latest rate
   
   
   • If there isn't, we leave the exchange rate field blank and make it compulsory.
   
   
2. For (1), when the document is saved, if there was previously no saved exchange rate or the exchange rate is different from the latest, the exchange rate is also saved to Currency Exchange as a new record.
3. If the document has been saved before, the latest exchange rate is retrieved into the exchange rate field.
4. In (3), if the exchange rate is changed, the exchange rate is also saved to Currency Exchange as a new record.

What's your reaction?
cc: @addymuliady

[joolsr]

Could I possibly request that a factor can also added as an option to use a slightly different rate ? This is because we have to pay £30 GBP whenever we accept another currency so we normally use a slightly worse exchange rate to our customers to make up for this charge to us.

Just a thought as you may not always want the absolute defined exchange rate.

Julian

⁣Sent from BlueMail ​

On 21 Jul 2017, 8:48 pm, at 8:48 pm, tundebabzy [e-mail] wrote:

Perhaps we can implement this as such:

1. If the document has never been saved, we leave the exchange rate
   field blank and make it compulsory.
2. For (1), when the document is saved, the exchange rate is also saved
   to Currency Exchange as a new record.
3. If the document has been saved before, the latest exchange rate is
   retrieved into the exchange rate field.
4. In (3), if the exchange rate is changed, the exchange rate is also
   saved to Currency Exchange as a new record.

What's your reaction?
cc: @addymuliady

--
You are receiving this because you are subscribed to this thread.
Reply to this email directly or view it on GitHub:
https://github.com/frappe/erpnext/issues/9973#issuecomment-317096049

[tundebabzy]

The exchange rate will be user defined not automatically defined (for now). Does that address your concern @joolsr

[joolsr]

Yes though getting auto rates that can have a factor added on would be better

Julian

⁣Sent from BlueMail ​

On 22 Jul 2017, 7:18 am, at 7:18 am, tundebabzy [e-mail] wrote:

The exchange rate will be user defined not automatically defined (for
now). Does that address your concern @joolsr

--
You are receiving this because you were mentioned.
Reply to this email directly or view it on GitHub:
https://github.com/frappe/erpnext/issues/9973#issuecomment-317159513

[Olawale1]

Hi @tundebabzy

Trust you're doing great. Basically what I'm suggesting is simply a validation that ensures the currency exchange being pulled from the 'Currency Exchange List' is for the exact day of the transaction (ie Posting Date) rather than the 'most recent' rate which is not best practice

If there is no rate available for the posting date (and there is an existing previous rate in the Currency Exchange List), the user should be prompted to create a new rate for that date in the Currency Exchange List or enter the rate manually

Thanks

[Olawale1]

Another way of looking at it is that the system should never be 'guilty' of auto-fetching an outdated rate

Kind regards,

[tundebabzy]

Makes sense. It means if there's an exchange rate already set for the date of transaction, we should fetch it otherwise force the user to set one. I'll edit my plan accordingly.

[ckosiegbu]

Hi @tundebabzy, @olawale1,

Regarding exchange rates, the standard logic in the more established ERP systems is that if an exchange rate is not maintained for the date on which a transaction is posted, the exchange rate for the nearest date earlier than it is selected. This is because not all organisations update their exchange rates daily. Some establish an average monthly or even weekly exchange rate at which all transactions must be booked. All transactions that happen in the following week or month then use this rate.
In this respect i'd say the current implementation works just fine. If your organisation needs daily exchange rates, then update the exchange rate take daily, either manually or using a script.

The only part I would change is this:

https://github.com/frappe/erpnext/blob/ddd48454206d8ef65323581209e60415b02af2f4/erpnext/setup/utils.py#L85

The whole try/except block should go. If the currency is not in the exchange rate table, a message should be displayed saying that nothing had been maintained for that currency pair possibly with a link pointing to where the update can be made (assuming the user has the appropriate authorization. If not they should ask the appropriate actor to do it.)

To replace the default behaviour of getting the exchange rate from fixer, we might want to introduce a task with configurable setting for the source of exchange rates. This task would update the exchange rate for specified currency pairs in the Currency Exchange doctype at the specified intervals.

Regards,
Chude

[Olawale1]

Hi @ckosiegbu

Trust you're doing great. Not sure which particular systems you're referring to but I've worked extensively with SAP and I can tell you categorically that it's mandatory to set an exchange rate for the date of any foreign currency transaction. If you think about it, it makes sense considering how volatile exchange rates can be. You don't have to set an exchange rate for every single day but you do need to set an exchange rate for the days on which a foreign currency transaction is being posted

Cheers!

[ckosiegbu]

Hi @olawale1,

I'm doing well thanks. I'm referring to most of the "big" ERP's. In the case of SAP R/3 for example, see point 2 at the following link: http://www.consultoria-sap.com/2009/11/faqs-currency-translation.html

Also, IAS 21 makes accommodation for the use of average exchange rates https://www.iasplus.com/en/standards/ias/ias21

In practice, you don't need to set an exchange rate on every day when a foreign currency transaction is being made. You can post your transactions using the average rate and then post a realized gain or loss at the time of clearing, payment or revaluation of the foreign currency item.

Regards,
Chude

[ckosiegbu]

Also, the current implementation doesn't stop one from manually maintaining exchange rates daily.
My proposed enhancement goes further to allow the Currency Exchange doctype to be updated automatically from a defined source at whatever interval is required by the organisation using ERPNext.
There are many reasons why the Currency Exchange doctype shouldn't be updated by a transaction (in the case where the currency pair required isn't yet updated in the Currency Exchange doctype). One that comes to mind is, if Accountant 1 posts a document today with an exchange rate of 100/1 between USD/NGN and then Accountant 2 posts a document later the same day with an exchange rate of 105/1 for the same currency pair. In your reporting or in the response to auditors at some point in the future, which exchange rate would be said to have applied on that day? What if Accountant 1 posts a completely wrong exchange rate for the currency pair on that day e.g. 1000/1. It would then mean that you stand the risk of mis-valuing all transactions using this currency pair until someone detects the error.

[tundebabzy]

One that comes to mind is, if Accountant 1 posts a document today with an exchange rate of 100/1 between USD/NGN and then Accountant 2 posts a document later the same day with an exchange rate of 105/1 for the same currency pair. In your reporting or in the response to auditors at some point in the future, which exchange rate would be said to have applied on that day?

Some months back in Nigeria, the NGN would lose up to 5% of its value in a single day. In such a case average exchange rate is not reasonable nor is choosing the opening exchange rate which will be more favourable to net profit. That's why accountants are supposed to use spot rates on the day of transaction.

Anyway, it's clear that not everyone will like to be forced to enter exchange rates at the transaction level. Perhaps we can allow the desired behaviour to be set in Company defaults so companies that want to be strict with IAS21 can do so.

[ckosiegbu]

Some months back in Nigeria, the NGN would lose up to 5% of its value in a single day. In such a case average exchange rate is not reasonable nor is choosing the opening exchange rate which will be more favourable to net profit. That's why accountants are supposed to use spot rates on the day of transaction.

Sure. However, in everyday accounting in such volatile circumstances, I guess the best one can really hope for is to use the closing rate (or average rate) of the previous day as all the financial reporting rules seem to indicate that each day must have a single rate. Also, in the hyper inflationary situations as Nigeria approached for a while earlier this year, all these rules go out the window and you revalue all your foreign currency transactions using the rate at the time of reporting.

If we are agreed on the accounting rules, then I think the current implementation is still the most ideal as it gives one the ability to maintain rates as frequently (or infrequently) as they like up to a days resolution. Area's for improvement will then be to define an integration that allows us to:

• Specify a resource for pulling exchange rates from
• Map the output of the resource to our Currency Exchange table
• Set frequency for update of rates (frequency would be no higher than one day)
• Send out a notification/give a warning if attempted update fails for any reason

[addymuliady]

@tundebabzy @ckosiegbu I am against average exchange rate for transactional purposes. I think @tundebabzy had solved one of the problems relating to this, where foreign exchange rate was originally based on average in Journal Entry. I am not sure who made the change but I hope the ERPNext team is clear with its position in this.

If there is a need to cater to average exchange rate, have it as part of accounts settings.

@ckosiegbu I have an app developed for revaluation. For this, it does calculate based on average exchange rate of the account balances. Do DM me if you are interested in the app.

We also have to bear in mind that ERPNext currently has its function where it fetches exchange rate automatically from http://fixer.io/

Thanks!

[tundebabzy]

Yes as regards average exchange rate, we don't use it any more. It's still in the API though for those who require it in their custom apps.

I'll leave this thread open a little bit longer but at the moment, majority seem to be OK with the present behaviour. At the least, I'm looking for people that want the behaviour adjustable.

[Olawale1]

Hi @tundebabzy

Trust you're doing great. I think we all agree that the IAS 21 condition for using the avarage exchange rate (i.e a reasonable approximation of actual) isn't feasible in many countries where the exchange rate isn't so stable. Obviously, the ideal situation as clearly stated is that "A foreign currency transaction should be recorded initially at the rate of exchange at the date of the transaction"

Any company that does a lot of foreign currency transactions appreciates the importance of this guideline. It doesn't really make much sense for a system to pull an exchange rate that's 6 months old (for example) just because it's the 'most recent' exchange rate defined. If any organisation wants to go ahead with this, they should be given an option to select it in the Accounts Settings or Company Defaults as you rightly suggested earlier

It is important to make provisions for those who would prefer to adhere strictly to the IAS 21 guidelines. As I mentioned earlier, systems like SAP make it mandatory to set an exchange rate for the actual date of every foreign currency transaction and I think it's worth emulating. I've also consulted with colleagues using other ERP Systems and it's the same

In summary, please add the validation but make it optional for those who don't want it

Thanks

[Olawale1]

@ckosiegbu

Trust you're doing great. @addymuliady is correct, ERPNext already pulls rates automatically from http://fixer.io so I believe the only parts pending would be to:

• Map the output of the resource to our Currency Exchange table
• Send out a notification/give a warning if attempted update fails for any reason

Cheers!

[addymuliady]

@tundebabzy

To add, it will be best if Exchange rates are refreshed based on the posting date or when there is a change of posting date.

Currently, it will only be refreshed upon adding of rows. When you add a row, it will look at the posting date and fetch accordingly.

However, if we are to change the posting date, the exchange rate will remain the same.

[tundebabzy]

To add, it will be best if Exchange rates are refreshed based on the posting date or when there is a change of posting date.

This has already been fixed in #10109. I think it will go live any moment from now

[tundebabzy]

After going through the discussion, I think we all agree that want to be able to control how the exchange rate is managed in our own different ways. I think it's useful if ERPNext makes this easier for everyone.

I think we should create Doctype "Currency Settings" from where ERPNext's exchange rate management behaviour can be tweaked. The available options would be:

• Require new exchange rate for each day (Off by default). Would be useful for companies that wish to ensure that unnecessarily stale exchange rates are not used
• Allow only one exchange rate per day (On by default). In reality it is possible to have more than one exchange rate in effect in a day.
• Automatically Update Currency Exchange (Empty table by default). Table will contain time and frequency for updating the currency exchange table.
• Manually Set Exchange Rate Action (Do nothing by default). Users can choose between "Do nothing", "Always Save To Currency Exchange" and "Save To Currency Exchange Once"

cc: @nabinhait

[tundebabzy]

Todo:

• [x] Currency Exchange Settings Doctype
• [x] validation
• [x] In get_exchange_rate check if settings allows for stale exchange rate
• [x] In forms, make conversion rate field read only if settings does not allow stale exchange rates
• [x] Add documentation

Currency Settings fields:

1. Allow Stale Exchange Rate
2. Stale exchange rate days

cc: @nabinhait @rmehta

[ckosiegbu]

@tundebabzy

Sorry to give this feedback this late but I think the following step is unnecessary

Require new exchange rate for each day (Off by default). Would be useful for companies that wish to ensure that unnecessarily stale exchange rates are not used

If we have this further step that you have already specified

Automatically Update Currency Exchange (Empty table by default). Table will contain time and frequency for updating the currency exchange table.

I think the problem can be boiled down to:

1. Do we want to maintain our exchange rates manually?
2. Or do we want to be able to specify a schedule and a source from which they will be able to be maintained automatically.

If we choose option 2, then we specify the source and the frequency (and maybe make the Currency Exchange doctype readonly?)

[tundebabzy]

Hi @ckosiegbu
Those my thoughts have been canned for now at least that's why they were not part of my todo tasks. For now, the settings will just allow administrators force users to update currency exchange records as desired. So if you want to make sure that your users update the exchange rate every day, simply uncheck "allow stale rates" in the settings and then specify how long stale is to the business which will be 1 in this case.

After this has been incorporated and we are certain that no new problems, we can think of how to uncouple fixer.io and make an exchange rate "fetcher" pluggable. Administrators will then be able to add as a setting what service to use if they don't fancy fixer.io or have something more capable than fixer.io .

[ckosiegbu]

Makes sense. Especially the pluggable framework for selecting service from which to get exchange rates. The philosophy in SMS Settings seems apt for this.

[Olawale1]

Hi @tundebabzy

Trust you're doing great. Thanks a lot for the great work so far. I believe your proposed approach caters for the immediate need. I however need to clarify this:

Require new exchange rate for each day (Off by default). Would be useful for companies that wish to ensure that unnecessarily stale exchange rates are not used

Please note that (as I mentioned a couple of times in this thread) this option doesn't force users to enter exchange rates for every single day in the year; it's only for days on which foreign currency transactions are posted. I know you're probably implementing it this way already but this is for the avoidance of doubt

Thanks again!

[tundebabzy]

10482