Thanks Dave Byman
Interwest Financial Group
I've set up a portfolio at the MSN Investor site. It allows you to export the
data as a tab-delimited file which you can then import into Excel. There's no
way to automate the exporting, however.
Sub URL_Get_Query()
Dim URL1 As String
Dim tempo As Worksheet, rngList As Range
baseURL = "URL;http://quote.pathfinder.com/money/quote/qc?symbols="
Set rngList = webdata.Range("A2", [A2].End(xlDown))
Set tempo = ThisWorkbook.Sheets.Add
webdata.Activate
Application.ScreenUpdating = False
For Each c In rngList
URL1 = baseURL & c.Value
With tempo.QueryTables.Add(Connection:=URL1, _
Destination:=tempo.Range("A1"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With
c.Offset(0, 2).Value = tempo.Range("E13").Value
Application.ScreenUpdating = True 'allow charts to update
Application.ScreenUpdating = False
tempo.UsedRange.Delete
Next c
Application.DisplayAlerts = False
tempo.Delete
End Sub
"T.N.Spearman" wrote:
>
> I've set up a separate Excel file for each company I want to
> track, naming each with the company's ticker symbol. I then develop
> the web query for the site I get quotes or other info from, and store
> the query, using the "Data - Get exrternal Data - New Web Query"
> toolbar selection in Excel 2000. Then open each file in turn, and in
> cell AI go "Data - Get External Data - Run Saved Query" then select
> the query you saved in the resulting list box, then hit "get data" In
> the box that then comes up I usually leave all the selections under
> "properties" at the defaults, and in "parameters" I select "Use the
> following value" and enter the ticker symbol in the text box.
>
> Once the query is set up, you can use code (every day) to open
> each file in turn, select cell A1, and run the query using the VBA
> code:
>
> Selection.QueryTable.Refresh BackgroundQuery:=False
>
> For convenience, I have set up DDE links linking the cells
> containing the data of interest in the file for each company to a
> master summary worksheet, which brings together the data for all the
> stocks I follow and from which I run the macro which refreshes the
> data. It's a bit complicated, but it works reasonably well.
>
> The preceeding descripton of setting up the query uses a
> manual method. the same task can be done with code but that gets a
> bit more complex, as shown in this example, which sets up a worksheet
> to download analyst recommendations. When you run this code, a
> dialogue box comes up into which you type the stocks ticker symbol,
> then select the box which tells the query to use the same ticker symbo
> each time the query refreshes.
>
> With ActiveSheet.QueryTables.Add(Connection:= _
> "FINDER;C:\WINDOWS\Application_
> Data\Microsoft\Queries\Zacks.iqy", Destination _
> :=ActiveCell)
> .Name = "Zacks"
> .FieldNames = False
> .RowNumbers = False
> .FillAdjacentFormulas = False
> .PreserveFormatting = False
> .RefreshOnFileOpen = False
> .BackgroundQuery = True
> .RefreshStyle = xlInsertEntireRows
> .SavePassword = False
> .SaveData = True
> .AdjustColumnWidth = True
> .RefreshPeriod = 0
> .WebSelectionType = xlAllTables
> .WebFormatting = xlWebFormattingAll
> .WebPreFormattedTextToColumns = False
> .WebConsecutiveDelimitersAsOne = True
> .WebSingleBlockTextImport = False
> .WebDisableDateRecognition = False
> .Refresh BackgroundQuery:=False
> End With
>
> Hope this helps.
T.N.Spearman <tnspe...@home.net> wrote in message
news:3849c1d0...@lh2.rdc1.tn.home.com...
dave byman <dave...@interwestfinancial.com> wrote in message
news:uKB0h$1Q$GA....@cppssbbsa02.microsoft.com...