Excel + Web Data

Question

I've spent hours scouring the net for anything to do with VBA calling data from the web in excel and nothing provides a tutorial on how to do so, only complicated examples un-related to what I want to do.

I want to pull data from either a .html or .xml site, doesn't matter, via Excel and find specific points in that data. I can find specific points but don't know how to get the data or scroll through multiple lines. I've done this exact sort of thing in mIRC (sort of C code).

http://eve-central.com/home/quicklook.html?typeid=438 http://api.eve-central.com/api/quicklook?typeid=438 Those are two examples, using roughly the same data I'd like to retrieve. It'd be FAR more specific with at most 100 or so different values.

I'd like to be TAUGHT how to do it, not given the code to do it, or if you must explain each part with comments in-depth please.
Edit: I'm just saying here that I don't want you to link me some random code that does something similar and expect me to modify it without an explanation. Not wanting someone to just write up a full guide of internet capability for Excel.

Thanks Nick

Edit: I'll try explaining it again in a different sense. I play a game called EVE Online, and there's a lot of "market" databases online that allow access in this form for programs to extract data of certain items' prices. Pretty much I want to do that in an Excel document and then obviously list them in a hidden sheet or something. I don't want to have to use a secondary program, I know it's entirely possible within just Excel.

Answer 1

This is a quick and rough answer, becuase what your doing sounds a little bit dodgy(see comment), but from my understanding of Excel, I think your best bet is to use the ODBC driver and connect directly with MS Access (then export into excel) to the same database that the website is using to to receive all the data.

Which will mean that you will need: 1. both the username and password to the database. 2. the associated sql queries that the website is using to retrieve the data. (which may include joins, functions etc.)

Otherwise, you will have to get it through the "front", and you will need to write your own html parser for the section of each page to generate an xml file so that excel can read the final contents table data cleanly. (for excel 2007 and up.) Doing so will also allow you be able to infer a column's 'data type' such as int, char, bigint etc. in the xml schema. But yeah, this might only need to be done probably in something like console C#/C++/java whatever, not in excel itself because I'm pretty sure excel doesn't have a feature that lets you write your own 'HTML to XML' parser for a website it knows nothing about.

hope this helps..

Answer 2

Have a look here - http://stackoverflow.com/questions/158633/how-can-i-send-an-http-post-request-to-a-server-from-excel-using-vba and http://stackoverflow.com/questions/817602/gethttp-with-excel-vba

That deals with the code part. A note regarding the links provided, refer to the MSDN documentation regarding the WinHttp.WinHttpRequest.5.1 and MSXML2.ServerXMLHTTP to gain better clarity regarding what the these libraries are used for. I also suggested reading up a bit about using COM objects in VBA

I'd like to be TAUGHT how to do it, not given the code to do it, or if you must explain each part with comments in-depth please.

Not to be rude, but this sounds to me like you want someone to teach you how to code. I dont think one can simply teach code.

Edit:

I misunderstood your question, correct me if I wrong, but the crux of your question revolves around I can find specific points but don't know how to get the data or scroll through multiple lines.

Using http://msdn.microsoft.com/en-us/library/aa163921(office.10).aspx, you would probably need to get all your result nodes into an XML object. You could then need to loop through the result set ( which I think is what you meant by scroll).

Related question: http://stackoverflow.com/questions/11305/how-to-parse-xml-in-vba/11325#11325

Answer 3

1) Copy a link to the price data you want. ie

http://api.eve-central.com/api/marketstat?typeid=34&typeid=35&regionlimit=10000002

2) In excel select the location you want the data to be imported to. Preferably a blank sheet.

3) Data -> Import External Data -> New Web Query.

4) Paste the link into the address bar. You can edit the link later on to add or subtract typeids.

5) Press the Go button.

6) Select one of the yellow arrow boxes. I chose the box directly above the line evec_api version.

7) Press the Ok button to import the data to the sheet you selected in step 2.

Now you can right click on the data to "Refresh Data".

Use whatever look up formulas to read the prices from this data to the rest of your sheet.

You can change the address of the Web Query by right clicking on any part of the data and selecting "Edit Query..."

Hope this gets you going in the right direction. This will work with Excel version 2002(XP) and 2003. Excel 2007 is more friendly to XML queries.