Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculating Month Average Exch Rate from Exchange Rates Calendar!!! | Excel Worksheet Functions | |||
how to calculate exchange rate? | Excel Discussion (Misc queries) | |||
exchange contents of cells | New Users to Excel | |||
Reindex exchange folders | Charts and Charting in Excel | |||
smart tag for exchange rates | Excel Discussion (Misc queries) |