How to extract from this site?

[Craigja]

Hi,

Im struggling to extract the data from the following website: https://www.takeover.ch/transactions/current

All I want is the 2 highlighted items in the screenshot below, basically if something appears in the offer section I want to pull out the offeror and offeree.  The current workflow I have accesses a number of similer sites but they all seem to have HTML tables, I dont think this site is an HTML table.

[Craigja]

I have this so far:

which creates textlist2,

text list 2 has:

But then what?  I think I need loop round each item, check if its blank?

[Karlis]

Hi Craig

I am not sure if this is the best practice but I would suggest playing around with the some of the UI automation actions. I managed to get the two values by the following process:

In order to set this up, I set an If function to find an "Offer" image (set-up in the images panel) and then looking for the name details of an UI element (set under the UI element panel):

You have to specify which details of the UI element you want to extract by going into "Edit selector"

The final selector builder would look something like this:

For the Title:

> document[Class="Chrome_RenderWidgetHostHWND"] > group:eq(2) > document[Id="transaction876"] > hyperlink[Name@=""] > text[Name@=""]

For the Offeror:

 > document[Class="Chrome_RenderWidgetHostHWND"] > group:eq(2) > document[Id="transaction876"] > text:eq(3)

[Craigja]

and would the above work if the offeror and offeree were different?  There may also be more than 1 record so I need to loop.

Ive not used the image before, I just have it in a table

[Karlis]

No, this would not work for the loop. I was trying to find a UI element that would list all the documents on this page but I couldn't quite see how. 

[Craigja]

yep, thats my problem - I can grab the text but cant figure out how to only extract what I need from it!

[Craigja]

So I can get to this stage with a table, but no idea how I can get out of the table only what Im interested in which is:

Cassiopea S.p.A. & Cosmo Pharmaceuticals N.V. Irland

Would welcome any other ideas as Im pretty much stumped at the moment!

[MichaelAnnis]

If the information you want will always be in the 3rd (#2) and 4th (#3) row, then you write:

Set Variable %Offeree% to %TextList2[2]%

Replace Text " (2021)" in %Offeree% to %Offeree%

Set Variable %Offeror% to %TextList2[3]%

Replace Text "Offeror: " in %Offeror% to %Offeror%

[Craigja]

Not quite that simple either - when you look at the webpage, I want the Offer section, and this could be one or more 'rows' of data.    So I think I need to try and turn the single column TextList2 into multiple columns, pivot it somehow....  if the row is pure numbers that signifies a new record:

Ignore row 1, easy to do, row text is not 'Offer'

row 2 is numbers only so a new 'record'

0796

Cassiopea S.p.A. (2021)

Offeror: Cosmo Pharmaceuticals N.V. Irland

Voluntry Offer

Offer initially friendly

Exchange offer

Then I would 'end' the table when the next record says 'Obligation to make an offer (existence/exemptions)'

If I could get to this, it would just be a case of taking the data in Columns 2 & 3 for every record in the table

[MichaelAnnis]

I have a workaround...not sure if you are going to like it.  Some of the syntax is missing below, but the logic will work.

Put %TextList2% into Excel, and write yourself a VBA that looks through this:

• Dim i As Long #1st row number of current section
• Dim s As Long #section number
• i = 1
• s = 1
• Do
  
  • #If Range("A:" & i).value is between 0000 and 9999 Then
    • #write Range("A:" & i +1) to Sheet2 Range ("A" & s) 'this gets the offeree
    • #Replace text " (2021) on Sheet2 Range ("A" & s) 'this removes the year from the offeree
    • #write Range("A:" & i + 2) to Sheet2 Range ("B" & s) 'this gets the offeror
    • #Replace text "Offeror: " on Sheet2 Range ("B" & s) 'this removes unneeded text from the offeror
    • s = s + 1 'declare a new section so the next offer goes into the next row on Sheet2
  • ExitIF
  • If Range("A:" & i).value = "Obligation to make an offer (existence/exemptions)"
    • Exit Loop
  • ExitIF
• i = i+1 'next row
• Loop Until 1=2 'we only want the loop to end on the 2nd IF, so we make something impossible here.

If you write this VBA, you will get all the information you are looking for listed out in a table on Sheet2.

Best of Luck!

[Craigja]

I was suspecting this coudnt be done in PAD and something like an excel macro was needed!  Ive never written any VBA and have no idea how to even start!  I think Im gonna need to call in the contract company that did the rest of the flow and see if they can help!

[MichaelAnnis]

Here you go:

Sub Macro1()
'
' Macro1 Macro

Dim i As Long '1st row number of current section
Dim s As Long 'section number
i = 1
s = 1
Do
Sheets("Sheet1").Activate
If Range("A" & i).Value >= 0 And Range("A" & i).Value <= 9999 Then
'copy the offeree to sheet2
Range("A" & i + 1).Select
Selection.Copy
Sheets("Sheet2").Select
Range("A" & s).Select
ActiveSheet.Paste
'copy the offeror to sheet2
Sheets("Sheet1").Select
Range("A" & i + 2).Select
Selection.Copy
Sheets("Sheet2").Select
Range("B" & s).Select
ActiveSheet.Paste
s = s + 1 'declare a new section so the next offer goes into the next row on Sheet2
End If
If Range("A" & i).Value = "Obligation to make an offer (existence/exemptions)" Then
Exit Do 'we stop looking for offers once we see this
End If
i = i + 1 'next row
Loop Until 1 = 2 'we only want the loop to end on the 2nd IF, so we make something impossible here.

'replace text on all column A and B of Sheet2
Sheets("Sheet2").Select
Columns("A:A").Select
Selection.Replace What:=" (2021)", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Columns("B:B").Select
Selection.Replace What:="Offeror: ", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Columns.AutoFit

End Sub

[MichaelAnnis]

Copy the Macro text, open a new Excel, View -> Macros; type in what you want to call it in the top bar, and click create.  Paste Macro text in there.  Delete out "Sub Macro1() from the top and "End" from the very bottom.  

Save this as a .xlsm.

In PAD, you will get %TextList2% to your clipboard text, use PAD to open the MACRO workbook, File -> New

Attach Excel Instance to New workbook, Paste clipboard text to A1, then run the Macro.  The information you want will be on Sheet2.

[Craigja]

Ah, ok can give that a go

[Anonymous]

Hello @Craigja 

I'm still in the middle, but I can now extract three pieces of info: numbers, headings, and paragraphs, from each item displayed on the page.

My flow:

Parameters for action "Extract data from web page":

Advanced settings for action "Extract data from web page":

The point here is to set extract as "Table".

Also, input "Base CSS selector" as below:

html > body > div:eq(0) > div > main > section:eq(1) > h2:contains('Offer') ~ article > div

3 CSS Selectors:

span:eq(0)

span:eq(1) > a > h3

span:eq(1) > p

As a result, I have a Datatable as below:

The hardest part is how to select only the items next to the heading "Offer". Currently, all items in the page are being extracted.

Because of <h2> tags and <article> tags are siblings instead of parent-child relationships, which makes this challenge difficult... 🤔

Thank you.