lalalada
07-25-2012, 08:33 PM
I've been trying to get data from the website
h ttp://bsr.twse.com.tw/bshtm/
You can enter the code(e.g. 3008) to get related transaction information
I used to use QueryTables.Add with URL= h ttp://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=2498&FocusIndex=All_100
to download the daily data
However, I find it requires the exact page number to get the the information this morning!:doh:
ex. for 3008. there are 20 pages, then the URL would be
h ttp://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=2498&FocusIndex=All_20
Would anyone please help me find a way to determine the page automatically? I'd like to download more than 800 stocks.
Or, would it be possible to download a csv file?
I found the following in the website's source code:
// window.open("bshtm/"+ HiddenField_spDate +"/"+ document.getElementById("hidTASKNO").value+"/"+ document.getElementById("hidTASKNO").value+".csv");
window.open("bsContent.aspx?StartNumber=" + document.getElementById("hidTASKNO").value + "&download=csv");
Thank you very much!
My code:
Sub get_brokerage_listed()
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim URL As String
For x = 2 To Range("F65536").End(xlUp).Row
code = CStr(Sheets("DataSource").Cells(x, 6))
sheetname = code + "_broker"
If Sheets(sheetname).exist = True Then
Sheets(sheetname).Delete
End If
Worksheets.Add
ActiveSheet.name = sheetname
webURL = "URL; h ttp://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=" & CStr(code) & "&FocusIndex=All_100"
With ActiveSheet.QueryTables.Add(Connection:=webURL, Destination:=Range("A1"))
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4,""table2"""
.Refresh BackgroundQuery:=False
End With
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
p.s. 'cause I'm a newcoming, I put a space in all http
h ttp://bsr.twse.com.tw/bshtm/
You can enter the code(e.g. 3008) to get related transaction information
I used to use QueryTables.Add with URL= h ttp://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=2498&FocusIndex=All_100
to download the daily data
However, I find it requires the exact page number to get the the information this morning!:doh:
ex. for 3008. there are 20 pages, then the URL would be
h ttp://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=2498&FocusIndex=All_20
Would anyone please help me find a way to determine the page automatically? I'd like to download more than 800 stocks.
Or, would it be possible to download a csv file?
I found the following in the website's source code:
// window.open("bshtm/"+ HiddenField_spDate +"/"+ document.getElementById("hidTASKNO").value+"/"+ document.getElementById("hidTASKNO").value+".csv");
window.open("bsContent.aspx?StartNumber=" + document.getElementById("hidTASKNO").value + "&download=csv");
Thank you very much!
My code:
Sub get_brokerage_listed()
On Error Resume Next
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Dim URL As String
For x = 2 To Range("F65536").End(xlUp).Row
code = CStr(Sheets("DataSource").Cells(x, 6))
sheetname = code + "_broker"
If Sheets(sheetname).exist = True Then
Sheets(sheetname).Delete
End If
Worksheets.Add
ActiveSheet.name = sheetname
webURL = "URL; h ttp://bsr.twse.com.tw/bshtm/bsContent.aspx?StartNumber=" & CStr(code) & "&FocusIndex=All_100"
With ActiveSheet.QueryTables.Add(Connection:=webURL, Destination:=Range("A1"))
.PreserveFormatting = True
.RefreshStyle = xlInsertDeleteCells
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "4,""table2"""
.Refresh BackgroundQuery:=False
End With
Next
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
p.s. 'cause I'm a newcoming, I put a space in all http