ORB / Retail Bond Pricing Information

[newbie99]

Hi,

I don't suppose anyone could recommend any sources to obtain prices for ORB bonds (I mean to get regular updates via an API or downloadable spreadsheet, I appreciate there are multiple ways to get quotes on individual bonds and ad-hoc downloads via the LSE site).

It seems very easy to get delayed pricing for LSE listed equities and even easier for US ones, but since Yahoo Finance stopped their offering there doesn't seem to be an easy source to obtain ORB data.

I think Quandl and EODData possibly include ORB bonds (having spot checked a couple of tickers), but only if you subscribe to their most premium package, which seems overkill for what I need.

If anyone has any thoughts that would be greatly appreciated.

[Itsallaguess]

I don't suppose anyone could recommend any sources to obtain prices for ORB bonds (I mean to get regular updates via an API or downloadable spreadsheet, I appreciate there are multiple ways to get quotes on individual bonds and ad-hoc downloads via the LSE site).

It seems very easy to get delayed pricing for LSE listed equities and even easier for US ones, but since Yahoo Finance stopped their offering there doesn't seem to be an easy source to obtain ORB data.

I think Quandl and EODData possibly include ORB bonds (having spot checked a couple of tickers), but only if you subscribe to their most premium package, which seems overkill for what I need.

If anyone has any thoughts that would be greatly appreciated.

Are you able to give any examples of the sorts of prices you're after?

For example, these two pages show ORB Bond prices, but it's not clear if they are exactly what you're looking for -

https://www.fixedincomeinvestor.co.uk/x/bondtable.html?groupid=3653

https://www.fixedincomeinvestor.co.uk/x/bondtable.html?groupid=3620

If the above is something that helps, then we could probably knock some Excel code up to scrape the prices based on specific ISIN's on the above two pages, so let me know if that would help.

Welcome to The Lemon Fool btw newbie99 - I see the above is your first post here.

Cheers,

Itsallaguess

[newbie99]

If the above is something that helps, then we could probably knock some Excel code up to scrape prices based on specific ISIN's on the above two pages, so let me know if that would help.

Welcome to The Lemon Fool btw newbie99 - I see the above is your first post here.

Cheers,

Itsallaguess

Thank you for the very rapid response

I was under the impression scraping was frowned upon, so I had discounted that, if sites allow scraping then yes that absolutely would work and that price table is nearly all I need, however what I had hoped is to be able to query a specific date, so for example if I forget / am unable to get the prices on a specific day I can go back and get them, whereas I believe most sites such as the one you linked to, only show the last update they have.

Additionally I was hoping to capture bid/offer, not just the offer side (as they can obviously be quite wide

Does that make sense (apologies if that's a bit wordy).

[hiriskpaul]

If your broker offers the bonds you might be able to set up a downloadable virtual portfolio or watchlist with the bonds (and other securities) in. For example, Hargreaves Lansdown offer this through their watchlist facility, of which you can have several watchlists.

[newbie99]

If your broker offers the bonds you might be able to set up a downloadable virtual portfolio or watchlist with the bonds (and other securities) in. For example, Hargreaves Lansdown offer this through their watchlist facility, of which you can have several watchlists.

Unfortunately HL still doesn't show Bid/Offer prices, just the offer side (and there's no way that I can see to go back historically).

The LSE portfolio page does show bid/offer, but you can't download (can copy paste admittedly, but it says they don't allow page scraping).

Its very frustrating as the old Yahoo Finance API allowed you to select any fields and select historic dates too, from what I could see it worked for everything on ORB as well as the PIBS and prefs (plus all the other more mainstream sectors).

Freetrade has an unofficial API out there, but they don't seem to offer any AIM stocks or Retail bonds/prefs/PIBS etc, so that doesn't help much.

I'm not aware if there are any other brokers that allow the type of download / service I'm after, perhaps there is one or two out there!

[Wozzitworthit]

The LSE Watchlist shows Bid and Offer and is downloadable

Woz

[Itsallaguess]

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

[newbie99]

The LSE Watchlist shows Bid and Offer and is downloadable

Woz

Ah yes, good point, for some reason I stupidly didn't check that as the e-mail alerts only had the offer side on!

I did check the portfolio page, rather than the watchlist one (which oddly only seems to allow you download transactions) and I never thought to check the watchlist page!

The only issue is that the LSE site specifically says page scraping isn't allowed, so I definitely can't automate that

[newbie99]

Hope this helps.

Cheers,

Itsallaguess

Thank you, that did indeed work (just one small change to this line, presumably just because we are using different versions of Excel at a guess:

Code: Select all

Set oXMLHTTP = CreateObject("MSXML2.XMLHTTP")

.

However, as helpful as that solution is, I'm still missing the elusive bid price!

Given the LSE site will probably block my IP if I dared try it there, I'm not even going to attempt that, so the search continues!