Obtaining data from webpage into Excel via VBA code

Niall_T

New Member
Joined
Aug 27, 2019
Messages
2
Trying to extract SOFR rates and dates from US Fed Bank website
HTML:
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.


wFQL6Ko.png



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

AYdXIOY

Can anyone help pinpoint where i'm going wrong?

Thanks in advance
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Niall_T

New Member
Joined
Aug 27, 2019
Messages
2
Forgot to add that this is my 1st post so grateful for any comments/suggestions to better layout the thread and issues
 

Forum statistics

Threads
1,136,954
Messages
5,678,754
Members
419,782
Latest member
gc75150

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top