Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

Stock quotes

12 views
Skip to first unread message

dave byman

unread,
Nov 30, 1999, 3:00:00 AM11/30/99
to
I'm trying to get stock quotes using a web query in VBA. The query stores
the quote in a temporary cell and then the function returns the quote.
However, when the code gets to Query1.Refresh, it restarts at the beginning
of the function. It then goes through the procedure to the end. When a
series of cells are calculated with this function, the first cell finishes
completely, but the rest of the cells stop at the refresh line. Does anyone
know what's happening or know a better way to retrieve plain text from a web
site w/o using web query?

Thanks Dave Byman
Interwest Financial Group

Myrna Larson

unread,
Nov 30, 1999, 3:00:00 AM11/30/99
to

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.


tim williams

unread,
Dec 4, 1999, 3:00:00 AM12/4/99
to
Yet another approach using a worksheet with a vertical list of stock
symbols starting in A2. Values are copied (in this case just current
price) to the sheet 'webdata'.
Tim.


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

unread,
Dec 5, 1999, 3:00:00 AM12/5/99
to

dave byman

unread,
Dec 10, 1999, 3:00:00 AM12/10/99
to
I think my problem was that the custom function I used in my fomula tried to
change the value of another cell. It then restarted the function.

T.N.Spearman <tnspe...@home.net> wrote in message
news:3849c1d0...@lh2.rdc1.tn.home.com...

Don Guillett

unread,
Dec 12, 1999, 3:00:00 AM12/12/99
to
Where can you add more code to this to automatically select a range or cell
to enter the parameter and the check box to "use this for refresh" When you
record a web query this part is NOT recorded. pls email. TIA
--
Don Guillett
SalesAid Software
Granite Shoals, Texas
don...@281.com

dave byman <dave...@interwestfinancial.com> wrote in message
news:uKB0h$1Q$GA....@cppssbbsa02.microsoft.com...

0 new messages