How to scrape data from a website into Excel
By
SpreadCheaters
Manually collecting market data from various websites can be a time-consuming task. Web scraping automates this process, enabling you to retrieve data quickly and efficiently. You can set up a data acquisition system through Excel which will save you valuable time and effort that can be directed toward analyzing the data and making informed investment choices.
In this tutorial, we will showcase the process of extracting stock market and major world indices data from an external web source through a practical example. It is very important to note that the data you want to scrape must be in the form of tables that Excel can detect.
Web scraping allows you to monitor and track changes on websites over time. This can be particularly useful for price monitoring, stock tracking, or tracking news updates. Businesses can automate the scraping process to gather real-time data and stay updated on relevant information.
Step 1 – Open the “From Web” dialogue box
– Open Microsoft Excel on your computer.
– Once Excel is open, navigate to the “Data” tab located at the top of the Excel window.
– Within the Data tab, you’ll find several options. Look for the “Get External Data” group of commands.
– Within the “Get and Transform Data” group, click on the “From Web” command button.
– This will open the “From Web” dialog box.
– Alternatively, you can use the shortcut key “Alt+D” and then press “G” to directly access the “From Web” dialog box.
Step 2 – Enter the Uniform Resource Locator (URL)
– In the “From Web” dialogue box, you’ll see two options: Basic and Advanced. We are going to choose the Basic option here.
Basic option: It includes entering the URL of the web page, importing the data, navigating through pages, previewing the selected data, and loading it into Excel.
Advanced option: It provides more control, such as selecting specific tables or elements to import, choosing columns and rows, setting refresh options, specifying parameters, and entering authentication credentials if required.
– After selecting the Basic option, enter the website URL from which you want to scrape data.
– Then, let Excel establish a connection with the website.
– After the connection is established, a “Navigator” dialogue box would appear on your screen.
Step 3 – Select the table to import
– Under display options, you’ll see an option named “Tables”.
– Click on this option and it will open the dataset that we wish to import in Excel.
– If you have more than one table then select the table that you want to import.
Step 4 – Loading the Data into the Sheet
– After selecting the desired table, click on the “Load” button at the bottom of the dialogue box.
– The data from the Website will be loaded into Excel in the form of a Table.
