In this blog post I will be implementing a simple web scraping using Power Automate Desktop (RPA) tool. There are some use cases when you will need to scrape or extract data from the website for analytics purposes. For this post, say you have a requirement to know whether the US stock market is overvalued or not. One of the factors to determine if the market is overvalued is to calculate the P/E ratio (Price to Earnings). Higher P/E ratio means market is probably overvalued and lower P/E ratio means it’s undervalued. Historically, in past 25 years the P/E ratio has been around 25. In US stock market, the S&P 500 are the top 500 companies in the US that pretty much determine the valuation of the whole market so you can safely assume determining P/E ratio for these companies would give you a good picture about the overall valuation. In order to get S&P 500’s yearly P/E ratios we will be using this website.
Assumptions
- You have basic understanding of Power Automate Desktop.
- You have installed Power Automate Desktop .
- Installed and configured Power Automate browser extension.
1. Open Power Automate Desktop and click “New Flow”
2. Add output variable to store the HTML table
If you want to add further automation like going through different pages then you can add input variable. The variable will contain values specific to the pages so when you launch the website the input variable value will determine which page to direct.
3. Under Web automation actions go to “Web form filling” and select “Launch new Chrome”.
If you prefer some other browser then feel free to select their launch action. Configure the Launch new Chrome action as shown below.
Make sure you have enabled “Wait for page to load” setting.
4. Extract data from the web page
When you open the website that you want to scrape a live web helper will popup. You will need to select some sample values from the website for the helper to understand and scrape the content that you wish extract. In our example, I selected few date and values from the table and the live web helper automatically extracted the rest of the values.
5. Set variable and close browser
6. Storing data in excel
You can store this data in any format but in this example I will be storing data in excel file. For simplicity, I have already created an Excel file with column names. In this example, I will be launching an existing excel to write the data in it and then close the excel.
Once you have data stored in excel, it can be used for ETL and analytics purposes. You can also use Power Automate Desktop flow in Power Automate cloud portal.
Conclusion
Power Automate provides web scraping functionality with little to no coding. You can build scrapper quickly to confirm if the data is of some value. In this post, we have covered a very simple web scraping example but it can be also used for complex online websites like Amazon, eBay, Dan Murphy, etc. Overall, looking at the P/E ratio of S&P 500 which is around 35, it does look like the market is overvalued and due for some correction. Again, I am not a stock broker or an expert but P/E ratio is considered one of the most important factor in determining a stock value. In our next example we will pick our regular online shopping websites.
Very good post. I absolutely appreciate this site. Continue the good work!