Advice for Web data extraction

There's no need to use IE automation for this, as Excel can load the data directly. On the link you gave, set the start and end dates to 11 June 2012, click Get Prices and copy the Download to Spreadsheet link at the bottom of the page. In Excel, use File - Open, paste the spreadsheet link as the file name and Excel retrieves the data. Recorded as a macro, this gives:

Please Login or Register  to view this content.

It's then one line of VBA to copy the Close price from E2 in table.csv to your workbook.

Hello Chippy, thanks for your help. However, I really want to make this process automated, instead I have to manually check the link of every date each time. And I would think it'd be the best if it does not involve and file downloaded to my computer.

My ideal solution is a code which could identify the data I need from the html and format the part of the data to extract only the number requires.

You don't have to manually click the link; you can construct the URL as a string with VBA code according to the query string parameters, i.e. this part:

?s=3888.HK&a=05&b=11&c=2012&d=05&e=11&f=2012&g=d&ignore=.csv"

a = start month (zero-based), b = start day, c = start year, then similarly for the d, e and f values for the end date.

The file (table.csv) is only downloaded to your computer temporarily; you don't need to save it.

Believe me, this really is the easiest way, if not the most efficient. If you don't like this method, I would use XMLhttp to extract the data. This is similar to using IE automation but doesn't have the overhead of invoking IE.

I get you chippy, a very efficient method indeed! Thanks, this solve all of my problems Do you have to close the csv file afterwards? or does it close itself?
Just want to learn a bit more, in case if I need to extract some data from a long coding like html, what would you use? I am very not familiar with text selecting, editing and stuff. I don't even know what does "Trim(doc.getElementsByTagName("span")(22).innerText)" do.... I just copied it from other people's work

Thanks for your reply Kyle! I am sure it does work but I have no idea what does most of the comments do. It would be much much helpful (I know i am asking too much!) if you could explain what does it do for each command. Or alternatively, do you know any web resources which tells you the meaning of each function or command of Macro?

In addition, does anyone know if i have a string with something like

nowrap align="right">Jun 11, 2012</td><td class="yfnc_tabledata1" align="right">3.54</td><td class="yfnc_tabledata1" align="right">3.63</td><td class="yfnc_tabledata1" align="right">3.51</td><td class="yfnc_tabledata1" align="right">3.63</td><td class="yfnc_tabledata1" align="right">1,807,000</td><td class="yfnc_tabledata1" align="right">3.63</td></tr><tr><td class="yfnc_tabledata1" nowrap align="right">

How can I extract the data I need? Let's say i want "1,807,000" from the above text.

Many thanks,

Gordon

Originally Posted by gordonf35

I get you chippy, a very efficient method indeed! Thanks, this solve all of my problems Do you have to close the csv file afterwards? or does it close itself?

You have to close it (with VBA). Here is the complete code which accepts a date from the user and retrieves the Close price and Volume into cells A1:B1 in your workbook.

Please Login or Register  to view this content.

It's not the most efficient or cleverest method, but it does the job and is simple to understand - you don't need to know anything about HTML tags, XML, string parsing, etc.

Originally Posted by Kyle123

Does the below help?

Please Login or Register  to view this content.

Many thanks, this is very helpful! I am sure it is detail and comprehensive enough! Just some questions if you don't mind. I was wondering what does (Open "GET"), (responsetext) means? And how do you define where to spilt, I can't identify the code Val(Split(.responseText, "<" & strProperty & ">")(1)) defining any splitting point. How or when did you define it?

THanks,

Gordon

---------- Post added at 02:28 PM ---------- Previous post was at 02:20 PM ----------

Originally Posted by Chippy

You have to close it (with VBA). Here is the complete code which accepts a date from the user and retrieves the Close price and Volume into cells A1:B1 in your workbook.

Please Login or Register  to view this content.

It's not the most efficient or cleverest method, but it does the job and is simple to understand - you don't need to know anything about HTML tags, XML, string parsing, etc.

Hi Chippy, thanks for your understanding. Yes, i only need a code which works. How did you guys learn all these. I have read some books about VBA but they only tell very simple programming command. By the way, what kind of date does you method accept? mm-dd-yyyy or dd-mmm-yy or others? I am now writing a code to convert user input, 20-08-2012 to AUG 20, 2012...

May be easy for you but it's soooooooooooooo difficult for me!

Originally Posted by gordonf35

By the way, what kind of date does you method accept? mm-dd-yyyy or dd-mmm-yy or others?

Any string or number that Excel can interpret as a date, for example in the UK you could enter 11/06/12, 11/6/12, 11-6-2012, 11/Jun/12, 11-Jun-12, etc. In the USA you would enter month, day, year so that Excel correctly interprets the local date. For user input it is better to specify the required format (e.g. dd/mm/yy) in the prompt and validate the input accordingly.

A better approach from a user interface POV is to display the calendar control on a userform and have the user select a date instead of typing it in.