[RESOLVED] Data Import from Internet

[Cerb]

Hi guys,

I have a problem importing data from the internet.

I was going to import tick data from that site:
http://deutsche-boerse.com/dbag/disp...Equi&wpbpl=ETR

In each table are 100 ticks, but a trading day sometimes consists of over 20000 ticks.

It appears that it's impossible to adress the required table by the url, i.e. if I type in my browser the adress above, I always get to the same table, namely the first.
And if I need the hundredth I have to click me through a vast amount of tables and copy and paste all of the ticks.
If I could adress each table by changing acertain number in the url it would be no problem importing the data using vba, but it's seemingly impossible.

So what is the most elegant way of importing that tick data from the site above?
(Perhaps somehow by the code of the site?)

Any helpful advice will be appreciated.

Thanks in advance.

[westconn1]

it would appear you can navigate to the javascript address for each page, see if this thread can help you at all
http://www.vbforums.com/showthread.p...40#post3636640

[Cerb]

it would appear you can navigate to the javascript address for each page, see if this thread can help you at all
http://www.vbforums.com/showthread.p...40#post3636640

Thanks for that,

but unfortunately that does not help me at all. The link above provides no help.

So would You please tell me how to navigate to the javascript adress if it solves my problem I described in the first post?

[Cerb]

Hi westconn1,

You refered my question to an older posting but that wasn't helpful at all.
Would You please help me to get the information I need?

As I already mentioned I want to get tick data from a certain site http://deutsche-boerse.com/dbag/disp...Equi&wpbpl=ETR
but I cannot navigate by the url to the required table sheet. I need all the data but cannot adress the whole table by changing the url. Adressing the sites by the url works here for instance. By changing the last number of that url: http://www.vbforums.com/private.php?do=newpm&u=53814 I can get to different pages of the vbforums site. (http://www.vbforums.com/private.php?do=newpm&u=53800 instead of 53814)
Do You see what I mean?
On the other site I only get to the next table by clicking on the page and the url doesn't change unfortunately.
Please help me to get the data I need.

Cerb

[westconn1]

i have looked at that site and have no solution to offer at this time

i am unable to find the element by code to click it although, i can clearly see it in the page source, complete with an id and href, which seems a bit strange to me

[westconn1]

i got this working you can give it a try

Set fr = wb.document.getelementsbytagname("iframe")
Set b = fr(0).contentwindow.document.getelementbyid("ctl05_ctl02_Page6")
b.Click

[Cerb]

Thank you very much westconn1,

I appreciate that you spend some of your time for resolving my problem.

Do you think that some friend of yours could help me with that problem?

I really need the solution for that.

Thanks in advance.

Sorry for sending two messages, I couldn't see in my browser that I already sent you a mail. Every time I tried to send you a message the browser broke down. And besides that there was no message listed in the sent items folder.

[Cerb]

i got this working you can give it a try

Set fr = wb.document.getelementsbytagname("iframe")
Set b = fr(0).contentwindow.document.getelementbyid("ctl05_ctl02_Page6")
b.Click

Now that was a pretty quick answer, thank you man.
As soon as I try it out I'll drop you a line.

[Cerb]

I'm sorry for that probably stupid question but how can I implement your suggestion in the following code: (I want to get the data (ticks of the stock) (several tables of it, for instance table 1 to 100) in my excel sheet, how can I do that?) So how can I get your code in my code working?

Sub zeitintervalle()
    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://deutsche-boerse.com/bf4dbag/DE/export/export.aspx?module=tickdata&isin=DE0005140008&navpath=http://deutsche-boerse.com/dbag/dispatch/de/isg/gdb_navigation/home" _
        , Destination:=Range("$A$1"))
        .Name = "home"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlAllTables
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With

End Sub

[westconn1]

i have no idea how that can be incorporated into your existing code
i was using an instance of internet explorer to navigate the website,

i have in the past been able to copy the data from the internet explorer instance and pastespecial into excel, it is in a thread in this forum someplace

[Cerb]

i have no idea how that can be incorporated into your existing code
i was using an instance of internet explorer to navigate the website,

i have in the past been able to copy the data from the internet explorer instance and pastespecial into excel, it is in a thread in this forum someplace

Sorry westconn1, but I don't know what you exactly mean by "explorer instance" and "pastespecial".
I wasn't able to find anything about it in this forum.

Would you please explain what you mean?

[Cerb]

i have no idea how that can be incorporated into your existing code
i was using an instance of internet explorer to navigate the website,

i have in the past been able to copy the data from the internet explorer instance and pastespecial into excel, it is in a thread in this forum someplace

That page looks like something I wanted:http://vba-corner.livejournal.com/4623.html

But I still don't know how to implement that to get what I want, namely the import of all tables from that web site:http://deutsche-boerse.com/dbag/disp...Equi&wpbpl=ETR
into my excel sheet.

Please help me westconn1.

All I got with the following code is to open the web page above automatically.
So what must be changed in that code to get what I want.
Could you post the changed code please?

'returns new instance of Internet Explorer
Function GetNewIE() As SHDocVw.InternetExplorer
  'create new IE instance
  Set GetNewIE = New SHDocVw.InternetExplorer
  'start with a blank page
  GetNewIE.Navigate2 "about:Blank"
End Function
'loads a web page and returns True or False depending on
'whether the page could be loaded or not
Function LoadWebPage(i_IE As SHDocVw.InternetExplorer, _
                     i_URL As String) As Boolean
  With i_IE
    'open page
    .Navigate i_URL
    'wait until IE finished loading the page
    Do While .ReadyState <> READYSTATE_COMPLETE
      Application.Wait Now + TimeValue("0:00:01")
    Loop
    'check if page could be loaded
    If .Document.URL = i_URL Then
      LoadWebPage = True
    End If
  End With
End Function
'finds an open IE site by checking the URL
Function GetOpenIEByURL(ByVal i_URL As String) As SHDocVw.InternetExplorer
Dim objShellWindows As New SHDocVw.ShellWindows

  'ignore errors when accessing the document property
  On Error Resume Next
  'loop over all Shell-Windows
  For Each GetOpenIEByURL In objShellWindows
    'if the document is of type HTMLDocument, it is an IE window
    If TypeName(GetOpenIEByURL.Document) = "HTMLDocument" Then
      'check the URL
      If GetOpenIEByURL.Document.URL = i_URL Then
        'leave, we found the right window
        Exit Function
      End If
    End If
  Next
End Function
    Function GetOpenIEByTitle(i_Title As String, _
                            Optional ByVal i_ExactMatch As Boolean = True) As SHDocVw.InternetExplorer
    Dim objShellWindows As New SHDocVw.ShellWindows

    If i_ExactMatch = False Then i_Title = "*" & i_Title & "*"
    'ignore errors when accessing the document property
    On Error Resume Next
    'loop over all Shell-Windows
    For Each GetOpenIEByTitle In objShellWindows
        'if the document is of type HTMLDocument, it is an IE window
        If TypeName(GetOpenIEByTitle.Document) = "HTMLDocument" Then
        'check the title
        If GetOpenIEByTitle.Document.Title Like i_Title Then
            'leave, we found the right window
            Exit Function
        End If
        End If
    Next
    End Function

Sub ExplorerTest()
Const myPageTitle As String = "Gruppe Deutsche Börse"
Const myPageURL As String = "http://deutsche-boerse.com/dbag/dispatch/de/isg/gdb_navigation/home?active=tickdata&module=M_TickData&wp=DE0005140008&wplist=null&foldertype=_Equi&wpbpl=ETR"
Const mySearchForm As String = "searchform"
Const mySearchInput As String = "searchInput"
Const mySearchTerm As String = "Document Object Model"
Const myButton As String = "Go"

Dim myIE As SHDocVw.InternetExplorer
  'check if page is already open
  Set myIE = GetOpenIEByTitle(myPageTitle, False)
  
  If myIE Is Nothing Then
    'page isn't open yet
    'create new IE instance
    Set myIE = GetNewIE
    'make IE window visible
    myIE.Visible = True
    'load page
    If LoadWebPage(myIE, myPageURL) = False Then
      'page wasn't loaded
      MsgBox "Couldn't open page"
      Exit Sub
    End If
  End If
  
  With myIE.Document.forms(mySearchForm)
    'enter search term in text field
    .elements(mySearchInput).Value = mySearchTerm
    'press button "Go"
    .elements(myButton).Click
  End With
       
End Sub

[westconn1]

i would be surprised if much of that code actually works, but i am often surprised
also it will need a reference to shdocvw

if it does work you can add the code i posted above to select a new page in the data

this is the code i used to find the button

Set wb = CreateObject("internetexplorer.application")
wb.navigate2 "http://deutsche-boerse.com/dbag/dispatch/de/isg/gdb_navigation/home?active=tickdata&module=M_TickData&wp=DE0005140008&wplist=null&foldertype=_Equi&wpbpl=ETR"
wb.Visible = True
Stop ' need to loop here till the page is loaded (wb.readystate = 4)
' i waited till i could see the page was loaded
Set newb = wb.document.getelementsbytagname("iframe")(0).getelementbyid("ctl05_ctl02_Page4")
Set fr = wb.document.getelementsbytagname("iframe")
' in this case there is only 1 iframe in the document, so no need to loop through all iframes
Set b = fr(0).contentwindow.document.getelementbyid("ctl05_ctl02_Page6")
b.Click

no reference should be required for this code to work

[Cerb]

i would be surprised if much of that code actually works, but i am often surprised
also it will need a reference to shdocvw

if it does work you can add the code i posted above to select a new page in the data

this is the code i used to find the button

Set wb = CreateObject("internetexplorer.application")
wb.navigate2 "http://deutsche-boerse.com/dbag/dispatch/de/isg/gdb_navigation/home?active=tickdata&module=M_TickData&wp=DE0005140008&wplist=null&foldertype=_Equi&wpbpl=ETR"
wb.Visible = True
Stop ' need to loop here till the page is loaded (wb.readystate = 4)
' i waited till i could see the page was loaded
Set newb = wb.document.getelementsbytagname("iframe")(0).getelementbyid("ctl05_ctl02_Page4")
Set fr = wb.document.getelementsbytagname("iframe")
' in this case there is only 1 iframe in the document, so no need to loop through all iframes
Set b = fr(0).contentwindow.document.getelementbyid("ctl05_ctl02_Page6")
b.Click

no reference should be required for this code to work

In line:

Set newb = wb.document.getelementsbytagname("iframe")(0).getelementbyid("ctl05_ctl02_Page4")

a runtime error 438 occurs telling: object doesn't support this property or method.

Would you post me the whole code that creates a sheet in excel containing all the data of all tables from that web site above?

Please , I am not able to do that by myself even if I spend two more days and weeks working on that problem. For you it's probably just a matter of a few minutes. Cause you're an expert and I am just a completely clueless newbie.

[westconn1]

my mistake, i should have removed that line from the code before posting

For you it's probably just a matter of a few minutes.

probably i have spent more than 3 hours on this so far, mainly cause it bugged me that i could not get it to work, i have not in any way tried to copy it into excel yet

ok you can try like this, i have not had complete results as i keep having trouble with my internet explorer loading pages, if any page does not load correctly it will error
as i kept crashing excel i ran the code in a vb6 project, the only difference was i needed to get an instance of excel, which you will not need to do

Set wb = CreateObject("internetexplorer.application")
wb.navigate2 "http://deutsche-boerse.com/dbag/dispatch/de/isg/gdb_navigation/home?active=tickdata&module=M_TickData&wp=DE0005140008&wplist=null&foldertype=_Equi&wpbpl=ETR"
wb.Visible = True
Do Until wb.readyState = 4
    DoEvents
Loop
Dim arr2()
ReDim arr2(1 To 10000, 1 To 3)
Set xl = GetObject(, "excel.application")
'no need for xl object from within excel
Set sht = xl.workbooks("book1").sheets("sheet1")
Set fr = wb.document.getelementsbytagname("iframe")
For mypage = 1 To 100
    Set ta = fr(0).contentwindow.document.getelementsbytagname("table")
    myarr = Split(ta(0).innerText, vbNewLine)
    For i = 1 To UBound(myarr)
        j = (mypage - 1) * 100 + i
        arr2(j, 1) = left(myarr(i), 8)
        arr2(j, 2) = Mid(myarr(i), 9, 6)
        arr2(j, 3) = Mid(myarr(i), 16)
    Next
    
    Set b = fr(0).contentwindow.document.getelementbyid("ctl05_ctl02_NextPage")
    If mypage = 100 Then Exit For
    b.Click
    xl.wait 2000
    'use application.wait within excel
    ' you can try adjusting the delay to get faster results, but if too quick next page will not load
    Do Until fr(0).readyState = "complete"
        DoEvents
    Loop
Next
sht.range("A1:c10000") = arr2
wb.quit
Set wb = Nothing

this code now gave me the full 9904 items in the excel worksheet
you may need to adjust the way i broke up the table rows, into the 2d array

[Cerb]

my mistake, i should have removed that line from the code before posting


probably i have spent more than 3 hours on this so far, mainly cause it bugged me that i could not get it to work, i have not in any way tried to copy it into excel yet

ok you can try like this, i have not had complete results as i keep having trouble with my internet explorer loading pages, if any page does not load correctly it will error
as i kept crashing excel i ran the code in a vb6 project, the only difference was i needed to get an instance of excel, which you will not need to do

Set wb = CreateObject("internetexplorer.application")
wb.navigate2 "http://deutsche-boerse.com/dbag/dispatch/de/isg/gdb_navigation/home?active=tickdata&module=M_TickData&wp=DE0005140008&wplist=null&foldertype=_Equi&wpbpl=ETR"
wb.Visible = True
Do Until wb.readyState = 4
    DoEvents
Loop
Dim arr2()
ReDim arr2(1 To 10000, 1 To 3)
Set xl = GetObject(, "excel.application")
'no need for xl object from within excel
Set sht = xl.workbooks("book1").sheets("sheet1")
Set fr = wb.document.getelementsbytagname("iframe")
For mypage = 1 To 100
    Set ta = fr(0).contentwindow.document.getelementsbytagname("table")
    myarr = Split(ta(0).innerText, vbNewLine)
    For i = 1 To UBound(myarr)
        j = (mypage - 1) * 100 + i
        arr2(j, 1) = left(myarr(i), 8)
        arr2(j, 2) = Mid(myarr(i), 9, 6)
        arr2(j, 3) = Mid(myarr(i), 16)
    Next
    
    Set b = fr(0).contentwindow.document.getelementbyid("ctl05_ctl02_NextPage")
    If mypage = 100 Then Exit For
    b.Click
    xl.wait 2000
    'use application.wait within excel
    ' you can try adjusting the delay to get faster results, but if too quick next page will not load
    Do Until fr(0).readyState = "complete"
        DoEvents
    Loop
Next
sht.range("A1:c10000") = arr2
wb.quit
Set wb = Nothing

this code now gave me the full 9904 items in the excel worksheet
you may need to adjust the way i broke up the table rows, into the 2d array

You are the man!
That's what I'm talking about. You are the reason I believe in internet. As long as there are people like you the world wide web has the right to exist. I tried to find any solution for my problem on a german forum but I guess those german bas***ds are nothing compared to you. Nobody on that forum would answer my post. Please let me be your friend from now on. Well no, being your friend would be inappropriate. So let me be one of your disciples. As long as it's no blasphemy. I must confess I will be pretty useless as your disciple but I sure won't be Judas.
OK, enough said, don't want to bother you any more. Don't be afraid, consider that disciple thang as an excessively enthusiastic THANK YOU BRO of a madman.