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:

Sub Macro2()
    Workbooks.Open Filename:= _
        "http://ichart.finance.yahoo.com/table.csv?s=3888.HK&a=05&b=11&c=2012&d=05&e=11&f=2012&g=d&ignore=.csv" _
        , Origin:=xlWindows
End Sub

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

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.

Sub Get_YFHD()
    
    Dim inputDate As String
    Dim URL As String
    
    inputDate = InputBox("Enter date")
    If inputDate = "" Then Exit Sub
    
    URL = "http://ichart.finance.yahoo.com/table.csv?s=3888.HK" & _
        "&a=" & Month(inputDate) - 1 & "&b=" & Day(inputDate) & "&c=" & Year(inputDate) & _
        "&d=" & Month(inputDate) - 1 & "&e=" & Day(inputDate) & "&f=" & Year(inputDate) & _
        "&g=d&ignore=.csv"
        
    'Retrieve table.csv from Yahoo
    Workbooks.Open URL
    
    'Copy Close and Volume to A1:B1
    ActiveWorkbook.Sheets(1).Range("E2:F2").Copy ThisWorkbook.ActiveSheet.Range("A1")
    
    'Close table.csv
    ActiveWorkbook.Close SaveChanges:=False
    
End Sub

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.

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 Kyle123

Does the below help?

Public Function GetStockProp(strTicker As String, datDay As Date, strProperty) As Double

'YQL Query = select * from yahoo.finance.historicaldata where symbol = '3888.HK' and startDate = '2012-04-11' and endDate = '2012-04-11'
'Test Here: http://developer.yahoo.com/yql/conso...72012-04-11%27

'StrProperty takes one of the follwoing parameters:
'   1. Date
'   2. Open
'   3. High
'   4. Low
'   5. Close
'   6. Volume
'   7. Adj_Close

Dim strDate As String

'Change the case to proper for the property we are trying to get so that we can extract it later
'We could also have changed it to uppercase and the returned text to uppercase instead
strProperty = Application.Proper(strProperty)
'Format the input date as yyyy-mm-dd as this is the format that the service expects
strDate = Format(datDay, "yyyy-mm-dd")

'Create an XMLHTTP object, this is used to run queries on the web.
With CreateObject("MSXML2.XMLHTTP")
    'Open a new request to the url below - notice how we have inserted the parameters we want
    'This is essentially just opening the web page like you do in a browser, so it returns the source data from the page
    .Open "GET", "http://query.yahooapis.com/v1/public/yql?q=select%20*%20from%20yahoo.finance.historicaldata%20where%20symbol%20%3D%20'" _
                    & strTicker & "'%20and%20startDate%20%3D%20'" _
                    & strDate & "'%20and%20endDate%20%3D%20'" _
                    & strDate & "'&env=store%3A%2F%2Fdatatables.org%2Falltableswithkeys"
    'Send the request
    .send
    'Wait for the page to be returned
    Do: DoEvents: Loop Until .readystate = 4
    'If no results are returned the text count=""0"" will be present as there are no results
    'In that case return 0
    If InStr(1, .responseText, "count=""0""") > 0 Then
        GetStockProp = 0
    Else
        'If count=""0"" is not present, some results have been returned. Now we need to get the
        'right result from the string that has been returned
        'The response text currently looks like this:
        '------------------------------------------------
        '<?xml version="1.0" encoding="UTF-8"?>
        '<query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" yahoo:count="1" yahoo:created="2012-08-20T12:20:26Z" yahoo:lang="en-US"><results><quote date="2012-04-11"><Date>2012-04-11</Date><Open>3.40</Open><High>3.45</High><Low>3.37</Low><Close>3.44</Close><Volume>6194100</Volume><Adj_Close>3.33</Adj_Close></quote></results></query><!-- total: 99 -->
        '<!-- engine2.yql.ac4.yahoo.com -->
        '------------------------------------------------
        'To Extract the data I've first used a split function, this splits the text into an array based on a term
        'So if we were to split on Close we'd get:
        ' (0)     '<?xml version="1.0" encoding="UTF-8"?>
        '         <query xmlns:yahoo="http://www.yahooapis.com/v1/base.rng" yahoo:count="1" yahoo:created="2012-08-20T12:20:26Z" yahoo:lang="en-US"><results><quote date="2012-04-11"><Date>2012-04-11</Date><Open>3.40</Open><High>3.45</High><Low>3.37</Low><Close>
        ' (1)     3.44</Close><Volume>6194100</Volume><Adj_Close>3.33</Adj_Close></quote></results></query><!-- total: 99 -->
        ' This is what the (1) relates to in the below
        'The val() function returns the first number from a string, in this case 3.44
        GetStockProp = Val(Split(.responseText, "<" & strProperty & ">")(1))
    End If
    'Clean up
    .abort
End With

End Function

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.

Sub Get_YFHD()
    
    Dim inputDate As String
    Dim URL As String
    
    inputDate = InputBox("Enter date")
    If inputDate = "" Then Exit Sub
    
    URL = "http://ichart.finance.yahoo.com/table.csv?s=3888.HK" & _
        "&a=" & Month(inputDate) - 1 & "&b=" & Day(inputDate) & "&c=" & Year(inputDate) & _
        "&d=" & Month(inputDate) - 1 & "&e=" & Day(inputDate) & "&f=" & Year(inputDate) & _
        "&g=d&ignore=.csv"
        
    'Retrieve table.csv from Yahoo
    Workbooks.Open URL
    
    'Copy Close and Volume to A1:B1
    ActiveWorkbook.Sheets(1).Range("E2:F2").Copy ThisWorkbook.ActiveSheet.Range("A1")
    
    'Close table.csv
    ActiveWorkbook.Close SaveChanges:=False
    
End Sub

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.