gowflow, now we need to go a step further with that email being imported to the Wire-Staging-FBME sheet, so here's what we need to do..
Can you get the conversion rate from USD to Euros from http://www.xe.com
? If not, not a biggie and you can use Oanda as you did before (a solution I LOVE, by the way) The reason I suggest xe.com is Earthport uses them to assess their wire fee. Either way, here's what we need to do when importing an email into Wire-Staging-FBME.
1. To Col J: paste the amount from the email body, after the EUR (example: Amount: EUR 850.00)
2. To Col i: If the amount in Col J is <USD 10k, then divide $2.00 by the USD to Euro conversion rate.
If the amount in Col J is USD 10k-50k, then divide $5.00 by the USD to Euro conversion rate
If the amount in Col J is USD is =USD 50k, then divide $10.00 by the USD to Euro conversion rate
3. To Col L: = Col J - Col i
4. To Col M: =IF(L793*1.85%<20, 20, L793*1.85%)
5. To Col F: =IF(L793*1.85%<20, L793-20, L793-(L793*1.85%))
Also, if I have to manually insert a value into Col J (as not all values come from an imported email), then I want the same thing to happen as above.
That's it. In a future related question I will want to be able to modify the loading fee rates as we did with the Mastercard sheet.