Trying to extract SOFR rates and dates from US Fed Bank website
, 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.
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
HTML:
https://apps.newyorkfed.org/markets/autorates/rates-search-page?rateType=R3
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.
Code:
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