Historic Exchange rate based on date (1 Viewer)

[andy_dyer]

Hi,

Not sure if this should be in here or in the query section as I don't really know where to start...

I have A LOT of exchange rate data in excel each in a massive matrix per month but alot I could trim out as I'm only really interest in one row per month

I have about 10 currencies then I would like to store a monthly exchange rate for based on a date and convert to euro which is out standard reporting currency.

I would like to be able to somehow get this into my database and then when I type in a date and select GBP it will lookup and populate a blank field for exchange rate.

I then need this to feature in a calculated field so that when I type in 100 in the amount field the amount in € field auto calculates based on the exchange rate that has been pulled through...

I guess my first challenge is to clean up my data and get rid of the currencies from that I don't need and the remove all currencies to apart from €.

I need to get my head around that table structure and then work out how I can get the field to pull this through when the exchange rate will be Dec-10 and the date I'll type in will be 12/12/2010...

If anyone has done anything like this before I'd be grateful for some advice...

 

[andy_dyer]

Hi...

I still could really do with some help I think I've sorted my data and got that imported

I have fields for each currency name and then records for each month - I've changed this date to 01/01/10 etc but still am struggliong to get a query written to say pick up this record if my text box on my form has 15/01/10 in it...

I can't seemt o find anythin in here around populating a field based on another field on a form that I could adapt for my purpose...

If anyone can help I'd be grateful...

 

[John Big Booty]

Perhaps this is the sort of thing you are trying to do?

 

[andy_dyer]

Hi John - thanks for your response - I've been out of the country doing the'other part' of my job so just got back and have some time to look at this now...

Just looking at what you have sent me and trying to work out how it works and how it could maybe help me...

 

[andy_dyer]

Ok - I've tried to place my tables and current test form on your database to keep them together...

My main challenges are;

1) having a date field and trying to look up a monthly exchange rate from a date i.e. looking up November 2011 euro exchnage rate from 14/11/2011

2) returning this exchange rate to a field in the record and storing it

3) then calculating the euro figure based on the local figure x the exchange rate and storing that figure in the record also

Does that greater detail help you, help me?

Thanks so much for your time so far!

 

[andy_dyer]

Hi everyone - still cannot get this exchange rate thing to correctly look up - if anyone has any suggestions what I am doing wrong or should try any help would be much appreciated - many thanks

 

[andy_dyer]

Hi everyone - still not made any progress on this feature... if anyone could point me in the right direction - would be most grateful.

Happy Christmas!!

 

[Simon_MT]

What are you trying to acheive.

A Multi-currency Conversion with cross rates
or
Simply converting AUD to the relate Exchange Rates?

The Attached database does the later.

Simon

 

[andy_dyer]

Hi Simon - apologies for not coming back to you sooner - I never received a notification that someone had replied!

I am trying to have three fields

Date
Currency
Exchange Rate to Euro

I will select a date for the date field i.e. 17/11/2011
I will select a currency from a combo box for currency field i.e. GBP

I want the database to go away and lookup the monthly exchange rate for November 2011 for GBP...

My exchange rate table has months on rows so one month per row and then currencies as columns so has about 25 different currencies and the table is made up of the euro exchange rates for each of the currencies for months.

I then want the correct exchange rate to be returned to my form and stored in the bound table...

Does that help and make any more sense?

Thanks for your help!!!

 

[Simon_MT]

So just let me be clear:

The Date I get and the exchange rates but the currency is that always going to be one currency.

The main (JPY, GBP, USD,EUR) currencies exchange rates are extracted from the Exchange Date combi and that then just leaves the AUD but your base currency is EUR. So does the Exchange Rate EUR in TBL_Exchg actually the rate for AUD.

Simon