CODEWITHSUNDEEP
excelimportweb-scrapingvba
Facundo
Facundo

Reputation: 739

Download a Table with Excel

I want to download the table that is in this page about Libor rates:

http://online.wsj.com/mdc/public/page/2_3020-libor.html (Specificaly, the one that its about USD)

By using data/import from web, if I select the table once I press import it does not appear the table, but that one appears if I import the whole page

Is there a way, using VBA may be, where I can obtain that table?

Upvotes: 0

Views: 709

Answers (1)

teylyn
teylyn

Reputation: 35915

As an alternative to VBA, you can download and install the free Microsoft Add-In for Excel called Power Query. With Power Query installed, the steps are:

  • Click the Power Query Ribbon
  • Click the "From Web" icon
  • paste the URL of the site
  • On the side bar, select Table0 and edit the query
  • You will see some transformation steps already applied. Add the following transformations:
  • Remove top rows (just 1 row)
  • Set the first row as header
  • Remove bottom rows (27 rows)
  • click "Close and Load" to write the transformed data to a table in the Excel file.

Now you only need to refresh the data connections when the web site has new numbers.

The screenshot has the Excel file with the Power Query ribbon and the result table in the background. The window on top is the Power Query interface. The transformation steps are listed in the panel on the right.

enter image description here

Each transformation step is recorded in a language called "M". In Power Query you can click View > Advanced Editor to view and change the M code. It is a very powerful language with a syntax that is quite different from Excel formulas or VBA. I'm just mentioning it here to make you aware that the transformations can be customized beyond clicking icons on the ribbon. The generated code is:

let
    Source = Web.Page(Web.Contents("http://online.wsj.com/mdc/public/page/2_3020-libor.html")),
    Data0 = Source{0}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Data0,{{"Column1", type text}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}}),
    #"Removed Top Rows" = Table.Skip(#"Changed Type",1),
    #"First Row as Header" = Table.PromoteHeaders(#"Removed Top Rows"),
    #"Removed Bottom Rows" = Table.RemoveLastN(#"First Row as Header",27)
in
    #"Removed Bottom Rows"

Upvotes: 1

Related Questions