Extract data from a web page

How many times have you copied and pasted data from a web page into your Excel workbook? Would you like your Excel workbook to be automatically connected and updated to data from a web page?

With the Get data from web feature you can connect to a web page and extract its data to view it directly in your Excel workbook in an updated form. Below I show you how to do it 👇👇👇

1. In your Excel workbook, click Data > Get Data > From Other Sources > From the Web :

2. Enter the full URL of the website you want to extract data from and click "OK" . In this example, we want to extract historical Apple stock price data from the Yahoo Finance website ( https://finance.yahoo.com/quote/AAPL/history?p=AAPL ):

3. From the drop-down list, select the last URL in the list , which is the most complete URL, and click "Connect" :

4. A window will open showing you the data tables available from the website. Click on each one until you find the data table you need . In our case, our data is located in the table called "Table 0", as seen below. On the right side of the screen you will see a preview of the data contained in each table. When you find the data you need, click "Load" :

5. Done! Your data will automatically be loaded into a new spreadsheet within your Excel workbook, as you can see in the example below:

6. To refresh the data manually, click Query > Refresh :

7. To automatically refresh the data, click Query > Properties > Check the "Refresh every 60 minutes" option > OK . You can choose how how often your data table will be updated:

Use this feature to automate loading data from your Excel workbooks. Please note that not all websites are optimized to retrieve data from them, so you may not always be able to do this.

Learn more about Excel in the "Excel for Work Course" on Udemy !