Web Scraping & Keywords Analysis Tool – SEO
Get the Excel with VBA code for Scraping Keywords from Website to help in your SEO tasks.
Search Engine Optimization (SEO) is the process of making web page content understandable to search robots. Then only, they can realize the purpose of our website and index our pages in a better position. But these days, it has become more of scraping keywords optimization or tweaking to bring websites in the 1st place of SERP (Search Engine Result Page).
Below topics mainly explains basic SEO tips on how to place your keywords in your content that will improve its meaning to Humans & also to Computers.
Keyword Scraping Excel – VBA Web Scraping
In one of my earlier articles, it is explained how to download webpage content with VBA. We are going to re-use that code here in the Keyword scraping Excel App, download content, parse the HTML tags and present the website data analytic.
- Open a New Excel workbook.
- Press Alt + F11 to view VB editor, Copy paste below code there.
- In the first sheet, Cell B1, Type a valid Website URL.
- Then, execute the below code by pressing F5 in Excel VBE.
Note: Free code is available only for link & scripts extracting. To analyze keywords & for good search engine optimization solutions:
Option Explicit 'This code is from http://officetricks.com 'Contact email@example.com for purchasing extended code for SEO and other purposes Public Sub Keyword_Scraping_Excel_SEO() Dim iRow As Double, iCol As Double, j As Integer Dim Web_URL As String, oHTMLFile As Object, hText As HTMLDocument Dim wTag As Object, dword As String 'Replace the URL of the webpage that you want to download Web_URL = VBA.Trim(ThisWorkbook.Sheets("Input").Cells(1, 2)) 'Create HTMLFile Object Set oHTMLFile = CreateObject("htmlfile") 'Get the WebPage Content to HTMLFile Object With CreateObject("msxml2.xmlhttp") .Open "GET", Web_URL, False .send oHTMLFile.body.Innerhtml = .responseText End With Set hText = oHTMLFile ThisWorkbook.Sheets("Links_Scripts").Activate ThisWorkbook.Sheets("Links_Scripts").Cells.ClearContents Dim tagIdx As Integer, wTxt As String iCol = 0 'Get H1, H2, H3, H4, H5, H6 For tagIdx = 1 To 6 iRow = 0 For Each wTag In hText.getElementsByTagName("H" & tagIdx) If iRow = 0 Then iCol = iCol + 1 iRow = iRow + 1 ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "H" & tagIdx End If wTxt = wTag.outerText If VBA.Trim(wTxt) <> "" Then iRow = iRow + 1 ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = wTag.outerText End If Next Next 'Get Bold Contents iRow = 0 For Each wTag In hText.getElementsByTagName("B") If iRow = 0 Then iRow = iRow + 1 iCol = iCol + 1 ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "Bold Text" End If wTxt = wTag.outerText If VBA.Trim(wTxt) <> "" Then iRow = iRow + 1 ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = wTag.outerText End If Next 'Get HREF Outlink URLs from Webpage iRow = 1 For tagIdx = 1 To hText.Links.Length If iRow = 1 Then iCol = iCol + 1 ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "Links" End If iRow = iRow + 1 ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = hText.Links.Item(tagIdx - 1).href Next 'Get Scripts present in Webpage iRow = 1 For tagIdx = 1 To hText.Scripts.Length If iRow = 1 Then iCol = iCol + 1 ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "Scripts" End If iRow = iRow + 1 ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = hText.Scripts.Item(tagIdx - 1).outerHTML Next End Sub
Once start execution, code fetch data from website using the “msxml2.xmlhttp” functionality and put the content in ‘htmlfile’ object. With the function ‘getElementsByTagName’ the HTML tags can be parsed and the data embedded in those tags can be extracted. Similarly, the above code will also fetch “href” links to other web pages and scripts running in that page.
HTML Tags – Search Engine Optimization(SEO) Solutions
Though Computers can understand only keywords, Experts in Google, BING and other Search Engine designers are making intelligent systems that can rank a webpage based on its true purpose and just not only with too many searchable keywords by scraping keywords.
The worst part is, if we attempt to populate too many computer friendly keywords, then it is more likely that people who actually read your content might end up disliking it. Search Engines these days consider the user satisfaction level also in their ranking logic. To succeed, a publisher has to balance both (Computer Vs Human).
Web And Keyword Scrape for SEO
If there are only few pages, then the above points can be verified manually. What if there are 50 or 100 or even more pages? Use few available SEO page testing tools or develop your own tool with VBA web scraping explained below.
Sometimes SEO plugins (of course, in free editions) might pose few limitations (like only 5 Articles for SEO Review per month, etc.,), creating our own tool will come as handy to overcome these situations and we can also do the required customization as per our need.
This VBA code will make Excel retrieve data from website (scraping keywords from Web), parse the HTML tags in it and list down the below mentioned web analytic data points.
- Headers (H1, H2, and so on till H6)
- Text that are Bold
- HREF link to Internal/External web pages
- Scripts present in the page
- Occurrences of Keywords in Content
This is something that every Blogger has to verify in any of his article to ensure that webpage SEO task is complete.
Tools for SEO: Web Scraping VBA Code
If you are preparing a blog/article, you would be having a few keywords from Google Keyword Planner that are related to the keywords. For example, consider this post. which explains about a messenger option.
By reading the header of this article, reader and computer, both should understand -what the topic is all about. Always prepare a list of keywords about what you are going to explain; then prepare content with clear explanations. In my topic I chose ‘Whatsapp’ & ‘Voice Call Feature’ as focus keywords.
If these keywords appear in any of the headers and my content explains concepts with these words, then search engines will index my page for those words. That means it assumes that My page explains something about Whatsapp and its features, so bookmark these pages and display in search result if someone searches for these words.
Download Keyword Scraping Excel Downloaded 559 Times
So, to improve the importance of these words, put them in Headers and bold it in appropriate places inside the content. That’s it. You now know how to do a basic level of SEO for a website. But, take much care about number of times you are repeating the keywords, if it occurs too often, then Search engines will mark it as spam and your page rank will go down.
How to Scrap a Website Keywords for SEO?
We can now verify whether our topic has proper headers, important keywords are placed in proper tags and repeated appropriately, all links going out of webpage are not spam and any scripts are making unwanted execution.
This tool can report you how many times the keyword is repeated, but not whether it is valid or not, because every search engine has its own criteria on this allowed number of repetition for a keyword. Try a research on that and modify your article accordingly.
It is possible that most of us use many plug-in for our WordPress blogs and few might be inserting links to refer the developer websites. With the above code, we will get to know what are the external links out webpage is referring to and we take appropriate action based on the results.