|
How to get right URL for a Web Query
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 |
How to get right URL for a Web Query
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 |
How to get right URL for a Web Query
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 |
| All times are GMT +1. The time now is 05:30 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com