Scraping Keywords – And – 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:Visual Basic12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485Option Explicit'This code is from https://officetricks.com'Contact firstname.lastname@example.org for purchasing extended code for SEO and other purposesPublic Sub Keyword_Scraping_Excel_SEO()Dim iRow As Double, iCol As Double, j As IntegerDim Web_URL As String, oHTMLFile As Object, hText As HTMLDocumentDim wTag As Object, dword As String'Replace the URL of the webpage that you want to downloadWeb_URL = VBA.Trim(ThisWorkbook.Sheets("Input").Cells(1, 2))'Create HTMLFile ObjectSet oHTMLFile = CreateObject("htmlfile")'Get the WebPage Content to HTMLFile ObjectWith CreateObject("msxml2.xmlhttp").Open "GET", Web_URL, False.sendoHTMLFile.body.Innerhtml = .responseTextEnd WithSet hText = oHTMLFileThisWorkbook.Sheets("Links_Scripts").ActivateThisWorkbook.Sheets("Links_Scripts").Cells.ClearContentsDim tagIdx As Integer, wTxt As StringiCol = 0'Get H1, H2, H3, H4, H5, H6For tagIdx = 1 To 6iRow = 0For Each wTag In hText.getElementsByTagName("H" & tagIdx)If iRow = 0 TheniCol = iCol + 1iRow = iRow + 1ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "H" & tagIdxEnd IfwTxt = wTag.outerTextIf VBA.Trim(wTxt) <> "" TheniRow = iRow + 1ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = wTag.outerTextEnd IfNextNext'Get Bold ContentsiRow = 0For Each wTag In hText.getElementsByTagName("B")If iRow = 0 TheniRow = iRow + 1iCol = iCol + 1ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "Bold Text"End IfwTxt = wTag.outerTextIf VBA.Trim(wTxt) <> "" TheniRow = iRow + 1ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = wTag.outerTextEnd IfNext'Get HREF Outlink URLs from WebpageiRow = 1For tagIdx = 1 To hText.Links.LengthIf iRow = 1 TheniCol = iCol + 1ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "Links"End IfiRow = iRow + 1ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = hText.Links.Item(tagIdx - 1).hrefNext'Get Scripts present in WebpageiRow = 1For tagIdx = 1 To hText.Scripts.LengthIf iRow = 1 TheniCol = iCol + 1ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = "Scripts"End IfiRow = iRow + 1ThisWorkbook.Sheets("Links_Scripts").Cells(iRow, iCol) = hText.Scripts.Item(tagIdx - 1).outerHTMLNextEnd 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 392 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.
Previous Post: WhatsApp Activate Voice Call Feature
Next Post: Top 4 Whatsapp Tips and Tricks You Didn’t know Existed
How To Scrap A Website Keywords? – Keyword Scraping Excel