Foreign Exchange

[peterthistle]

With Excel 2003, is there a way to have a cell automatically update th
foreign exchange rate of yen to British pounds from the internet, an
then use that cell to do calculations

 

[Ron Coderre]

Here's something to get you started:

Open a new workbook
On Sheet1, enter the following:
A1: FromCurrency
B1: CAD

A2: ToCurrency
B2: USD

A3: FromAmount
B3: 10000

A4: ToAmount
B4: =I3

Next:
Copy the below VBA code into a workbook module
(Note: I listed all of the options so you can play with the code)

'---Start of code
Option Explicit

Sub ConvertFX()
Dim FromCurr As String
Dim ToCurr As String
Dim FromAmt As Currency
Dim ToAmtLoc As String
Dim URL2Use As String

With ActiveSheet
FromCurr = .Range("B1").Value
ToCurr = .Range("B2").Value
FromAmt = .Range("B3").Value
ToAmtLoc = "A4"

URL2Use = "URL;http://finance.yahoo.com/currency/convert?amt=" _
& FromAmt & "&from=" _
& FromCurr _
& "&to=" & ToCurr _
& "&submit=Convert"

With .QueryTables.Add(Connection:= _
URL2Use _
, Destination:=Range("E1"))
.Name = "Convert_Currency_Result"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "15"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
End With
End Sub
'---End of code

Now, from the vb menu: Debug>Compile
to see if there are any errors.
If no...continue, below

Go to the Sheet1
Tools>Macro>Macros
Run: ConvertFX

If all goes well, the query results should appear in cells E1:K3 and cell B4
(the ToAmount) should display 8413.967 (the current conversion from Canadian
Dollars to US Dollars for 10,000.

Does that help?

***********
Regards,
Ron

 

[Ron Coderre]

If the only currency conversion you ever want to do is from JPY to GBP, then

Data>Import External Data>New Web Query
-Use this website:
http://finance.yahoo.com/currency/convert?amt=123.45&from=JPY&to=GBP&submit=Convert

-Select the table in that site with the conversion rate

Once you get the results in Excel, you can set up formulas that reference
the Fx rate.
Whenever you want to get the latest rate, right click on the results range
and select Refresh Data

Does that help?

***********
Regards,
Ron