With Excel 2003, is there a way to have a cell automatically update the foreign exchange rate of yen to British pounds from the internet, and then use that cell to do calculations?
With Excel 2003, is there a way to have a cell automatically update the foreign exchange rate of yen to British pounds from the internet, and then use that cell to do calculations?
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
"peterthistle" wrote:
>
> With Excel 2003, is there a way to have a cell automatically update the
> foreign exchange rate of yen to British pounds from the internet, and
> then use that cell to do calculations?
>
>
> --
> peterthistle
> ------------------------------------------------------------------------
> peterthistle's Profile: http://www.excelforum.com/member.php...o&userid=28921
> View this thread: http://www.excelforum.com/showthread...hreadid=486641
>
>
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/co...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
"peterthistle" wrote:
>
> With Excel 2003, is there a way to have a cell automatically update the
> foreign exchange rate of yen to British pounds from the internet, and
> then use that cell to do calculations?
>
>
> --
> peterthistle
> ------------------------------------------------------------------------
> peterthistle's Profile: http://www.excelforum.com/member.php...o&userid=28921
> View this thread: http://www.excelforum.com/showthread...hreadid=486641
>
>
This thread helped me a lot!.. I was banging my head in trying to figure this out. Thanks!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks