Donate to Remove ads

Got a credit card? use our Credit Card & Finance Calculators

Thanks to tea42,yyuryyub,Howyoudoin,Anonymous,EverybodyKnows, for Donating to support the site

HYPTUSS - RNS news item harvesting

Discussions regarding financial software
Itsallaguess
Lemon Half
Posts: 7783
Joined: November 4th, 2016, 1:16 pm
Has thanked: 3525 times
Been thanked: 7343 times

HYPTUSS - RNS news item harvesting

#498926

Postby Itsallaguess » May 6th, 2022, 4:57 pm

I've been having a play with trying to harvest portfolio-relevant RNS news articles, and have got it working to the point where I thought it might be interesting to other Excel VBA-capable 'experimenters', to perhaps gain some feedback.

It will use a list of HYPTUSS portfolio epics to go through a set of date-based news-releases, defaulting to 'todays date', and will then list relevant RNS details for that date, only when relevant to the currently held portfolio.

Here's some quick and dirty instructions if anyone wants to have a play ON A COPY of their current Excel HYPTUSS file -

1. Create a new sheet - called 'RNS News' or similar

2. From the 'Developer' tab, insert an 'Active X' command button on the sheet

3. In 'Design Mode', double-click the above command button and DELETE the two default lines of VBA code (default VBA password for HYPTUSS is 'pleaseletmein')

4. Copy and paste the following Excel VBA code onto the above command button (note - there's a 'Select all' button at the top of the following CODE window, to help with then doing a CTRL-C copy of all the code...) -

Code: Select all

Private Sub CommandButton1_Click()

Dim epic_string, company, midbit, rns_epic, date_to_use As String

Dim lastrow, done, rowsdown, rowdown, pno As Single
Dim do_again, elems, openPos, closePos As Single
Dim startrow, checkitem As Single

Dim oXMLHTTP, html, nodeColumnElements As Object


' Build a string of current portfolio holding EPICS

Let epic_string = ""

Let lastrow = Sheets("High Yield Portfolio").Range("C65536").End(xlUp).Row

    For rowsdown = 6 To lastrow
        Let done = 0
        ' Convert some known odd-ball EPICS to the required Investegate format (BT-A and all 2-character EPICs for now - there may be more..)
        If Sheets("High Yield Portfolio").Cells(rowsdown, 3) = "BT-A" Then
            epic_string = epic_string + "(BT.A)"
            Let done = 1
        ElseIf Len(Sheets("High Yield Portfolio").Cells(rowsdown, 3)) = 2 Then
            epic_string = epic_string + "(" & Sheets("High Yield Portfolio").Cells(rowsdown, 3) & ".)"
            Let done = 1
        End If
   
        If done = 0 Then
            epic_string = epic_string & "(" & Sheets("High Yield Portfolio").Cells(rowsdown, 3) & ")"
        End If
    Next rowsdown

' Clear the current RNS list in the first four columns of the sheet

Columns("A:D").Select
    Selection.ClearContents
Range("G3").Select

' Insert some column headings on the first row

Cells(1, 1) = "Time"
Cells(1, 2) = "Company"
Cells(1, 3) = "Announcement"
Cells(1, 4) = "URL Link"

' Parse todays RNS pages and pull out any relevant RNS articles (ignores Form8.x, Director/PDMR Shareholding, and Transaction in Own Shares news items)

Set html = CreateObject("htmlfile") ' New MSHTML.HTMLDocument

Set oXMLHTTP = CreateObject("Microsoft.XMLHTTP")

Let rowdown = 2
Let pno = 1
Let do_again = 1

' Ask the user for the YYYYMMDD format date to be used - prefill text box with today's date to help

date_to_use = InputBox("Please enter the date to be searched - " & vbCrLf & "Date format YYYYMMDD (Default is todays date)", "Date -", Format(Date, "YYYYMMDD"))

' Loop through all RNS pages on that date, and pull out EPIC-relevant RNS articles

Do While do_again = 1

' Open the Investegate website for the relevant share, and then scrape the data

oXMLHTTP.Open "GET", "https://www.investegate.co.uk/Index.aspx?date=" & date_to_use & "&arch=1" & "&pno=" & pno & "&cb=" & Timer() * 100, False
oXMLHTTP.send

html.body.innerHTML = oXMLHTTP.responseText

' Check to see if this is the last page of RNS articles for today, or if we're going to carry on after this page

If InStr(html.body.innerHTML, "pno=" & pno + 1) = 0 Then
    do_again = 0
End If

Set nodeColumnElements = html.getElementsByTagName("td")

For elems = 0 To nodeColumnElements.Length - 1

        If (InStr(nodeColumnElements(elems).innerHTML, "STRONG") > 0 Or InStr(nodeColumnElements(elems).innerHTML, "strong") > 0) _
        And InStr(nodeColumnElements(elems).innerHTML, "(") > 0 _
        And InStr(nodeColumnElements(elems).innerHTML, ")") > 0 _
        And InStr(nodeColumnElements(elems + 1).innerText, "Form 8.") < 1 _
        And InStr(nodeColumnElements(elems + 1).innerText, "Director/PDMR Shareholding") < 1 _
        And InStr(nodeColumnElements(elems + 1).innerText, "Transaction in Own Shares") < 1 Then
   
                    ' Find if RNS EPIC is in EPIC_STRING
                    Let company = nodeColumnElements(elems).innerText
                     
                    openPos = InStr(company, "(")
                    On Error Resume Next
                    closePos = InStr(company, ")")
                    On Error Resume Next
                    midbit = Mid(company, openPos + 1, closePos - openPos - 1)
                   
                    If openPos <> 0 And Len(midbit) > 0 Then
                         rns_epic = "(" & midbit & ")"
                    End If
                 
            ' It's a portfolio-relevant RNS news article, so insert the information onto the worksheet
                 
            If InStr(epic_string, rns_epic) > 0 Then
                Cells(rowdown, 1) = nodeColumnElements(elems - 4).innerText ' TIME
                Cells(rowdown, 2) = nodeColumnElements(elems).innerText ' COMPANY NAME AND (EPIC)
                Cells(rowdown, 3) = nodeColumnElements(elems + 1).innerText ' RNS TITLE
                Cells(rowdown, 4) = Replace(nodeColumnElements(elems + 1).getElementsByTagName("A")(0).href, "about:", "https://www.investegate.co.uk") ' URL TO RNS ITEM
                Let rowdown = rowdown + 1
            End If
           
            Let elems = elems + 2
        End If

Next elems

' Loop to the next RNS page whilst variable do_again = 1

pno = pno + 1

Loop

End Sub


5. Come out of 'Design Mode' on the Developer tab

6. Click the Command Button

7. Use default date or alter to suit - the entered date must be in YYYYMMDD format, so it's 20220506 for today, 20220505 for yesterday, etc...

Hope it's useful, and I'd be interested to hear any feedback.

Cheers,

Itsallaguess

88V8
Lemon Quarter
Posts: 3448
Joined: November 4th, 2016, 11:22 am
Has thanked: 1409 times
Been thanked: 1340 times

Re: HYPTUSS - RNS news item harvesting

#498974

Postby 88V8 » May 6th, 2022, 8:14 pm

Beyond my pay grade, but thankyou....

Hallucigenia has a some sort of scraper for his daily update in the Oilies board.... which consolidates in a way that might be an option for HYPP, pace the recent thread....

V8

csearle
Lemon Quarter
Posts: 4078
Joined: November 4th, 2016, 2:24 pm
Has thanked: 3495 times
Been thanked: 1466 times

Re: HYPTUSS - RNS news item harvesting

#499212

Postby csearle » May 8th, 2022, 9:46 am

Itsallaguess wrote:Hope it's useful, and I'd be interested to hear any feedback.
I have just tried it and got as far as the pop-up announcing an invalid password. Obviously I re-tried several times checking the shift lock. C.

Itsallaguess
Lemon Half
Posts: 7783
Joined: November 4th, 2016, 1:16 pm
Has thanked: 3525 times
Been thanked: 7343 times

Re: HYPTUSS - RNS news item harvesting

#499221

Postby Itsallaguess » May 8th, 2022, 10:01 am

csearle wrote:
Itsallaguess wrote:
Hope it's useful, and I'd be interested to hear any feedback.


I have just tried it and got as far as the pop-up announcing an invalid password.

Obviously I re-tried several times checking the shift lock.


It should work Chris - pleaseletmein - has just worked fine again for me when downloading the latest HYPTUSS version from the Weebly site, and it's the only one we've ever used just to help avoid unintentional breakages rather than keeping the VBA locked - it's always been open-source as far as we're concerned.

Would be interested to hear how you get on, but if you still have issues with your own version of the tool, you can hopefully get going with the latest version in case your own password has perhaps been changed for some reason?

http://lemonfoolfinancialsoftware.weebly.com/hyp-top-up.html

Cheers,

Itsallaguess

Itsallaguess
Lemon Half
Posts: 7783
Joined: November 4th, 2016, 1:16 pm
Has thanked: 3525 times
Been thanked: 7343 times

Re: HYPTUSS - RNS news item harvesting

#499244

Postby Itsallaguess » May 8th, 2022, 11:32 am

To give a feel for the RNS-list output created by this new process, I've followed the earlier instructions for a vanilla HYPTUSS download, and added the following tickers to the default BP-based portfolio -

HINT (Henderson International Income Trust)
SPT (Spirent Communications)
APAX (Apax Global Alpha Limited)

I've then run the new RNS-harvesting process using the following date for last Friday -

20220506

And here's the output RNS list for that date, based on the above portfolio holdings -

Image

Source - A demo HYPTUSS spreadsheet

Cheers,

Itsallaguess

Hallucigenia
Lemon Quarter
Posts: 1333
Joined: November 5th, 2016, 3:03 am
Has thanked: 87 times
Been thanked: 755 times

Re: HYPTUSS - RNS news item harvesting

#499307

Postby Hallucigenia » May 8th, 2022, 4:23 pm

88V8 wrote:Hallucigenia has a some sort of scraper for his daily update in the Oilies board.... which consolidates in a way that might be an option for HYPP, pace the recent thread....


88V8 is referring to this, the current incarnation of my RNSbot. However, it's in PHP running on a local web server, it's not particularly geared to local use with Excel etc, and it's also a bit of a mess at the moment.

The background is that some 15 years ago I created a bot that collated links from various sources - Investegate, ASX, Toronto - since the "tidemark" of the last post, tried to parse the most meaningful bit of RNS's, and then automatically posted to TMF just after 7am. There were two "front-ends" for the oil and mining boards, which contained the list of tickers and also some custom bits - the oil version grabbed oil prices and news from Oilbarrel etc, the mining one grabbed news from Minesite.

Investegate made some changes which broke it in a way that needed a fairly major rewrite, and then TMF UK stopped their boards so I didn't do any more with it. Recently I've started to resurrect it - I've got the "grab from Investegate" bit working, and done some work on the parsing of eg shareholding forms but since the parsing is still fairly broken it's not really fit to post automatic extracts at the moment. And I've got no code to post automagically on TLF at the moment. So I've just been running the bot to get a list of links and then manually parsing them and posting manually.

So assuming our hosts were amenable to having a bot posting on the boards, then I'd need to write a post-to-TLF module (but that's something I'd want to do anyway) and then if I was given a list of tickers the bot could automatically post just after 7am (or whenever) all the RNS's from those tickers in the previous 24 hours. However at this stage it wouldn't try to extract any of the text, except for the bits that I know work OK like the shareholding announcements. And I'm not sure how much value that would add over people just using Investegate in ticker-list mode or IAAG's Excel version :

https://www.investegate.co.uk/Index.asp ... X,PSN,SBRY

That URL format is limited to about 21 tickers (it's probably 64-characters-excluding-commas or something, it used to be 64 tickers back in the old days) whereas my bot can handle effectively unlimited numbers so that might be a consideration.

But if that would be of use and TLF were OK with it, I'm happy to do that.

Itsallaguess
Lemon Half
Posts: 7783
Joined: November 4th, 2016, 1:16 pm
Has thanked: 3525 times
Been thanked: 7343 times

Re: HYPTUSS - RNS news item harvesting

#499374

Postby Itsallaguess » May 9th, 2022, 7:23 am

88V8 wrote:
Beyond my pay grade, but thankyou....


I'd hate for people to be put off having a play with this if it's something that might interest them, and the process really is quite simple, so to help with that I've created a video that demonstrates the small number of end-to-end steps needed to show this RNS news harvesting process initially working on a fresh copy of the HYPTUSS Excel download file -

Portfolio RNS News harvesting demo video - https://player.vimeo.com/video/707670466

A copy of the latest HYPTUSS Excel download can be found here - http://lemonfoolfinancialsoftware.weebly.com/hyp-top-up.html

If you're interested after watching the above video, then downloading a fresh copy of the vanilla Excel version and having a play on that, and following the small number of steps in the above video should hopefully give you enough confidence to then implement it initially on a COPY of your own HYPTUSS file, and then going from there if you're happy with everything.

(Please note - to access the 'Developer' tab in Excel, if it's not there by default, there's some separate instructions here - https://trumpexcel.com/excel-developer-tab/)

Anyway, I hope the above video link helps for anyone with an interest in this type of stuff.

Cheers,

Itsallaguess

kiloran
Lemon Quarter
Posts: 3551
Joined: November 4th, 2016, 9:24 am
Has thanked: 2545 times
Been thanked: 2127 times

Re: HYPTUSS - RNS news item harvesting

#499527

Postby kiloran » May 9th, 2022, 8:38 pm

I've added IAAG's code for retrieving RNS announcements to a test version of HYPTUSS. You can download it at
http://lemonfoolfinancialsoftware.weebl ... _page.html

All comments on the usefulness and implementation of this potential new feature are welcome.

--kiloran

MDW1954
Lemon Quarter
Posts: 1998
Joined: November 4th, 2016, 8:46 pm
Has thanked: 360 times
Been thanked: 781 times

Re: HYPTUSS - RNS news item harvesting

#499584

Postby MDW1954 » May 10th, 2022, 8:56 am

Hallucigenia wrote:
But if that would be of use and TLF were OK with it, I'm happy to do that.


So do you want me to ask?

MDW1954 (moderator)

MDW1954
Lemon Quarter
Posts: 1998
Joined: November 4th, 2016, 8:46 pm
Has thanked: 360 times
Been thanked: 781 times

Re: HYPTUSS - RNS news item harvesting

#499726

Postby MDW1954 » May 10th, 2022, 9:03 pm

MDW1954 wrote:
Hallucigenia wrote:
But if that would be of use and TLF were OK with it, I'm happy to do that.


So do you want me to ask?

MDW1954 (moderator)


Just to update this for folks, I received a PM from Hallucigenia that in effect said "yes", and consequently submitted the request, adding that I thought it was a useful step forward.

MDW1954

Itsallaguess
Lemon Half
Posts: 7783
Joined: November 4th, 2016, 1:16 pm
Has thanked: 3525 times
Been thanked: 7343 times

Re: HYPTUSS - RNS news item harvesting

#508026

Postby Itsallaguess » June 18th, 2022, 6:53 am

The development Excel HYPTUSS test file containing this new 'Automatic RNS news harvesting' functionality has been updated with some new enhancements -

  • Ability to enter any two-date period of up to two weeks, where portfolio-related RNS news items can be retrieved from
  • User-definable 'Parameters' settings, which can set the RNS news-harvesting process to IGNORE news-items that don't interest the HYP owner (e.g 'Transaction in own shares' etc...)
  • Final report-window showing the number of individual IGNORED news items related to any of the above user-defined parameters

The 'Rev 2' test file with the above new functionality can be downloaded here -

http://lemonfoolfinancialsoftware.weebly.com/hyp_test_page.html

The above Rev 2 Excel HYPTUSS test file has been initially set up with the following small demo HYP portfolio -

Image

The Rev 2 test file has also been given a small initial list of RNS news items to automatically IGNORE via the 'Parameters' sheet -

Image

As an example to demonstrate this new multi-date functionality, if we run it and enter 6th June and 17th June as the RNS period we want to search within, we will get the following portfolio-related RNS results from this new process, reporting on the period including and between those two dates -

Image

For info - the date-windows will initially always contain 'todays date', so anyone wishing to just carry out an RNS news-check 'for today' can simply press the OK button on each window to use the pre-filled date fields.

Finally, the new improved functionality on the Rev 2 test file will end the process by reporting on how many of the user-defined IGNORE news items were seen during the harvesting process -

Image

(Source for all the above images is the Rev 2 HYPTUSS Excel test file linked at the start of this post)

When I initially started to develop this new functionality, I was looking to deliver a number of benefits to the way I capture portfolio-relevant RNS news items -

  • A 'single-click' RNS news harvesting process that was only relevant to the actual HYP portfolio holdings of the user
  • The ability to define a reasonable date-range within which to carry out the portfolio-related RNS news search
  • The ability for the user to define their own IGNORE parameters, to make sure they control just which RNS news items they either get notified about, or can happily ignore

The current Rev 2 Excel test file linked at the start of this post now delivers on all of those initial requirements, and having been using this new process in my own HYPTUSS portfolio file for a number of weeks now, I'm happy that it delivers on all of the above requirements, and so I thought it might be good to encourage some further user-testing and hopefully generate some feedback if anyone wanted to have a play themselves, entering their own HYP into the Rev 2 test file linked above.

Many thanks, as always, to kiloran for his help during these development phases of the above new functionality.

Cheers,

Itsallaguess


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 2 guests