Obtaining data from webpage into Excel via VBA code

[Niall_T]

Trying to extract SOFR rates and dates from US Fed Bank website

https://apps.newyorkfed.org/markets/autorates/rates-search-page?rateType=R3

, using excel sheet to drive the dates (basically all data from 1st April 2019 to present), back into the same excel sheet to link into other formulas and calculations.

Problem encountered when trying to pull rates and dates through - believe its linked into the fact that the once the dates are submitted in the first URL, and the request is submitted it loads into a completely different URL. And I think that using this URL in the 2nd part of the code it doesn't take into account the dates for the search and therefore shows 0 records.

Sub getratestoexcelsheet()
    Const cURL = "https://apps.newyorkfed.org/markets/autorates/rates-search-page?rateType=R3" 'Enter the web address here
    
    Dim IE As InternetExplorer
    Dim doc As HTMLDocument
    Dim StartDate As HTMLInputElement
    Dim EndDate As HTMLInputElement
    Dim HTMLelement As IHTMLElement
    
        
    Set IE = New InternetExplorer
    
    IE.Visible = True
    IE.navigate cURL
    
    'Wait for initial page to load
    
    Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    
    Set doc = IE.document
    
    'enter date into startdate box
    Set StartDate = doc.getElementById("startDate")
    StartDate.Value = Sheets("sheet1").Range("D1").Value 'Value taken from excel sheet - format mm/dd/yyyy
    
[LEFT][COLOR=#222222][FONT=Verdana]    'enter date into enddate box[/FONT][/COLOR][/LEFT]
    Set EndDate = doc.getElementById("EndDate")
    EndDate.Value = Sheets("sheet1").Range("D2").Value  [COLOR=#222222][FONT=Verdana]'Value taken from excel sheet - format mm/dd/yyyy[/FONT][/COLOR]    
    
   'click submit button
    Set createpin = IE.document.all.Item("submit")
    createpin.Click
                    
    'Wait for the new page to load + 10 secs to ensure page is loaded
        Do While IE.readyState <> READYSTATE_COMPLETE Or IE.Busy: DoEvents: Loop
    Application.Wait Now + TimeValue("0:00:10")


'Pull data into Excel sheet - sheet 2 A1    
With Sheet2.QueryTables.Add(Connection:= _
"URL;https://apps.newyorkfed.org/markets/AutoRates/Rates-Search-Results-Page", _
Destination:=Range("$A$1"))
.Name = "SOFR_Rates"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlgrid_10
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

IE.Quit
End Sub

I've tried to analyse the URL source and find the table but with my very limited knowledge I don't think its set into a table format and so haven't been able to extract any data..... I believe its all under but not sure and haven't been able to use any code excerpts from similar posts to get the data into the excel sheet

Can anyone help pinpoint where i'm going wrong?

Thanks in advance

 

[Niall_T]

Forgot to add that this is my 1st post so grateful for any comments/suggestions to better layout the thread and issues