How to input values into dropdown box of web page using Excel VBA

Question

I'm trying to operate a website to display desired option chain data with an Excel VBA macro. The website -- CBOE.com -- has an input field for the ticker symbol of the desired option chains. My code has been able to drive that part of the webpage and a default option chain is displayed. It defaults to the most current month that options expire (May 2018 as of this note). From there the user can input other expiration dates for which to have other option chains (for the same symbol) to be retrieved and displayed. This is where my code seems to be breaking down.

Just above the default option chain display is a dropdown input box labeled "Expiration:" where a list of other expiration months can be selected. Once selected, a green Submit button must be clicked to get the specified option chain for the selected expiration month. Alternatively, below the default option chain are explicit filter buttons for expiration months also.

As said, my code gets to the point of specifying the symbol and getting default option chains displayed, but I can't seem to get the dropdown input field for other expiration months to work.

If anyone can see where and how my code is deficient, I'd really appreciate that insight.

Many thanks. --Mark. Here is my core code in question:

Sub getmarketdata_V3()

Dim mybrowser As Object, myhtml As String
Dim htmltables As Object, htmltable As Object
Dim htmlrows As Object, htmlrow As Object
Dim htmlcells As Object, htmlcell As Object
Dim xlrow As Long, xlcol As Integer
Dim exitat As Date, symbol As String
Dim flag As Integer

On Error GoTo errhdl

Const myurl = "http://www.cboe.com/delayedquote/quote-table"

symbol = UCase(Trim(Range("ticker").Text))

With Range("ticker").Worksheet
    Range(Range("ticker").Offset(1, 0), Cells(Rows.Count, Range("ticker").Column + 13)).ClearContents
End With

Set mybrowser = CreateObject("internetexplorer.application")

mybrowser.Visible = True

mybrowser.navigate myurl

While mybrowser.busy Or mybrowser.readyState <> 4
    DoEvents
Wend

With mybrowser.document.all

    exitat = Now + TimeValue("00:00:05")
    Do
        .Item("ctl00$ContentTop$C002$txtSymbol").Value = symbol
        .Item("ctl00$ContentTop$C002$btnSubmit").Value = "Submit"
        .Item("ctl00$ContentTop$C002$btnSubmit").Click
        If Err.Number = 0 Then Exit Do
        Err.Clear
        DoEvents
        If Now > exitat Then Exit Do
    Loop
End With

'This With statement is to refresh the mybrowser.document since the prior With statement pulls up a partially new webpage
With mybrowser.document.all
    On Error Resume Next
    exitat = Now + TimeValue("00:00:05")

'Tried using "ID" label to select desired month--in this case 2018 July is a dropdown option:
'Usind this label seems to blank out the value displayed in the dropdown input box, but does not cause
'any of the options to display nor implant "2018 July" in it either. It just remains blank and no new option
'chain is retrieved.
    .Item("ContentTop_C002_ddlMonth").Select
    .Item("ContentTop_C002_ddlMonth").Value = "2018 July"
    .Item("ContentTop_C002_ddlMonth").Click

'Then tried using "Name" label to select desired month--in this case 2018 July is an option:

  '  .Item("ctl00$ContentTop$C002$ddlMonth").Value = "2018 July"
  '  .Item("ctl00$ContentTop$C002$ddlMonth").Click

  '  .Item("ctl00$ContentTop$C002$btnFilter").Value = "View Chain"

  '  .Item("ctl00$ContentTop$C002$btnFilter").Click


    End With

While mybrowser.busy Or mybrowser.readyState <> 4
    DoEvents
Wend

'Remaining logic, except for this error trap logic deals with the option chain results once it has been successfully retrieved.
'For purposes of focus on the issue of not being able to successfully have such a table displayed, that remaining process logic is not
'included here.


errhdl:
If Err.Number Then MsgBox Err.Description, vbCritical, "Get data"
On Error Resume Next
mybrowser.Quit
Set mybrowser = Nothing
Set htmltables = Nothing

End Sub

Answer 1

For your code:

These 2 lines change the month and click the view chain (I tested with symbol FLWS). Make sure you have sufficient delays for page to actually have loaded.

mybrowser.document.querySelector("#ContentTop_C002_ddlMonth").Value = "201809"
mybrowser.document.querySelector("#ContentTop_C002_btnFilter").Click

---

I found the above sketchy for timings when added into your code so I had a quick play with Selenium basic as well. Here is an example with selenium:

Option Explicit
'Tools > references > selenium type library 

Public Sub GetMarketData()
   
    Const URL As String = "http://www.cboe.com/delayedquote/quote-table"
    Dim d As ChromeDriver, symbol As String
    
    symbol = "FLWS"
    Set d = New ChromeDriver
    
    With d
        .Start
        .Get URL

        Dim b As Object, c As Object, keys As New keys
        
        Set b = .FindElementById("ContentTop_C002_txtSymbol")
        b.SendKeys symbol
        .FindElementById("ContentTop_C002_btnSubmit").Click
        Set c = .FindElementById("ContentTop_C002_ddlMonth")
        c.Click
        c.SendKeys keys.Down                     'move one month down
        .FindElementById("ContentTop_C002_btnFilter").Click
        
        Stop '<<delete me later
        .Quit
        
    End With
  
End Sub

Answer 2

Try the below approach, in case you wanna stick to IE. I tried to kick out hardcoded delay from the script. It should get you there. Make sure to fill in the text field with the appropriate ticker from the below script before execution.

There you go:

Sub HandleDropDown()
    Const url As String = "http://www.cboe.com/delayedquote/quote-table"
    Dim IE As New InternetExplorer, Html As HTMLDocument, post As Object, elem As Object

    With IE
        .Visible = True
        .navigate url
        While .Busy Or .readyState <> 4: DoEvents: Wend
        Set Html = .document
    End With

    Do: Set post = Html.getElementById("ContentTop_C002_txtSymbol"): DoEvents: Loop While post Is Nothing
    post.Value = "tickername"  ''make sure to fill in this box with appropriate symbol

    Html.getElementById("ContentTop_C002_btnSubmit").Click

    Do: Set elem = Html.getElementById("ContentTop_C002_ddlMonth"): DoEvents: Loop While elem Is Nothing
    elem.selectedIndex = 2 ''just select the month using it's dropdown order

    Html.getElementById("ContentTop_C002_btnFilter").Click
End Sub

Reference to add to the library:

Microsoft Internet Controls
Microsoft HTML Object Library