Formula For Regularly Importing Internet Data

[Jonathan Hartley]

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

 

[Dave F]

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

 

[Dave F]

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

 

[Jonathan Hartley]

Thanks a ton,

Jon

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

 

[Gord Dibben]

Data>Import External Data>New 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