Data Source from Web (or .csv file d/l from web)

[thereuare]

This is a webpage which opens a CSV file and displays the data in a table on the webpage (page is from Yahoo Finance, stock symbol MSFT):

http://table.finance.yahoo.com/table.csv?a=3&b=27&c=2002&d=6&e=28&f=2002&s=msft&y=0&g=d&ignore=.csv

I'd like to be able to prompt the user for a stock symbol using a Dialog Box, and then have my macro read the corresponding CSV file. I already have the program able to read a CSV file if it's on my hard drive, but how can i use the data coming from a web source (instead of a file on my hard drive)?

I'm not married to using the above method/website, just thought i'd add it to aid anyone that could help.

Thanks for any help OR pointing me in the right direction!

 

[thereuare]

Anybody know how this can be done?

 

[PaddyD]

thereuare,

have you looked at using a dynamic web query?

Take a look at the following:

http://www.mrexcel.com/board/viewtopic.php?topic=12110&forum=2

& download the excel web connectivity guide. Given the example data you gave, looks like it might be just the thing.

Does this help?

Paddy

 

[thereuare]

Thank you for the reply.

I've used web queries before, and i don't think it'll work in this situation because:

1) to the best of my knowlege, web queries are "fixed" to one webpage, i want the user to input a stock symbol in an dialogue box which would specify the appropriate web page(in the link above in my post, click on it to see how the webpage appears, then do it again but take the MSFT out of the link and subsitute another stock symbol, like HD for Home Depot... i need the data from the input box to get to the appropriate webpage)

2) it appears that the above webpage downloads data from a CSV file (notice the graphic of how much of the download is complete while you're waiting for the page to load) and then displays it in a table on the page. I don't need to view the page, i just want my macro to read the data from that appears in that page

I was hoping there'd be something simple that would let me read/open/get the data over the web and use a variable in place of "MSFT" above. Then i could just have the symbol inputted in the dialogue box assigned to the variable.

Am i explaining this well enough?

Incidentally, since the formatting of a web query usually isn't too good (as you pointed out), i usually import to Sheet1, and then have Sheet2 do a VLOOKUP for whatever criteria i'm looking for. Hope maybe that helps you some if you've never done it that way before.

Please add any add'l thoughts you may have, given the above info of what i'm trying to do.

 

[thereuare]

I just learned that web queries are not "fixed" like i said above, as i just tried the pre-loaded webquery "Microsoft Investor Stock Quotes", and it gave me a pop up window and used the data i inputted.

So i now know it can be done, but how can i get a similar pop-up box to work with the Yahoo link i gave above??

Thanks <once again>

 

[PaddyD]

thereuare,

Given the exta info, I still think web queries might be the way to go:

1) you can return data from multiple pages wiith the same query, as long as those pages are (a) on the same site, and (b) accessable from the input to a single form. Looks like this is probably the case in the eg you gave.

2) as far as the 'web queries return horribly formatted excel pages' is concerned, I agree. But, in this case the pages being returned are already in a table format with no 'extra' bits, so that shouldn't be a problem.


To see if I'm right, could you post back the address of the page you would put 'msft', 'hd' etc into to get back the results you quoted above...

Paddy
This message was edited by PaddyD on 2002-06-29 21:24

 

[thereuare]

Here's a page for you to look at:
http://table.finance.yahoo.com/k?s=msft&g=d

The table is obtained by clicking on "Download Spreadsheet Format" near the bottom of the page.

Something else that i learned playing around with it...

Right mouse clicking and looking at the properties of the Download Spreadsheet Format. The direct link properties are:
http://table.finance.yahoo.com/table.csv?a=3&b=29&c=2002&d=6&e=30&f=2002&s=msft&y=0&g=d&ignore=.csv

Where: a=starting month of data
b=starting date of data
c=starting year of data
d=ending month of data
e=ending day of data
f=ending year of data
s=stock symbol
y=i'm not sure what y is
g=i'm not sure what g is

Shouldn't there be an easy way to set up an input box, where the entry entered is assingned to a variable called SYMBOL?

If this could be done, i could just have the above link look something like this, simply substituing SYMBOL for MSFT (where SYMBOL would be whatever was entered into the input box):
http://table.finance.yahoo.com/tabl...&d=6&e=30&f=2002&s=SYMBOL&y=0&g=d&ignore=.csv

Lastly, if the above can all be done, would it be possible to have the data read without the data being put into a worksheet, and then using:

Do While Not EOF(1)
Input #1, CheckValue

in order to read the data and do something with it.

Thanks for sticking this one out with me, if i'm not clear on anything above, please ask and i'll post more info.

Thanks again.

PS- in using the above link and substituting in different stock symbols where the variable SYMBOL would go, it sometimes works and gives me the table i'm looking for, but often i get an illegal operation error with the following details:
EXCEL caused an invalid page fault in
module EXCEL.EXE at 0187:300eb5a5.
This message was edited by thereuare on 2002-06-30 09:30

 

[PaddyD]

thereuare,

good detective work so far!

OK - using the following query:

WEB
1
http://table.finance.yahoo.com/table.csv?a=3&b=29&c=2002&d=6&e=30&f=2002&s=msft&y=0&g=d&ignore=.csv

I get the following table returned:

Book2
	A	B	C	D
1	Date,Open,High,Low,Close,Volume
2	28-Jun-02,54.55,55.79,54.00,54.70,44777800
3	27-Jun-02,54.60,54.94,52.86,54.92,47906200
4	26-Jun-02,51.05,54.39,51.05,54.13,56065700
5	25-Jun-02,54.70,55.00,52.80,52.95,42012600
6	24-Jun-02,52.09,54.64,51.85,54.16,52261600
7	21-Jun-02,53.40,54.55,52.28,52.28,58410000
8	20-Jun-02,54.37,55.10,53.90,54.10,43200600
9	19-Jun-02,55.47,55.94,54.32,54.36,40623500
10	18-Jun-02,55.53,56.29,55.35,55.99,38759200
11	17-Jun-02,55.66,56.44,55.37,55.68,47243300
12	14-Jun-02,53.15,55.55,53.00,55.25,54716800
13	13-Jun-02,54.84,55.48,54.20,54.22,39885800
...

Which is a nice, clean table, unlike most of te returns from web queries. It should be straightforward to record a macro to do the text to cols stuff...


So - can we get this returned directly from the input to a dynamic web query?
Unfortunately, no, 'cos the page is generated from the input to multiple forms, and the dynamic query will only cope with input from one.

But, we could get the users to input the parameters for a static query that was created by a macro that used their data to write the last line of the query (formatted as you've worked out above for a to s...) & then run the query.

How's your VB?

Take a look at:

http://www.mrexcel.com/board/viewtopic.php?topic=11973&forum=2

where reefland has made a start on something very similar...

Does this help?

Paddy

 

[dano0726]

Somewhere in the vicinity of Thereuare's post, I have a question. How does one setup the web query were the stock/commodity quotes will be row after row of intraday (say 60 minute bars/intervals) from the Web data source?

What about from a nonWeb (LPT1) source?

Thanks

dano0726

 

[PaddyD]

dano0726,

Welcome to the board!

Don't want to put you off, but could you start a new thread on this one Include a link back to this post if you think there's info here that will help.

I ask 'cos it will allow us to keep all the various bits of info separate & enable us to attack each issue with a little more focus.

Perhaps include the web site you're look to pull the data from & anything else that might be useful...

Paddy