accessing data on the web

[mp]

I found this amazing ability in Excel to get data from a web site via the
following
an example url
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=rok&f=a"
ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range("C7"))
returns data on a stock named rok

when i try to do that from c# I tried to establish a data source with the
wizard
menu Data|Add new data source
brings up Data SourceConfiguration Wizard
I selected service (guessing that means online web data???)
that brings up add service reference dialog
I tried pasting that url in to the address textbox but tried every variation
i can think of and just get errors

i get an error with that same url
"The document at the url ....
<i tried many variations>
http://download.finance.yahoo.com/d/quotes.csv?s=rok&f=a
http://download.finance.yahoo.com/d/quotes.csv?
http://download.finance.yahoo.com/d/quotes
http://download.finance.yahoo.com/d/
http://download.finance.yahoo.com/
"was not recognized as a known document type.
etc etc etc"

is it better/required to use ado.net to get to web data?

thanks
mark

 

[Jeff Johnson]

I found this amazing ability in Excel to get data from a web site via the
following
an example url
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=rok&f=a"
ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range("C7"))
returns data on a stock named rok

when i try to do that from c# I tried to establish a data source with the
wizard
menu Data|Add new data source
brings up Data SourceConfiguration Wizard
I selected service (guessing that means online web data???)
that brings up add service reference dialog
I tried pasting that url in to the address textbox but tried every
variation i can think of and just get errors

What you're hitting is a simple URL. It's not a service the way Visual
Studio thinks of services. Try this:

byte[] response = new
System.Net.WebClient().DownloadData("http://download.finance.yahoo.com/d/quotes.csv?s=rok&f=a");

System.Diagnostics.Debug.WriteLine("*** BEGIN RESPONSE ***");
System.Diagnostics.Debug.WriteLine(Encoding.UTF8.GetString(response));
System.Diagnostics.Debug.WriteLine("*** END RESPONSE ***");

Then check the Output window. (Actually, when I hit your URL I got "N/A" as
the response. Maybe you need to tweak it....)

 

[Anders Eriksson]

I found this amazing ability in Excel to get data from a web site via the
following
an example url
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=rok&f=a"
ActiveSheet.QueryTables.Add(Connection:="URL;"& qurl,
Destination:=DataSheet.Range("C7"))
returns data on a stock named rok

What you're hitting is a simple URL. It's not a service the way Visual
Studio thinks of services. Try this:

byte[] response = new
System.Net.WebClient().DownloadData("http://download.finance.yahoo.com/d/quotes.csv?s=rok&f=a");

System.Diagnostics.Debug.WriteLine("*** BEGIN RESPONSE ***");
System.Diagnostics.Debug.WriteLine(Encoding.UTF8.GetString(response));
System.Diagnostics.Debug.WriteLine("*** END RESPONSE ***");

Then check the Output window. (Actually, when I hit your URL I got "N/A" as
the response. Maybe you need to tweak it....)

First you need to be logged in to Yahoo!
I did a search for Microsoft MSFT. and got this URL
"http://download.finance.yahoo.com/d/quotes.csv?s=MSFT&f=sl1d1t1c1ohgv&e=.csv"

Using that URL I got Jeff's program to write out

*** BEGIN RESPONSE ***
"MSFT",27.881,"1/5/2011","1:57pm",-0.2065,27.91,27.975,27.77,34727688

*** END RESPONSE ***

// Anders

 

[mp]

I found this amazing ability in Excel to get data from a web site via the
following
an example url
qurl = "http://download.finance.yahoo.com/d/quotes.csv?s=rok&f=a"
ActiveSheet.QueryTables.Add(Connection:="URL;" & qurl,
Destination:=DataSheet.Range("C7"))
returns data on a stock named rok

when i try to do that from c# I tried to establish a data source with the
wizard
menu Data|Add new data source
brings up Data SourceConfiguration Wizard
I selected service (guessing that means online web data???)
that brings up add service reference dialog
I tried pasting that url in to the address textbox but tried every
variation i can think of and just get errors

What you're hitting is a simple URL. It's not a service the way Visual
Studio thinks of services. Try this:

byte[] response = new
System.Net.WebClient().DownloadData("http://download.finance.yahoo.com/d/quotes.csv?s=rok&f=a");

System.Diagnostics.Debug.WriteLine("*** BEGIN RESPONSE ***");
System.Diagnostics.Debug.WriteLine(Encoding.UTF8.GetString(response));
System.Diagnostics.Debug.WriteLine("*** END RESPONSE ***");

Then check the Output window. (Actually, when I hit your URL I got "N/A"
as the response. Maybe you need to tweak it....)

yep, the one tag 'a' didn't produce any return, don't know why...
added some other tags and got a result
That is fantastic!!!
thanks a ton - that's super simple...i'm amazed
Mark

 

[mp]

I found this amazing ability in Excel to get data from a web site via the
following

[]

But it seems to me that you may be better off just to use HttpWebRequest
to retrieve the data. CSV is not always trivial to parse, but it's
reasonably simple and for stock data you may never run into the
complexities that can come up, such as quotes (typographical, that is) and
quoted commas.

Pete

thanks Pete,
I'll look into HttpWebRequest
Jeff's method also worked nicely.
Thanks
Mark

 

[Jeff Johnson]

yep, the one tag 'a' didn't produce any return, don't know why...
added some other tags and got a result
That is fantastic!!!
thanks a ton - that's super simple...i'm amazed

WebClient is very handy...and also very
you-can-have-any-color-you-want-as-long-as-it's-black. If you need to do
some tweaking then you have to take a step back and directly use the
HttpWebRequest/Response classes as Pete mentioned. It's good to get to know
those classes, but for straightforward stuff I stick to WebClient.

If you're serious about Web work and have some time to spare, I highly
recommend reading the RFC on HTTP. It's dry and boring and informative as
hell. While you're doing it you should also have some sort of traffic
monitoring program on hand (I like Fiddler) so that you can see the actual
HTTP packets that are being transmitted and received. The HTTP protocol is
pretty simple and once you understand what it's about you'll be better able
to use the .NET classes that deal with it.