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
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.
When I do this with the firms listed below, my query results shows list.
http://www.osfi-bsif.gc.ca/Eng/wt-ow/Pages/wwr-er.aspx?sc=1&gc=1&ic=1#WWRLink111
Can you please help
It looks like the HTML in the source contains an unordered list with list elements. Power Query only works well with table HTML. You would be better off copying and pasting this data into Excel and then using something like this to get the data into a proper table https://www.howtoexcel.org/power-query/transform-a-column-to-a-table-with-power-query/.
Thank you
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
Yes, it’s definitely possible. Check out the Manage Parameters command in the Home tab of the Power Query Editor.
You could also reference a named range https://www.howtoexcel.org/power-query/how-to-reference-a-named-range-in-power-query/.
Or create a table of parameters https://www.howtoexcel.org/power-query/how-to-parameterize-your-power-query/.