Extract a Single row from first table on the web page by using IMPORTXML function.

donlincolnmre2

Board Regular
Joined
Dec 23, 2006
Messages
142
I’m looking to extract a single row from a table, from the following web site.

AAPL | Apple Inc. Stock Price & News - WSJ

There are several financial tables that are stacked but they end with the word Last Trade

The word "Last Trade" appears several times in different tables but I'M ONLY INTERESTED IN THE VERY FIRST TABLE FROM THE TOP. with this word and once this word is found i'm looking to extract the ROW just above it.

Any help would be greatly appreciated.

Thanks.
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
If you are happy to use Power Query this should work for you.

Steps:
  • Data > Get Data > From Other Sources > Blank query (last item in menu)
    The Power Query window will open up
  • Home > Advanced Editor (3rd from the left)
  • Highlight everything in the editor window and replace it with the code below and hit Done
  • In the right and query pane give the Query a meaningful name without spaces (this will also become the name of the Table it creates in Excel).
  • Home > Close & Load (this will create a new sheet, if you want it on a specific sheet select Close & Load To)

Power Query:
[/INDENT]
let
    Source = Web.Page(Web.Contents("https://www.wsj.com/market-data/quotes/AAPL/options")),
    Data0 = Source{0}[Data],
    #"Added Index" = Table.AddIndexColumn(Data0, "Index", 1, 1, Int64.Type),
    #"Filtered Rows LastTrade" = Table.SelectRows(#"Added Index", each ([Calls Last] = "Last Trade")),
    IndexLastTradeNo = #"Filtered Rows LastTrade"{0}[Index],
    #"Filtered Rows ByIndexNo" = Table.SelectRows(#"Added Index", each ([Index] = IndexLastTradeNo - 1))
in
    #"Filtered Rows ByIndexNo"
[INDENT]

Let me know how you go.
 
Upvote 0
I'm using Excel 2000 and i think that version doesn't have the Query functionality.

So i'm using google sheet to use the IMPORTXML.

Below is the setup that i have but it missing something in the XML link.

Let me know if this can be fixed.

Thanks.
0510210715.JPG
 
Upvote 0

Forum statistics

Threads
1,223,516
Messages
6,172,777
Members
452,477
Latest member
DigDug2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top