Exchange rate (1 Viewer)

[Gismo]

Hi all,

I am opening a new post for this, I asked about this before but did not get to where I want
I need to be able to profide an exchange rate from currency and a to currency and if possible as in googles sheet, when you supply a date it will give the exchange rate of that day

as in google sheet:

=GOOGLEFINANCE("CURRENCY:"&A2&B2, "price", G2, 1, "DAILY")

 

[sonic8]

I am opening a new post for this, I asked about this before but did not get to where I want
I need to be able to profide an exchange rate from currency and a to currency and if possible as in googles sheet, when you supply a date it will give the exchange rate of that day

If you asked about a topic before and didn't get answers leading to a resolution, it is a good idea to provide at least a link to the older threads for people to look-up what did not lead to a solution.

If you want to implement currency conversion in your Access application you either ...
... need to call a web service doing the conversion for you. - This might be suitable if your application only rarely needs to perform such conversions.
... or you download all the required exchange rates to do the conversion yourself. - This is the better approach if your application needs to convert currencies all the time and in bulk with different dates/rates.

I've implemented the latter approach in a couple of applications. As I and most of my customers are based in Europe, I retrieve the exchange rates right from the horse's mouth: ECB - Euro foreign exchange reference rates

 

[Gismo]

If you asked about a topic before and didn't get answers leading to a resolution, it is a good idea to provide at least a link to the older threads for people to what did not lead to a solution.

If you want to implement currency conversion in your Access application you either ...
... need to call a web service doing the conversion for you. - This might be suitable if your application only rarely needs to perform such conversions.
... or you download all the required exchange rates to do the conversion yourself. - This is the better approach if your application needs to convert currencies all the time and in bulk with different dates/rates.

I've implemented the latter approach in a couple of applications. As I and most of my customers are based in Europe, I retrieve the exchange rates right from the horse's mouth: ECB - Euro foreign exchange reference rates

Do you manually download the exchanges from the website or do you retrieve them via Access?

I do not do the exchanges that often but would like Access to look for the exchange for the Invoice date selected and the from / to unit as the invoice is generated

I am not sure on how to do this automatic call for update from the web

 

[sonic8]

Do you manually download the exchanges from the website or do you retrieve them via Access?

Actually neither.
I have the exchanges rates downloaded in imported into the database automatically using a small .Net client application, which is run daily by the Windows Task Scheduler.

The reason to use .Net instead of Access/VBA for this is that .Net comes with built in XML handling (the ECB provides the rates in XML format) and that it is somewhat complicated to set up Access to be run by the Task Scheduler.

 

[Gasman]

@isladogs has an Exchange Rate DB, that you could interrogate or include in your DB?

You can test it out as an accde and the purchase if it fits your needs?
It would save you a lot of time with coding. You would need to create your own account with the supplier for the data.

Currency Exchange Rate Tracker

This free Access app gets the latest currency exchange rates from an online data source in JSON format

isladogs.co.uk

 

[arnelgp]

there is already an exchange rate here:
Exchange Rate/ currency conversion | Access World Forums (access-programmers.co.uk)

question is what is your "base" currency, so i may modify it for you.

 

[Gismo]

there is already an exchange rate here:
Exchange Rate/ currency conversion | Access World Forums (access-programmers.co.uk)

question is what is your "base" currency, so i may modify it for you.

The base currency will be Uganda shilling

 

[Gismo]

The base currency will be Uganda shilling

Will I be able to enter a date and the exchange rate of that date to be displayed, not just the rate of the day?

 

[arnelgp]

i made a demo. Exchange from GBP to Uganda Shilling.

 

[arnelgp]

and another version conversion from both.

 

[Gismo]

Thank you arnelgp
What I would like if possible is when I raise an invoice, to look at the date and the supplier currency then update the exchange from the web.
I dont neceseraly want to go to a seperate form to enter the date en select the from to currency
Most of the time it would be purchasing from south Africa (ZAR) converting to UGX

Basically all I would need is a code to look at
Transaction Date
From : ZAR
To: UGX
Result: in control Exchange rate
Table: Exchange Rate

And I only want to lookup when From is not UGX

Hope this makes sense
Hope you can assist

 

[Gismo]

Error on

xchng = CCur(Left$(strData, n - 1))

 

[Gasman]

Error on

xchng = CCur(Left$(strData, n - 1))

And you cannot be bothered to say what that error is?

 

[Gismo]

And you cannot be bothered to say what that error is?

Appo;ogies
Run time error 13, type mismatch

 

[Gasman]

Well examine strData and see what it contains.
Then use that Left() to see what is produced and adjust to suit.

You have to at least try and do some of the work yourself, else you will never learn anything, if it is just handed to you on a plate.

arnel or anyone else cannot see what the problem is from what you have supplied so far.
You have to at least say what is in strData and to do that you yourself has to look at it, so take the initiative and try and work it out for yourself.

 

[Gismo]

Well examine strData and see what it contains.
Then use that Left() to see what is produced and adjust to suit.

You have to at least try and do some of the work yourself, else you will never learn anything, if it is just handed to you on a plate.

arnel or anyone else cannot see what the problem is from what you have supplied so far.
You have to at least say what is in strData and to do that you yourself has to look at it, so take the initiative and try and work it out for yourself.

Thank you for the advise
I actually did have a look at the code but not sure what .responcetext is or where it comes from

This error appears when I do e debug print on responcetext, a run time error 2147483638, The data necessary to complete this operation is not yet available
I checked the web site and the conversion from rand to UGX is available though

 

[Gasman]

That is the issue when you have code handed to you on a plate.

I cannot open arnel's db, as I only have 2007, so you will need to wait for someone else. Sorry.

 

[arnelgp]

here you test the datasheet form.

 

[Gismo]

here you test the datasheet form.

Thank you arnelgp

Where are you saving the exchange rate?

I am still getting the same error in the code when I open you DB
Type mismatch

xchng = CCur(Left$(strData, n - 1))

 

[arnelgp]

it is saving to Purchase table (field: [To UGX])