Getting Web table data into excel

Question

I wanted to get monthly data from "https://investing.com/indices/us-spx-500-historical-data" into excel through vba.

Here is the source code of Time Frame dropdown menu:-

<select id="data_interval" class="newInput selectBox float_lang_base_1">
        <option value="Daily" selected="">Daily</option>
        <option value="Weekly">Weekly</option>
        <option value="Monthly">Monthly</option>
    </select>enter code here

Here is the Table source Code:

<table class="genTbl closedTbl historicalTbl" id="curr_table" tablesorter="">enter code here

Here is the VBA code I am using,

Sub GetData()
Dim IE As Object
Dim Links
Set IE = CreateObject("InternetExplorer.Application")
IE.navigate ("https://uk.investing.com/indices/us-spx-500-historical-data")
IE.Visible = True
Do
    DoEvents
Loop Until IE.readyState = 4
Set Links = IE.document.getElementById("data_interval")
Links.selectedIndex = 2
End Sub

2 is the Monthly Index number, It can select the Monthly from dropdown menu but the table not update itself from weekly to Monthly, I am also wanting to get this data into excel

Vityata · Accepted Answer · 2017-03-01 17:07:46Z

1

Try like this, it may work for you, depending on your Internet Explorer.

Option Explicit

Sub GetData()

    Dim IE      As Object
    Dim Links   As Object
    Dim evt     As Object

    Set IE = CreateObject("InternetExplorer.Application")
    IE.navigate ("https://uk.investing.com/indices/us-spx-500-historical-data")
    IE.Visible = True
    Do
        DoEvents        
    Loop Until IE.readyState = 4

    Set Links = IE.document.GetElementById("data_interval")
    Set evt = IE.document.createevent("htmlevents")
    evt.initevent "change", True, False

    Links.Focus
    Links.SelectedIndex = 2
    Links.FireEvent ("onChange")

    Links.dispatchevent evt

End Sub

Tribute to these guys for firing the event correctly.

edited Mar 1, 2017 at 17:07

answered Mar 1, 2017 at 16:52

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

M Eqbal Over a year ago

Thanks buddy, its worked on previous version of IE but not current IE version.

QHarr Over a year ago

Very nice approach + 1

Collectives™ on Stack Overflow

Getting Web table data into excel

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related