MS Excel: help with a formula for exchange rate

city_knight

Hi!
I wondered if anybody uses formulae in Excel that are a function of an exchange rate..
What I'd like to do it to have a cell that updates its value each time the sheet is opened with the current interbank euro/pound fx rate so that I can formulate other amounts using the updated value.
The Help files suggest parsing a webpage with VB script but this sounds like far to much effort for something simple
All solutions appreciated!

Red_Rose_Exile

try this method

Select any cell then go to Data | Get external data | New web query,

in the text box type:

http://finance.yahoo.com/currency/convert?amt=1&from=EUR&to=GBP&submit=Convert

(or wherever you want your data to come from)

click OK,

select New worksheet in the popup window,

click on Properties,

Under Refresh Control, select refresh every and refresh data on file open
set the amount of minutes interval you wish the data to refresh

click OK and OK again.

city_knight

That's great - I can see how that all would work
Now need to find out why the Excel browser doesn't display web pages!

Red_Rose_Exile

Check that your firewall is allowing Excel access to the net

city_knight

All very cool One last issue - when I use this rate cell (B2) in a formula, then fill down, it looks to a value in B3 that doesn't exist. How should I format a cell reference within a formula so that when I "fill down", it doesn't increment the row number on this element of the function?

Red_Rose_Exile

Put a $ symbol in between the cell x and y references so the cell reference becomes B$2

city_knight

You're a legend Thanks very much indeed!

cbrink

I see it's been a long time since this thread was born, hopefully somebody will read and help me.

I like the idea discussed here but I want to control when the rates must be updated with a button.
Any idea on how i can achieve this?

To refine the process I would also like to retrieve only the currencies that I'm interested in and it can be done with separate buttons if necessary.
If this is not possible I will retrieve the full list.

Any help will be much appreciated.

rmg1

You can write code to run the refresh whenever you want. As for getting on ly certain queries, I'm not sure.

The code for controlling the refresh would be something along the lines of:-
Sub refresher()
dim ws as worksheet,qt as querytable
for each ws in worksheets
for each qt in ws.querytables
qt.refresh
next qt
next ws
End Sub