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:
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.
To import the data:
- Go to the Power Query tab.
- Then in the Get External Data section click the From Web.
- Enter the URL which you’re trying to import your table from.
- Click the OK button.
In the Navigator window:
- Select your table.
- Select the Table View tab.
- Check the data in the pane below is what you’re trying to import.
- Click the Load button.
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.
- Your data.
- To refresh the data, go to the Data tab.
- In the Connections area, click the Refresh button.