how to fetch the dividend % on several stock exchanges (formula works but not for all)

Question

The formula below works, but not always. It looks to be dependent of the stock exchange where it is traded on.

=IMPORTXML(CONCATENATE("https://finance.yahoo.com/quote/", index(A2)),"/html/body/div[1]/div/div/div[1]/div/div[3]/div[1]/div/div[1]/div/div/div/div[2]/div[2]/table/tbody/tr[6]/td[1]")

Can someone help me with the formula of ticker CPINV.BR? In Yahoo Finance, it can be found here. I want to obtain the 'Forward Dividend & Yield' of 7.17%. How to I do this?

FYI, below an example of a Google Sheets where the formula above works for some tickers and not for others. https://docs.google.com/spreadsheets/d/1VUgq63x3n5UxvWUiyuW7sG8iM6dZ7i3tZSLN3Tfq2AI/edit?usp=sharing

Can also someone link the description on how the table in Yahoo are built up? This is so useful since I want to be able to filter on several parameters, but can't determine the 'table and row' logic on Yahoo Finance.

Answer 1

Here is code using Powershell with HTMLAgilityPack. I used Descendants

#needed to upgrade from PS 5 to PS 7
Add-Type -path "$env:UserProfile\Desktop\PowerShell\htmlagilitypack\lib\NetCore45\htmlagilitypack.dll"

$URI = 'https://finance.yahoo.com/quote/CPINV.BR?p=CPINV.BR&.tsrc=fin-srch'
<#
.SYNOPSIS
    Takes an HTML input and converts it to an HTMLAgilityPack htmlNode object that can be navigated using Linq
.DESCRIPTION
    Long description
.EXAMPLE
    PS C:\> <example usage>
    Explanation of what the example does
.INPUTS
    [String[]]
    [System.IO.FileInfo[]]
.OUTPUTS
    [HtmlAgilityPack.HtmlDocument]
    [HtmlAgilityPack.HtmlNode]
.NOTES
    General notes
#>
function ConvertFrom-Html {
    [CmdletBinding(DefaultParameterSetName="String")]
    param (
        #The HTML text to parse. Accepts multiple separate documents as an array. This also accepts pipeline from Invoke-WebRequest
        [Parameter(ParameterSetName="String",Mandatory,ValueFromPipeline,ValueFromPipelineByPropertyName,Position=0)]
        [String[]]$Content,

        #The URI or URIs from which to retrieve content. This may be faster than using Invoke-WebRequest but is less flexible in the method of retrieval (for instance, no POST)
        [Parameter(ParameterSetName="URI",Mandatory,ValueFromPipeline,ValueFromPipelineByPropertyName)]
        [System.URI[]]$URI,

        #Path to file or files containing HTML content to convert. This accepts pipeline from Get-Childitem or Get-Item
        [Parameter(ParameterSetName="Path",Mandatory,ValueFromPipeline,ValueFromPipelineByPropertyName)]
        [System.IO.FileInfo[]]$Path,

        #Do not return the Linq documentnode, instead return the HTMLDocument object. This is useful if you want to do XPath queries instead of Linq queries
        [switch]$Raw

    )

    begin {
    }

    process {
        #Find the type of input and bind it to inputObject
        $inputObject = $null
        foreach ($contentType in "Content","URI","Path") {
            if ((Get-Variable -erroraction SilentlyContinue $contentType).value) {
                $inputObject = (Get-Variable $contentType).value
                break
            }
        }
        if (-not $inputObject) {write-error "Input Object Type Not Identified. If you see this then ConvertFrom-HTML needs better input validation"}

        #Unwrap any arrays. This allows us to accept both pipeline and parameter input
        $inputObject | ForEach-Object {
            $inputItem = $PSItem
            $htmlDoc = new-object HtmlAgilityPack.HtmlDocument

            #Process all object types into a common HTML document format
            switch ($inputItem.GetType().FullName) {
                "System.String" {
                    $htmlDoc.LoadHtml($inputItem)
                }
                "System.Uri" {
                    $htmlDoc = (new-object HtmlAgilityPack.HtmlWeb).Load($inputItem)
                }
                "System.IO.FileInfo" {
                    $htmlDoc.Load($inputItem)
                }
                Default {
                    write-error "Object Type not supported or implemented. If you see this error then ConvertFrom-HTML has improper input validation"
                    continue
                }
            }
            if ($inputItem) {
                if ($Raw) {
                    $htmlDoc
                } else {
                    $htmlDoc.DocumentNode
                }
            }
        }

    }
}
$wc = New-Object System.Net.WebClient
$res = $wc.DownloadString($URI)
$html = ConvertFrom-Html -Content $res
$tbody = $html.Descendants('tbody')

Look like there is two Results

Attributes           : {}
ChildNodes           : {tr, tr, tr, tr...}
Closed               : True
ClosingAttributes    : {}
EndNode              : HtmlAgilityPack.HtmlNode
FirstChild           : HtmlAgilityPack.HtmlNode
HasAttributes        : False
HasChildNodes        : True
HasClosingAttributes : False
Id                   :
InnerHtml            : <tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td class="C($primaryColor)
                       W(51%)"><span>Previous Close</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="PREV_CLOSE-value">13.50</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Open</span></td><td
                       class="Ta(end) Fw(600) Lh(14px)" data-test="OPEN-value">13.50</td></tr><tr class="Bxz(bb)
                       Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td class="C($primaryColor)
                       W(51%)"><span>Bid</span></td><td class="Ta(end) Fw(600) Lh(14px)" data-test="BID-value">0.00 x
                       0</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td
                       class="C($primaryColor) W(51%)"><span>Ask</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="ASK-value">0.00 x 0</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Day&#x27;s
                       Range</span></td><td class="Ta(end) Fw(600) Lh(14px)" data-test="DAYS_RANGE-value">13.30 -
                       13.68</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td
                       class="C($primaryColor) W(51%)"><span>52 Week Range</span></td><td class="Ta(end) Fw(600)
                       Lh(14px)" data-test="FIFTY_TWO_WK_RANGE-value">10.70 - 16.52</td></tr><tr class="Bxz(bb)
                       Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td class="C($primaryColor)
                       W(51%)"><span>Volume</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="TD_VOLUME-value"><fin-streamer data-symbol="CPINV.BR"
                       data-field="regularMarketVolume" data-trend="none" data-pricehint="2" data-dfield="longFmt"
                       value="36,813" active="">36,813</fin-streamer></td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) Bdbw(0)! "><td class="C($primaryColor) W(51%)"><span>Avg.
                       Volume</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="AVERAGE_VOLUME_3MONTH-value">35,029</td></tr>
InnerText            : Previous Close13.50Open13.50Bid0.00 x 0Ask0.00 x 0Day&#x27;s Range13.30 - 13.6852 Week
                       Range10.70 - 16.52Volume36,813Avg. Volume35,029
LastChild            : HtmlAgilityPack.HtmlNode
Line                 : 26
LinePosition         : 9697
InnerStartIndex      : 100941
OuterStartIndex      : 100934
InnerLength          : 1894
OuterLength          : 1909
Name                 : tbody
NextSibling          :
NodeType             : Element
OriginalName         : tbody
OuterHtml            : <tbody><tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td
                       class="C($primaryColor) W(51%)"><span>Previous Close</span></td><td class="Ta(end) Fw(600)
                       Lh(14px)" data-test="PREV_CLOSE-value">13.50</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Open</span></td><td
                       class="Ta(end) Fw(600) Lh(14px)" data-test="OPEN-value">13.50</td></tr><tr class="Bxz(bb)
                       Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td class="C($primaryColor)
                       W(51%)"><span>Bid</span></td><td class="Ta(end) Fw(600) Lh(14px)" data-test="BID-value">0.00 x
                       0</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td
                       class="C($primaryColor) W(51%)"><span>Ask</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="ASK-value">0.00 x 0</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Day&#x27;s
                       Range</span></td><td class="Ta(end) Fw(600) Lh(14px)" data-test="DAYS_RANGE-value">13.30 -
                       13.68</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td
                       class="C($primaryColor) W(51%)"><span>52 Week Range</span></td><td class="Ta(end) Fw(600)
                       Lh(14px)" data-test="FIFTY_TWO_WK_RANGE-value">10.70 - 16.52</td></tr><tr class="Bxz(bb)
                       Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td class="C($primaryColor)
                       W(51%)"><span>Volume</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="TD_VOLUME-value"><fin-streamer data-symbol="CPINV.BR"
                       data-field="regularMarketVolume" data-trend="none" data-pricehint="2" data-dfield="longFmt"
                       value="36,813" active="">36,813</fin-streamer></td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) Bdbw(0)! "><td class="C($primaryColor) W(51%)"><span>Avg.
                       Volume</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="AVERAGE_VOLUME_3MONTH-value">35,029</td></tr></tbody>
OwnerDocument        : HtmlAgilityPack.HtmlDocument
ParentNode           : HtmlAgilityPack.HtmlNode
PreviousSibling      :
StreamPosition       : 100934
XPath                : /html[1]/body[1]/div[1]/div[1]/div[1]/div[1]/div[1]/div[3]/div[1]/div[1]/div[1]/div[1]/div[1]/di
                       v[1]/div[2]/div[1]/table[1]/tbody[1]
Depth                : 0

Attributes           : {}
ChildNodes           : {tr, tr, tr, tr...}
Closed               : True
ClosingAttributes    : {}
EndNode              : HtmlAgilityPack.HtmlNode
FirstChild           : HtmlAgilityPack.HtmlNode
HasAttributes        : False
HasChildNodes        : True
HasClosingAttributes : False
Id                   :
InnerHtml            : <tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td class="C($primaryColor)
                       W(51%)"><span>Market Cap</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="MARKET_CAP-value">499.35M</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Beta (5Y
                       Monthly)</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="BETA_5Y-value">0.22</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>PE Ratio
                       (TTM)</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="PE_RATIO-value">23.17</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>EPS
                       (TTM)</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="EPS_RATIO-value">0.58</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Earnings
                       Date</span></td><td class="Ta(end) Fw(600) Lh(14px)" data-test="EARNINGS_DATE-value"><span>Mar
                       06, 2024</span> - <span>Mar 11, 2024</span></td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Forward Dividend &amp;
                       Yield</span></td><td class="Ta(end) Fw(600) Lh(14px)" data-test="DIVIDEND_AND_YIELD-value">0.94
                       (6.96%)</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td
                       class="C($primaryColor) W(51%)"><span>Ex-Dividend Date</span></td><td class="Ta(end) Fw(600)
                       Lh(14px)" data-test="EX_DIVIDEND_DATE-value"><span>May 31, 2023</span></td></tr><tr
                       class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) Bdbw(0)! "><td
                       class="C($primaryColor) W(51%)"><span>1y Target Est</span></td><td class="Ta(end) Fw(600)
                       Lh(14px)" data-test="ONE_YEAR_TARGET_PRICE-value">15.20</td></tr>
InnerText            : Market Cap499.35MBeta (5Y Monthly)0.22PE Ratio (TTM)23.17EPS (TTM)0.58Earnings DateMar 06, 2024
                       - Mar 11, 2024Forward Dividend &amp; Yield0.94 (6.96%)Ex-Dividend DateMay 31, 20231y Target
                       Est15.20
LastChild            : HtmlAgilityPack.HtmlNode
Line                 : 26
LinePosition         : 11868
InnerStartIndex      : 103112
OuterStartIndex      : 103105
InnerLength          : 1850
OuterLength          : 1865
Name                 : tbody
NextSibling          :
NodeType             : Element
OriginalName         : tbody
OuterHtml            : <tbody><tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td
                       class="C($primaryColor) W(51%)"><span>Market Cap</span></td><td class="Ta(end) Fw(600)
                       Lh(14px)" data-test="MARKET_CAP-value">499.35M</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Beta (5Y
                       Monthly)</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="BETA_5Y-value">0.22</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>PE Ratio
                       (TTM)</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="PE_RATIO-value">23.17</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>EPS
                       (TTM)</span></td><td class="Ta(end) Fw(600) Lh(14px)"
                       data-test="EPS_RATIO-value">0.58</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Earnings
                       Date</span></td><td class="Ta(end) Fw(600) Lh(14px)" data-test="EARNINGS_DATE-value"><span>Mar
                       06, 2024</span> - <span>Mar 11, 2024</span></td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s)
                       Bdc($seperatorColor) H(36px) "><td class="C($primaryColor) W(51%)"><span>Forward Dividend &amp;
                       Yield</span></td><td class="Ta(end) Fw(600) Lh(14px)" data-test="DIVIDEND_AND_YIELD-value">0.94
                       (6.96%)</td></tr><tr class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) "><td
                       class="C($primaryColor) W(51%)"><span>Ex-Dividend Date</span></td><td class="Ta(end) Fw(600)
                       Lh(14px)" data-test="EX_DIVIDEND_DATE-value"><span>May 31, 2023</span></td></tr><tr
                       class="Bxz(bb) Bdbw(1px) Bdbs(s) Bdc($seperatorColor) H(36px) Bdbw(0)! "><td
                       class="C($primaryColor) W(51%)"><span>1y Target Est</span></td><td class="Ta(end) Fw(600)
                       Lh(14px)" data-test="ONE_YEAR_TARGET_PRICE-value">15.20</td></tr></tbody>
OwnerDocument        : HtmlAgilityPack.HtmlDocument
ParentNode           : HtmlAgilityPack.HtmlNode
PreviousSibling      :
StreamPosition       : 103105
XPath                : /html[1]/body[1]/div[1]/div[1]/div[1]/div[1]/div[1]/div[3]/div[1]/div[1]/div[1]/div[1]/div[1]/di
                       v[1]/div[2]/div[2]/table[1]/tbody[1]
Depth                : 0