Formula For Regularly Importing Internet Data

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
 
Thanks a ton,

Jon

Dave F said:
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
Click to expand...
 
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
 
Back
Top