Extracting data from a website to your system gets boring and difficult when you do it line by line. If you don’t know how to automatically extract data from a website to your datasheet, then a data analyst job might frustrate you. If you use an excel sheet to save the data you get from different websites over the internet, you can collect data faster and more conveniently.
Yes, now you can do automatic data collection. The data from the website will get extracted, and then you can save it in an excel sheet. For example, if you work for a financial analyst company, your job description would be to get daily stock prices from different websites in your excel workbook.
So extracting the stock prices automatically to your excel workbook gets easier when it happens automatically. There will be many jobs where you will have to extract data into your excel sheet, but you can do it more swiftly.
Steps on extracting data automatically from a website to your excel sheet
Only excel will allow you to extract data automatically from the web pages. There is one thing you need to pay attention that the data you want to extract from a website has to be in the format of pre-data format or tables. If the data is not in one of these formats, you have to win another battle to convert the data into one. After that, you will be able to save it in your excel sheet.
Extracting data automatically from Google finance pages
So, let’s assume you don’t have to fight that battle right now, and you have found a website where the data is in one of these formats, and now you have to extract it automatically to your excel sheet. Let’s dive into automatically extracting data from a website to your excel sheet. To collect data from the web, you will have to use excel from the web command in the data ribbon.
- First, you need to open your excel sheet where you would like to save the automatically extracted data from one of the google finance pages. Open the data ribbon and select the web command on the top left corner of the excel sheet.
- A new web query box will appear on your screen, and you will have to paste the link of the Google finance page in the dialog box and click on the go.
- The web page will appear inside the dialog box and then spot the yellow arrow in the query box
- Now, it is time to select the data you want to extract over the yellow arrows. The data will get highlighted, and the yellow arrows will become green.
- Once your data is selected, now it is time to import it from the web page to your excel sheet. When you click on the import button, a new dialog box will appear, and the system will ask you for the location of the data. You will have to select a cell in the excel sheet and save it.
- Once the data starts getting extracted from the web page to your excel workbook, you might get a cryptic message, but you need to hit OK. Don’t worry, aliens have not attacked your system. It is just a simple message that your excel sheet is populating.
- Once the importing process gets completed, you will see the data in your excel sheet.
You can update your excel sheet data.
If you want to update data in your excel sheet, you can do that automatically and manually. It won’t take long. Here are the steps for updating your excel sheet data:
- Click on the data ribbon, and from the drop-down menu, select refresh all.
- If you have only one data in your excel sheet, you can click on refresh, but if you have more than one data in the sheet, you will click on refresh all.
- You can do this refreshing automatically by setting up a time for refreshing data saved in the excel sheet. For this, you will have to click on the connection properties option. The dialog box will appear on your screen.
- Name the connection, add a description for the connection and enter a command under refresh every 120 minutes or more.
- Save the command, and your data will get refreshed automatically after that time.
The final word
If you are doing a data analyst job, you would understand how long it would take to extract data line by line from the website to your excel sheet. That is why we have discussed a much simpler and time-saving way of automatically collecting data from a website to your excel sheet above.