Slow web scrape

[kiloran]

I'm developing a Calc document which will be used by a number of users. It scrapes data from a number of web pages, but is painfully slow.
A simplified version of the macro is:

Code: Select all

Sub TestMacro1

	dim args(2) as new com.sun.star.beans.PropertyValue
	
	url = "http://uk.advfn.com/p.php?pid=financials&symbol=L%5ESSE"
				
	document = ThisComponent.CurrentController.Frame 
	dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") 
	
	Rem ---- set param -------- 
	args(0).Name = "FileName" 
	args(0).Value = url 
	args(1).Name = "FilterName" 
	args(1).Value = "calc_HTML_WebQuery" 
	args(2).Name = "Source" 
	args(2).Value = "HTML_23" 
	
	dispatcher.executeDispatch(document, ".uno:InsertExternalDataSource", "", 0, args())
end sub

This grabs a table from the web page and pastes it into the active sheet. It typically takes 8-10 seconds to run, using various versions of OpenOffice and LibreOffice on Windows7 and Ubuntu. A comparable script using Excel runs in about 1 second.

Any suggestions how I can scrape data more quickly?

Many thanks,
--kiloran

[Charlie Young]

It doesn't seem to improve anything to use the API instead of the dispatcher, but I think it's neater.

This times itself (in seconds), and yes, it's slow.

Code: Select all

Sub TestMacro2
	
	Dim args(2) as new com.sun.star.beans.PropertyValue
	Dim document as object
	Dim sheet as object
	Dim cell as object
	Dim url As String
	Dim StartTime As Double
	Dim StopTime As Double
	Dim arealinks as Object
	
	StartTime = SysTime
	url = "http://uk.advfn.com/p.php?pid=financials&symbol=L%5ESSE"
	         
	document = ThisComponent
	sheet = document.getCurrentController().getActiveSheet()
	cell = sheet.getCellRangeByName("A1")
	arealinks = document.arealinks
	
	arealinks.insertAtPosition(cell.getCellAddress(),url,"HTML_23","calc_HTML_WebQuery","")

	StopTime = SysTime
	MsgBox(86400 * (StopTime - StartTime))
end sub

Function SysTime() as Double
	Dim svc as Object
			
	svc = createUnoService( "com.sun.star.sheet.FunctionAccess" )  'Create a service to use Calc functions
		
	SysTime = svc.callFunction("NOW",Array())
End Function

I had some Python that could be adapted for this. The biggest problem here is the code needs to be tailored to the specific webpage to be scraped.

Here, we find the beginning of the table (HTML_23), by searching for the string

Code: Select all

<td class='sb' style='color:white' valign='middle' align='right'>Total Dividend Amount</td></tr><TR bgcolor='#f0f0E7' >

then taking everything up to </table>, then taking that string and getting the items between the <td class=> and </td> tags.

I am converting the dates to ISO format. My thinking is that it would ultimately make more sense to dump this into a Base table, but Calc will work well for illustration purposes.

It is short on documentation, but you (or anyone) can ask questions here.

Code: Select all

import uno
import os
import unohelper
import urllib2
import re
from datetime import datetime

context = XSCRIPTCONTEXT

def TestDividends(*dummy):
    oDoc = context.getDocument()
    oSheet = oDoc.getCurrentController().getActiveSheet()
    outData = []   
    Div = getDividends()
    DivLen = len(Div)
    DivRange = oSheet.getCellRangeByPosition(0,0,9,DivLen/10 - 1)
    lastrow = 0
    rowlist = []
    for i in range(DivLen):
        r = i/10
        if r != lastrow:
            outData.append(tuple(rowlist))
            rowlist = []
            lastrow = r
        c = i % 10
        if c == 0:
            date_object=datetime.strptime(Div[i],'%d %b %Y')
            datestr = date_object.isoformat()[0:10]
            rowlist.append(datestr)
        elif c >= 4 and c <= 8:
            if Div[i] != '-':
                date_object=datetime.strptime(Div[i],'%d/%m/%Y')
                datestr = date_object.isoformat()[0:10]
                rowlist.append(datestr)
            else:
                rowlist.append(Div[i])
        elif c == 3 or c == 9:
            if Div[i] != '-':
                rowlist.append(float(Div[i]))
            else:
                rowlist.append(Div[i])
        else:
            rowlist.append(Div[i])
    outData.append(tuple(rowlist))
    DivRange.setDataArray(tuple(outData))            
    return
     
def getDividends():
    url = "http://uk.advfn.com/p.php?pid=financials&symbol=L^SSE"
    Dividends = []
    req = urllib2.Request(url)
    ur = urllib2.urlopen(req)
    s = ur.read()  
    reg = re.compile("[\t\n]")
    s = reg.sub('',s)
    loc = s.find("<td class='sb' style='color:white' valign='middle' align='right'>Total Dividend Amount</td></tr><TR bgcolor='#f0f0E7' >")
    s = s[loc + len("<td class='sb' style='color:white' valign='middle' align='right'>Total Dividend Amount</td></tr><TR bgcolor='#f0f0E7' >"):]
    loc = s.find("</table>")
    t = s[0:loc].strip()
    loc = t.find("<td class=")
    
    while loc != -1:
        t = t[loc:]
        loc2 = t.find(">")
        loc3 = t.find("</td>")
        divstr = t[loc2 + 1:loc3]
        Dividends.append(divstr)
        t = t[loc3 + len("</td>"):]
        loc = t.find("<td class=")
    
    return Dividends
   

g_exportedScripts = TestDividends,

I don't presume it's the last word in Python web scraping -- there is a lot more in the urllib2 library than I'm using -- but it seems that most of the time (a couple of seconds) is used in retrieving the table string rather than parsing it afterward. I am also just getting the data and not the column headings (which could be fixed anyway).

Only way I can see to maybe speed this up more is to try c++, which I might experiment with here out of my own curiosity.

Edit: Changed Python to fix misspelling of the word "dividend," incorrect dimensioning of DivRange, and changed to using setDataArray on general principles, even though it doesn't matter much here (it may be a wee bit faster).

[kiloran]

Many thanks for the suggestion, Charlie.

I was wondering about replacing the OO Basic with Python/JavaScript/whatever. I decided to look more closely at Python since it seems to be more common on this forum, so I downloaded Python 3.3 and played around a bit using Eclipse.
I created a very crude script to download a web page and it seemed to be very quick, so based on this and your thoughts, I'm going to develop this further.
I've got a lot of learning to do (I've no idea how to integrate a Python script into OO, and I'll need to learn Python) but I'm sure I'll progress with the help of Google and this forum.
I've no problem adapting the code to each web page, I've a fair experience of doing this kind of thing with Perl.

Stand by for more questions!