How to export web data to Excel

Exporting data from a web page into Excel can be a bit of a challenge. Sometime Copy and Paste will work but most of the time, if you want something complex, it doesn't. WebGet gives you more control over this process. Why not sign up for a free account and give it a try?

In this tutorial we will show you how to export a table inside a web page to Excel. These steps are replicated in the video above.

To use WebGet you will need to sign up for a free account. After doing this and logging in, click on the Record New Task button to start extracting data. Record new task button In this example we are going to extract stock price information from Yahoo Finance. So, for the Starting page, type and click Start: Start export web data to excel macro After the page has loaded click in the search bar and type in some stock ticker codes. For this example I am going to use the following: "EBAY, AMZN, AAPL, GOOG, IBM, MSFT, AXP, NFLX". Input stock codes Next, click on the magnifying glass next to the search bar and select Click: Click search The Click operation will show up in the action panel on the right. Type "Search" into the Name field and press the Click button: Click action Now we are going to extract the table of stocks. Click somewhere in the table and select Export Table (the Export Table option will be enabled in the popup menu because we are clicking on a web table). If your data isn't a web table you can still extract it using Loop and Extract actions. Extract table Now, in the table action panel to the right, enter a name for the data in the table. It is best to use a singular name here (not plural): Set table name By default WebGet will extract everything it finds in the table. Now we will clean it up and remove a few columns. Click on the Next Field button once. The "Last Price" field should display. Next column button Change the Data Type of this field to Currency. This is so it exports to Excel in the correct format. Data type Now click on the Next Field button: Next field button The "Change" field should now be selected. Lets remove the + symbol so that it exports to Excel correctly. Click on the "show text manipulation settings" link: Show text manipulation settings Then type the + sybmol into the Remove Characters field: Remove characters Now change the Data Type to Decimal Number: Data type Click on the Next Field button again: Next field button Now the "% Chg" field should be showing. Lets remove the + and % characters from this field and set its Data Type to Decimal Number too: Percent change field Now we are going to remove a few fields. Click on the Next Field button: Next field button This should select the Currency field. Lets delete this field by clicking the Remove button: Remove column The field will be removed. Continue this process and remove all fields except Volume and Market Cap.

Now click the Extract Table button: Extract table button That's it for the recording. Click Finish & Save: Finish and save button Give the recording a name and then click Save & Run: Save export data to Excel task After the task finishes running you will see the extracted data. Click on the Download Excel button. Export to Excel button The Excel download will appear in the bottom left hand corner of the browser. Click on it: Excel file download This will open the data in Excel: Web data in Excel You can also export multiple runs to Excel. Lets run the task again by clicking the Run Again button: Run again button After that run finishes click on the History tab: Run history tab You should have one item in the "Since" drop down list box. Select this item and click Show: Show run history button Now you will see the data for two runs. You can select more than two runs of data by changing the "Since" value. Web data history This data can also be exported to Excel by clicking the Download Excel button and then opening the file.

Now lets schedule this task to run at the end of the day and email us the Excel file. Click on the Schedule button: Schedule button In the Output section next to Send Email, tick "After every run" and leave the Email Attachment as Excel: Send email option Then go down to the Schedule section and set the Frequency to Daily, untick Saturday and Sunday and set the Time of Day: Schedule task Then click the Save button: Save button That completes this tutorial. You will now receive your portfolio of stocks emailed to you in Excel format at the end of each trading day.