newbie99 wrote:
yes that absolutely would work and that price table is nearly all I need
Try the following -
Set up a new Excel sheet with the following sample-data in the correct rows and columns (you may wish to try with a smaller number of samples at first..) -
The above ORB issuer names and ISIN codes are from the two pages discussed earlier (
https://tinyurl.com/vercjng /
https://tinyurl.com/tp38wuf)
Once you've populated some sample ORB data into Columns A and B from Row 2 downwards, then attach the following code to the VBA command-button (note that there's a scroll-bar to the right of the code window below..) -
Code: Select all
Private Sub CommandButton1_Click()
Dim myUrl As String
Set html = New HTMLDocument
Set oXMLHTTP = CreateObject("MSXML2.ServerXMLHTTP.6.0")
Let LastRow = Range("A65536").End(xlUp).Row
For rowsdown = 2 To LastRow
myUrl = "https://www.fixedincomeinvestor.co.uk/x/bondtable.html?groupid=3653"
With oXMLHTTP
.Open "GET", myUrl, False
.send
End With
html.body.innerHTML = oXMLHTTP.responseText
Set spans = html.getElementById("main").getElementsByTagName("td")
For i = 0 To spans.Length - 1
If spans(i).innerText = Sheets("Sheet2").Cells(rowsdown, 2) Then
Sheets("Sheet2").Cells(rowsdown, 3) = spans(i - 3).innerText
Sheets("Sheet2").Cells(rowsdown, 4) = spans(i + 4).innerText
Sheets("Sheet2").Cells(rowsdown, 4) = RTrim(Sheets("Sheet2").Cells(rowsdown, 4))
End If
Next i
Next rowsdown
For rowsdown = 2 To LastRow
myUrl = "https://www.fixedincomeinvestor.co.uk/x/bondtable.html?groupid=3620"
With oXMLHTTP
.Open "GET", myUrl, False
.send
End With
html.body.innerHTML = oXMLHTTP.responseText
Set spans = html.getElementById("main").getElementsByTagName("td")
For i = 0 To spans.Length - 1
If spans(i).innerText = Sheets("Sheet2").Cells(rowsdown, 2) Then
Sheets("Sheet2").Cells(rowsdown, 3) = spans(i - 3).innerText
Sheets("Sheet2").Cells(rowsdown, 4) = spans(i + 4).innerText
Sheets("Sheet2").Cells(rowsdown, 4) = RTrim(Sheets("Sheet2").Cells(rowsdown, 4))
End If
Next i
Next rowsdown
MsgBox "ORB prices updated"
End Sub
Please note that I've used "Sheet2" for my Excel VBA example above, but whichever sheet name you use, the relevant sheet name will have to be reflected in the above code wherever I've used "Sheet2"..
Also note that whilst the above VBA code is only getting price and currency data at the moment, the code is hopefully able to be added to for additional column information by working left and right from the ISIN column on the original pages. We are finding the ISIN column with this VBA code, on each of the two pages, and then looking 3 columns to the left for the currency, hence
spans(i-3).innertext, and four columns to the right for the price, so hence
spans(i+4).innertext in the code. Hopefully that might help if you need any additional information such as maturity or yield fields.
You'll have to enable the
HTML Object Library in the VBA Editor (
Tools / References), but once you've done that then you should be able to press the command button and get similar price-data to the following snapshot -
Hope this helps.
Cheers,
Itsallaguess