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: 7528
Joined: November 4th, 2016, 1:16 pm
Has thanked: 3379 times
Been thanked: 7004 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: 3305
Joined: November 4th, 2016, 11:22 am
Has thanked: 1278 times
Been thanked: 1272 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: 4021
Joined: November 4th, 2016, 2:24 pm
Has thanked: 3375 times
Been thanked: 1419 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: 7528
Joined: November 4th, 2016, 1:16 pm
Has thanked: 3379 times
Been thanked: 7004 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: 7528
Joined: November 4th, 2016, 1:16 pm
Has thanked: 3379 times
Been thanked: 7004 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: 1107
Joined: November 5th, 2016, 3:03 am
Has thanked: 81 times
Been thanked: 600 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: 7528
Joined: November 4th, 2016, 1:16 pm
Has thanked: 3379 times
Been thanked: 7004 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: 3483
Joined: November 4th, 2016, 9:24 am
Has thanked: 2463 times
Been thanked: 2057 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: 1935
Joined: November 4th, 2016, 8:46 pm
Has thanked: 340 times
Been thanked: 719 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: 1935
Joined: November 4th, 2016, 8:46 pm
Has thanked: 340 times
Been thanked: 719 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


Return to “Financial Software - Discussion”

Who is online

Users browsing this forum: No registered users and 2 guests