#1   Report Post  
Posted to microsoft.public.excel.misc
peterthistle
 
Posts: n/a
Default Foreign Exchange


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

  #2   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default Foreign Exchange

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: DebugCompile
to see if there are any errors.
If no...continue, below

Go to the Sheet1
ToolsMacroMacros
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


  #3   Report Post  
Posted to microsoft.public.excel.misc
Ron Coderre
 
Posts: n/a
Default Foreign Exchange

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

DataImport External DataNew 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Calculating Month Average Exch Rate from Exchange Rates Calendar!!! StanUkr Excel Worksheet Functions 0 September 14th 05 11:12 AM
how to calculate exchange rate? papa404 Excel Discussion (Misc queries) 2 August 2nd 05 01:43 PM
exchange contents of cells \jeremy via OfficeKB.com\ New Users to Excel 6 July 8th 05 03:14 AM
Reindex exchange folders Mark C Charts and Charting in Excel 0 December 21st 04 05:19 PM
smart tag for exchange rates normdrexel Excel Discussion (Misc queries) 1 November 26th 04 09:05 PM


All times are GMT +1. The time now is 11:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"