Currency converter using Access

[MD12]

Hi.. I trying to use Access to create FX conversion but do not really know where to start and what method to embark on. I will be grateful if anyone has any suggestion for me.

I have a table with customer ID, different currency holdings ie AUD, CHF, EUR and USD but not all customer ID has the same currency holding. ie. customer A may have only AUD and USD, customer B has only EUR and CHF

The reason for trying to create a simple FX convertor is because on a particular day I may need to transfer different USD amount from every customer for fees payment. The problem I have is that some customer do not have USD or some has insufficient USD for me to transfer as the cash may be sitting in other currencies ie EUR and/or AUD

Hence am trying to use Access to help propose FX conversion for me to ie sell other currencies to buy USD

Anyone here has any non programming suggestions for me?

 

[GaryPanic]

pad out the enquiry
is the default currency going to be always USD or will the currency change ? (Will you sell USd to Euro ?)

one would assume you need to have a journal entry of say
Sell Euros at 1.2 to USD, you are going to need a history table (to tell you what you have done and when

so your sell option would need an exchange rate at the point of sale and then to convert and update the USD amount (and minus the EUR amount by this amount

so
Starting Euro Amount - Sell x amount - x-rate to USD
1,000 250 @ 1.2 rate 250/1.2 or 250 *0.8 = 208.33


then your qry needs to update the USD amount by this amount and reduce the Euro amount

so update query = x(USD) + this amount and another one y (EURO) - euro amount

filtered by client id and currency id to update a table


this is a bit sketchy - but you can do this all with qry's and a couple of tables - you can make it look smarter with VB coding

 

[MD12]

Hi Gary

Thanks for the suggestions. it doesnt always have to be transfering USD. I may need to transfer EUR at times

I can figure out the logic for if there is sufficient USD, no FX conversion required but i cannot figure out how to tell the query to first wipe out my CHF and if CHF is not enough, convert just enough EUR to USD

These are the tables that i have:
Cash table
AUDCHFEURUSDCust A1000 1000Cust B 10001000

Payment transfer table
USDCust A-1500Cust B-1600

FX rate Table
AUD0.73413CHF1.05435EUR1.14475USD1
End result that i need
Cust ASellAUD1000Buy USD734.13Cust BSell CHF1000Buy USD1054.35Cust BSell EUR476.65Buy USD545.65

 

[MD12]

Sorry the tables didnt come out correct in my previous post

Cash table:
CustID ! AUD ! CHF ! EUR ! USD !
Cust A ! 1000 ! 0 ! 0 ! 1000 !
Cust B ! 0 ! 1000 ! 1000 ! 0 !

Payment Table:
CustID ! USD !
Cust A ! -1500 !
Cust B ! -1600 !

FX Rate Table:
AUD ! 0.73413 !
CHF ! 1.05435 !
EUR ! 1.14475 !
USD ! 1 !

End result that i need
Cust A ! Sell ! AUD ! 1000 ! Buy ! USD ! 734.13 !
Cust B ! Sell ! CHF ! 1000 ! Buy ! USD ! 1054.35!
Cust B ! Sell ! EUR ! 476.65 ! Buy ! USD ! 545.65 !

 

[GaryPanic]

OK

So
do you have a currency every client
ie
client No1
Euro nil
ChF 500
GBP 250
USD nil


or just
Client no 1
CHF 500
GBP 250


not that it makes much difference .
assume the top option you will end up with potentially a lot of nil transaction

but here we go


Euro nil sell 0 @ exchange rate 1.2 = USD 0.00
CHF 500 sell 350 @ exchange rate 5 = USD 70.00
GBP 250 sell 0.00 @ exchange rate 1.6 = USD 0.00
USD amount xxxx

so you can do on your form this calculation ( you need to keep a track record of this as well so you will need an append qry )

query(update) to actually do this and another query (update) to take off the amounts
so CHF 500 - 350 = 150

and your dollar will be 350 /5 = 70 therefore the update will be in the USD (update to USD + (CHF350/5)

 

[GaryPanic]

Ahh - right now you are in to If statements

are you looking at doing client by client on a batch/mass run ?
write it down how you want it to progress

amount due = x
If USD is < X then ..
check CHF if CHF is null or then check ..

work out your logic on how you want this to progress going downwards

but you are into the world of if statements and I get lost at the first one

 

[MD12]

Hi Gary...thanks a lot for the advice. I manage to use nested If to identify which needs conversion but I m stuck for days on how I can make it propose the required amount to convert in sequence ie buy jpy sell chf and then buy remaining jpy sell gbp. Aim is to do mass run for multiple customers

I have attached my db and wonder if you will be able to help me take a look.Query2 is my end result but only good till identifying which currency needs conversion