How to get right URL for a Web Query

[Nathan Gutman]

How can one determine which is the correct URL to use in a Web Query?
For example when I use:

WEB
1
http://finance.yahoo.com/d/quotes.csv?s=fpurx+vtmsx&f=sl1d1t1c1ohgv&e=.csv

I get all the stok quotes in one cell, not parsed.

When I use:

WEB
1
http://moneycentral.msn.com/investor/external/excel/quotes.asp?symbol=fpurx,vtsmx

I get the data parsed OK but it contains a lots of redundant info.

What URL would I, for example use, to access data from Quicken.com?

What method do you use to find stuff like that out?
Thanks,
Nathan

 

[Don Guillett]

With xp home and xl2002, when using the 1st one I get

FPURX 17.93 12/16/2003 6:00pm 0.1 N/A N/A N/A N/A
VTMSX 16.98 12/16/2003 6:02pm 0.02 N/A N/A N/A N/A


When using the 2nd I get the web page.

Last Previous Close High Low Volume Change % Change 52 Wk High 52
Wk Low Market Cap EPS P/E Ratio # Shares Out
Fidelity Puritan Chart News 17.93 17.83 17.93 0.00 0 +0.10 +0.56%
0.00 0.00 0 0.00 0.00 0
Vanguard Total Stock Mkt Idx Chart News 25.22 25.08 25.22 0.00 0
+0.14


You can use either source, from Excel with a query, for your url.( I prefer
Yahoo) on a dummy page and then lookups to get the info desired.

Here is one from Quicken.com
http://www.quicken.com/investments/quotes/?defview=FULL&p=IBM,+FE,dell,t

Here is part of a macro I use to get data from Yahoo into Excel

URLAddress = "http://finance.yahoo.com/d/quotes.csv?s="
mystringend = symbols & "&f=snd1t1l1ohgpv&e=.csv"
myurl = URLAddress + mystringend
With Sheets("Data").QueryTables.Add(Connection:="URL;" & myurl, _
Destination:=Sheets("Data").Range("b2"))
.BackgroundQuery = True
.TablesOnlyFromHTML = False
.Refresh BackgroundQuery:=False
.SaveData = True
End With



Don Guillett
SalesAid Software
(e-mail address removed)

 

[Nathan Gutman]

Thanks, two questions:

When I run the Yahoo query I get everything into one cell like that:

FPURX,17.93,"12/16/2003","6:00pm",+0.10,N/A,N/A,N/A,N/A
VTMSX,16.98,"12/16/2003","6:02pm",+0.02,N/A,N/A,N/A,N/A

How can I get my Excel97 to recognize that this is a comma delimited
text and put the values into separate cells?

Second question:
How have you determined what is the correct address syntax for
Quicken?

 

[Don Guillett]

1. Use Data>text to columns
With Sheets("Data")
..Range("b2:b300").TextToColumns Destination:=[Data!b2],
DataType:=xlDelimited, Comma:=True
..Columns("A:X").EntireColumn.AutoFit
..Columns("f:f").Font.Bold = True
..[mydate] = Format(Now, "mmm dd, yyyy at h:mm AM/PM")
End With

2. I went to quicken.com and fished around.
3 . I can custom design whatever you want.