CODEWITHSUNDEEP
xmlgoogle-sheetsgoogle-sheets-formulafinanceyahoo-finance
Ryuu
Ryuu

Reputation: 1

How can I scrape data from yahoo finance to a google sheet using importhtml?

I was trying to create a google sheet for some stock analysis.

The webpage that I am scraping is: https://finance.yahoo.com/quote/FVRR/financials/

Now I know we can use importhtml to scrape data. I used a similar function to extract data from google finance before using the formula:

=let(Σ,importhtml("https://www.google.com/finance/quote/FVRR:NYSE","table",1), filter(index(Σ,,2),left(index(Σ,,1),10)="Net income"))

But doing it with Yahoo, I am unable to understand what the right parameters would be for specifying the table, column and row.

An add on in this case would be how would I extract data quarterly instead of annually. (I want to do both). Just the url takes us to the page that displays the data annually. How can I click on quarterly and extract data from that?

Any insight will be deeply appreciated.

I tried changing the parameters, changing the table numbers from 1,2,3 etc. But it wasn't working. I also couldn't figure out how to switch to quarterly and extract data from there.

Upvotes: 0

Views: 342

Answers (1)

jdweng
jdweng

Reputation: 34421

Using a Powershell Script

$uri = 'https://finance.yahoo.com/quote/FVRR/financials/'
$source = Invoke-WebRequest -uri $uri
$tableHeader =$source.ParsedHtml.body.getElementsByClassName('tableHeader')[0]
$headers = $tableHeader.getElementsByClassName('column') | foreach( {$_.InnerText})
$tableBody = $source.ParsedHtml.body.getElementsByClassName('tableBody')
$table = [System.Collections.ArrayList]::new()
foreach($row in $tableBody[0].ChildNodes)
{
   $newRow = New-Object -TypeName psobject

   $title = $row.getElementsByClassName('rowTitle')[0].title
   $newRow | Add-Member -NotePropertyName Breakdown -NotePropertyValue $title

   $columns = $row.getElementsByClassName('column') | foreach( {$_.InnerText})
   for($i = 1; $i -lt $headers.Count; $i++)
   {
      $newRow | Add-Member -NotePropertyName $headers[$i].Trim() -NotePropertyValue $columns[$i]
   }

   $table.Add($newRow) | out-null
}
$table | Format-Table

Results

Breakdown                                                  TTM         12/31/2023  12/31/2022  12/31/2021  12/31/2020  12/31/2019
---------                                                  ---         ----------  ----------  ----------  ----------  ----------
Total Revenue                                              366,943.00  361,375.00  337,366.00  297,662.00  189,510.00  --
Cost of Revenue                                            61,628.00   61,846.00   65,948.00   51,723.00   33,188.00   --
Gross Profit                                               305,315.00  299,529.00  271,418.00  245,939.00  156,322.00  --
Operating Expense                                          317,438.00  314,638.00  318,323.00  291,279.00  168,132.00  --
Operating Income                                           -12,123.00  -15,109.00  -46,905.00  -45,340.00  -11,810.00  --
Net Non Operating Interest Income Expense                  24,004.00   20,427.00   3,358.00    -18,240.00  -2,538.00   --
Other Income Expense                                       --          -264.00     -27,363.00  -1,273.00   -262.00     --
Pretax Income                                              11,617.00   5,054.00    -70,910.00  -64,853.00  -14,610.00  --
Tax Provision                                              2,876.00    1,373.00    577.00      159.00      200.00      --
Net Income Common Stockholders                             8,741.00    3,681.00    -71,487.00  -65,012.00  -14,810.00  --
Diluted NI Available to Com Stockholders                   8,741.00    3,681.00    -71,487.00  -65,012.00  -14,810.00  --
Basic EPS                                                  0.23        0.10        -1.94       -1.81       -0.46       --
Diluted EPS                                                0.22        0.09        -1.94       -1.81       -0.46       --
Basic Average Shares                                       38,332.32   38,066.20   36,856.14   35,955.01   32,323.64   --
Diluted Average Shares                                     39,629.37   39,151.05   36,856.14   35,955.01   32,323.64   --
Total Operating Income as Reported                         -12,123.00  -15,109.00  -74,534.00  -45,340.00  -11,810.00  --
Total Expenses                                             379,066.00  376,484.00  384,271.00  343,002.00  201,320.00  --
Net Income from Continuing & Discontinued Operation        8,741.00    3,681.00    -71,487.00  -65,012.00  -14,810.00  --
Normalized Income                                          8,939.64    3,873.28    -49,870.23  -64,082.71  -14,618.74  --
Interest Income                                            26,730.00   23,153.00   12,866.00   9,826.00    1,867.00    --
Interest Expense                                           --          2,541.00    8,912.00    27,932.00   4,036.00    --
Net Interest Income                                        24,004.00   20,427.00   3,358.00    -18,240.00  -2,538.00   --
EBIT                                                       10,581.00   7,595.00    -61,998.00  -36,921.00  -10,574.00  --
EBITDA                                                     15,993.00   13,582.00   -51,813.00  -30,045.00  -6,236.00   --
Reconciled Cost of Revenue                                 61,628.00   61,846.00   65,948.00   51,723.00   33,188.00   --
Reconciled Depreciation                                    5,412.00    5,987.00    10,185.00   6,876.00    4,338.00    --
Net Income from Continuing Operation Net Minority Interest 8,741.00    3,681.00    -71,487.00  -65,012.00  -14,810.00  --
Total Unusual Items Excluding Goodwill                     -264.00     -264.00     -27,363.00  -1,273.00   -262.00     --
Total Unusual Items                                        -264.00     -264.00     -27,363.00  -1,273.00   -262.00     --
Normalized EBITDA                                          16,257.00   13,846.00   -24,450.00  -28,772.00  -5,974.00   --
Tax Rate for Calcs                                         0.00        0.00        0.00        0.00        0.00        --
Tax Effect of Unusual Items                                -65.36      -71.72      -5,746.23   -343.71     -70.74      --

Upvotes: 0

Related Questions