Captures data from a Web site

This script allows the capture of stock prices displayed in Most Active table on www.stockcharts.com Web site. It shows the use of CaptureTableHTML and WriteExcel functions ; for a step by step explanation using Recording mode, see Web, how to capture data.

You can find the corresponding script file under Script_Examples, sub-directory of scripts.

 

'Declare the arrays needed by CaptureTableHTML function

dim name$(10)

dim value$(10)

dim value_diff$(10)

dim percent$(10)

'************************************************************

'Utilities functions

'************************************************************

function calcul_nb_row()

'This function calculates the number of rows in the captured array

'The return code of CaptureTableHTML gives the number of rows

'of the captured table (including the title line)

 

UsePage("Market Summary - StockCharts.Com")
ret = CaptureTableHTML("TABLE[CONTENT='Major Markets']", "R1C2:R10C2", name$())

nb_row=ret

calcul_nb_row=ret

endfunction

'---------------------------------------------------------

function capture_table(nb_row)

'This function captures the nb_row stock values displayed in the table which has nb_row lines.

UsePage("Market Summary - StockCharts.Com")
ret = CaptureTableHTML("TABLE[CONTENT='Major Markets']", "R1C2:R"+str$(nb_row)+"C2", name$())

ret = CaptureTableHTML("TABLE[CONTENT='Major Markets']", "R1C3:R"+str$(nb_row)+"C3", value$())

ret = CaptureTableHTML("TABLE[CONTENT='Major Markets']", "R1C4:R"+str$(nb_row)+"C4", value_diff$())

ret = CaptureTableHTML("TABLE[CONTENT='Major Markets']", "R1C5:R"+str$(nb_row)+"C5", percent$())

endfunction

'---------------------------------------------------------

function insert_values(nb_row)

'This function writes the values into Excel

ret=WriteExcel(file_excel$,"A1:A"+str$(nb_row),name$())

ret=WriteExcel(file_excel$,"B1:B"+str$(nb_row),value$())

ret=WriteExcel(file_excel$,"C1:C"+str$(nb_row),value_diff$())

ret=WriteExcel(file_excel$,"D1:D"+str$(nb_row),percent$())

endfunction

 

'*********************************************************

'Variable to be initialized

'Give there the Excel filename where you want to write the stock values.

'The Excel file must exist and must not be opened.

file_excel$="c:\wttest\quotation.xlsx"

 

 

'*********************************************************

'Main Program

'*********************************************************

 

'Open page for extracting values

StartBrowser("IE", "http://stockcharts.com/index.html",3)

 

'Click Market Summary

UsePage("StockCharts.com - Simply the Web's Best Stock Charts")

ClickHTMLElement("A[INNERTEXT= 'Market Summary']")

 

'Calculate how many stock values must be captured

nb_name=calcul_nb_row()

 

'Capture values

capture_table(nb_name)

 

 

'Close browser

CloseBrowser()

 

'Insert values in excel file

insert_values(nb_name)

'Open excel file for display

shell("excel "+chr$(34)+file_excel$+chr$(34),3)