How To Import A Table From A Website Using Power Query

2016-12-02

In this post we’re going to explore how to pull data from a table on a webpage into Excel. In our example we are going to pull stock price data from Google Finance for a given company. As this type of data is updated frequently (ie the market value of a stock changes almost continuously), we would want any importing solution to be able to refresh our data easily with the push of a button. Power Query will allow us to do just that once we set up our query.

For our example we’re going to pull the historical share prices for Apple Inc. These can be found here:

https://www.google.com/finance/historical?q=NASDAQ%3AAAPL

step-001-how-to-import-a-table-from-a-website-using-power-query

We are going to import this entire table into Excel using Power Query. Power Query makes this type of data grabbing very easy, quick and painless.

step-002-how-to-import-a-table-from-a-website-using-power-query

To import the data:

  1. Go to the Power Query tab.
  2. Then in the Get External Data section click the From Web.
  3. Enter the URL which you’re trying to import your table from.
  4. Click the OK button.

step-003-how-to-import-a-table-from-a-website-using-power-query

In the Navigator window:

  1. Select your table.
  2. Select the Table View tab.
  3. Check the data in the pane below is what you’re trying to import.
  4. Click the Load button.

step-004-how-to-import-a-table-from-a-website-using-power-query

Now the data will appear in your sheet. If you need to refresh the data all you need to do is go to the Data tab and hit the Refresh button.

  1. Your data.
  2. To refresh the data, go to the Data tab.
  3. In the Connections area, click the Refresh button.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.

Subscribe

Advertisement

Related Articles

Comments

5 Comments

  1. Parham Nasseri

    Thank you

  2. Chris

    In your example above, is it possible to change the last section of the URL “=NASDAQ%3AAAPL”, with data from a particular cell in the Workbook? That way you can easily change the search term in the cell only and it will automatically update the URL and imported data?
    Thank you

Get The Latest News

Follow Us

Follow us on social media to stay up to date with the latest tips in Excel!

Pin It on Pinterest

Share This