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

[donlincolnmre2]

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.

 

[Alex Blakenburg]

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)

[/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.

 

[donlincolnmre2]

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.