Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula For Regularly Importing Internet Data
I was wondering whether it is possible to regularly update a particular piece
of data in excel. I'm currently working with exchange rates, so it would be wonderful excel to obtain the exact exchange rate from a particular website and pasting that into a designated cell. If it could do this regularly, that would be fantastic. Thanks, Jonathan |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula For Regularly Importing Internet Data
What version of Excel are you using? If, for example, you're using XL
03 for Windows you can use the "Get external data" tool under the Data menu to generate a web query. That web query could be attached to a button on your spreadsheet to refresh. That's just one example of how you could do this. There are also templates available which extract stock prices from various web services; presumably these could be modified for FOREX purposes. Dave On Aug 1, 10:36 am, Jonathan Hartley <Jonathan wrote: I was wondering whether it is possible to regularly update a particular piece of data in excel. I'm currently working with exchange rates, so it would be wonderful excel to obtain the exact exchange rate from a particular website and pasting that into a designated cell. If it could do this regularly, that would be fantastic. Thanks, Jonathan |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula For Regularly Importing Internet Data
To give a quick example of what I was referring to in my earlier
response, the following recorded macro would retrieve W.R. Berkley's trading history from 1984 to present: Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/1/2007 by df78700 ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://ichart.finance.yahoo.com/table.csv? s=WWW&d=7&e=1&f=2007&g=d&a=11&b=18&c=1984&ignore=. csv" _ , Destination:=Range("W1")) .Name = "hp?s=WWW&a=11&b=18&c=1984&d=07&e=1&f=2007&g=d _1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "20" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub This could obviously be cleaned up a bit. Dave On Aug 1, 10:36 am, Jonathan Hartley <Jonathan wrote: I was wondering whether it is possible to regularly update a particular piece of data in excel. I'm currently working with exchange rates, so it would be wonderful excel to obtain the exact exchange rate from a particular website and pasting that into a designated cell. If it could do this regularly, that would be fantastic. Thanks, Jonathan |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula For Regularly Importing Internet Data
Thanks a ton,
Jon "Dave F" wrote: To give a quick example of what I was referring to in my earlier response, the following recorded macro would retrieve W.R. Berkley's trading history from 1984 to present: Sub Macro2() ' ' Macro2 Macro ' Macro recorded 8/1/2007 by df78700 ' ' With ActiveSheet.QueryTables.Add(Connection:= _ "URL;http://ichart.finance.yahoo.com/table.csv? s=WWW&d=7&e=1&f=2007&g=d&a=11&b=18&c=1984&ignore=. csv" _ , Destination:=Range("W1")) .Name = "hp?s=WWW&a=11&b=18&c=1984&d=07&e=1&f=2007&g=d _1" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .WebSelectionType = xlSpecifiedTables .WebFormatting = xlWebFormattingNone .WebTables = "20" .WebPreFormattedTextToColumns = True .WebConsecutiveDelimitersAsOne = True .WebSingleBlockTextImport = False .WebDisableDateRecognition = False .WebDisableRedirections = False .Refresh BackgroundQuery:=False End With End Sub This could obviously be cleaned up a bit. Dave On Aug 1, 10:36 am, Jonathan Hartley <Jonathan wrote: I was wondering whether it is possible to regularly update a particular piece of data in excel. I'm currently working with exchange rates, so it would be wonderful excel to obtain the exact exchange rate from a particular website and pasting that into a designated cell. If it could do this regularly, that would be fantastic. Thanks, Jonathan |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula For Regularly Importing Internet Data
DataImport External DataNew Web Query.
Once set up you can refresh at will. Gord Dibben MS Excel MVP On Wed, 1 Aug 2007 07:36:00 -0700, Jonathan Hartley <Jonathan wrote: I was wondering whether it is possible to regularly update a particular piece of data in excel. I'm currently working with exchange rates, so it would be wonderful excel to obtain the exact exchange rate from a particular website and pasting that into a designated cell. If it could do this regularly, that would be fantastic. Thanks, Jonathan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting Data from the Internet to Excel | Excel Discussion (Misc queries) | |||
Regularly email spreadsheets | Excel Discussion (Misc queries) | |||
Regularly used files | Excel Discussion (Misc queries) | |||
Importing Data Into Formula Based Cells | Excel Worksheet Functions | |||
Importing Data Into Formula Based Cells | Excel Worksheet Functions |