continuing on from previous question - adding to what that solution gave
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.
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.
Glenn Ray
goflow should get paid for all this work! :-)
ASKER
Yes, he should. He's been a tremendous help to me.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you, gowflow, it worked just fantastic for the Earthport emails and will test it on a Western Union emails when one comes in. Should get one today or tomorrow.
While waiting, if you wanted to work on another, let me know. It is the next step in this process to create a separate spreadsheet and email it in an attachment to the bank, much as you did with the MasterCard workbook.
While waiting, if you wanted to work on another, let me know. It is the next step in this process to create a separate spreadsheet and email it in an attachment to the bank, much as you did with the MasterCard workbook.
ok shoot.
gowflow
gowflow
ASKER
Ok, I'm going to close this one so that I can ask a related question. I will continue to test and let you know if there are any problems. Great work, gowflow. Thanks again.
ASKER
now if you think this related question is two or three, let me know and I'll split it up:
https://www.experts-exchange.com/questions/27481068/continuing-from-previous-now-submitting-spreadsheet-to-Visa-bank.html
https://www.experts-exchange.com/questions/27481068/continuing-from-previous-now-submitting-spreadsheet-to-Visa-bank.html
Well now that you made the question in one maybe too late to split it up will be up to you to accept it when we reach the below points:
1) I need clarification on the button to create the file it seem you want it to pull data from 2 sheets will it be always looking in the 2 sheets each time it is run or you need 1 button for each sheet to run separately ?
2) Will the file created include infor from the 2 sheets in 1 same file or 1 file for every sheet ?
Upon your reply I could then propose how to split this into questions so you would be enlightented to when close the question.
gowflow
1) I need clarification on the button to create the file it seem you want it to pull data from 2 sheets will it be always looking in the 2 sheets each time it is run or you need 1 button for each sheet to run separately ?
2) Will the file created include infor from the 2 sheets in 1 same file or 1 file for every sheet ?
Upon your reply I could then propose how to split this into questions so you would be enlightented to when close the question.
gowflow
ASKER
No it is not too late. You can do one part and I'll ask another related question. You can break it up by the 3 points.
1. Yes, it will always look in the two sheets. One button to look in two sheets would be best.
2. The file created will be one sheet, getting data from the two sheets. So the button when clicked will gather data from the two sheets and compile that data into one sheet.
How about you just do part 1. Then I'll submit related questions for part 2 then when done, give you the last related question which would be part 3?
1. Yes, it will always look in the two sheets. One button to look in two sheets would be best.
2. The file created will be one sheet, getting data from the two sheets. So the button when clicked will gather data from the two sheets and compile that data into one sheet.
How about you just do part 1. Then I'll submit related questions for part 2 then when done, give you the last related question which would be part 3?
What was the button in MC to create the Excel pushtobook ??
gowflow
gowflow
ASKER
It is on the MC Consolidated page and labeled: Push to Book 1
ok we can go ahead with your suggestion in ID: 37249363
gowflow
gowflow
ASKER
Great! Am looking forward to it.