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). |