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.cs...1c1ohgv&e=.csv

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

When I use:

WEB
1
http://moneycentral.msn.com/investor...ol=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

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/q...+FE%2Cdell%2Ct

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

"Nathan Gutman" wrote in message
...
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.cs...1c1ohgv&e=.csv

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

When I use:

WEB
1

http://moneycentral.msn.com/investor...ol=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

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?

On Tue, 16 Dec 2003 17:39:11 -0600, "Don Guillett"
wrote:

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/q...+FE%2Cdell%2Ct

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

"Nathan Gutman" wrote in message
.. .
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.cs...1c1ohgv&e=.csv

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

When I use:

WEB
1

http://moneycentral.msn.com/investor...ol=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

1. Use Datatext 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.
--
Don Guillett
SalesAid Software

"Nathan Gutman" wrote in message
...
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?

On Tue, 16 Dec 2003 17:39:11 -0600, "Don Guillett"
wrote:

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/q...%2C+FE%2Cdell%
2Ct

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

"Nathan Gutman" wrote in message
.. .
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.cs...1c1ohgv&e=.csv

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

When I use:

WEB
1


http://moneycentral.msn.com/investor...p?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